add data into newly created column in csv

This topic contains 11 replies, has 4 voices, and was last updated by Profile photo of mark mark 1 month, 2 weeks ago.

  • Author
    Posts
  • #55706
    Profile photo of mark
    mark
    Participant

    I promise I've banged my head on this one for a good while, but just not able to get around it.

    $vms = get-cluster N-MMK-COOP-151-166 | Get-VM

    foreach ($vm in $vms) {
    get-stat -Entity $vm -Start 9/27/2016 -Finish 10/18/2016 -CPU | Select Value | export-csv -Path B:\scripts\GetHostStats\$vm.csv
    }

    The above creates a csv file for each VM. I'd like to have the data for each VM just be a new column in the same csv file. Thanks for any thoughts!

  • #55711
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    To capture the output of the loop, you assign a variable to it:

    $vms = Get-Cluster N-MMK-COOP-151-166 | Get-VM
    
    $results = foreach ($vm in $vms) {
       Get-Stat -Entity $vm -Start 9/27/2016 -Finish 10/18/2016 -CPU | Select Value 
    }
    
    $results | Export-Csv -Path B:\scripts\GetHostStats\Stats.csv -NoTypeInformation
    

    However, per the documentation on Get-Stat, you can pass VM's ByValue for Entity. You should be able to do something like this:

    $results = Get-Cluster N-MMK-COOP-151-166 | 
    Get-VM |
    Get-Stat -Start 9/27/2016 -Finish 10/18/2016 -CPU | 
    Select Value 
    
    $results | export-csv -Path B:\scripts\GetHostStats\Stats.csv -NoTypeInformation
    
  • #55714
    Profile photo of random commandline
    random commandline
    Participant
    $vms = get-cluster N-MMK-COOP-151-166 | Get-VM
    
    $values = foreach ($vm in $vms) {get-stat -Entity $vm -Start 9/27/2016 -Finish 10/18/2016 -CPU | Select Value}
    $values | export-csv -Path B:\scripts\GetHostStats\$vm.csv
    
  • #55745
    Profile photo of mark
    mark
    Participant

    Thank you for the answers. These do place all the results into one csv file, but the results are appended to the same/first column. I'm trying to place each new set of results into a new column within the same csv.

  • #55768
    Profile photo of mohit goyal
    mohit goyal
    Participant

    This is an odd method of storing output. However I'm sure you must have some reason behind it. I could not test below code (due to lack of environment) but you should be able to use something like this:

    $vms = Get-Cluster N-MMK-COOP-151-166 | Get-VM
    
    $results = New-Object -TypeName PSObject
    
    foreach ($vm in $vms) {
       $Value = Get-Stat -Entity $vm -Start 9/27/2016 -Finish 10/18/2016 -CPU | Select Value 
       $results | Add-Member -MemberType NoteProperty -Name $vm.name -Value $Value
    }
    
    $results | Export-csv -Path B:\scripts\GetHostStats\Stats.csv
    
  • #55784
    Profile photo of mark
    mark
    Participant

    thanks, for the time on this. I'd be open to different storage methods. your code cycles through successfully, but the csv has this data. It does nicely in placing each VM into it's own column, but wondering why the $Value field is not picked up.

    EI0001VAPP FCADBSMMK1 VROBUSTENGQA1
    System.Object[] System.Object[] System.Object[]

    Here's how it comes back when run straight from powercli//powershell

    PowerCLI B:\scripts\GetHostStats\merge> Get-Stat -Entity FCADBSMMK1 -Start 9/27/2016 -Finish 10/18/2016 -CPU | Select Value

    Value
    —–
    2.62
    2.71
    2.5
    2.61
    2.47
    2.45
    2.46
    5.42
    2.93

  • #55801
    Profile photo of mohit goyal
    mohit goyal
    Participant

    Just modify last line:

    $results | Export-csv -Path B:\scripts\GetHostStats\Stats.csv -NoTypeInformation
    
    • #55811
      Profile photo of mark
      mark
      Participant

      hmm. i'm missing something. this is still producing the same results:

      $vms = Get-Cluster L-WLK-FSLab-2 | Get-VM

      $results = New-Object -TypeName PSObject

      foreach ($vm in $vms) {
      $Value = Get-Stat -Entity $vm -Start 9/27/2016 -Finish 10/18/2016 -CPU | Select Value
      $results | Add-Member -MemberType NoteProperty -Name $vm.name -Value $Value
      }

      $results | Export-csv -NoTypeInformation -Path B:\scripts\GetHostStats\merge\Stats.csv

  • #55814
    Profile photo of mark
    mark
    Participant

    when I add in a write-host I do get values. not sure why it's failing to come through with the export-csv.

    EIWLKHTLVM001
    @{Value=0.74} @{Value=0.66} @{Value=0.5} @{Value=0.36} @{Value=0.28} @{Value=0.24} @{Value=1.6} @{Value=155} @{Value=137} @{Value=105} @{Value=75} @{Value=59} @{Value=50} @{Value=333}

    foreach ($vm in $vms) {
    $Value = Get-Stat -Entity $vm -Start 9/27/2016 -Finish 10/18/2016 -CPU | Select Value
    $results | Add-Member -MemberType NoteProperty -Name $vm.name -Value $Value
    write-host $vm.name
    write-host $Value
    }

  • #55816
    Profile photo of mark
    mark
    Participant

    here's a write-host though for $results

    PowerCLI B:\scripts\GetHostStats\merge> .\8merge.ps1
    @{BIGIPTEST=}
    @{BIGIPTEST=; EIWLKHTLVM009=System.Object[]}
    @{BIGIPTEST=; EIWLKHTLVM009=System.Object[]; EIWLKHTLVM001=System.Object[]}

  • #55817
    Profile photo of mark
    mark
    Participant

    This will give me the values, but running into issues trying to get the values into an array and then exporting them into a csv file.

    $vms = Get-Cluster L-WLK-FSLab-2 | Get-VM

    foreach ($vm in $vms) {
    $values = Get-Stat -Entity $vm -Start 10/23/2016 -Finish 10/23/2016 -CPU | Select Name, @{Name=$vm.Name;Expression={[string]::join(“;”, ($_.Value))}}
    }

  • #55819
    Profile photo of mark
    mark
    Participant

    PowerCLI B:\scripts\GetHostStats\merge> .\9merge.ps1
    @{Name=; BIGIPTEST=6.09} @{Name=; BIGIPTEST=316}
    @{Name=; EIWLKHTLVM009=4.39} @{Name=; EIWLKHTLVM009=914}
    @{Name=; EIWLKHTLVM001=0.8} @{Name=; EIWLKHTLVM001=168}

You must be logged in to reply to this topic.