Problem with get-stat command and scripting

[picard1701]

Hi,

I'm currently trying to write a simple script that could get resource pool statistics, but having problems with it.

I am using vSphere 4.0, vCenter Server 4.0, ESX 4.0(update 4), PowerCLI 5.1 release2

I wanted to get the statistics that belonged to Cluster1 so I put the following variable.

$rp1 = Get-ResourcePool -Location Cluster1

next, I wanted to get the cpu statistics of the resource pools so I put the following variable.

$metrics_rp = "cpu.usagemhz.average","cpu.usagemhz.maximum"

next. I put the get-stat cmdlet into the following variable

$rpstats = Get-Stat -Entity ($rp1) -Stat $metrics_rp -Start $midnight.adddays(-1).addminutes(-1) -Finish $midnight.addminutes(-1) | Select-Object timestamp,value,unit,metricid,entity

finally, I would get the statistics and convert it into a csv file

$rpstats | Export-Csv C:\stats\rp_stats.csv

Now the problem is when I try to run this, the script takes a very long time to complete.

or when I try to do this for multiple clusters, it takes forever or it does not even complete.

(for multiple cluster i would just add more variables e.g. $rp2 = Get-ResourcePool -Location Cluster2)

It seems that it takes time when I make the get-stat command into a variable.

I have 2 two clusters hanging on my vCenter server and about 15 resource pools on each cluster.

I would appreciate it if someone could tell me a better way to this or if I'm doing this wrong.

Thanks in advance.

[LucD]

I would try to limit the script to 1 call to Get-Stat.

For that you would need to get all the resourcepools into 1 variable, for example

The resulting metrics will then be for all resourcepools, but you can easily split the results up per resourcepool by using the Group-Object cmdlet.

For example

$metrics_rp = "cpu.usagemhz.average","cpu.usagemhz.maximum" 
$rp = Get-Cluster -Name cluster1,cluster2 | Get-ResourcePool

Get-Stat -Entity $rp -Stat $metrics_rp -Start $midnight.adddays(-1).addminutes(-1) -Finish $midnight.addminutes(-1) | 
Group-Object -Property {$_.Entity.Name} | %{
  $_.Group |
  Select-Object timestamp,value,unit,metricid,entity |
  Export-Csv C:\stats\$($_.Group[0].Entity.Name)_stats.csv
}

This will produce a CSV file per resourcepool

[picard1701]

LucD, thank you for taking the time to help me.

i ran the script you wrote and it produced a CSV file per resource pool. Thank you.

please excuse my ignorance but im new to this, is there a way to obtain the average and the maximum value too? while still exporting it to a csv file per resource pool?

I know I am supposed to use the measure-object cmdlet but i am not sure how to incorportate it into the script...

[LucD]

Do you mean the average and maximum value for the complete interval ?

And should the individual metrics per interval also be included in the CSV files ?

[picard1701]

LucD,

Sorry for not explaining well.

To answer your question, the first one is yes but the second one is might not be necessarily because what I am trying to achieve is the following.

1. Obtain the average value of the “cpu.usagemhz.average” and the maximum value of  the “cpu.usagemhz.maximum” of the complete interval for one day. Export it into a csv file.
2. repeat this until I have 1 month of average and maximum value data.
3. make a chart so I could report it.

[LucD]

No problem, try it like this

$midnight = Get-Date -Hour 0 -Minute 0 -Second 0
$metrics_rp = "cpu.usagemhz.average","cpu.usagemhz.maximum" 
$rp = Get-Cluster -Name cluster1,cluster2 | Get-ResourcePool

Get-Stat -Entity $rp -Stat $metrics_rp -Start $midnight.adddays(-1).addminutes(-1) -Finish $midnight.addminutes(-1) | 
Group-Object -Property {$_.EntityId} | %{
  $cluster =   &{
    $parent = $_.Group[0].Entity.Parent
    while ($parent -and  $parent.ExtensionData -isnot [VMware.Vim.ClusterComputeResource]){
      $parent = $parent.Parent
    }
    $parent
  }
  Select-Object -InputObject $_ -Property @{N="Interval start";E={$midnight.adddays(-1)}},
    @{N="Resource Pool";E={$_.Group[0].Entity.Name}},
    @{N="Cluster";E={$cluster.Name}},
    @{N="CPU MHz Avg";E={$_.Group | Where {$_.MetricId -eq "cpu.usagemhz.average"} | 
      Measure-Object -Property Value -Average | Select -ExpandProperty Average}},
    @{N="CPU MHz Max";E={$_.Group | Where {$_.MetricId -eq "cpu.usagemhz.maximum"} | 
      Measure-Object -Property Value -Maximum | Select -ExpandProperty Maximum}} |
  Export-Csv C:\stats\$($cluster.Name)-$($_.Group[0].Entity.Name)_stats.csv
}

[picard1701]

LucD, thank you for helping with me,

but the script you offered only got me blank results.

I will post a sample result with a attach file.

[LucD]

Must have had an off-day yesterday, that script was indeed incorrect.:smileycry:

I updated the script above, please try again.

Note that I added the clustername in the CSV filename to cope with duplicate resourcepool names.

[picard1701]

Hi, sorry for not responding for so long. I am the same person questioning about the following topic.

http://communities.vmware.com/thread/446565

The script you offered above worked great and I left it for a month. After a month I started gathering the results.

However the results I got was what I was mentioning on the other thread.

Is there a way to incorporate what I want to do here on your "get-stat2" script?

If not, is there a way to divide the results by two in the script above, so that I can get an approximate data?

[LucD]

See my reply to the other thread

[picard1701]

Last time you showed me how to get the average and maximum stats of the complete interval for one day.

I was looking at your blog and saw the post below about custom sampling periods.

PowerCLI & vSphere statistics – Part 2 – Come together | LucD notes

If I wanted the same thing above but a data of 1 hour intervals for one whole day, is it possible to combine the two scripts?

Thanks.

[LucD]

Try something like this

$midnight = Get-Date -Hour 0 -Minute 0 -Second 0
$metrics_rp = "cpu.usagemhz.average","cpu.usagemhz.maximum"
$rp = Get-Cluster -Name cluster1,cluster2 | Get-ResourcePool

Get-Stat -Entity $rp -Stat $metrics_rp -Start $midnight.adddays(-1).addminutes(-1) -Finish $midnight.addminutes(-1) |
Group-Object -Property {$_.EntityId} | %{
  $cluster =   &{
    $parent = $_.Group[0].Entity.Parent
    while ($parent -and  $parent.ExtensionData -isnot [VMware.Vim.ClusterComputeResource]){
      $parent = $parent.Parent
    }
    $parent
  }
  $report = @()
  $_.Group | Group-Object -Property {$_.Timestamp.Hour} | %{
    $row = "" | Select "Interval Start","Resource Pool",Cluster,"CPU MHz Avg","CPU MHz Max"
    $row."Interval Start" = $_.Group | Sort-Object -Property Timestamp |
      Select -First 1 | Select -ExpandProperty Timestamp
    $row.Cluster = $cluster.Name
    $row."Resource Pool" = $_.Group[0].Entity.Name
    $row."CPU MHz Avg" = $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.average"} |
      Measure-Object -Property Value -Average | Select -ExpandProperty Average
    $row."CPU MHz Max" = $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.maximum"} |
      Measure-Object -Property Value -Maximum | Select -ExpandProperty Maximum
    $report += $row
  }
  $report | Export-Csv C:\stats\$($cluster.Name)-$($_.Group[0].Entity.Name)_stats.csv
}

[picard1701]

Thanks for the script!

I'm getting very close to what I need.

Up to here, the script you offered gave me the hourly stats average of  the "cpu.usage.average" and the average of the "cpu.usage.maximum".

(I made a mistake by saying I wanted the maximum of the "cpu.usage.maximum" when what I really wanted was the average of the "cpu.usage.maximum".

So I changed the part like below.)

This what I was asking before

$row."CPU MHz Avg" = $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.average"} |
      Measure-Object -Property Value -Average | Select -ExpandProperty Average
    $row."CPU MHz Max" = $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.maximum"} |
      Measure-Object -Property Value -Maximum | Select -ExpandProperty Maximum

I changed the script to the following to get the average of the "cpu.usage.maximum"

$row."CPU MHz Avg" = $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.average"} |
      Measure-Object -Property Value -Average | Select -ExpandProperty Average
    $row."CPU MHz Max" = $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.maximum"} |
      Measure-Object -Property Value -Average | Select -ExpandProperty Average

So this gives me 24 stat results per day.

While this is great, what I finally need is the average of the 24 hourly average stats results and the maximum of the 24 hourly maximum stats results.

One average stat and one maximum stat of the day.

Is this possible?

[LucD]

Sorry, but I'm confused now.

The version at the beginning gave 1 Avg and 1 Max value over 24 hours.

Is that what you are looking for ?

[picard1701]

Sorry I know it’s confusing.

Okay here's the deal. Please try to bear with me.

Right now the vCenter Server collects data at an interval duration of 5 minutes.

I have set the vCenter Server to save that data for 5 days. The statistic level is 4.

Yes, as you said I want 1 Avg and 1 Max value over 24 hours. That doesn’t change.

The first script you offered worked just fine for the average value, but when it comes to max value I noticed that getting 1 max value data from a 5 minute interval duration over 24 hours wasn’t going to be an accurate statistic (for example if some process used 95% of the cpu for only 20 seconds in a day and the rest of the day was only 5%, the first script would still pickup the 95% usage. And that wouldn’t be an accurate statistic)

So for the max value I decided that it would be better to first average the data hourly.

This means I would want the following.

The average of “5min duration data * 12 times” of cpu.usage.maximum

And after 24 hours you would get 24 max value results. (one per hour.)

I think the second script you offered did just that.

Finally in order for me to get 1 max value of the day, I would want the max value within those 24 max value results. And that would be the max value over 1 day (24 hours).

After collecting 1 month of this, I was thinking of using the Import-Csv command to combine all the csv files into one so that I could make a chart..

I’m sorry my explanation is no so good….

If you don’t understand please ask me where.

[LucD]

Ok, I think I see what you want.

Try this version

$midnight = Get-Date -Hour 0 -Minute 0 -Second 0
$metrics_rp = "cpu.usagemhz.average","cpu.usagemhz.maximum"
$rp = Get-Cluster -Name MyCluster | Get-ResourcePool

Get-Stat -Entity $rp -Stat $metrics_rp -Start $midnight.adddays(-1).addminutes(-1) -Finish $midnight.addminutes(-1) |
Group-Object -Property {$_.EntityId} | %{
  $cluster = &{
    $parent = $_.Group[0].Entity.Parent
    while ($parent -and $parent.ExtensionData -isnot [VMware.Vim.ClusterComputeResource]){
      $parent = $parent.Parent
    }
    $parent
  }
  $hourAvg = @()
  $hourMax = @()

  $_.Group | Group-Object -Property {$_.Timestamp.Hour} | %{
    $hourAvg += $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.average"} |
    Measure-Object -Property Value -Average | Select -ExpandProperty Average
    $hourMax += $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.maximum"} |
    Measure-Object -Property Value -Maximum | Select -ExpandProperty Maximum
  }
  $report = @()
  $row = "" | Select "Interval Start","Resource Pool",Cluster,"CPU MHz Avg","CPU MHz Max"
  $row."Interval Start" = $_.Group | Sort-Object -Property Timestamp |
  Select -First 1 | Select -ExpandProperty Timestamp
  $row.Cluster = $cluster.Name
  $row."Resource Pool" = $_.Group[0].Entity.Name
  $row."CPU MHz Avg" = $hourAvg | Measure-Object -Average |
    Select -ExpandProperty Average
  $row."CPU MHz Max" = $hourMax |  Measure-Object -Maximum |
    Select -ExpandProperty Maximum
  $report += $row
  $report | Export-Csv C:\$($cluster.Name)-$($_.Group[0].Entity.Name)_stats.csv
}

Note that there will be 1 CSV file per resourcepool. Is that what you want ?

[picard1701]

Thanks for helping.

I tried the above script you offered but unfortunately did not get what I want for the max value.

I attached 2 excel files in order for you to better understand what I need.

"stats1.xls" is a result of the previous script you offered (the one that gets 24 stat results). I added some explanation to it.

"stats2.xls" is the result of the script above. Both data are from the same date.

Which means the results should be the same, however the final max value is not.

https://www.dropbox.com/s/2gnwfwpgf9ot103/stats1and2.zip

>Note that there will be 1 CSV file per resourcepool. Is that what you want ?

If it is possible to put multiple resourcepool result in 1 CSV file, that would be super helpful (I wouldn't have to go through many csv files)

[picard1701]

Hi,

Were you able to get a look at the excel file i posted?

[LucD]

There was a typo in the script. This line

$hourMax = $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.maximum"} |

should have been like this

$hourMax += $_.Group | Where {$_.MetricId -eq "cpu.usagemhz.maximum"} |

The script above has been corrected.

[picard1701]

Thank you for your help LucD!

Your script got me what I wanted.

Now all I have to do is gather the files.

As you mentioned the script you offered gives me 1 csv file per 1 resource pool a day.

When I have collected 1 month of data, I would want to organize this into 1 monthly csv file per 1 resource pool.

To do that I was thinking of the following.

$rpname = "rp1"

$folder = "C:\powercli\stats"

dir C:\powercli\stats *$rpname* | Import-Csv |

Where-Object {$_."Resource Pool" -match "$rpname"}|

Sort-Object "Interval Start" -Descending |

Export-Csv -Path $folder\$rpname.csv -NoTypeInformation

The script above imports all the csv files that has the name rp1 and then exports it again with the resource pool name for the file name, Which is good.

However I would have to make a script for each resource pool and execute them over and over.

I wanted to know if it was possible to loop this script for multiple resource pools (e.g. rp1, rp2, rp3, rp4......)

I assumed I would use the "foreach-object" cmdlet but I am not sure how to.

Thank you in advance.

[LucD]

What is the layout of the name you used for the daily CSV files ?

Since that filename contains the name of the resourcepool, we could use the Group-Object to group the CSV files that belong together.

For example, assume the CSV filenames are following this layout: cluster-rp1-date.csv

Then you could do something like this

$folder = "C:\powercli\stats"

Get-ChildItem -Path $folder -Filter "*.csv" |
Group-Object -Property {$_.Name.Split('-')[1]} | %{
  $monthArray = @()
  $_.Group | %{
    $monthArray += Import-Csv -Path "$folder\$($_.name)"
  }
  $rp = $monthArray[0]."Resource Pool"
  $monthArray | Export-Csv -Path "$folder\$($rp)-Month.csv" -NoTypeInformation -UseCulture
}

[picard1701]

The layout of the daily csv file will be "Clustername-ResourcePoolname-Date.csv"

so something like "cluster1-rp1-20130811.csv"

I tried the script above but resulted in error.

Below is the error I got (sorry some parts are in Japanese...)

Import-Csv : ファイル "C:\powercli\stats\" を開けません。

発生場所 C:\test_scripts\gather9.ps1:7 文字:30

+     $monthArray += Import-Csv <<<<  -Path "$folder\$name"

    + CategoryInfo          : OpenError: (:) [Import-Csv]、DirectoryNotFoundException

    + FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand

The error is saying that it could not open file "C:\powercli\stats" when doing the Import-Csv.

[LucD]

I made a typo in the name part of the CSV file on the Import-Csv cmdlet.

I corrected it, can you try again ?

[picard1701]

It worked! Thank you.

Turns out that I had forgotten I need not only resource pool data but also ESX-host data as well.

What would the script be like if ESX daily stat csv files were in the same folder as the resource pool daily stat csv files?

The layout the daily ESX stat file would be like "Cluster1-ESXhost1-20130814.csv"

[mikedylan]

Hi Luc:

If we wanted to look at some other merics like memory utilization from the cluster level to host, how would we get the names? I used these:

$metrics_rp = "cpu.usage.average","mem.usage.average"

I get this error:

ResourceUnavailable: (cpu.usage.average:String)

[LucD]

The metrics are documented under the PerformanceManager entry in the SDK Reference.

In my Dutch VMUG: The Statistics Reporting Session blog post I included a script that looks on the vSphere server and reports what counters are actually there.

That is a strange error you are getting.

How did you call the Get-Stat cmdlet ?