Query average CPU of replicated VMs

As I continue to learn PowerCLI and PowerShell in general, I appreciate when there is a request for any kind of report with some values that may not come in 'canned' presentation. Few weeks ago, I was asked to schedule a report of all of the replicated VMs with their provisioned space and average CPU usage among other values. Without even looking to see if the Web Client GUI or other tools could provide this information; I wanted to use a PS One-liner, not only for my own practice but because it can be easily scheduled to run weekly or monthly.

For the basic query and filtering, I have watched enough PowerCLI videos on Pluralsight (😉) to know how to filter out certain VMs and get many of their properties. In this case, all replicated VMs reside on datastores with a common part of the name, so that was simple; but rounding the ProvisionedSpace in GB to fit nicely in Excel and listing the Average CPU usage for the last week was not, at least for me. Thankfully, we have Google and many unselfish bloggers who have shared how this type of properties can be displayed properly.

Below is the command I'm using and as always just wanted to post it here as reference for the future and in hope that someone else may find it useful.

The command will list VMs residing on datastores with "RPL" in their name; display the VM's Name, allocated CPU and RAM, ProvisionedSpaceGB rounded and Average CPU usage for the last week; this will be exported as a .CSV file.

Get-Datastore RPL* | Get-VM | Select Name,NumCpu,MemoryGB,@{n="Provisionedspace(GB)"; E={[math]::round($_.ProvisionedSpaceGB)}},@{N="CPU Usage (Average), Mhz" ; E={[Math]::Round((($_ | Get-Stat -Stat cpu.usagemhz.average -Start (Get-Date).AddDays(-7) -IntervalMins 5 | Measure-Object Value -Average).Average),2)}} | Export-Csv C:\FileName.csv -NoTypeInformation

This property rounds the Provisioned Space in GB, otherwise it will display a very long number.

@{n="Provisionedspace(GB)";E={[math]::round($_.ProvisionedSpaceGB)}}


This part averages the CPU for the last week.
@{N="CPU Usage (Average), Mhz" ; E={[Math]::Round((($_ | Get-Stat -Stat cpu.usagemhz.average -Start (Get-Date).AddDays(-7) -IntervalMins 5 | Measure-Object Value -Average).Average),2)}}

---