Hi,
I have having issue with plotting data with Performance Chart, please help.
Below scripts, Performance charts shows blank
$chart1 = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' -XRange 'Usage[Name]' -YRange @('Usage[Mem Usage( % )]','Usage[CPU Usage( % )]')
$sStat = @{
Stat = 'cpu.usage.average','mem.usage.average','net.usage.average','disk.usage.average'
Start = (Get-Date -Hour 10 -Minute 0 -Second 0).AddDays(-1)
Finish = (Get-Date -Hour 10 -Minute 0 -Second 0).AddDays(-1).AddHours(12)
Instance = ''
MaxSamples = [int]::MaxValue
Entity = Get-VM -Name (Get-Content ".\servers.txt")
ErrorAction = 'SilentlyContinue'
}
Get-Stat @sStat |
Group-Object -Property {$_.Entity.Name} |
ForEach-Object -Process {
$_.Group | Group-Object -Property Timestamp |
ForEach-Object -Process {
New-Object -TypeName PSObject -Property ([ordered]@{
VM = $_.Group[0].Entity.Name
Timestamp = $_.Group[0].Timestamp
CpuAvg = ($_.Group | where{$_.MetricId -eq 'cpu.usage.average'}).Value
MemAvg = ($_.Group | where{$_.MetricId -eq 'mem.usage.average'}).Value
DiskAvg = ($_.Group | where{$_.MetricId -eq 'disk.usage.average'}).Value
NetAvg = ($_.Group | where{$_.MetricId -eq 'net.usage.average'}).Value
})
}
} | Export-Excel -Path ".\report.xlsx" -WorkSheetname Usage -TableName Usage -ExcelChartDefinition $chart1 -Show
I suspect this might be more in line of what you are looking for?
$yRange = $vms.Name | %{"Usage[$_-CpuAvg]","Usage[$_-MemAvg]"} | Sort-Object
$headers = $yRange.replace('Usage[','').replace(']','')
$chart1 = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' -XRange 'Usage[Timestamp]' `
-YRange $yRange -SeriesHeader $headers
$sStat = @{
Stat = 'cpu.usage.average','mem.usage.average','net.usage.average','disk.usage.average'
Start = (Get-Date -Hour 10 -Minute 0 -Second 0).AddDays(-1)
Finish = (Get-Date -Hour 10 -Minute 0 -Second 0).AddDays(-1).AddHours(12)
Instance = ''
MaxSamples = [int]::MaxValue
Entity = $vms
ErrorAction = 'SilentlyContinue'
}
Get-Stat @sStat |
Group-Object -Property {$_.Timestamp} |
ForEach-Object -Process {
$obj = [ordered]@{
Timestamp = $_.Name
}
$_.Group | Group-Object -Property {$_.Entity.Name} |
ForEach-Object -Process {
$obj.Add("$($_.Name)-CpuAvg",($_.Group | where{$_.MetricId -eq 'cpu.usage.average'}).Value)
$obj.Add("$($_.Name)-MemAvg",($_.Group | where{$_.MetricId -eq 'mem.usage.average'}).Value)
$obj.Add("$($_.Name)-DiskAvg",($_.Group | where{$_.MetricId -eq 'disk.usage.average'}).Value)
$obj.Add("$($_.Name)-NetAvg",($_.Group | where{$_.MetricId -eq 'net.usage.average'}).Value)
}
New-Object -TypeName PSObject -Property $obj
} | Export-Excel -Path ".\report.xlsx" -WorkSheetname Usage -TableName Usage -ExcelChartDefinition $chart1 -Show -ClearSheet
---------------------------------------------------------------------------------------------------------
Was it helpful? Let us know by completing this short survey here.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
When you define the chart, you have to use columnnames (and the worksheetname) to define the X and Y ranges.
Something like this
-YRange @('Usage[CpuAvg]','Usage[MemAvg]') -SeriesHeader @('CpuAvg','MemAvg')
But what exactly do you want to plot?
Timestamp (X) vs the values (Cpu,Mem,Net,Disk) for each VM?
Then you will have to reformat the data, you can only have 1 X range
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
I suspect this might be more in line of what you are looking for?
$yRange = $vms.Name | %{"Usage[$_-CpuAvg]","Usage[$_-MemAvg]"} | Sort-Object
$headers = $yRange.replace('Usage[','').replace(']','')
$chart1 = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' -XRange 'Usage[Timestamp]' `
-YRange $yRange -SeriesHeader $headers
$sStat = @{
Stat = 'cpu.usage.average','mem.usage.average','net.usage.average','disk.usage.average'
Start = (Get-Date -Hour 10 -Minute 0 -Second 0).AddDays(-1)
Finish = (Get-Date -Hour 10 -Minute 0 -Second 0).AddDays(-1).AddHours(12)
Instance = ''
MaxSamples = [int]::MaxValue
Entity = $vms
ErrorAction = 'SilentlyContinue'
}
Get-Stat @sStat |
Group-Object -Property {$_.Timestamp} |
ForEach-Object -Process {
$obj = [ordered]@{
Timestamp = $_.Name
}
$_.Group | Group-Object -Property {$_.Entity.Name} |
ForEach-Object -Process {
$obj.Add("$($_.Name)-CpuAvg",($_.Group | where{$_.MetricId -eq 'cpu.usage.average'}).Value)
$obj.Add("$($_.Name)-MemAvg",($_.Group | where{$_.MetricId -eq 'mem.usage.average'}).Value)
$obj.Add("$($_.Name)-DiskAvg",($_.Group | where{$_.MetricId -eq 'disk.usage.average'}).Value)
$obj.Add("$($_.Name)-NetAvg",($_.Group | where{$_.MetricId -eq 'net.usage.average'}).Value)
}
New-Object -TypeName PSObject -Property $obj
} | Export-Excel -Path ".\report.xlsx" -WorkSheetname Usage -TableName Usage -ExcelChartDefinition $chart1 -Show -ClearSheet
---------------------------------------------------------------------------------------------------------
Was it helpful? Let us know by completing this short survey here.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Perfect LucD, This is what I am looking for
Thank you very much.
Hi LucD,
I'm getting error while executing the report.
thanks
V
Looks like you might be missing the back-tick at the end of the previous line.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
LucD,
My apologies. Can you point out which line ? Please
Thanks
V
The end of this 1st line
-YRange $yRange -SeriesHeader $headers
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
it worked but disk usage not showing in graph.
Thanks
V
You will need to update this line with the metrics you want.
$yRange = $vms.Name | %{"Usage[$_-CpuAvg]","Usage[$_-MemAvg]"} | Sort-Object
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
you mean to say like
$yRange = $vms.Name | %{"Usage[$_-CpuAvg]","Usage[$_-MemAvg]","Usage[$_-DiskAvg]"} | Sort-Object
Thanks
V
You just need to check if Excel accepts more than 2 data series for the Y axis
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
LucD,
I'm looking the report for disk usage/cpu/Memory in millisecond.
Thanks
V
For disk usage you can use a latency metric to get something in milliseconds.
But for CPU and memory, I don't immediately see what metric you could use for something meaningful in milliseconds.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thank you, LucD. Can you please help where i need to make changes for Disk usage in ms ? what parameter need to input ?
Thanks
V
You will first need to decide which metric you want to see.
I can't make that decision for you.
Have a look at the available disk metrics, but be aware that the Statistics Level needs to be at the right value to be able to see specific metrics (the Level column)
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference