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
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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
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
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.
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.
There are a few things to remember when collecting statitics.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thank you very much. Shared linked is very explanatory. thanks.
Mission Accomplished
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.
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
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
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)
Hi,
Could you please let us know to generate the graphical report, i imported excel from the powershell gallery.
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
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.
I suggest you create a new thread, reference this thread and specify your additional requirements.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
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?