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
To get labels, you need to use the SeriesHeader parameter.
-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.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Can you first delete the XLSX file?
And then try with this code?
-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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
You can add a row with the Totals to the report.
Something like this
-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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
Not sure what you are trying to, but you can have only 1 property on the X axis.
-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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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 ?
To get labels, you need to use the SeriesHeader parameter.
-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.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thank you very much LucD