Powershell and VMware Stats

Our production environment is a mixture of VMs and Physical boxes. Perfmon is used to get a good idea on hoe are physical boxes are doing, but it's a different story with VMs. I got a request to read and get stats for CPU and memory per guest across the entire cluster and add that to a sql database.

There is a toolkit available from VMWare which will allow you to work with Perl of Powershell. Powershell was obviously my first choice:

############################################################
#ClusterStats.ps1      
#
#This is read as the % CPU used by the guest calculated
#against the effective CPU and % memory used by the
#guest against the effective Memory.  Both of these
#values are calculated across the entire cluster.
############################################################

Add-PSsnapin VMware.VimAutomation.Core
Initialize-VIToolkitEnvironment.ps1

$vcserver="vmm01.web.com"
$portvc="443"
$user="prod\jlangley"
$password="MyPassword"

connect-VIServer $vcserver -port $portvc -User $user -Password $password -WarningAction SilentlyContinue

$clusterName = "WEB ESX  Cluster 01"
$report = @()

 $cluster = Get-Cluster -Name $clusterName | Get-View
 $clusterCPU = $cluster.Summary.EffectiveCpu
 $clusterMem = $cluster.Summary.EffectiveMemory # In Mb

 $from = (get-date).AddDays(-1).Date
 $to = $from.AddDays(1).AddMinutes(-1)


 Get-Cluster | % {
   $clusterName = $_.Name
   $_ | Get-VM | % {
  # 30/120/1 day
  $cpu = $_ | Get-Stat -Stat cpu.usagemhz.average -IntervalMins 120 -Start $from -Finish $to
  $mem = $_ | Get-Stat -Stat mem.consumed.average -IntervalMins 120 -Start $from -Finish $to # In Kb

  
   for($i=0; $i -lt $mem.Count; $i++){
       $row = "" | select VM, Timestamp, CPUperc, Memperc
       $row.VM = $cpu[$i].Entity.Name
       $row.Timestamp = $cpu[$i].Timestamp
       $row.CPUperc = "{0:N2}" -f ($cpu[$i].Value / $clusterCPU * 100)
       $row.Memperc = "{0:N4}" -f ($mem[$i].Value / $clusterMem * 100 / 1Kb)
 
       $conn = New-Object System.Data.SqlClient.SqlConnection
       $conn.ConnectionString = "Server=DB200\DW01;Database=PERF_WEB;Integrated Security=False;UID=stats;Password=MyPassword"
       $cmd =  New-Object System.Data.SqlClient.SqlCommand
       $cmd.CommandTimeout = 60
      
       $conn.Open()
       $cmd.Connection = $conn

       $cmd.CommandText ="INSERT VMClusterCounterData VALUES (@MachineName, @CounterDateTime, @CPUPerCluster, @MemPerCluster)"
 
       $vmParam = $cmd.CreateParameter()
       $vmParam.ParameterName = "@MachineName";
       $vmParam.Value = $row.VM
       $cmd.Parameters.Add($vmParam)

       $timestampParam = $cmd.CreateParameter()
       $timestampParam.ParameterName = "@CounterDateTime";
       $timestampParam.Value = $row.Timestamp
       $cmd.Parameters.Add($timestampParam)
 
       $cpuParam = $cmd.CreateParameter()
       $cpuParam.ParameterName = "@CPUPerCluster";
       $cpuParam.Value = $row.CPUperc
       $cmd.Parameters.Add($cpuParam)  
 
       $memParam = $cmd.CreateParameter()
       $memParam.ParameterName = "MemPerCluster";
       $memParam.Value = $row.Memperc
       $cmd.Parameters.Add($memParam)
 
       #execute query
       $cmd.ExecuteNonQuery()
      
       #close connection
       $conn.Close()
  }
 }
}

disconnect-viserver -confirm:$false