Dear Experts,
I need to automatically generate a monthly performance report on the average and the maximum usage of the resource pool cpu, using PowerCLI.
I am new to this (PowerCLI and Powershell) so I might not be making much sense but here is my environment and what I want to do.
Environment
In my environment there is 1 vCenter Server, 3 clusters, about 5 resource pools on each cluster and several virtual machines. Lets say that the names of each cluster and resource pools are “cluster1, cluster2, cluster3” and “rp1, rp2, rp3…rp15”.
The statistic level on the vCenter Server is level 4 (all metrics) and 5 minute interval data is saved in the vCenter Server for 5 days. I need to retrieve data for the following counters.
“cpu.usagemhz.average" and "cpu.usagemhz.maximum" in a 5 minute interval. I am only interested in the aggregated data.
What I want to do
- Retrieve data on a daily bases and export it to a csv file. (so that I can get a 5 minute interval data for one whole day)
- I only want resource pool cpu data which belongs to cluster1 and cluster2. not cluster3.
- When retrieving the data I want the “timestamp, value, unit, metricid, and entity” and sort it by timestamp in a descending order, but I don’t want all the metricid’s and entities to be mixed up either. (so I want all the cpu.usagemhz.average on rp1 in a timestamp descending order first and then all the cpu.usagemhz.maximum on rp1 in a timestamp descending order next, and then the same for rp2). something like below.
| Header 1 | Header 2 | Header 3 | Header 4 | Header 5 |
|---|---|---|---|---|
| TimeStamp | Value | Unit | MetricId | Entity |
| 2013/3/7 00:00 | 293 | MHz | cpu.usagemhz.average | rp1 |
| 2013/3/7 00:05 | 279 | MHz | cpu.usagemhz.average | rp1 |
| 2013/3/7 00:10 | 300 | MHz | cpu.usagemhz.average | rp1 |
| 2013/3/7 00:00 | 500 | MHz | cpu.usagemhz.maximum | rp1 |
| 2013/3/7 00:05 | 550 | MHz | cpu.usagemhz.maximum | rp1 |
| 2013/3/7 00:10 | 600 | MHz | cpu.usagemhz.maximum | rp1 |
- Since resource pool cpu statistics aren’t able to retrieve data in percentage and only in Mhz, I need to convert (or calculate) the Mhz into percentage. Is this possible if I knew the total available MHz capacity on the cluster? (for instance if cluster1 total was 3000Mhz and cluster2 total was 4000MHz)
- Calculate the overall average of the Mhz data and the percentage data of 1 whole day.
- When exporting it to a csv file, is it possible to make a csv file with multiple work sheets? I would like the csv file to be one resource pool data per work sheet if possible.
- Finally, I would need to generate a bar chart which would contain both percentage and MHz which would look something like this.
- This is an additional task. After retrieving a month full of 5 minute interval data for “cpu.usagemhz.average”, is it possible to get an overall average of all the data that was taken? From day 1 to day30 (or31) So that I can eventually make a report in a quarter bases? (eventually a whole year) the chart would look something like this.
All of this doesn’t have to be all in one script. More automation the better.
Any help will be appreciated. Thanks!
I will attach files for better chart view.