Hi Folks,
I'm pretty new to PowerCLI. I have a need to create a script that will collect cluster & hosts information for the previous day. Essentially, each day I log into Virtual Center, click on the cluster level, then go to View > Reports > Performance and set it to collect the performance metrics for 1 day. I then do this for every ESX host in the cluster. Can someone provide me a PowerCLI script that'll accomplish that? I'm attaching an Excel spreadsheet that has metrics collected from the cluster level for the previous day.
Any help would be greatly appreciated.
Thanks!
I'm not able to open the sample.xls file in Excel 2007. It gives an error. For information about vSphere performance reporting Luc Dekens's wrote some excellent blog posts. The first one you can find here:
PowerCLI & vSphere statistics – Part 1 – The basics.
Regards, Robert
You may want to look @ Virtu-Al's Daily vCheck Report script and PowerGUI (or vEcoShell formerly VESI) w/ the VMWare Community Powerpack.
http://www.virtu-al.net/featured-scripts/vcheck/
http://www.virtu-al.net/featured-scripts/vmware-powerpack/
http://vcommunity.vizioncore.com/administration/vecoshell/default.aspx
Chris Nakagaki (Zsoldier)
Except for the graph the following will produce a similar XLS file as the Report-Performance option in the vSphere client.
It uses the Export-Xls function from my Beyond Export-Csv: Export-Xls post.
$clusterName = <your-clustername> function Export-Xls{ <# .SYNOPSIS Saves Microsoft .NET Framework objects to a worksheet in an XLS file .DESCRIPTION The Export-Xls function allows you to save Microsoft .NET Framework objects to a named worksheet in an Excel file (type XLS). The position of the worksheet can be specified. .NOTES Author: Luc Dekens .PARAMETER InputObject Specifies the objects to be written to the worksheet. The parameter accepts objects through the pipeline. .PARAMETER Path Specifies the path to the XLS file. .PARAMETER WorksheetName The name for the new worksheet. If not specified the name will be "Sheet" followed by the "Ticks" value .PARAMETER SheetPosition Specifies where the new worksheet will be inserted in the series of existing worksheets. You can specify "begin" or "end". The default is "begin". .PARAMETER NoTypeInformation Omits the type information from the worksheet. The default is to include the "#TYPE" line. .PARAMETER AppendWorksheet Specifies if the worksheet should keep or remove the existing worksheet in the spreadsheet. The default is to append. .EXAMPLE PS> Export-Xls - #> param( [parameter(ValueFromPipeline = $true,Position=1)] [ValidateNotNullOrEmpty()] $InputObject, [parameter(Position=2)] [ValidateNotNullOrEmpty()] [string]$Path, [string]$WorksheetName = ("Sheet " + (Get-Date).Ticks), [string]$SheetPosition = "begin", [switch]$NoTypeInformation = $true, [switch]$AppendWorksheet = $true ) Begin{ $excelApp = New-Object -ComObject "Excel.Application" $originalAlerts = $excelApp.DisplayAlerts $excelApp.DisplayAlerts = $false $excelApp.Visible = $true if(Test-Path -Path $Path -PathType "Leaf"){ $workBook = $excelApp.Workbooks.Open($Path) } else{ $workBook = $excelApp.Workbooks.Add() } $sheet = $excelApp.Worksheets.Add($workBook.Worksheets.Item(1)) if(!$AppendWorksheet){ $workBook.Sheets | where {$_ -ne $sheet} | %{$_.Delete()} } $sheet.Name = $WorksheetName if($SheetPosition -eq "end"){ $nrSheets = $workBook.Sheets.Count 2..($nrSheets) |%{ $workbook.Sheets.Item($_).Move($workbook.Sheets.Item($_ - 1)) } } $tempCsvName = $env:Temp + "\Report-" + (Get-Date).Ticks + ".csv" $array = @() } Process{ $array += $InputObject } End{ $array | Export-Csv -Path $tempCsvName -NoTypeInformation:$NoTypeInformation $csvBook = $excelApp.Workbooks.Open($tempCsvname) $csvSheet = $csvBook.Worksheets.Item(1) $csvSheet.UsedRange.Copy() | Out-Null $sheet.Paste() $sheet.UsedRange.EntireColumn.AutoFit() | Out-Null if($excelApp.Version -lt 14){ $csvbook.Application.CutCopyMode = $false } $csvBook.Close($false,$null,$null) Remove-Item -Path $tempCsvName -Confirm:$false $workbook.Sheets.Item(1).Select() $workbook.SaveAs($Path) $excelApp.DisplayAlerts = $originalAlerts $excelApp.Quit() Stop-Process -Name "Excel" } } $todayMidnight = Get-Date -Hour 0 -Minute 0 -Second 0 $start = $todayMidnight.AddDays(-1).AddSeconds(1) $finish = $todayMidnight $metricsCPU = "cpu.usage.average","cpu.usageMhz.average" $metricsMem = "mem.granted.average","mem.active.average","mem.vmmemctl.average","mem.consumed.average","mem.swapused.average" $metricsVM = "vmop.numpoweron.latest","vmop.numpoweroff.latest","vmop.numvmotion.latest","vmop.numsvmotion.latest" $statsCPU = Get-Stat -Entity (Get-Cluster -Name $clusterName) -Stat $metricsCPU -Start $start -Finish $finish $statsMem = Get-Stat -Entity (Get-Cluster -Name $clusterName) -Stat $metricsMem -Start $start -Finish $finish $statsVM = Get-Stat -Entity (Get-Cluster -Name $clusterName) -Stat $metricsVM -Start $start -Finish $finish ($statsCPU | Group-Object -Property Timestamp).GetEnumerator() | %{ $row = "" | Select Time,"CPU Usage in MHz","CPU Usage" $row.Time = $_.Name $row."CPU Usage in MHz" = ($_.Group | where {$_.MetricId -eq "cpu.usageMhz.average"}).Value $row."CPU Usage" = ($_.Group | where {$_.MetricId -eq "cpu.usage.average"}).Value $row } | Export-Xls -Path ("C:\" + $clusterName + "-performance.xls") -WorksheetName "CPU" -AppendWorksheet:$false ($statsMem | Group-Object -Property Timestamp).GetEnumerator() | %{ $row = "" | Select Time,"Memory Granted","Memory Active","Memory Balloon","Memory Consumed","Memory Swap Used" $row.Time = $_.Name $row."Memory Granted" = ($_.Group | where {$_.MetricId -eq "mem.granted.average"}).Value $row."Memory Active" = ($_.Group | where {$_.MetricId -eq "mem.active.average"}).Value $row."Memory Balloon" = ($_.Group | where {$_.MetricId -eq "mem.vmmemctl.average"}).Value $row."Memory Consumed" = ($_.Group | where {$_.MetricId -eq "mem.consumed.average"}).Value $row."Memory Swap Used" = ($_.Group | where {$_.MetricId -eq "mem.swapused.average"}).Value $row } | Export-Xls -Path ("C:\" + $clusterName + "-performance.xls") -WorksheetName "Memory" -SheetPosition "end" ($statsVM | Group-Object -Property Timestamp).GetEnumerator() | %{ $row = "" | Select Time,"VM power on count","VM power off count","VMotion count","Storage VMotion count" $row.Time = $_.Name $row."VM power on count" = ($_.Group | where {$_.MetricId -eq "vmop.numpoweron.latest"}).Value $row."VM power off count" = ($_.Group | where {$_.MetricId -eq "vmop.numpoweroff.latest"}).Value $row."VMotion count" = ($_.Group | where {$_.MetricId -eq "vmop.numvmotion.latest"}).Value $row."Storage VMotion count" = ($_.Group | where {$_.MetricId -eq "vmop.numsvmotion.latest"}).Value $row } | Export-Xls -Path ("C:\" + $clusterName + "-performance.xls") -WorksheetName "VMoperations" -SheetPosition "end"
Note that the script uses the complete previous day, while the vSphere client goes back 24 hours from the time you run the report.
____________
Blog: LucD notes
Twitter: lucd22
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Hi Luc,
Thanks a lot for providing this script. However, I'm pretty new to powerCLI and so can you let me know how to run this script? Whether any parameters are to be specified and if so, in what format? I get the following error when I run this script without inputting any parameters.
Missing closing ')' in expression.
At \report-cluster-performance.ps1:36 char:2
[ <<<< ValidateNotNullOrEmpty()}
Thanks a lot for your help.
You will have to update the first line of the script, where it says <your-clustername> you will have to put the name of your cluster for which you want the report.
If your cluster is called for example "cluster1", the first line becomes
$clusterName = "cluster1" ...
To run the script you have several options:
1) You save the file as a .ps1 file and from the PowerCLI prompt you do
.\report-cluster-performance.ps1
2) You load the script into the PowerShell v2 ISE and after you have updated the clustername, you select or press F5.
You must have the PowerCLI snapin loaded before you do that.
If you need a more general intro to PowerCLI and how to use it, there are several good introductory blog posts available.
Some examples:
Introduction to using VMware vSphere PowerCLI
____________
Blog: LucD notes
Twitter: lucd22
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Luc,
Thanks for your response. I am not sure what I'm doing wrong since that is the exact manner in which I executed this script. I'm still hitting the same error. I'll keep digging, if you can think of something else let me know.
Thanks again for your help.
From where did you copy the script ?
From the code window or from the attached script ?
It looks as if there is a square bracket missing and the forum sw is known to have problems with square brackets and certain browsers.
Can you try the code from the attached file ?
____________
Blog: LucD notes
Twitter: lucd22
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
I tried both ways. Same result.
Some more question while trying to pinpoint the problem.
From where do you run the script ? PowerCLI prompt, PowerShell ISE, PowerGui ... ?
You do have PowerShell v2 installed ?
Could you perhaps attach a screenshot of the run and the error message ?
____________
Blog: LucD notes
Twitter: lucd22
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Luc,
Fantastic call on the version of powershell. On the dev box I was working off of, only v1 was installed. Thinking this could be the issue, I took another test box and installed v2. Sure enough, the script worked like a charm. Thanks for your help once again!
By the way, do you have similar scripts that'll pull the same reports for each individual host?
Thanks again, you da man!
I don't have such a report handy but it shouldn't be too difficult.
It will require another set of metrics for most worksheet but that shouldn't be a problem.
I assume you want a spreadsheet per host ?
____________
Blog: LucD notes
Twitter: lucd22
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Luc,
That's correct. A spreadsheet per host.
Thanks a ton for your help.
Hey Luc,
Do you have an estimate on when the host script would be available? I don't mean to rush you or impede on your progress but my boss just asked me when I would have one available for him.
Thanks again for all your help.
No problem, nearly finished. Will publish at the latest tomorrow morning (CET).
____________
Blog: LucD notes
Twitter: lucd22
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thank you so very much. You are great!
Attached a script that produces 1 spreadsheet per host in a cluster.
You define the name of the cluster in the first line.
The script produces the following worksheets: CPU, Disk, Managementagent, Memory, Network and System.
____________
Blog: LucD notes
Twitter: lucd22
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Luc, Thank you so very much. I'm going to play around with it and will let you know if I have any questions/issues.
Again, thanks for all your help.
Fyi, just updated my Beyond Export-Csv: Export-Xls post.
The Export-Xls now has a -ChartType parameter, which means you can emulate completely what the vSphere client Report option is doing !
____________
Blog: LucD notes
Twitter: lucd22
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference