Automation

Hello All,

I'm having the hardest time creating a script that will give me Cluster CPU and Memory Usage. I just need something simple if there is such a thing?

Example of what I'm looking for:

I need to present these in graphical format but I could look to do something with a pivot table afterwards.

Any help would be greatly appreciated.

Thanks

The following will get the data into an Excel spreadheet.

It uses the Export-Excel cmdlet from the Doug's ImportExcel module.

You can change the Start date.

The script places everything in one worksheet, but it can easily be adapted to have separate worksheets for CPU and memory.

Thanks for the very quick reply LucD!

I've got Doug's ImportExcel module downloaded but I'm having issues getting it imported. We are using PowerCli 5.8. This is my error

Unable to find type [PSPlot]: make sure that the assembly containing this type is loaded.

At C:\Windows\System32\WindowsPowerShell\v1.0\Modules\ImportExcel\ImportExcel.psm1:25 char:29

+         [OutputType([PSPlot] <<<< )]

    + CategoryInfo          : InvalidOperation: (PSPlot:String) [], ParentContainsErrorRecordException

    + FullyQualifiedErrorId : TypeNotFound

Import-Module : The specified module '.\ImportExcel.psm1' was not loaded because no valid module file was found in any module directory.

At line:1 char:14

+ Import-Module <<<<  .\ImportExcel.psm1

    + CategoryInfo          : ResourceUnavailable: (.\ImportExcel.psm1:String) [Import-Module], FileNotFoundException

    + FullyQualifiedErrorId : Modules_ModuleNotFound,Microsoft.PowerShell.Commands.ImportModuleCommand

I checked the .psd1 and there wasn't a  minimum version specified

Instead of specifying the Cluster would it be possible to return all clusters in each vCenter server? or would that require a loop?

After a bit of research it looks like it doesn't like Get-Stat -Entity "Cluster"

If I replace the cluster name with the name of my vcenter server the command runs successfully.

Get-Stat -Entity 'vcenter' -Stat $stat -Start $start

works without any issues

This is the error

Get-Stat : 13/07/2016 15:24:58    Get-Stat        A specified parameter was not correct.

querySpec.size   

At line:13 char:9

+ Get-Stat <<<<  -Entity $clusterName -Stat $stat -Start $start |

    + CategoryInfo          : NotSpecified: (:) [Get-Stat], InvalidArgument

    + FullyQualifiedErrorId : Client20_RuntimeDataServiceImpl_GetStats_ErrorRetreivingPerfMetrics,VMware.VimAutomation.ViCore.Cmdlets.Commands.GetViStats

Are you by any chance on vSPhere 5.5U3 ?

Then it might be the problem described in Veeam's KB2071

Hello LucD

Get-Stat -Entity clustername is resulting nothing after upgrade of vSpehre 6 U2 even after setting adavance setting to -1

Get-AdvancedSetting -Entity YourvCenter -Name config.vpxd.stats.MaxQueryMetrics |

Set-AdvancedSetting -Value -1

Did you check the value of maxQuerySize in the file vpxd.cfg ?

Hello LucD,

Thanks for your reply.

Below is entry in vpxd.cfg file

<stats>

      <maxQueryMetrics>-1</maxQueryMetrics>

    </stats>

    <support>

Depending on which Historical Interval you are looking at, you will have to wait till the aggregation jobs have done their thing.

What interval are you looking at?

When did you change the value?

Before upgrading the VC I was able to collect up to 90 days of CPU and memory usage at cluster entity . Now I dont see results for even 2 days ..

I have changed the advance settings a day before

And do you see that Performance data in the vSphere or Web client?

Hello Luc,

I do see performance chart in webclient but no results when I run below coomand

Webclient > clutserts > select cluster name > performance > advanced > CPU I do see values available from Feb

PowerCLI C:\> Get-Cluster -Name Clustername | Get-Stat -start (get-date).AddDays(-40) -Finish (Get-Date).AddDays(-10) -MaxSamples 10000 -stat cpu.usage.average | Measure-Object -Property value -Average -Maximum -Minimum | select Average

PowerCLI C:\>

Hello LucD

I opened a support case with VMware on this . They pointing the problem with latest version of PCLI interacion with vSpehre6.

The same set of command works fine when I run against my other VC which is VCSA.

We are on vSphere 5.5U3. Unfortunately we won't be able to apply their workaround as that applies to the Veeam One application

That is indeed a Veeam KB article, but has no further relation to a Veeam product.

It's purely a vCenter issue.

Yep. It helped when I clicked through to the vmware kb rather than just reading that page.

I'm followed that in our lab and it seems to have done the trick although I'll have to revisit the Export-Excel function.

The output has displayed in 30 minute intervals. Is there a way to total these in the script as if I run this for the 30 day interval requested there will be a lot of data generated.

I really appreciate your help

If you run for a 30 day interval you will get 1 observation per day.

See my PowerCLI & vSphere statistics – Part 1 – The basics for some info on Historical Intervals.

But if want to have an average per day/per week/per month, that can be done.

Let me know what you would to have.

Hi Luc

That makes a bit more sense after reading your guide. I think I've got what I need with

Add-PSSnapin VMware.VimAutomation.Core

Connect-VIServer 'vcenter'

$clusterName = 'cluster name'

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

$entity = Get-Cluster -Name $clusterName

$start = (Get-Date).AddDays(-30)

Get-Stat -Entity $clusterName -Stat $stat -Start $start -MaxSamples 30 -IntervalMins 1440 |

Group-Object -Property Timestamp |

Sort-Object -Property Name |

Select @{N='Cluster';E={$entity.Name}},

    @{N='Time';E={$_.Group[0].Timestamp}},

    @{N='CPU GHz Capacity';E={$script:capacity = [int]($entity.ExtensionData.Summary.TotalCPU/1000); $script:capacity}},

    @{N='CPU GHz Used';E={$script:used = [int](($_.Group | where{$_.MetricId -eq 'cpu.usagemhz.average'} | select -ExpandProperty Value)/1000); $script:used}},

    @{N='CPU % Free';E={[int](100 - $script:used/$script:capacity*100)}},

    @{N='Mem Capacity GB';E={$script:mcapacity = [int]($entity.ExtensionData.Summary.TotalMemory/1GB); $script:mcapacity}},

    @{N='Mem Used GB';E={$script:mused = [int](($_.Group | where{$_.MetricId -eq 'mem.usage.average'} | select -ExpandProperty Value) * $script:mcapacity/100); $script:mused}},

    @{N='Mem % Free';E={[int](100 - $script:mused/$script:mcapacity*100)}} |

Export-Csv -Path C:\temp\cluster-stats.csv -notypeinformation

Would you say that was the best way to do it until I can get the Export-Excel function to work?

The Export-Excel function is quite easy to use.

First, do you see the cmdlet?

If you don't, you may have place the module in a wrong folder. Check your $PSModulePath variable.

To use, you just pipe your objects to it.

Something like this

HI Luc

I figured out the problem. The function needs Powershell V3 at least and the server I was running it from was only V2. I've got it imported to my desktop now so hopefully it will work.

Thanks a lot for all your help :smileyhappy:

I've got a couple of questions Luc if you could spare me some more time.

How would I be able to change the time of the collection. At the minute it has taken a counter for 01:00 every day. Is there a way to change this to sometime during the day or has it been aggregated already and that is just the display?

Is there a way to do a Get-Cluster and the a foreach so that the script would only need to run against each vcenter server once? Ideally each cluster would appear on a separate worksheet

Thanks

Yes, that is the time the aggregation was done.

To run against all clusters, and store each cluster's data in a separate worksheet in the spreadsheet, you could do

Something has gone a bit wrong. When running the original script I get

With the new script it returns

Seems I forgot to replace some $entity by $cluster.

I updated the script above

Brilliant.

Thanks a lot for your help

How to change the date:

$start = (Get-Date).AddDays(-30)

By interval Date example:

$start = 2022-09-01

$finish= 2022-09-30

You can't with the Get-Stat cmdlet.
You will have to use a Where-clause after the Get-Stat, like I showed in PowerCLI & vSphere statistics – Part 2 – Come together

This script works for some Cluster, but it does not work for some Cluster (in same datacenter).

Get-Stat : 18/02/2020 08:32:02 Get-Stat This operation is restricted by the administrator - 'vpxd.stats.maxQueryMetrics'. Contact your

system administrator.

At line:7 char:16

+ $clusterName | Get-Stat -Stat $stat -MaxSamples 20  |

+                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Get-Stat], RestrictedByAdministrator

    + FullyQualifiedErrorId : Client20_RuntimeDataServiceImpl_GetStats_ErrorRetreivingPerfMetrics,VMware.VimAutomation.ViCore.Cmdlets.Com

   mands.GetViStats

Thank you for your answer.

That is a known issue, see Re: This operation is restricted by the administrator Error when running get-stats command

Thank you so much for your answer.

Hi, Thank you for your script. But I do not know why in last 30 Min result is wrong.

When I used -Realtime and -MaxSamples.

Time               CPU GHz Capacity CPU GHz Used CPU % Free Mem Capacity GB Mem Used GB Mem % Free

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

14/02/2020 10:10:00              441          211         52            5342        4038         24

14/02/2020 10:15:00              441          216         51            5342        4037         24

14/02/2020 10:20:00              441          218         51            5342        4037         24

14/02/2020 10:25:00              441          209         53            5342        4037         24

14/02/2020 10:30:00              441          208         53            5342        4037         24

14/02/2020 10:35:00              441          213         52            5342        4037         24

14/02/2020 10:40:00              441          206         53            5342        4037         24

14/02/2020 10:45:00              441          197         55            5342        4037         24

14/02/2020 10:50:00              441          210         52            5342        4037         24

14/02/2020 10:55:00              441          203         54            5342        4037         24

14/02/2020 11:00:00              441          210         52            5342        4037         24

14/02/2020 11:05:00              441          240         46            5342        4037         24

14/02/2020 11:10:00              441          226         49            5342        4037         24

14/02/2020 11:15:00              441          222         50            5342        4037         24

14/02/2020 11:20:00              441          238         46            5342        4037         24

14/02/2020 11:25:00              441            0        100            5342        3575         33

14/02/2020 11:30:00              441            0        100            5342        2881         46

14/02/2020 11:35:00              441            0        100            5342        2881         46

14/02/2020 11:40:00              441            0        100            5342        2297         57

14/02/2020 11:45:00              441            0        100            5342        1283         76

Thank you  for your answer.

Cluster performance data is created by one of the rollup processes running on the vCenter.

This rollup job runs every 30 minutes.

So for cluster data, the Realtime data is not really 'realtime', and you should not take the last 30 minutes into account.

Thank you so much for your answer.