Unable to plot chart

[ganapa2000]

Hi,

I am trying to get the CPU and Memory usage at Folder level based on Total CPU and Total Memory, I am getting error

Please help.

Script

$chart = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' `

-XRange @('Total[CPU Total]','Total[Memory Total]') -YRange @('Usage[CPU Used]','Usage[Memory Used]')

$fld = 'JOPS'

$tcpu = '250'

$tmem = '750'

get-folder $fld | Select @{N="Name" ; E={ $_.Name}},

@{N="CPU Used" ; E={get-folder $fld | get-vm | Measure-Object -Property NumCPU -Sum | Select -ExpandProperty Sum}},

@{N="Memory Used" ; E={ get-folder $fld| get-vm |Measure-Object -Property MemoryGb -Sum | Select -ExpandProperty Sum}} |

Export-Excel -Path d:\chart_report.xlsx -WorkSheetname Usage -TableName Usage -ExcelChartDefinition $chart -Show

Error:

At D:\chart1.ps1:9 char:113

+ ... rkSheetname Usage -TableName Usage -ExcelChartDefinition $chart -Show

+                                                                          ~

The hash literal was incomplete.

At D:\chart1.ps1:17 char:3

+ #>

+   ~

The hash literal was incomplete.

    + CategoryInfo          : ParserError: (:) [], ParseException

    + FullyQualifiedErrorId : IncompleteHashLiteral

[LucD]

Can you first delete the XLSX file?
And then try with this code?

$chart = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' `

-XRange 'Usage[Name]' -YRange 'Usage[CPU Used]','Usage[Memory Used]'

$fld = 'JOPS'

$tcpu = '250'

$tmem = '750'

Get-Folder $fld | Get-VM | Select Name,

    @{N="VMHost" ; E={ $_.Parent.Name}},

    @{N="CPU Used" ; E={ $_ |Measure-Object -Property NumCPU -Sum | Select -ExpandProperty Sum}},

    @{N="Memory Used" ; E={ $_ |Measure-Object -Property MemoryGb -Sum | Select -ExpandProperty Sum}} |

Export-Excel -Path C:\Temp\chart_report.xlsx -WorkSheetname Usage -TableName Usage -ExcelChartDefinition $chart -Show

[ganapa2000]

Hi LucD,

Thank you, Now the chart is getting generated.

But I am looking for Folder level VM CPU Sum and Memory Sum, Where I would like to see Total CPU as 250 and Total Memory as 750 and its usage in the graph

how can I do it ?

I tried as below now

$chart = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' `

-XRange 'Usage[Name]' -YRange 'Usage[CPU Used]','Usage[Memory Used]'

$fld = 'Jiva'

$tcpu = '250'

$tmem = '750'

Get-Folder $fld | Select @{N="Folder";E={$_.Name}},

    @{N="CPU Used" ; E={get-folder $fld | get-vm | Measure-Object -Property NumCPU -Sum | Select -ExpandProperty Sum}},

    @{N="Memory Used" ; E={get-folder $fld | get-vm | Measure-Object -Property MemoryGb -Sum | Select -ExpandProperty Sum}} |

Export-Excel -Path D:\chart_report.xlsx -WorkSheetname Usage -TableName Usage -ExcelChartDefinition $chart -Show

getting the graph as attached.

[LucD]

You can add a row with the Totals to the report.

Something like this

$chart = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' `

    -XRange 'Usage[Name]' -YRange 'Usage[CPU Used]','Usage[Memory Used]'

$fld = 'JOPS'

$tcpu = '250'

$tmem = '750'

$report = Get-Folder $fld | Get-VM | Select Name,

    @{N="VMHost" ; E={ $_.Parent.Name}},

    @{N="CPU Used" ; E={ $_ |Measure-Object -Property NumCPU -Sum | Select -ExpandProperty Sum}},

    @{N="Memory Used" ; E={ $_ |Measure-Object -Property MemoryGb -Sum | Select -ExpandProperty Sum}}

$report += ('' | Select @{N='Name';E={'Total'}},

    @{N="VMHost" ; E={''}},

    @{N="CPU Used" ; E={ $tcpu}},

    @{N="Memory Used" ; E={ $tmem }})

$report |

Export-Excel -Path C:\Temp\chart_report.xlsx -WorkSheetname Usage -TableName Usage -ExcelChartDefinition $chart -Show

[ganapa2000]

Hi LucD,

I modified the script as below

$chart = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' `

-XRange @('Total[CPU Total]','Total[Memory Total]') -YRange @('Usage[Total CPU]','Usage[Used CPU]','Usage[Total Memory]','Usage[Used Memory]')

$fld = 'JOPS'

$tcpu = '250'

$tmem = '750'

get-folder $fld | Select @{N="Name" ; E={$_.Name}},

@{N="Total CPU" ; E={$tcpu}},

@{N="Used CPU" ; E={get-folder $fld | get-vm | Measure-Object -Property NumCPU -Sum | Select -ExpandProperty Sum}},

@{N="Total Memory" ; E={$tmem}},

@{N="Used Memory" ; E={ get-folder $fld| get-vm |Measure-Object -Property MemoryGb -Sum | Select -ExpandProperty Sum}} |

Export-Excel -Path d:\chart_report.xlsx -WorkSheetname Usage -TableName Usage -ShowCategory -ExcelChartDefinition $chart -Show

I am getting the output as attached.

how can I get the Total and Used graph in the same bar or pie.

Please help.

[LucD]

Not sure what you are trying to, but you can have only 1 property on the X axis.

$chart = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' `

  -XRange 'Total[Name]' -YRange @('Usage[Total CPU]','Usage[Used CPU]','Usage[Total Memory]','Usage[Used Memory]')

$fld = 'JOPS'

$tcpu = '250'

$tmem = '750'

Get-Folder $fld | Select @{N="Name" ; E={$_.Name}},

@{N="Total CPU" ; E={$tcpu}},

@{N="Used CPU" ; E={Get-Folder $fld | get-vm | Measure-Object -Property NumCPU -Sum | Select -ExpandProperty Sum}},

@{N="Total Memory" ; E={$tmem}},

@{N="Used Memory" ; E={ Get-Folder $fld | get-vm |Measure-Object -Property MemoryGb -Sum | Select -ExpandProperty Sum}} |

Export-Excel -Path d:\chart_report.xlsx -WorkSheetname Usage -TableName Usage -ShowCategory -ExcelChartDefinition $chart -Show

[ganapa2000]

LucD,

In the chart, I am not able to get the label details, which bar represents what ?

Also, For CPU Total and CPU Used, I am getting multiple bars, is it possible to get CPU Total and CPU Used in one bar with different colours ?

[LucD · Accepted solution]

To get labels, you need to use the SeriesHeader parameter.

$chart = New-ExcelChart -Title Usage -ChartType BarClustered -Header 'Usage' `

  -XRange 'Total[Name]' -YRange @('Usage[Total CPU]','Usage[Used CPU]','Usage[Total Memory]','Usage[Used Memory]') `

  -SeriesHeader 'Total CPU','Used CPU','Total Memory','Used Memory'

To get the values 'stacked' in one bar you could go for the -ChartType BarStacked, but then all 4 values would be stacked in 1 bar.
Remember, this doesn't offer more possibilities then what you have with the Chart Wizard in Excel.

[ganapa2000]

Thank you very much LucD