Posted by
Alan Renouf
Technical Marketing
Recently at Partner Exchange 2012 I presented with a colleague of mine Vladimir Goranov who is an R&D Manager at VMware and focuses on automation and PowerCLI.
Our presentation was entitled “vCloud Infrastructure Automation – powered by PowerCLI”.
In the presentation we took people through some basic reporting and then demonstrated how this could be taken one step further to produce some nice easy to view reports which included hooking into Excel and automatically creating a spreadsheet complete with several tabs and bar graphs.
After this we demonstrated how the current version of PowerCLI – Version 5.0.1 could be used to not only view information but also create more advanced scripts which would allow you to create Organizations, Organization VDCs and Users. This will be shown in part 2 of this blog series but for now lets stick to the reporting.
Advanced Reporting
With PowerShell being able to integrate into multiple applications a great way to automatically present your data is through Excel, this can be made even easier with a great function created by Luc Dekens called Export-XLS, with his advanced function we can export the data and create the graphs we need with a couple of lines of code. An example of this can be seen in the screenshot below:
The Video
The below video was used in the presentation we gave at Partner Exchange, follow through to see both basic and Advanced Reporting:
The Script
As mentioned you will first of all need to add the Export-XLS function to your PowerCLI session which can be found on Luc’s site here. After this change the output filename in the script below and your advanced reports will be created giving you nice graphs for each of your Organization vDCs resources and a sheet showing which of your users has created the most VMs.
$Filename = "c:\UrgentWork\CloudStats.xlsx"
Get-OrgVdc | Select Name,
@{N="TotalCPUUsedGhz";E={[Math]::Round($_.CPUUsedGhz, 2)}},
@{N="TotalCPULimitGhz";E={[Math]::Round($_.CPULimitGhz, 2)}}
| Export-Xls -Path $Filename -WorksheetName "CPUUsage" -ChartType "xlColumnClustered" -AppendWorksheet:$false
Get-OrgVdc | Select Name,
@{N="TotalMemUsedGB";E={[Math]::Round($_.MemoryUsedGB,2)}},
@{N="TotalMemLimitGB";E={[Math]::Round($_.MemoryLimitGB, 2)}}
| Export-Xls -Path $Filename -WorksheetName "MemoryUsage" -ChartType "xlColumnClustered"
Get-OrgVdc | Select Name,
@{N="TotalStorageUsedGB";E={[Math]::Round($_.StorageUsedGB,2)}},
@{N="TotalStorageLimitGB";E={[Math]::Round($_.StorageLimitGB,2)}}
| Export-Xls -Path $Filename -WorksheetName "StorageUsage" -ChartType "xlColumnClustered"
Get-CIUser | Select Name,
@{N="TotalDeployedVMs";E={$_.DeployVMCount}},
@{N="TotalStoredVMs";E={$_.StoredVMCount}}
| Export-Xls -Path $Filename -WorksheetName "User VM Counts" -ChartType "xlColumnClustered"
Invoke-Item $Filename
Here is the code for the other scripts which were used as part of the reporting section in the presentation and the above video demonstration:
#List all vApps in the Cloud Infrastructure
Get-CIVApp
#List all vApps owned by a particular user
Get-CIVApp -Owner MrUser01
# Find OrgVDCs which have potential sales for storage
Write-Host "OrgVDCs which have potential sales for storage"
Get-OrgVdc | Where { $_.AllocationModel -eq "AllocationPool" } | Select Name,
@{N="StorageRemainingGBPerc";E={[Math]::Round((100-($_.StorageusedGB*100/$_.StorageLimitGB)),2)}}
| Where { $_.StorageRemainingGBPerc -lt 30 }
# Find OrgVDCs which have potential sales for Memory
Write-Host "OrgVDCs which have potential sales for Memory"
Get-OrgVdc | Where { $_.AllocationModel -eq "AllocationPool" } | Select Name,
@{N="MemRemainingGBPerc";E={[Math]::Round((100-($_.MemoryUsedGB*100/$_.MemoryLimitGB)),2)}}
| Where { $_.MemRemainingGBPerc -lt 30 }
# Find OrgVDCs which have potential sales for CPU
Write-Host "OrgVDCs which have potential sales for CPU"
Get-OrgVdc | Where { $_.AllocationModel -eq "AllocationPool" } | Select Name,
@{N="CpuRemainingGhzPerc";E={[Math]::Round((100-($_.CpuUsageGhz*100/$_.CpuLimitGhz)),2)}}
| Where { $_.CpuRemainingGhzPerc -lt 30 }
In part two of this series of blog posts we will show how you can use the current PowerCLI 5.0.1 Cmdlets to create new Orgs, Org VDCs and Users.
