Automation

 View Only
  • 1.  Unable to plot chart

    Posted Oct 29, 2019 11:28 AM

    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



  • 2.  RE: Unable to plot chart

    Posted Oct 29, 2019 12:19 PM

    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



  • 3.  RE: Unable to plot chart

    Posted Oct 29, 2019 12:31 PM

    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.



  • 4.  RE: Unable to plot chart

    Posted Oct 29, 2019 12:44 PM

    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



  • 5.  RE: Unable to plot chart

    Posted Oct 30, 2019 11:10 AM

    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.



  • 6.  RE: Unable to plot chart

    Posted Oct 30, 2019 11:54 AM

    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



  • 7.  RE: Unable to plot chart

    Posted Oct 30, 2019 12:54 PM

    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 ?



  • 8.  RE: Unable to plot chart
    Best Answer

    Posted Oct 30, 2019 01:10 PM

    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.



  • 9.  RE: Unable to plot chart

    Posted Oct 31, 2019 05:47 AM

    Thank you very much LucD