Daily Host averages for a seven day period?

[wee_rabbit]

Hi, All.

Just under a week ago I hadn't really touched Powershell/PowerCLI other than working with Windows Server Core. After a request for VI host metrics from my boss and thanks to help from the forum and its members, I am now getting my head around things and have two scripts running as scheduled tasks that gather on the fly / one week average information for our ESX / ESXi hosts.

I have hit a bit of a brick wall though; he also wants me to collect basic rolling data for the hosts on a daily basis and I've been trying to put together a script that I can run once a week, that collects daily averages, per day, for seven days. At the very basic of levels, let's say that I have two hosts and I want to collect the CPU average per day, for seven days, which would look something like:

HOST    DAY     CPU AVERAGE

host1     day1     average value

host1     day2     average value

host1     day3     average value

host1     day4     average value

host1     day5     average value

host1     day6     average value

host1     day7     average value

host2     day1     average value

etc

I simply cannot get me head around this (and after a week, it's not through lack of trying or trawling of forums) and would appreciate any and all help. I also need memory and network averages but if someone can help with the above, I should be able to add to the code to include these items.

TIA,

Carl

[LucD · Accepted solution]

The Group-Object cmdlet will do that for you.

Use the "day" part of the timestamp to group your statistical data per day/per host.

This is an example

$esxImpl = Get-VMHost 
$todayMidnight = (Get-Date -Hour 0 -Minute 0 -Second 0).AddMinutes(-1)

$stats = Get-Stat -Entity $esxImpl -Stat cpu.usage.average -Start $todayMidnight.AddDays(-2) -Finish $todayMidnight
 $groups = $stats | where {$_.Instance -eq ""} | Group-Object -Property {$_.Timestamp.Day, $_.Entity.Name}
$report = $groups | % {
  New-Object PSObject -Property @{
    Description = $_.Group[0].Description
    Entity = $_.Group[0].Entity
    MetricId = $_.Group[0].MetricId
    Timestamp = $_.Group[0].Timestamp.ToShortDateString()
    Unit = $_.Group[0].Unit
    Value = [math]::Round(($_.Group | Measure-Object -Property Value -Average).Average, 2)
  }
}
$report | Export-Csv "C:\Daily-cpu.csv" -NoTypeInformation -UseCulture 

[wee_rabbit]

Works like a charm! Thank you so much for your help.

I've read a lot of your forum posts - could I just say that the manner, speed and thoroughness of your replies is a credit to yourself and the forum. Keep up the good work; again, your help is very much appreciated.

ATB,

Carl

[wee_rabbit]

I thought that amending to include mem.usage.average would be a doddle but I'm struggling again - how would you capture mem.usage average as well as cpu.usage. average with this script, please?

[LucD]

You can leave the grouping the same, just extract the CPU and memory statistics seperately.

Something like this

$esxImpl = Get-VMHost  
$todayMidnight = (Get-Date -Hour 0 -Minute 0 -Second 0).AddMinutes(-1)
$metrics = "cpu.usage.average","mem.usage.average"

 $stats = Get-Stat -Entity $esxImpl -Stat $metrics -Start $todayMidnight.AddDays(-2) -Finish $todayMidnight
$groups = $stats | where {($_.MetricId -eq "cpu.usage.average" -and $_.Instance -eq "") -or $_.MetricId -eq "mem.usage.average"} | 
  Group-Object -Property {$_.Timestamp.Day, $_.Entity.Name}
$report = $groups | % {
  New-Object PSObject -Property @{
    Description = $_.Group[0].Description
    Entity = $_.Group[0].Entity
    MetricId = $_.Group[0].MetricId
    Timestamp = $_.Group[0].Timestamp.ToShortDateString()
    Unit = $_.Group[0].Unit
    CpuAvg = [math]::Round(($_.Group | where {$_.MetricId -eq "cpu.usage.average"} | Measure-Object -Property Value -Average).Average, 2)
    MemAvg = [math]::Round(($_.Group | where {$_.MetricId -eq "mem.usage.average"} | Measure-Object -Property Value -Average).Average, 2)
  }
}
$report | Export-Csv "C:\Daily-cpu.csv" -NoTypeInformation -UseCulture 

[wee_rabbit]

Perfect! A thousand thanks, once again.