Ok, I think I have it. This is not an easy task. I had to use PsCustomObject and a hash table to marry the stats. I've run this against 12 clusters and the data looks great.
##Usage: Use "connect-viserver <vcenter/esxhost>" first.
##Enter either one VM OR one Clustername.
##
#Get variable for single VM or arrary for all VMs in a cluster. Comment out to test with line 14.
Write-Host "Choose one: VM or Cluster name"
$readVM= Read-Host "Enter VM name [blank]"
$readClus= Read-Host "Enter Cluster name [blank]"
if ($readVM -ne "") {$vms=Get-VM -Name $readVM | Where {$_.PowerState -eq "PoweredOn"}}
else {$vms=Get-VM -Location $readClus | Where {$_.PowerState -eq "PoweredOn"}}
#$vms = Get-VM | select -First 5 #Test
#Object to populate with stats elements.
$allmetrics = @()
#Set variables for peak load hours and days.
$todayMidnight = (Get-Date -Hour 0 -Minute 0 -Second 0).AddMinutes(-1) #Change start of day to midnight.
$workingDays = "Monday","Tuesday","Wednesday","Thursday","Friday"
$dayStart = New-Object DateTime(1,1,1,9,0,0) # 9:00 AM
$dayEnd = New-Object DateTime(1,1,1,23,59,0) # 11:59 PM
#Set variable for metrics found in get-stattype
$metrics="cpu.usage.average", "cpu.usagemhz.average", "mem.usage.average", `
"cpu.ready.summation", "cpu.idle.summation", "cpu.costop.summation"
#Store desired metrics
$stats = Get-Stat -Entity $vms -start $todayMidnight.AddDays(-20) -Finish $todayMidnight.AddDays(-1) -MaxSamples 10000 `
-stat $metrics
#Filter stats for peak hours
$stats | Where-Object {
$workingDays -contains $_.Timestamp.DayOfWeek -and
$_.Timestamp.TimeOfDay -gt $dayStart.TimeOfDay -and
$_.Timestamp.TimeOfDay -lt $dayEnd.TimeOfDay} | `
#Put array of filtered stats on to pipeline and declare custom stats group object with desired field names
Group-Object -Property Entity | %{
$vmstat = "" | Select Name, "MemMax%", "MemAvg%", "MemMin%", "CPUMax%", "CPUAvg%", "CPUMin%", CPUMaxMhz, CPUAvgMhz, CPUMinMhz, `
"CPURdyMax%", "CPURdyAvg%", "CPURdyMin%", "CPUctspMax%", "CPUctspAvg%"
#Define metrics arrays for various metric types with various value types
$cpu = $_.Group | where {$_.MetricId -eq "cpu.usage.average"} | Measure-Object -Property value -Average -Maximum -Minimum
$cpuMhz = $_.Group | where {$_.MetricId -eq "cpu.usagemhz.average"} | Measure-Object -Property value -Average -Maximum -Minimum
$mem = $_.Group | where {$_.MetricId -eq "mem.usage.average"} | Measure-Object -Property value -Average -Maximum -Minimum
$cpuRdy = $_.Group | where {$_.MetricId -eq "cpu.ready.summation"}| Measure-Object -Property value -Average -Maximum -Minimum
$cpuCtsp = $_.Group | where {$_.MetricId -eq "cpu.costop.summation"}| Measure-Object -Property value -Average -Maximum -Minimum
#Define properties based on desired field names in custom stats object with input from stats arrays.
$vmstat.Name = $_.name
$vmstat.CPUMaxMhz = [int]$cpuMhz.Maximum
$vmstat.CPUAvgMhz = [int]$cpuMhz.Average
$vmstat.CPUMinMhz = [int]$cpuMhz.Minimum
$vmstat."CPUMax%" = [int]$cpu.Maximum
$vmstat."CPUAvg%" = [int]$cpu.Average
$vmstat."CPUMin%" = [int]$cpu.Minimum
$vmstat."CPURdyMax%" = [Math]::ROUND(($cpuRdy.Maximum/72000),2) #Calculation based on 2 hour sample size. Rounnd 2 decimal places.
$vmstat."CPURdyAvg%" = [Math]::ROUND(($cpuRdy.Average/72000),2)
$vmstat."CPURdyMin%" = [Math]::ROUND(($cpuRdy.Minimum/72000),2)
$vmstat."CPUctspMax%" = [Math]::ROUND(($cpuCtsp.Maximum/72000),2)
$vmstat."CPUctspAvg%" = [Math]::ROUND(($cpuCtsp.Average/72000),2)
$vmstat."MemMax%" = [int]$mem.Maximum
$vmstat."MemAvg%" = [int]$mem.Average
$vmstat."MemMin%" = [int]$mem.Minimum
#Append all properties to primary object
$allmetrics += $vmstat
}
#Get vminfo
$vminfo = $vms | select Name, VMHost, NumCpu, MemoryMB, `
@{N="Cluster";E={get-cluster -vmhost $_.vmhost}},
@{N="Datastores";E={$_|Get-Datastore}}
#Set date for csv output.
$date=get-date -uformat %m-%d-%Y
#Add all values to hash table and join them
$allvalues = @{}
$vminfo | ForEach-Object { $allvalues.Add($_.Name, $_) }
$allmetrics | ForEach-Object {
[PsCustomObject]@{
Name = $allvalues[$_.Name].Name;
VMHost = $allvalues[$_.Name].VMhost;
NumCpu = $allvalues[$_.Name].NumCpu;
MemoryMB = $allvalues[$_.Name].MemoryMB;
Cluster = $allvalues[$_.Name].Cluster;
Datastores = $allvalues[$_.Name].Datastores;
"CPUMax%" = $_."CPUMax%";
"CPUAvg%" = $_."CPUAvg%";
"CPUMin%" = $_."CPUMin%";
CPUMaxMHZ = $_.CPUMaxMHZ;
CPUAvgMHZ = $_.CPUAvgMHZ;
CPUMinMHZ = $_.CPUMinMHZ;
"MemMax%" = $_."MemMax%";
"MemAvg%" = $_."MemAvg%";
"MemMin%" = $_."MemMin%";
"CPURdyMax%" = $_."CPURdyMax%";
"CPURdyAvg%" = $_."CPURdyAvg%";
"CPURdyMin%" = $_."CPURdyMin%";
"CPUctspMax%" = $_."CPUctspMax%";
"CPUctspAvg%" = $_."CPUctspAvg%";
}
} | `
#Export to CSV file.
Export-Csv .\vmstats.extended.20days.$readVM$readClus.$date.csv -NoTypeInformation -UseCulture
#Open CSV document when complete.
Invoke-item .\vmstats.extended.20days.$readVM$readClus.$date.csv
