get-stats - converting stats to a table like old VCenter 6 did when exporting from console

[trichaun64]

So I need to take get-stats output and format it like Vcenter used to when using the GUI so our reporting can be automated (currently it's a manual download task across 11 different VCs).  I can't quite figure out how to PowerShell it. 

Here's an example of what I want:

Here's what I'm doing:

$CPUstats = 'cpu.usagemhz.average','cpu.usage.average'
$start = (Get-Date).AddDays(-1)
$results=Get-Stat -Entity $cluster -Stat $CPUstats -Start $start -IntervalMins 120 -MaxSamples 5 |  sort-object timestamp | Select-Object timestamp,value,unit,metricid
$results | ft

which spits out:

As you can see I've got multiple timestamps and I want to put cpu.usagemhz.average and cpu.usage.average  result for each matching timestamp in one row (not column).  Just not sure how to PowerShell that (or if there's a way with get-stat that I'm not seeing?).

Any help greatly appreciated.  

[LucD]

You can use the Group-Object cmdlet.
Something like this for example

$CPUstats = 'cpu.usagemhz.average','cpu.usage.average'
$start = (Get-Date).AddDays(-1)
Get-Stat -Entity $cluster -Stat $CPUstats -Start $start -IntervalMins 120 -MaxSamples 5 |  
Group-Object -Property Timestamp |
ForEach-Object -Process {
    New-Object -TypeName PSObject -Property ([ordered]@{
        Timestamp = $_.Name
        CPUusage = $_.Group | Where-Object {$_.MetricId -eq 'cpu.usage.average'} | Select-Object -ExpandProperty Value
        CPUusagemhz = $_.Group | Where-Object {$_.MetricId -eq 'cpu.usagemhz.average'} | Select-Object -ExpandProperty Value
    })
} |
Sort-Object -Property timestamp

[trichaun64]

Thanks LucD.  That gave me blank values so not sure what's missing (get-stat produces the output though).  

I took your example and did this which works for me:

$CPUstats = 'cpu.usagemhz.average','cpu.usage.average'
$start = (Get-Date).AddDays(-1)
$results=Get-Stat -Entity $cluster -Stat $CPUstats -Start $start -IntervalMins 120 -MaxSamples 5 |  sort-object timestamp | Select-Object timestamp,value,unit,metricid
$results | ft

$results | Group-Object -Property TimeStamp | Select `
  @{ n="Time"; e={$_.Name} },
  @{ n="cpu.usagemhz.average"; e={ ($_.Group | ? {$_.MetricId -eq "cpu.usagemhz.average"}).Value } },
  @{ n="cpu.usage.average"; e={ ($_.Group | ? {$_.MetricId -eq "cpu.usage.average"}).Value } }

Thanks for your help 🙂 

[trichaun64]

Ah I figured it out.... .MetricId not .name 😀

  $CPUstats = 'cpu.usagemhz.average','cpu.usage.average'
$start = (Get-Date).AddDays(-1)
Get-Stat -Entity $cluster -Stat $CPUstats -Start $start -IntervalMins 120 -MaxSamples 5 |  
Group-Object -Property Timestamp |
ForEach-Object -Process {
    New-Object -TypeName PSObject -Property ([ordered]@{
        Timestamp = $_.Name
        CPUusage = $_.Group | Where-Object {$_.MetricId -eq 'cpu.usage.average'} | Select-Object -ExpandProperty Value
        CPUusagemhz = $_.Group | Where-Object {$_.MetricId -eq 'cpu.usagemhz.average'} | Select-Object -ExpandProperty Value
    })
} |
Sort-Object -Property timestamp

[LucD]

Oops, I corrected the typo above.