VMware Cloud Community
MrTaylor2
Contributor
Contributor
Jump to solution

Powercli Script to Capture ESXi Cluster CPU & Memory Usage

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:

Cluster nameCPU GHz CapacityCPU GHz UsedCPU % Free

 

Cluster nameMemory CapacityMemory UsedMemory % Free

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

1 Solution

Accepted Solutions
LucD
Leadership
Leadership
Jump to solution

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

$fileName = 'C:\Temp\cluster-stats.xlsx'

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

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

foreach($cluster in Get-Cluster){

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

    Group-Object -Property Timestamp |

    Sort-Object -Property Name |

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

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

        @{N='CPU GHz Capacity';E={$script:capacity = [int]($cluster.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]($cluster.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-Excel -Path $fileName -WorkSheetname $cluster.Name

}

 


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

View solution in original post

Reply
0 Kudos
32 Replies
LucD
Leadership
Leadership
Jump to solution

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.

$clusterName = 'MyCluster'

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

$entity = Get-Cluster -Name $clusterName

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

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

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-Excel -Path C:\cluster-stats.xlsx -WorkSheetname 'Stats' -AutoSize -FreezeTopRow


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
MrTaylor2
Contributor
Contributor
Jump to solution

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?

Reply
0 Kudos
MrTaylor2
Contributor
Contributor
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

Are you by any chance on vSPhere 5.5U3 ?

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


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
panhvr
Contributor
Contributor
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
panhvr
Contributor
Contributor
Jump to solution

Hello LucD,

Thanks for your reply.

Below is entry in vpxd.cfg file

<stats>

      <maxQueryMetrics>-1</maxQueryMetrics>

    </stats>

    <support>

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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?


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
panhvr
Contributor
Contributor
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
MrTaylor2
Contributor
Contributor
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

It's purely a vCenter issue.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
MrTaylor2
Contributor
Contributor
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos
MrTaylor2
Contributor
Contributor
Jump to solution

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?

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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.

export-excel-1.jpg

To use, you just pipe your objects to it.

Something like this

$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-Excel -Path C:\Temp\cluster-stats.xlsx


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

MrTaylor2
Contributor
Contributor
Jump to solution

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 Smiley Happy

Reply
0 Kudos
MrTaylor2
Contributor
Contributor
Jump to solution

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

Reply
0 Kudos
LucD
Leadership
Leadership
Jump to solution

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

$fileName = 'C:\Temp\cluster-stats.xlsx'

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

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

foreach($cluster in Get-Cluster){

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

    Group-Object -Property Timestamp |

    Sort-Object -Property Name |

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

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

        @{N='CPU GHz Capacity';E={$script:capacity = [int]($cluster.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]($cluster.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-Excel -Path $fileName -WorkSheetname $cluster.Name

}

 


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

Reply
0 Kudos