1 2 Previous Next 20 Replies Latest reply on May 22, 2019 9:20 AM by NAS91

    Automate CSV output to performance chart view powercli

    SK90 Novice

      Team,

       

      I was wondering if i can automate below powercli script CSV output to a graphical chart(X,Y coordinates)  to understand capacity management of devices. This script will produce output in hourly basis & same need to convert into graphical chart. Urgent response is highly appreciable. thanks in advance.

       

       

      connect-viserver vCenter_Name

      $vm= Get-Content D:\Script\Server.txt

      Get-VM $vm | Select Name, `

      @{N="Mem Usage( % )" ; E={ $_ | get-stat -Stat mem.usage.average -MaxSamples 1 -Start (Get-Date).AddDays(-1) -IntervalMins 60}} , `

      @{N="CPU Usage( % )" ;E={ $_ | get-stat -Stat cpu.usage.average -MaxSamples 1 -Start (Get-Date).AddDays(-1) -IntervalMins 60}} |

      Export-Csv -Path d:Report.csv -NoTypeInformation

        • 1. Re: Automate CSV output to performance chart view powercli
          LucD Guru
          User ModeratorsCommunity WarriorsvExpert

          If you use Doug's ImportExcel module (available in the PowerShell Gallery) , you can export to a XLSX file and include a chart.

           

          Connect-VIServer vCenter_Name

           

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

              -XRange 'Usage[Name]' -YRange @('Usage[Mem Usage( % )]','Usage[CPU Usage( % )]')

          $vm= Get-Content D:\Script\Server.txt

           

          Get-VM $vm |

          Select Name,

              @{N="Mem Usage( % )" ; E={ $_ | Get-Stat -Stat mem.usage.average -MaxSamples 1 -Start (Get-Date).AddDays(-1) -IntervalMins 60}} ,

              @{N="CPU Usage( % )" ;E={ $_ | Get-Stat -Stat cpu.usage.average -MaxSamples 1 -Start (Get-Date).AddDays(-1) -IntervalMins 60}} |

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

           

          chart.png

          • 2. Re: Automate CSV output to performance chart view powercli
            SK90 Novice

            Hi LuCD,

             

            Thank you very much for your time & reply. I just added one more column "D" that is Network Usage in KBps, however Column "A" and "B" is in %. Now the chart is not accommodating both values (% and KBps).  In snapshot 2 it's segregated in X,Y coordinates. Please help on this.

             

            Snapshot 1.

             

             

             

            Snapshot 2

             

            • 3. Re: Automate CSV output to performance chart view powercli
              LucD Guru
              Community WarriorsUser ModeratorsvExpert

              The ImportExcel module doesn't provide that functionality yet.
              You can have two separate charts on the same worksheet though.

               

              Connect-VIServer vCenter_Name

               

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

                  -XRange 'Usage[Name]' -YRange @('Usage[Mem Usage( % )]','Usage[CPU Usage( % )]')

              $chart2 = New-ExcelChart -Title UsageNet -ChartType BarClustered -Header 'UsageNet' `

                  -XRange 'Usage[Name]' -YRange @('Usage[Net Usage( Kbps )]')

              $vm= Get-Content D:\Script\Server.txt

               

              Get-VM $vm |

              Select Name,

                  @{N="Mem Usage( % )" ; E={ $_ | Get-Stat -Stat mem.usage.average -MaxSamples 1 -Start (Get-Date).AddDays(-1) -IntervalMins 60}} ,

                  @{N="CPU Usage( % )" ;E={ $_ | Get-Stat -Stat cpu.usage.average -MaxSamples 1 -Start (Get-Date).AddDays(-1) -IntervalMins 60}},

                  @{N="Net Usage( Kbps )" ;E={ $_ | Get-Stat -Stat net.usage.average -MaxSamples 1 -Start (Get-Date).AddDays(-1) -IntervalMins 60}} |

              Export-Excel -Path C:\Temp\report.xlsx -WorkSheetname Usage -TableName Usage -ExcelChartDefinition $chart1,$chart2 -Show

               

               

              You'll have to go for something as described in How to: Add Secondary Axes to a Chart

              But that would also require you to have Excel installed on the station where you run the script.
              And the coding is a bit more cumbersome.

              • 4. Re: Automate CSV output to performance chart view powercli
                SK90 Novice

                Thanks a ton !  You made my day. Now I can share data to management in effective way & it's credit goes to you.

                 

                Please fix my last doubt as well. I am Setting statistics interval time time for 60 minutes, but it's pulling data of 30 minutes gap & if i set interval time 90 minutes it's pulling data of 2 hours. Not sure why setting interval time of 60 minutes pulling data of 30 minutes gap. Also help me to know which parameter should i take for disk performance as disk.usage.average value is no more available in ESXi6.5, however in ESXi5.5 it's available.

                 

                • 5. Re: Automate CSV output to performance chart view powercli
                  SK90 Novice

                  Please ignore my comment for "disk.usage.average". I did cross check out of 10 servers found that only in 2 server counter "disk.usage.average" is missing that i will check with vmware. Please help to understand interval statistics gap.

                  • 6. Re: Automate CSV output to performance chart view powercli
                    LucD Guru
                    Community WarriorsvExpertUser Moderators

                    There are a few things to remember when collecting statitics.

                    • when there is no stat data for the Start datetime, there will be no data returned for the complete interval (Start-Finish)
                    • on the vCenter there are aggregation jobs running. These change the intervals depending on the age of the data. See my PowerCLI & vSphere statistics – Part 1 – The basics post for more info on that aggregation process.
                    • 7. Re: Automate CSV output to performance chart view powercli
                      SK90 Novice

                      Thank you very much. Shared linked is very explanatory. thanks.

                       

                      Mission Accomplished

                      • 8. Re: Automate CSV output to performance chart view powercli
                        SK90 Novice

                        Hi LuCD,

                         

                        I need your further help on this as management has different view & want report(weekly, 2 hours interval) like below per VM basis. Can we fetch below report  for each VM/bulk VM & convert to either in single chart or double.

                         

                         

                         

                        • 9. Re: Automate CSV output to performance chart view powercli
                          LucD Guru
                          User ModeratorsCommunity WarriorsvExpert

                          2 hour intervals are only available for the past month, if you want to go back further in time, there will only be data with 1 value per day.

                          How far back do your reports need to go?

                          • 10. Re: Automate CSV output to performance chart view powercli
                            SK90 Novice

                            we need a script that can export last week statistics of a VM in excel report(interval - either hourly or 2 hour interval)  & the same can be convert to chart..

                             

                            How far back do your reports need to go? >> only for last week

                             

                             

                            • 11. Re: Automate CSV output to performance chart view powercli
                              SK90 Novice

                              Hi LuCD,

                               

                              Thanks for your help.

                              Below is the script that is fetching data per VM basis & to accommodate all 4 parameters into single chart i have taken help of Excel chart advanced formula option as script was not able to convert all 4 parameters into chart(% and Kbps). Now each time we are running the script Excel automatically converting data to graphics chart. Below is the output  

                               

                              $servers=Get-Content C:\Temp\ESB\servers.txt

                              foreach ($server in $servers)

                              {

                                  $vm=Get-VM $server

                                  if($? -eq $false)

                                  {

                                      continue

                                  }

                                  $fileName="C:\Temp\ESB\$server.csv"

                                  $diskusageaverage=Get-Stat -entity $vm -MaxSamples 168 -Start (Get-Date).AddDays(-7) -IntervalMins 120 -stat ‘disk.usage.average’ | ? { $_.instance -eq "" } | select Entity,Timestamp,Value |Sort-Object Timestamp

                                  $memusageaverage=Get-Stat -entity $vm -MaxSamples 168 -Start (Get-Date).AddDays(-7) -IntervalMins 120 -stat ‘mem.usage.average’ | ? { $_.instance -eq "" } | select Entity,Timestamp,Value |Sort-Object Timestamp

                                  $cpuusageaverage=Get-Stat -entity $vm -MaxSamples 168 -Start (Get-Date).AddDays(-7) -IntervalMins 120 -stat ‘cpu.usage.average’ | ? { $_.instance -eq "" } | select Entity,Timestamp,Value |Sort-Object Timestamp

                                  $netusageaverage=Get-Stat -entity $vm -MaxSamples 168 -Start (Get-Date).AddDays(-7) -IntervalMins 120 -stat ‘net.usage.average’ | ? { $_.instance -eq "" } | select Entity,Timestamp,Value |Sort-Object Timestamp

                                  Set-Content -Value "ServerName,AVG_CPU_Time,AVG_CPU,AVG_MEM,AVG_disk,AVG_Net" -Path $fileName

                                  $diskus=0

                                  for ($i=0;$i -lt $cpuusageaverage.Count ; $i++)

                                  {

                                      if (($diskus -lt $diskusageaverage.count) -and ($cpuusageaverage[$i].Timestamp -eq $diskusageaverage[$diskus].Timestamp))

                                      {

                                          Add-Content -Value "$($cpuusageaverage[$i].Entity.Name),$($cpuusageaverage[$i].Timestamp),$($cpuusageaverage[$i].Value),$($memusageaverage[$i].Value),$($diskusageaverage[$diskus].Value),$($netusageaverage[$i].value)" -Path $fileName

                                          $diskus++

                                      }

                                      else

                                      {

                                              Add-Content -Value "$($cpuusageaverage[$i].Entity.Name),$($cpuusageaverage[$i].Timestamp),$($cpuusageaverage[$i].Value),$($memusageaverage[$i].Value),0,$($netusageaverage[$i].value)" -Path $fileName

                                      }

                                  }

                              }

                              return(0)

                               

                               

                               

                               

                               

                               

                               

                               

                              • 12. Re: Automate CSV output to performance chart view powercli
                                LucD Guru
                                User ModeratorsvExpertCommunity Warriors

                                Nice!

                                Thanks for sharing.

                                • 13. Re: Automate CSV output to performance chart view powercli
                                  Srinivas_k Novice

                                  Hi,

                                  Could you please let us know to generate the graphical report, i imported excel from the powershell gallery.

                                  • 14. Re: Automate CSV output to performance chart view powercli
                                    LucD Guru
                                    Community WarriorsUser ModeratorsvExpert

                                    Don't know how the other graphs were produced, but the ones in my answer were done with New-ExcelChart and the ExcelChartDefinition parameter on the Export-Excel cmdlet.

                                    See an example higher up in this thread.

                                    1 2 Previous Next