vmWare CPU Ready Reports With Powershell and MariaDB

I recently needed to get VM CPU ready times across four different vCenter clusters. Based on code in the vmWare forums and a couple of excellent KB articles, I created a script that queries them and writes the results to a MariaDB database. The Powershell script runs as a task every 15 minutes and takes about 5-6 mins to query the remote sites. Setting up the database and web front end is more complex, but there is great flexibility in saving, trending, and manipulating this kind of data. As a bonus, delivering reports to non-technical audiences or other teams is simple with a secure link they can view in their browser. Here’s a mock up of what the end result looks like in the web interface:

The full script is on Github @ https://github.com/t0ta11ed/scripts

Starting off, import vmWare and the awesome MySQLLib functions module. I’ve included this in a zip on the Github page along with the required MySQL .Net Connector.

Import-Module VMware.VimAutomation.Core
Import-Module MySQLLib

Next connect to the database, clear the previous data in the table, set a start time, and create a new array for any VMs with CPU ready 10% or more.

$conn = New-MySQLConnection -server x.x.x.x -database somedatabase -user someuser -password userpassword
Invoke-MySQLQuery "DELETE FROM ClusterCPUReady" -connection $conn
$start = get-date
$over10 = New-Object System.Collections.ArrayList

Next, connect to vCenter. Normally I would pull my ESX servers from another table holding server inventory info, but here an array to populate the host names should be fine for a small cluster. You could also pull them from a text file.

Connect-VIServer -Server SomeVcenter -User SomeUser -Password 'UserPw' | Out-Null
$esxhosts = @('host1','host2','host3')

Set the sample interval, statistic, and finish/start times. This will be used for the Get-Stat cmdlet.

$interval = 5
$stat = 'cpu.ready.summation'
$finish = Get-Date
$start = ($finish).AddHours(- $interval)

Calling on Get-Stat, using the variables. This also sets the aggregated instance “”, and a MaxSample of 1. Finally, only pull the last 10, this narrows down the vast amount of data this can spit out to the most recent samples. This can be changed up as needed.

foreach ($esxhost in $esxhosts) {
Get-Stat -Entity $esxhost -Stat $stat -Start $start -Finish $finish -Instance "" -MaxSamples 1 -Realtime | select -last 10 |

Now group all the results, convert the ready ms time to a percentage, then insert data into the database and disconnect from vCenter. Optionally, if there is any VM with a ready time 2000 ms/10% or higher, we add it to the array created in the beginning of the script to include in an email and send that off.

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

$_.Group |
%{

$VM = $_.Entity.Name
$timeStamp = $_.Timestamp
$readyMs = $_.Value
$readyPerc = "{0:P2}" -f ($_.Value/($_.Intervalsecs*1000))
$esxi = $_.Entity.VMHost
Invoke-MySQLQuery "INSERT into ClusterCPUReady (Date,VM,ReadyMs,ReadyPerc,EsxHost,Location) VALUES ('$timeStamp','$VM','$readyMs','$readyPerc','$esxi','SiteName')" -conn $conn
if ($readyMs -ge "2000") { $over10.Add("$timestamp $VM $readyMs $readyPerc $esxi") | Out-Null }
}

}
}
Disconnect-VIServer * -Confirm:$false

if ($over10) {
# Send an email
}