How to format get-stat output on multiple metrics

[luketheyeti]

Hello!

I'm trying to format the output from get-stat with multiple metrics into a format I can graph easily in Excel.  What I get from the command is in the format:

MetricId                Timestamp                          Value Unit    

--------                ---------                          ----- ----     --------

cpu.usage.average       6/19/2018 5:25:00 PM                4.77 %               

cpu.usage.average       6/19/2018 5:20:00 PM                4.66 %               

cpu.usage.average       6/19/2018 5:15:00 PM                4.76 %               

cpu.usage.average       6/19/2018 5:10:00 PM                4.34 %               

cpu.usage.average       6/19/2018 5:05:00 PM                 4.3 %               

cpu.usagemhz.average    6/19/2018 5:25:00 PM                2633 MHz             

cpu.usagemhz.average    6/19/2018 5:20:00 PM                2569 MHz             

cpu.usagemhz.average    6/19/2018 5:15:00 PM                2626 MHz             

cpu.usagemhz.average    6/19/2018 5:10:00 PM                2394 MHz             

cpu.usagemhz.average    6/19/2018 5:05:00 PM                2372 MHz  

and what I want is something like:

Timestamp	cpu.usage.average	cpu.usagemhz.average
5:25:00	4.77	2633
5:20:00	4.66	2569
5:15:00	4.76	2626
5:10:00	4.34	2394
5:05:00	4.3	2372

Can someone point me towards the cmdlets that would accomplish this?

Thank you!

[LucD · Accepted solution]

Try something like this

$esxName = 'MyEsx'

$stat = 'cpu.usage.average','cpu.usagemhz.average'

$esx = Get-VMHost -Name $esxName

Get-Stat -Entity $esx -Stat $stat -Instance '' -Realtime -MaxSamples 5 |

Group-Object -Property Timestamp | %{

   New-Object PSObject -Property ([ordered]@{

   Timestamp = $_.Name

   VMHost = $_.group[0].Entity.Name

   'cpu.usage.average' = $_.Group | where{$_.MetricId -eq 'cpu.usage.average'} | select -ExpandProperty Value

   'cpu.usagemhz.average' = $_.Group | where{$_.MetricId -eq 'cpu.usagemhz.average'} | select -ExpandProperty Value

   })

}

There are more 'grouping' examples in my Statistics posts.

[luketheyeti]

Thanks Luc,

I actually spent a decent amount of last night looking at your posts on collecting stats and I think I finally understand what's going on with your uses of Group-Object and New-Object. I'm admittedly super new to working with anything other than default objects and default object properties so this was way over my head.

Thankfully, learning to manipulate data like this is going to completely change every piece of data I work with going forward :smileygrin:

Lucas

[LucD]

Keep learning, and please come back if you have questions.