Performance Chart shows blank

[ganapa2000]

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

[LucD]

When you define the chart, you have to use columnnames (and the worksheetname) to define the X and Y ranges.

Something like this

$chart1 = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' -XRange 'Usage[VM]' `

   -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

[LucD · Accepted solution]

I suspect this might be more in line of what you are looking for?

$vms = Get-VM -Name (Get-Content ".\servers.txt")

$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.

[ganapa2000]

Perfect LucD, This is what I am looking for

Thank you very much.

[vmk2014]

Hi LucD,

I'm getting error while executing the report.

thanks

V

[LucD]

Looks like you might be missing the back-tick at the end of the previous line.

[vmk2014]

LucD,

My apologies. Can you point out which line ? Please

Thanks

V

[LucD]

The end of this 1st line

$chart1 = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' -XRange 'Usage[Timestamp]' `

   -YRange $yRange -SeriesHeader $headers

[vmk2014]

it worked but disk usage not showing in graph.

Thanks

V

[LucD]

You will need to update this line with the metrics you want.

$yRange = $vms.Name | %{"Usage[$_-CpuAvg]","Usage[$_-MemAvg]"} | Sort-Object

[vmk2014]

you mean to say like

$yRange = $vms.Name | %{"Usage[$_-CpuAvg]","Usage[$_-MemAvg]","Usage[$_-DiskAvg]"} | Sort-Object

Thanks

V

[LucD]

You just need to check if Excel accepts more than 2 data series for the Y axis

[vmk2014]

LucD,

I'm looking the report for disk usage/cpu/Memory in millisecond.

Thanks

V

[LucD]

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.

[vmk2014]

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

[LucD]

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)