Solved: Cluster CPU and RAM Usage Report

mibiras · ‎08-20-2018

Hello, I have more clusters and I need report with average CPU[GHz] and memory[GB] usage for last 12 months for each cluster and month

Table should like this:

Month	cl01 CPU usage	cl01 RAM usage	cl02 CPU usage	cl02 RAM usage
jan
feb
mar
apr

For calculating monthly averages should be used this:

$AverageMemoryUsage = Get-Stat -Entity ($cluster)-start (get-date).AddDays(-30) -Finish (Get-Date)-MaxSamples 31 -stat mem.usage.average  
 $AverageMemoryUsage = $AverageMemoryUsage | Measure-Object -Property value -Average  
 $AverageMemoryUsage = $AverageMemoryUsage.Average  
 $AverageMemoryUsage = [system.math]::ceiling($AverageMemoryUsage)

Could help me somebody with this ?

LucD · ‎08-22-2018

No, no problem.
Try this version.
Note that you will need the attached, slightly adapted Get-Stat2 function.

$stat = 'cpu.usagemhz.average','mem.consumed.average'

$start = (Get-Date).AddMonths(-12)

$global:DefaultVIServers |

ForEach-Object -Process {

$vc = $_

Get-Cluster -Server $vc |

ForEach-Object -Process {

Get-Stat2 -Entity $_.ExtensionData -Stat $stat -Start $start -Interval HI4 -Instance '' -Server $vc

}

} |

Group-Object -Property {$_.Timestamp.ToString('yyyyMM')} |

Sort-Object -Property @{E={$_.Name}} |

ForEach-Object -Process {

$obj = [ordered]@{

Month = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($_.Name.SubString(4,2))) $($_.Name.SubString(0,4))"

}

$_.Group | Group-Object -Property {$_.Entity} |

Sort-Object -Property Name |

ForEach-Object -Process {

$mem = $_.Group | where{$_.CounterName -eq 'mem.consumed.average'} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$cpu = $_.Group | where{$_.CounterName -eq 'cpu.usagemhz.average'} |

where{$_.Instance -eq ''} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$obj.Add("$($_.Name) CPU Usage GHz", [math]::Ceiling($cpu/1000))

$obj.Add("$($_.Name) RAM Usage GB", [math]::Ceiling($mem/1MB))

}

New-Object PSObject -Property $obj

} | Export-Csv -Path .\report.csv -UseCulture -NoTypeInformation

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

View solution in original post

LucD · ‎08-20-2018

Try like this.

Note that the months (in alphabetical order) do not necessarily belong to the same year.
A chronological order would be more accurate imho.

Also note that the mem.usage.average gives percentage of memory used, while you seemed to ask for the value in GB.
Hence I went with the mem.consumed.average metric.

$stat = 'cpu.usageMHz.average','mem.consumed.average'

$start = (Get-Date).AddMonths(-12)

$cluster = Get-Cluster

Get-Stat -Entity $cluster -Stat $stat -Start $start |

Group-Object -Property {$_.Timestamp.Month} |

Sort-Object -Property @{E={$_.Name -as [int]}} |

ForEach-Object -Process {

$obj = [ordered]@{

Month = (Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($_.Name)

}

$_.Group | Group-Object -Property {$_.Entity.Name} |

Sort-Object -Property Name |

ForEach-Object -Process {

$mem = $_.Group | where{$_.MetricId -eq 'mem.consumed.average'} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$cpu = $_.Group | where{$_.MetricId -eq 'cpu.usagemhz.average'} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$obj.Add("$($_.Name) CPU Usage GHz", [math]::Ceiling($cpu/1000))

$obj.Add("$($_.Name) RAM Usage GB", [math]::Ceiling($mem/1MB))

}

New-Object PSObject -Property $obj

} | Export-Csv -Path .\report.csv -UseCulture -NoTypeInformation

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

mibiras · ‎08-21-2018

LucD thank you very much for your help, You are right, It will be better if column with month will be in chronological order and if this will also contain year. Is it possible to have there something like this: January 2017 or 01-2017... ?

And I have also one problem - there is big diference between values from vCenter when I export to CSV graph Averege Usage in Mhz and values from command

Get-Stat -Entity (Get-cluster h1-cl01) -Stat cpu.usagemhz.average -Start (Get-Date).AddDays(-7) -IntervalMins 30

MetricId Timestamp Value Unit Instance

-------- --------- ----- ---- --------

cpu.usagemhz.average 21. 8. 2018 7:30:00 224714 MHz

cpu.usagemhz.average 21. 8. 2018 7:00:00 208193 MHz

cpu.usagemhz.average 21. 8. 2018 6:30:00 201652 MHz

From vCenter Perfomance graph:

Time Usage in MHz for h1-cl01

Tue Aug 21 06:30:00 GMT+0200 2018 104432

Tue Aug 21 07:00:00 GMT+0200 2018 109423

Tue Aug 21 07:30:00 GMT+0200 2018 114282

Is it solvable ?

LucD · ‎08-21-2018

It might be that due to your statistics level you keep all instances, while you only need the aggregate instance.

Try like this

$stat = 'cpu.usageMHz.average','mem.consumed.average'

$start = (Get-Date).AddMonths(-12)

$cluster = Get-Cluster

Get-Stat -Entity $cluster -Stat $stat -Start $start |

Group-Object -Property {$_.Timestamp.ToString('yyyyMM')} |

Sort-Object -Property @{E={$_.Name}} |

ForEach-Object -Process {

$obj = [ordered]@{

Month = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($_.Name.SubString(4,2))) $($_.Name.SubString(0,4))"

}

$_.Group | Group-Object -Property {$_.Entity.Name} |

Sort-Object -Property Name |

ForEach-Object -Process {

$mem = $_.Group | where{$_.MetricId -eq 'mem.consumed.average'} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$cpu = $_.Group | where{$_.MetricId -eq 'cpu.usagemhz.average'} |

where{$_.Instance -eq ''} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$obj.Add("$($_.Name) CPU Usage GHz", [math]::Ceiling($cpu/1000))

$obj.Add("$($_.Name) RAM Usage GB", [math]::Ceiling($mem/1MB))

}

New-Object PSObject -Property $obj

} | Export-Csv -Path .\report.csv -UseCulture -NoTypeInformation

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

mibiras · ‎08-21-2018

It is not caused statistics level because I used same level for vCenter and for Get-Stat and also when I used for example cpu.capacity.demand.average I have same numbers from vCenter and from Get-Stat as you can see bellow

Get-Stat -Entity (Get-cluster h1-cl01) -Stat cpu.capacity.demand.average -Start (Get-Date).AddDays(-1) -IntervalMins 30

cpu.capacity.demand.... 21. 8. 2018 7:30:00 121672 MHz

cpu.capacity.demand.... 21. 8. 2018 7:00:00 112885 MHz

cpu.capacity.demand.... 21. 8. 2018 6:30:00 108967 MHz

and from vCenter

Tue Aug 21 06:30:00 GMT+0200 2018 108967

Tue Aug 21 07:00:00 GMT+0200 2018 112885

Tue Aug 21 07:30:00 GMT+0200 2018 121672

So, there should be some bug in counter cpu.usageMHz.average with Get-Stat I think. There are big differences also when I compare output from your script to vCenters yearly CPU graph. Mem statistics is OK.

LucD · ‎08-21-2018

I did some further verification , and the cpu.usagemhz.average values obtained through Get-Stat are indeed constantly lower than what the Web Client is showing under the Performance tab.

As a verification I obtained the statistical data directly with the QueryPerf method, thus bypassing the Get-Stat cmdlet.

In fact I used my Get-Stat2 function.

And I can confirm, there seems to be something wrong with the Get-Stat cmdlet.
It constantly returns values that are lower than the ones returned by QueryPerf (and the Web Client).

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

mibiras · ‎08-22-2018

OK, I will use cpu.capacity.demand.average. it is quite good for us.

But I have harder task, is possible to create similar report for used space for datastore cluster ?

If yes I create new discussion.

LucD · ‎08-22-2018

I adapted the above script for my Get-Stat2 function.
Not only does the script run faster, the results are now corresponding with what you see in the Web Client.

$stat = 'cpu.usagemhz.average','mem.consumed.average'

$start = (Get-Date).AddMonths(-12)

Get-Cluster |

ForEach-Object -Process {

Get-Stat2 -Entity $_.ExtensionData -Stat $stat -Start $start -Interval HI4 -Instance ''

} |

Group-Object -Property {$_.Timestamp.ToString('yyyyMM')} |

Sort-Object -Property @{E={$_.Name}} |

ForEach-Object -Process {

$obj = [ordered]@{

Month = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($_.Name.SubString(4,2))) $($_.Name.SubString(0,4))"

}

$_.Group | Group-Object -Property {$_.Entity} |

Sort-Object -Property Name |

ForEach-Object -Process {

$mem = $_.Group | where{$_.CounterName -eq 'mem.consumed.average'} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$cpu = $_.Group | where{$_.CounterName -eq 'cpu.usagemhz.average'} |

where{$_.Instance -eq ''} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$obj.Add("$($_.Name) CPU Usage GHz", [math]::Ceiling($cpu/1000))

$obj.Add("$($_.Name) RAM Usage GB", [math]::Ceiling($mem/1MB))

}

New-Object PSObject -Property $obj

} | Export-Csv -Path .\report.csv -UseCulture -NoTypeInformation

For your used space question, I did a post on that, see Datastore Usage Statistics

And yes, create a new thread for that question.

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

mibiras · ‎08-22-2018

this last version works great, I have only one issue according my region, even if I run

function Set-Culture([System.Globalization.CultureInfo] $culture) { [System.Threading.Thread]::CurrentThread.CurrentUICulture = $culture ; [System.Threading.Thread]::CurrentThread.CurrentCulture = $culture } ; Set-Culture en-US ; [system.threading.thread]::currentthread.currentculture

I have few months not correctly displayed :

Apr-18 this is correct

m?j 2018

j?n 2018

j?l 2018

Any idea ?

LucD · ‎08-22-2018

On screen it's not too obvious, but for the CSV file, try saving with the Encoding parameter.

Export-Csv .\report.csv -NoTypeInformation -Encoding UTF8

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

mibiras · ‎08-22-2018

When I use UTF8, diacritics in excel is OK but stil function

Month = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($_.Name.SubString(4,2))) $($_.Name.SubString(0,4))"

didnt convert máj 2018 to may-2018 as we want, but this is not big problem, I can correct it manually in excel

But what will help me. if script will report multiple vcenters as it done in VMware PowerCLI Forum - VMware {code}

LucD · ‎08-22-2018

How do you intend to visualise the vCenter?

All in the column header? Like "vCenterName-ClusterName-CPU Usage"?

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

mibiras · ‎08-22-2018

it is enough ClusterName-CPU Usage because we dont have clusters with same name, but if this should be problem, you could also use vCenterName-ClusterName-CPU Usage as you suggest.

LucD · ‎08-22-2018

No, no problem.
Try this version.
Note that you will need the attached, slightly adapted Get-Stat2 function.

$stat = 'cpu.usagemhz.average','mem.consumed.average'

$start = (Get-Date).AddMonths(-12)

$global:DefaultVIServers |

ForEach-Object -Process {

$vc = $_

Get-Cluster -Server $vc |

ForEach-Object -Process {

Get-Stat2 -Entity $_.ExtensionData -Stat $stat -Start $start -Interval HI4 -Instance '' -Server $vc

}

} |

Group-Object -Property {$_.Timestamp.ToString('yyyyMM')} |

Sort-Object -Property @{E={$_.Name}} |

ForEach-Object -Process {

$obj = [ordered]@{

Month = "$((Get-Culture).DateTimeFormat.GetAbbreviatedMonthName($_.Name.SubString(4,2))) $($_.Name.SubString(0,4))"

}

$_.Group | Group-Object -Property {$_.Entity} |

Sort-Object -Property Name |

ForEach-Object -Process {

$mem = $_.Group | where{$_.CounterName -eq 'mem.consumed.average'} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$cpu = $_.Group | where{$_.CounterName -eq 'cpu.usagemhz.average'} |

where{$_.Instance -eq ''} |

Measure-Object -Property Value -Average |

Select -ExpandProperty Average

$obj.Add("$($_.Name) CPU Usage GHz", [math]::Ceiling($cpu/1000))

$obj.Add("$($_.Name) RAM Usage GB", [math]::Ceiling($mem/1MB))

}

New-Object PSObject -Property $obj

} | Export-Csv -Path .\report.csv -UseCulture -NoTypeInformation

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

mibiras · ‎08-22-2018

Something wrong. It gets data only from last connected vcenter. To connect I use this:

$hosts = @( 
    "vcenter01", 
    "vcenter02", 
    "vcenter03" 
);
$user = "1234" 
$password = "12345"
Connect-VIServer -Server $hosts -User $user -Password $password

LucD · ‎08-22-2018

What do you have in $global:defaultviservers?

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

mibiras · ‎08-22-2018

I solved it with:

Set-PowerCLIConfiguration -DefaultVIServerMode multiple -Confirm

LucD · ‎08-23-2018

Does that mean that your question was answered :smileygrin:

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