8 Replies Latest reply on Oct 30, 2019 10:46 PM by ganapa2000

    Unable to plot chart

    ganapa2000 Hot Shot

      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

        • 1. Re: Unable to plot chart
          LucD Guru
          User ModeratorsvExpertCommunity Warriors

          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

          Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
          • 2. Re: Unable to plot chart
            ganapa2000 Hot Shot

            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.

            • 3. Re: Unable to plot chart
              LucD Guru
              Community WarriorsUser ModeratorsvExpert

              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

              Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
              • 4. Re: Unable to plot chart
                ganapa2000 Hot Shot

                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.

                • 5. Re: Unable to plot chart
                  LucD Guru
                  User ModeratorsCommunity WarriorsvExpert

                  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

                  Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                  • 6. Re: Unable to plot chart
                    ganapa2000 Hot Shot

                    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 ?

                    • 7. Re: Unable to plot chart
                      LucD Guru
                      User ModeratorsCommunity WarriorsvExpert

                      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.

                      Blog: http://lucd.info | Twitter: @LucD22 | PowerCLI Reference co-author: http://tinyurl.com/hkn4glz
                      • 8. Re: Unable to plot chart
                        ganapa2000 Hot Shot

                        Thank you very much LucD