add data into newly created column in csv

Welcome Forums General PowerShell Q&A add data into newly created column in csv

This topic contains 11 replies, has 4 voices, and was last updated by

 
Participant
2 years, 2 months ago.

  • Author
    Posts
  • #55706

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 438
    Helping Hand
    Rank: Contributor

    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

    Participant
    Points: 16
    Rank: Member
    $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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    Just modify last line:

    $results | Export-csv -Path B:\scripts\GetHostStats\Stats.csv -NoTypeInformation
    
    • #55811

      Participant
      Points: 0
      Rank: Member

      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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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

    Participant
    Points: 0
    Rank: Member

    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}

The topic ‘add data into newly created column in csv’ is closed to new replies.