Home > Blogs > VMware PowerCLI Blog

Reporting with the PowerCLI vCD Cmdlets

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="TotalMemLimitGB";E={[Math]::Round($_.MemoryLimitGB, 2)}}

    | Export-Xls -Path $Filename -WorksheetName "MemoryUsage" -ChartType "xlColumnClustered"
Get-OrgVdc | Select Name,

    | Export-Xls -Path $Filename -WorksheetName "StorageUsage" -ChartType "xlColumnClustered"
Get-CIUser | Select Name,


    | 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

#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,

    | 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,

    | 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,

    | 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.

Get notification of new blog postings and more by following VMware PowerCLI on Twitter: @PowerCLI

This entry was posted in Reporting on by .
Alan Renouf

About Alan Renouf

Alan Renouf is a Product Line Manager at VMware focusing on API's, SDK's and CLI's, He is responsible for providing the architects and operators of private and public cloud infrastructure with the toolkits/frameworks and command-line interfaces they require to build a fully automated software-defined datacenter. Alan is a frequent blogger at http://blogs.vmware.com/PowerCLI a book author and has a personal blog at http://virtu-al.net. You can follow Alan on twitter as @alanrenouf.

2 thoughts on “Reporting with the PowerCLI vCD Cmdlets

  1. Jens Kalski

    Luc’s Site zeigt HTTP 403

  2. Sabuj Banerjee

    Hi Alan,

    I am looking to get capacity information for all provider VDCs in a cell but for the storage part i am getting all blanks. Can you please help ? My vcloud director version is 5.1

    Get-ProviderVdc | Select Name, @{N=”TotalCPUGhz”;E={[Math]::Round($_.CpuTotalGHz, 2)}},@{N=”TotalCPUAllocatedGhz”;E={[Math]::Round($_.CpuAllocatedGHz, 2)}}, @{N=”TotalCPUUsedGhz”;E={[Math]::Round($_.CPUUsedGhz, 2)}}, @{N=”TotalMemGB”;E={[Math]::Round($_.MemoryTotalGB, 2)}}, @{N=”TotalMemAllocatedGB”;E={[Math]::Round($_.MemoryAllocatedGB, 2)}}, @{N=”TotalMemUsedGB”;E={[Math]::Round($_.MemoryUsedGB,2)}}, @{N=”TotalStorageGB”;E={[Math]::Round($_.StorageTotalGB,2)}}, @{N=”TotalStorageAllocatedGB”;E={[Math]::Round($_.StorageAllocatedGB,2)}}, @{N=”TotalStorageUsedGB”;E={[Math]::Round($_.StorageUsedGB,2)}}


Leave a Reply

Your email address will not be published. Required fields are marked *