Automation

I'm using get-stat to get two different metrics for a cluster and I want to export them to a CSV file in a certain format. I'm getting CPU in Mhz and % and by default it outputs it in the following format:

But in order for it to be usable I need it in the following format:

Is there a way to control the manner in which it creates the CSV?

Thanks in advance

Perhaps a stupid question, but why don't you get the cpu.usage.average counter, that is already in % ?

I'm getting the average and the mhz counters. It's the order I need them in when I open the CSV in Excel. I'll generate a graph from it then. Needs to be PowerShell 2.0 (I saw your Excel function in the other post).

EDIT - notice how in my first post there's two tables. In the first table it lists the two counters in one column. In the second it lists them in a column each with the timestamp in a single column and not repeated.

Ok, I see.

But how are you getting those 2 columns ?

Are these from 2 separate calls to Get-Stat ?

I'm using the following the code at the moment:

$WarningPreference = "SilentlyContinue"

$metrics = "cpu.usagemhz.average","cpu.usage.average"

$start = (get-date).AddDays(-7)

$finish = get-date

$stats = get-stat -Entity (get-cluster *) -Stat $metrics -Start $start -Finish $finish -Interval 86400

$groups = $stats | Group-Object -Property {$_.TimeStamp, $_.MetricId, $_.Value, $_.Unit, $_.Entity}

$report = $groups | % {

  New-Object PSObject -Property @{

  'TimeStamp' = $_.Group[0].TimeStamp

  'MetricId' = $_.Group[0].MetricId

  'Value' = $_.Group[0].Value

  'Unit' = $_.Group[0].Unit

  'Entity' = $_.Group[0].Entity

  }

}

$report | Select TimeStamp, Value, Unit | Export-Csv "C:\out.csv" -NoTypeInformation

This gives me this:

but I need to seperate the metrics into seperate columns like in the second table. I ran that off in excel to get an idea of what I need.

Try like this

$WarningPreference = "SilentlyContinue"
$metrics = "cpu.usagemhz.average","cpu.usage.average"
$start = (get-date).AddDays(-7)
$finish = get-date
$stats = get-stat -Entity (get-cluster *) -Stat $metrics -Start $start -Finish $finish -Interval 86400


$groups = $stats | Group-Object -Property {$_.TimeStamp,$_.Entity.Name}
$report = $groups | % {
  New-Object PSObject -Property @{
  'TimeStamp' = $_.Group[0].TimeStamp
  'Entity' = $_.Group[0].Entity.Name
  'MHz' = $_.Group | where {$_.MetricId -eq "cpu.usagemhz.average"} | Select -ExpandProperty Value
  '%' = $_.Group | where {$_.MetricId -eq "cpu.usage.average"} | Select -ExpandProperty Value
  }
}

$report | Select TimeStamp, Entity, MHz, '%' |
Export-Csv "C:\out.csv" -NoTypeInformation