Exporting Group-Objects by entities to CSV

[Syncr0s]

Hi All,

I know that my query really to PS related in general, but since what I am trying to do is centred on the vSphere 4.1 environment someone might have done it before.

I love LucD's script to gain metrics of my VMs in terms of disk, cpu and memory, like http://communities.vmware.com/message/2039937, http://www.lucd.info/2011/07/08/powercli-vsphere-statistics-part-5-rollup-types/ and http://www.lucd.info/2010/01/05/powercli-vsphere-statistics-part-2-come-together/. I am wanting to be able to export the information to a CSV for each VM, and have tried two different methods for getting this to work.

I tried changing the location in the script where the export occurs, and putting the variable for the name in the export file name, but even though the redefined variable is correct (I checked it via a write-host immediately prior to the export-csv) it doesn't pick up the name and continually overwrites the file with the next machines data.

I tried to change the script using a ForEach loop operating for each machine in the array defined by entities, and running the report against each entity and then using a select statement to get the information I need. Again I tried using the name defined by $_.Group[0].Entity.Name in the select, in the export-csv command. Again it seems to ignore the definition for the VM name and overwrites the data within the same file.

Has anyone tried what I am attempting to know if it is possible?

Geoff

[LucD]

Hi Geoff, could you perhaps attach the script you are using ?

That would make it easier to see where it might went wrong.

[Syncr0s]

Hi Luc,

I am using the attached script which is your one from your blog with timings changed slightly.

As for my attempts at getting the export per machine, please don't ask, as I've butchered it now and didn't version track.

One copy of the script had in it the below, but this exported information which was not part of the filtered report (do I just need to do a select instead of sort-object?).

ForEach ($entity in $entities){

    $report = Get-Stat -Entity $entity -Stat $metrics -start $Morning -Finish $Night -IntervalMins 5  | where { $_.value -ne "-0.01" } 

    $report | Group-Object -Property EntityId,Timestamp | %{

        New-Object PSObject -Property @{

        Name = $_.Group[0].Entity.Name

        Time = $_.Group[0].Timestamp

        CpuAvg = ($_.Group | where {$_.MetricId -eq "cpu.usage.average"}).Value

        MemAvg = ($_.Group | where {$_.MetricId -eq "mem.usage.average"}).Value

        }

    $report | Sort-Object Time | Export-Csv ".\$entity_$Datefile.csv" -NoTypeInformation -UseCulture

    }

}

The version I had where I changed the method I cannot find a working copy, unfortunately, or maybe fortunately.

Geoff

[LucD · Accepted solution]

Split the grouping in 2 (1st the VM, then the timestamp).

Something like this

If ((Get-PSSnapin "VMware.VimAutomation.Core" -ErrorAction SilentlyContinue) -eq $null) {

    Add-PSSnapin "VMware.VimAutomation.Core"

}

$Datefile = ( get-date ).ToString(‘yyyy-MM-dd-hhmmss’)

$Morning = (get-date -Hour 7 -Minute 0 -Second 0).AddMinutes(-1)

$Night = (get-date -Hour 18 -Minute 0 -Second 0).AddMinutes(-1)

$metrics = ("cpu.usage.average","mem.usage.average")

$entities = Get-VM |where {$_.PowerState -eq "PoweredOn"}

Get-Stat -Entity $entities -Stat $metrics -start $Morning -Finish $Night -IntervalMins 5 |

where { $_.value -ne "-0.01"} |

Group-Object -Property {$_.Entity.Name} | %{

    $vmName = $_.Name

    $report = $_.Group | Group-Object -Property Timestamp | %{

        New-Object PSObject -Property @{

            Name = $vmName

            Time = $_.Group[0].Timestamp

            CpuAvg = ($_.Group | where {$_.MetricId -eq "cpu.usage.average"}).Value

            MemAvg = ($_.Group | where {$_.MetricId -eq "mem.usage.average"}).Value

        }

        $report | Sort-Object Time | Export-Csv ".\$($vmName).csv" -NoTypeInformation -UseCulture

    }

}

[Syncr0s]

LucD wrote:

Split the grouping in 2 (1st the VM, then the timestamp).

Something like this

        $report | Sort-Object Time | Export-Csv ".\$($vmName).csv" -NoTypeInformation -UseCulture

    }

}

Hi Luc,

It indeed works, and I note the loop within a loop which countered my ForEach. I didn't think about defining the name higher up the order.

The bit I don't understand is the "encapsulation" of the $vmName in parentheses preceded by the additional $. Is there some information which you can point me at which explains why this is required?

Geoff

edit: Damn i was too early.... it seems that the name of the file is not the data which is contained in it. The file name contains the data of the VM immediately preceding it, ie 25146.csv contains the data of VM 25145.

Message was edited by: Syncr0s

[LucD]

Strange, I can not reproduce that behaviour.

Does it change when you replace

$vmName = $_.Name

with

$vmName = $_.Group[0].Entity.Name

The $($var) notation is not really necessary in this case. You can use

$report | Sort-Object Time | Export-Csv ".\$vmName.csv" -NoTypeInformation -UseCulture

as well. I took the habit of using this notation when using variables in strings.

That notation is used when you reference a property of a variable

"The property is $($var.property)"

Or when doing a calculation

"The value is $($var + 1)"

[Syncr0s]

LucD wrote:

Strange, I can not reproduce that behaviour.

Does it change when you replace

$vmName = $_.Name

with

$vmName = $_.Group[0].Entity.Name

No I still get the same thing.

The first file is empty, and the second file contains the first server's data so and and so forth.

This is using PowerCLI 4.1 for Virtual Centre 4.1.0.17435.

Geoff

[LucD]

Found it, the line doing the epxort to the CSV was in the wrong place.

Try it like this

	If ((Get-PSSnapin "VMware.VimAutomation.Core" -ErrorAction SilentlyContinue) -eq $null) {
	Add-PSSnapin "VMware.VimAutomation.Core"
	}

$Datefile = ( get-date ).ToString(‘yyyy-MM-dd-hhmmss’)

$Morning = (get-date -Hour 7 -Minute 0 -Second 0).AddMinutes(-1)

$Night = (get-date -Hour 18 -Minute 0 -Second 0).AddMinutes(-1)

$metrics = ("cpu.usage.average","mem.usage.average")

$entities = Get-VM |where {$_.PowerState -eq "PoweredOn"}

Get-Stat -Entity $entities -Stat $metrics -start $Morning -Finish $Night -IntervalMins 5 |

where { $_.value -ne "-0.01"} |

Group-Object -Property {$_.Entity.Name} | %{

    $vmName = $_.Name

    $report = $_.Group | Group-Object -Property Timestamp | %{

	New-Object PSObject -Property @{
		Name = $vmName
		Time = $_.Group[0].Timestamp
		CpuAvg = ($_.Group | where {$_.MetricId -eq "cpu.usage.average"}).Value
		MemAvg = ($_.Group | where {$_.MetricId -eq "mem.usage.average"}).Value
	}

    }

  $report | Sort-Object Time | Export-Csv ".\$($vmName).csv" -NoTypeInformation -UseCulture

}

[Syncr0s]

Copy and paste is terrible as the clipboard always messes things up. It's like Mike Tv on Charlie and the Chocolate Factory isn't it?

That one worked and treat, and I am sure many people will thank you, especially for later versions of PowerCLI which support PowerShell 2, so they can use the -append to add the data to existing files rather than a new file for each time the script is run. Yes I know about reading in the existing file as an array, adding to it, and then writing it back out, but there is something to be said for vendors enabling the functionality so there is standardised implementations and commands.

Geoff