VMware Cloud Community
SK90
Enthusiast
Enthusiast

Automate CSV output to performance chart view powercli

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

20 Replies
LucD
Leadership
Leadership

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


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

SK90
Enthusiast
Enthusiast

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.

pastedImage_0.png

Snapshot 2

pastedImage_1.png

Reply
0 Kudos
LucD
Leadership
Leadership

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.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
SK90
Enthusiast
Enthusiast

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.

pastedImage_0.png

Reply
0 Kudos
SK90
Enthusiast
Enthusiast

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.

Reply
0 Kudos
LucD
Leadership
Leadership

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.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
SK90
Enthusiast
Enthusiast

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

Mission Accomplished Smiley HappySmiley Happy

Reply
0 Kudos
SK90
Enthusiast
Enthusiast

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.

pastedImage_0.png

pastedImage_1.png

Reply
0 Kudos
LucD
Leadership
Leadership

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?


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
SK90
Enthusiast
Enthusiast

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

pastedImage_0.png

pastedImage_1.png

Reply
0 Kudos
SK90
Enthusiast
Enthusiast

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)

pastedImage_0.png

pastedImage_1.png

Reply
0 Kudos
LucD
Leadership
Leadership

Nice!

Thanks for sharing.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Srinivas_k
Enthusiast
Enthusiast

Hi,

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

Reply
0 Kudos
LucD
Leadership
Leadership

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.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Srinivas_k
Enthusiast
Enthusiast

Hi LucD,

i checked your post, can you help me how to get the graph with interval per VM ?

if a VM is taken stat for 1-hour interval can we get that 1 hour intervals details in graph?

Thank you in Advance.

Reply
0 Kudos
Subhashmts
Contributor
Contributor

This script is good .I just need only Average CPU and Average memory usages for few VMs and I need to collect in every 15 minutes at least
Reply
0 Kudos
LucD
Leadership
Leadership

I suggest you create a new thread, reference this thread and specify your additional requirements.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
Subhashmts
Contributor
Contributor

Same script is fine ..

I just need only below for few VMs (Selective VMS) and automatically send the report as CSv via email

Average CPU Usage

Average memory usage

Data collection every 15 minutes at least

Reply
0 Kudos
AdrianSahota
Contributor
Contributor

How were you able to get the multiple values onto the Dark Graph show above, where CPU,Memory, NIC and Disk are all on the same graph?

Reply
0 Kudos