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
