Hello,
I'm collecting various statistics from my ESXi Hosts / VMs and output them to .csv. Those files are getting then indexed, so that different live/saved reports can be generated.
All the scripts I found/modified so far are working providing a pretty standard output.
Only those VM Disk Stats are giving me headaches.
To make the example easy, I'll run the script on 1 VM with 1 metric.
This code is working (from LucD)...
$metrics = "disk.numberwrite.summation"
$vms = get-vm -Name MyVM | where {$_.PowerState -eq "PoweredOn"}
Get-Stat -Entity $vms -Stat $metrics -maxsamples 1 -IntervalSecs 20 |
Group-Object -Property EntityId | Foreach-Object{
New-Object PSObject -Property @{
VM = $_.Group[0].Entity.Name
Instance = $_.Group[0].Instance
MetricId = $_.Group[0].MetricId
Value = $_.Group[0].Value
}
} | Select VM,Instance,MetricId,Value |
Export-Csv "C:\report.csv" -NoTypeInformation
... but with an output of instances and values per columns (which is an obstacle, when trying to index the resulted .csv)
Entity Instance MetricId Value
MyVM xyz.12345e8069872a000000963d00008015 disk.numberwrite.summation 7
MyVM xyz.12345e8069872a000000963d00008012 disk.numberwrite.summation 0
Now, I modify the script as followed and....
$metrics = "disk.numberwrite.summation"
$vms = get-vm -Name MyVM| where {$_.PowerState -eq "PoweredOn"}
Get-Stat -Entity $vms -Stat $metrics -maxsamples 1 -IntervalSecs 20 |
Group-Object -Property EntityId | Foreach-Object{
New-Object PSObject -Property @{
VM = $_.Group[0].Entity.Name
Instance1 = $_.Group[0].Instance
Instance2 = $_.Group[1].Instance
DiskNumberWrite1 = $_.Group[0].Value
DiskNumberWrite2 = $_.Group[1].Value
}
} | Select VM,Instance1,Instance2,DiskNumberWrite1,DiskNumberWrite2 |
Export-Csv "C:\report1.csv" -NoTypeInformation
.. I can get the desired output, with instances and values per line with the VM Name as "primary key" so to say:
Entity Instance1 Instance2 DiskNumberWrite1 DiskNumberWrite2
MyVM xyz.12345e8069872a000000963d00008015 xyz.12345e8069872a000000963d00008012 7 0
My question:
The example is fine but in real life, we have VMs with 2 to 10 instances, how can we get and output the Instance[x], Value[x] as variables ?
With twice the word Power in PowerShell und PowerCli, I'm sure it's possible... Any hint would be greatly appreciated.
Try this version
$metrics = "disk.numberread.summation","disk.numberwrite.summation",
"disk.read.average","disk.write.average"
$vms = Get-VM | where {$_.PowerState -eq "PoweredOn"}
Get-Stat -Entity $vms -Stat $metrics -maxsamples 1 -IntervalSecs 20 |
Group-Object -Property EntityId | Foreach-Object{
$row = [ordered]@{
VM = $_.Group[0].Entity.Name
}
$i = 1
$_.Group | where {$_.Instance -ne ""} | Group-Object -Property Instance | %{
$row.Add("Instance$i",$_.Values[0])
foreach($metric in $metrics){
$metricValue = $_.Group | where {$_.MetricId -eq $metric} | Select -ExpandProperty Value
$row.Add("$($metric.Split('.')[1])$i",$metricValue)
}
$i++
}
New-Object PSObject -Property $row
} | Sort-Object -Property {$_ | Get-Member -MemberType NoteProperty | Measure-Object | Select -ExpandProperty Count} -Descending |
Export-Csv "C:\report1.csv" -NoTypeInformation -UseCulture
It uses the order in whcih you specified the counters in the $metrics variable.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Try something like this
$metrics = "disk.numberwrite.summation"
$vms = Get-VM | where {$_.PowerState -eq "PoweredOn"}
$report = Get-Stat -Entity $vms -Stat $metrics -maxsamples 1 -IntervalSecs 20 |
Group-Object -Property EntityId | Foreach-Object{
$row = New-Object PSObject -Property @{
VM = $_.Group[0].Entity.Name
}
$i = 1
$_.Group | Group-Object -Property Instance | %{
$row | Add-Member -Name "Instance$i" -Value $_.Group[0].Instance -MemberType NoteProperty -PassThru |
Add-Member -Name "Value$i" -Value $_.Group[0].Value -MemberType NoteProperty
$i++
}
$row
}
$report | Sort-Object -Property {$_ | Get-Member | Measure-Object | select -ExpandProperty Count} -Descending |
Export-Csv "C:\report1.csv" -NoTypeInformation -UseCulture
It will add a property per instance.
At the end it needs to sort descending on the number of properties in a row.
This to avoid a problem with the Export-Csv cmdlet, that takes the first line to determine how many columns there are in the CSV
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thanks, I tried but can't come around the following error:
ForEach-Object : Cannot bind parameter 'Process'. Cannot convert the "@{VM=MyVM}" value of type "System.Management.Automation.PSCustomObject" to type "System.Management.Automation.ScriptBlock".
At C:\MyScript.ps1:6 char:48
+ $_.Group | Group-Object -Property Instance | % <<<< {
+ CategoryInfo : InvalidArgument: (:) [ForEach-Object], ParameterBindingException
+ FullyQualifiedErrorId : CannotConvertArgumentNoMessage,Microsoft.PowerShell.Commands.ForEachObjectCommand
From where and how are you running the script ?
The PowerCLI prompt, a GUI....
And which PowerShell version are you running this in ?
Do a $psversiontable.
Btw I discovered a flaw in my original script, I just updated the code above.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
I tried running the script both:
- in a Poweshell command prompt, where I ran the PowerCLI initialize script
- in a PowerCLI prompt
I manually connect to one of my ESXi Host, and then call the script like, .\TheScript.ps1
Here's the PS version:
Name | Value |
---- | ----- |
CLRVersion | 2.0.50727.5466 |
BuildVersion | 6.1.7601.17514 |
PSVersion | 2.0 |
WSManStackVersion | 2.0 |
PSCompatibleVersions | {1.0, 2.0} |
SerializationVersion | 1.1.0.1 |
PSRemotingProtocolVersion | 2.1 |
PowerCLI version = VMware vSphere PowerCLI 5.1 Release 2 build 1012425
Yes, I guess you added a way to increment, right ? The same error still comes then for each VM.
Can you attach the script as you are using it ?
There might have gone something wrong during the copy/paste.
I just ran the script again, and I can't reproduce the error you are getting.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
It looks as if a number <CR><LF> were lost during the copy/paste.
That is a known issue with the Jive forum SW and some older IE versions.
In any case try my attached version
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
You guessed right, the attached script is running flawlessly, and the output is great.
I'll have to add more metrics, does it involve drastic changes in this script ?
I guess it would involve another Group-Object block to distinguish between the different metrics.
What do you want to add ?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Well ideally, I'd like to add the following metrics:
"disk.numberread.summation","disk.read.average","disk.write.average"
So that I can get IOPS and Throughput for each Instance/VM.
Try this new version
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thanks for the feedback.
At first it looked fine, but then I realized the column sorting is random, for example this is the output from 2 different ESXi Host:
HostA
VM Instance1 write1 numberwrite1 numberread1 read1 Instance2 write2 numberwrite2 numberread2 read2
HostB
VM Instance1 numberwrite1 write1 read1 numberread1 Instance2 numberwrite2 write2 read2
Can we sort the columns with a recurrent pattern ? Then I can parse the results using regex later on for field extraction.
The property order is not garantueed, unless you use the [ordered] tag that was introduced in PowerShell v3.
See my Export-Xlsx, the sequel, and ordered data post.
An example how this can be done.
$metrics = "disk.numberwrite.summation","disk.numberread.summation","disk.read.average","disk.write.average"
$vms = Get-VM | where {$_.PowerState -eq "PoweredOn"}
Get-Stat -Entity $vms -Stat $metrics -maxsamples 1 -IntervalSecs 20 |
Group-Object -Property EntityId | Foreach-Object{
$row = [ordered]@{
VM = $_.Group[0].Entity.Name
}
$i = 1
$_.Group | where {$_.Instance -ne ""} | Group-Object -Property Instance | %{
$row.Add("Instance$i",$_.Values[0])
$_.Group | Group-Object -Property MetricId | %{
$row.Add("$($_.Values[0].Split('.')[1])$i",$_.Group[0].Value)
}
$i++
}
New-Object PSObject -Property $row
} | Sort-Object -Property {$_ | Get-Member -MemberType NoteProperty | Measure-Object | Select -ExpandProperty Count} -Descending |
Export-Csv "C:\report1.csv" -NoTypeInformation -UseCulture
The alternative is to use a Select-Object where you specify the properties in the order you want them before the Export-Csv.
But that would require that you know the names of all the properties, which is not obvious since we added them dynamically.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
I went for the v3 upgrade alternative.
I read about the [ordered] tag which should enable outputs in the same order as the input. Could it be that we need this tag at an additional position in the script ?
I ran the new version (with [ordered]) and the old version (without [ordered]) and compared the outputs.
The output order didn't change. Both scripts still ouput with the same random pattern:
VM Instance1 write1 numberwrite1 numberread1 read1 Instance2 write2 numberwrite2 numberread2 read2 Instance3 numberread3 numberwrite3 read3 write3
Afaik that is no random order in the new version, the order that you see now is the order in which the properties are added to the rows (instance first, folllowed by the 4 metrics).
If you want a different order, the script will need to be changed.
In fact these lines will need to be changed
$_.Group | Group-Object -Property MetricId | %{
$row.Add("$($_.Values[0].Split('.')[1])$i",$_.Group[0].Value)
}
But you should be seeing this same order every time when you run the script, unless the Get-Stat changes the order in which the metrics are returned.
How did you want to see the columns ?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
The general order -instance first, followed by the 4 metrics- is correct, but the order of the 4 metrics is changing:
for Instance2 we have order:
write2 | numberwrite2 | numberread2 | read2 |
for Instance3 we have order:
numberread3 | numberwrite3 | read3 | write3 |
I'd need to be sure that this order remains the same for each line in the output so that later on, I can catch the each field VM/Instance/Values with regex for example:
"(?P<VM>\w+)","(?P<Instance1>\w+.\w+)","(?P<Write_IOPS1>[0-9]*)","(?P<Write_Kbps1>[0-9]*)","(?P<Read_Kbps1>[0-9]*)","(?P<Read_IOPS1>[0-9]*)"
If the order of the 4 metrics changes, I'll get wrong values in most of the final fields.
It's true, I can see the same order every time I run the script... targeting the same ESXi host. Unfortunately, running the script against 2 different hosts brings different output orders for the metrics.
If Get-Stat is really changing the order, I guess I should re-think the strategy to get the ouput in line and order, or would you see another alternative ?
Try this version
$metrics = "disk.numberread.summation","disk.numberwrite.summation",
"disk.read.average","disk.write.average"
$vms = Get-VM | where {$_.PowerState -eq "PoweredOn"}
Get-Stat -Entity $vms -Stat $metrics -maxsamples 1 -IntervalSecs 20 |
Group-Object -Property EntityId | Foreach-Object{
$row = [ordered]@{
VM = $_.Group[0].Entity.Name
}
$i = 1
$_.Group | where {$_.Instance -ne ""} | Group-Object -Property Instance | %{
$row.Add("Instance$i",$_.Values[0])
foreach($metric in $metrics){
$metricValue = $_.Group | where {$_.MetricId -eq $metric} | Select -ExpandProperty Value
$row.Add("$($metric.Split('.')[1])$i",$metricValue)
}
$i++
}
New-Object PSObject -Property $row
} | Sort-Object -Property {$_ | Get-Member -MemberType NoteProperty | Measure-Object | Select -ExpandProperty Count} -Descending |
Export-Csv "C:\report1.csv" -NoTypeInformation -UseCulture
It uses the order in whcih you specified the counters in the $metrics variable.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
That's it, really impressive ! I tried it on 4 hosts, the order is now fixed, across all metrics. Thank you very much !
Now that the output is fine, I modified the script to get an average value of the metrics:
$vms = Get-VM | where {$_.PowerState -eq "PoweredOn"}
Get-Stat -Entity $vms -Stat $metrics -maxsamples 3 -IntervalSecs 20 |
Group-Object -Property EntityId | Foreach-Object{
$row = [ordered]@{
VM = $_.Group[0].Entity.Name
}
$i = 1
$_.Group | where {$_.Instance -ne ""} | Group-Object -Property Instance | %{
$row.Add("Instance$i",$_.Values[0])
foreach($metric in $metrics){
$metricValue = $_.Group | where {$_.MetricId -eq $metric} | Select -ExpandProperty Value
$metricValueAvg = [Math]::Round(($metricValue | Measure-Object -Average).Average,1)
$row.Add("$($metric.Split('.')[1])$i",$metricValueAvg)
}
$i++
}
New-Object PSObject -Property $row
} | Sort-Object -Property {$_ | Get-Member -MemberType NoteProperty | Measure-Object | Select -ExpandProperty Count} -Descending |
Export-Csv "C:\report2.csv" -NoTypeInformation -UseCulture
I changed maxsamples from 1 to 3, and with the measure-object -Average, I should get the average of the 3 consolidated metrics over 3x20 secs, so 1 min. Right ?