PowerCLI Report – Audit Your Environment


I’ve been working on producing a daily report to audit who’s been modifying compute resources and while looking for some resources, found a great blog by Luc Dekens (@LucD22). However, one of the problems was that neither events or tasks keep original values, only retain new values.

In this blog post, instead of going through the script line by line, I would rather go through what I’ve done to overcome this constraint.


Initially, I thought about deploying a database (MySQL or MSSQL or whatever…) to store the compute resource data, i.e. CPU, memory and hard disk(s). But it wasn’t a good idea as it introduces another management layer. After a few minutes, one thing popped up in my head that how about using a .csv file to store the data? Did a quick brainstorm and came up with the following:

  1. For the first run, get the list of VM, CPU, memory, hard disks and capacity and export the list as a .csv file (I will call vm_report.csv from now on).
  2. For the second run and so on, for each event, generate an array to contain what has been modified by comparing the new value to old value from the .csv file. For instance, CPU 1->2.
  3. Once the for loop against events is finished, send an email to administrators for review.
  4. Export the new list to overwrite vm_report.csv.

Finished writing a script and started testing. One thing I found is that the script ran for the second time with no issues but for the third time, it failed. Well, it was obvious as the vm_report.csv was already up-to-date and I was trying to get the comparison where there will be none!

I was looking at the event object closely and figured out that each event has a unique element called key. Which means, if I can somehow filter the events out using the keys, there will be no overlap.

Eventually, I generated another .csv file called vm_events_keys.csv with the list of keys. Before the script queries the event information, it first looks at whether the vm_events_keys.csv contains the event key. If there is, it means it’s already been queried before so it could be ignored. Otherwise it’s a new event.

In summary:

  1. Get the list of information and save it in vm_report.csv.
  2. Get the list of keys of events and save it in vm_events_keys.csv.
  3. For each event, ensure vm_events_keys.csv does not contain the key of the event. If the event is a new one, generate report
  4. If there are new events, update vm_report.csv and vm_events_keys.csv. Otherwise do nothing.
  5. Send an email with report to administrators for review.

Before I move on to walk through, some people might ask about the period of events the script queries for, i.e. begin time and end time. Let me make it simple. Literally, the script looks for the time between the users execute the script. For example, if you ran the script at 7pm and then run it again at 10pm, the period will be 3 hours. Then after 24 hours you run the script, it will query for 24 hours of events.

Time for walk through!

Walk Through

In this walk through, I will be running the script for the three times.

The screenshot below shows you it was the first time running the script. It outputs a several warnings saying there are no .csv files as well as no new events:


The following screenshot is after the vm_report.csv and vm_events_keys.csv were generated. For the second run, it shows you a nice report showing what’s been changed as well as old values and new values.

The last run produces a warning saying “No new events found” and the reason is simple, I already ran it before and the vm_keys_events.csv is updated!


Looking at the inbox, I also got the result in a table format:



The following is the script written. Did minimal comments, feel free to leave a reply if you need any clarifications.

## report function that outputs the result
function report {
    param ($event, $change, $operation, $value)
    return $event | Select @{N="Event Time";E={[datetime]($_.CreatedTime).addHours(12)}}, @{N="Cluster";E={$_.ComputeResource.Name}}, @{N="VM";E={$_.VM.Name}}, @{N="Log";E={$_.FullFormattedMessage}}, @{N="Username";E={$_.UserName}}, @{N="Change";E={$change}}, @{N="Operation";E={$operation}}, @{N="Value";E={$value}}

## update_report function that updates the vm_report .csv file
function update_report {
    param($vm_report, $vm, $change, $operation, $value)
    switch (([string]$operation).ToLower()) {        
        add {
            $harddisk = $vm | %{ [String]::Join(":", ($_.HardDisks.ExtensionData.DeviceInfo.Label)) }
            $harddisk_capacity = $vm | %{ [String]::Join(":", ( ($_.HardDisks.ExtensionData.DeviceInfo.Summary -replace " KB"))) }
            ($vm_report | where {$_.Name -eq $vm.Name})."Hard Disk" = $harddisk
            ($vm_report | where {$_.Name -eq $vm.Name})."Capacity" = $harddisk_capacity
        remove {     
            $harddisk = $vm | %{ [String]::Join(":", ($_.HardDisks.ExtensionData.DeviceInfo.Label)) }
            $harddisk_capacity = $vm | %{ [String]::Join(":", ( ($_.HardDisks.ExtensionData.DeviceInfo.Summary -replace " KB"))) }
            ($vm_report | where {$_.Name -eq $vm.Name})."Hard Disk" = $harddisk
            ($vm_report | where {$_.Name -eq $vm.Name})."Capacity" = $harddisk_capacity
        edit { 
            ($vm_report | where {$_.Name -eq $vm.Name})."Capacity" = $value
        modify {
            if ($change -eq 'Memory') {
                ($vm_report | where {$_.Name -eq $vm.Name}).MemoryGB = $value
            } elseif ($change -eq 'CPU') {
                ($vm_report | where {$_.Name -eq $vm.Name}).CPU = $value
        default { 
    return $vm_report

## send_email function that sends the final result to the end user
function send_email {
    param ($event_report, $today)

    $header = @"
TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: #6495ED;}
TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;}

    $body = "<h2>Today's Date: " + $today + "</h2>"
    if (!$event_report) {
        $body += "There are no new events"
    } else {
        $body += $event_report | Sort "Event Time" | ConvertTo-HTML -Head $header | Out-String
    Send-MailMessage -From "Email Sender Address" -To "Email Receiver Address" -Subject "vSphere Audit Report" -BodyasHtml -Body $body -SmtpServer "SMTP Server"

## List of administrator users you would like to exclude. For example, exclude yourself as you know you have the right to modify
$admins = "com.vmware.vadm|administrator|YourAccount"

## Check for vm_report.csv & vm_events_keys.csv files and if they don't exist, generate one
if (Test-Path "vm_report.csv") {
    $vm_report = Import-Csv "vm_report.csv"
} else {
    $vm_report = Get-VM | Sort Name | Select Name, @{N="CPU";E={$_.NumCpu}}, 
    @{N="Hard Disk";E={[String]::Join(":", ($_.HardDisks.ExtensionData.DeviceInfo.Label))}}, 
    @{N="Capacity";E={[String]::Join(":", ( ($_.HardDisks.ExtensionData.DeviceInfo.Summary -replace " KB")))}}
    $vm_report | Export-CSV -UseCulture -NoTypeInformation "vm_report.csv"
    Write-Warning "No vm_report.csv found therefore, exported a new list. Run it again after at least one event occurs"

if (Test-Path "vm_events_keys.csv") {
    $events_keys = Import-Csv "vm_events_keys.csv"
} else {
    $events_keys = @{"Key" = ""}
    Write-Warning "No vm_events_keys.csv found, starting with an empty array"

## Create vCenter API object, EventManager
$event_manager = Get-View EventManager
$event_filter_spec = New-Object VMware.Vim.EventFilterSpec
$event_filter_spec.Type = "VmReconfiguredEvent"

$event_filter_spec.Type = "VmReconfiguredEvent"
$event_filter_spec.Time = New-Object VMware.Vim.EventFilterSpecByTime

## Define the Event Filter. 
## Start date is based on the last modified date of the vm_report.csv file.
## End date is now
$last_modified = (Get-Item vm_report.csv) | %{$_.LastWriteTime}
$today = Get-Date
$hour_difference = -($today - $last_modified).TotalDays

$event_filter_spec.Time.BeginTime = (Get-Date).AddDays($hour_difference)
$event_filter_spec.Time.EndTime = (Get-Date)

## Query events with the Event Filter defined above
$events = $event_manager.QueryEvents($event_filter_spec) | where {$_.Username -notmatch $admins} | Sort CreatedTime

## For each event, see if the event key is found in $vm_events_keys list
## If found, then skip it otherwise save the result to $event_report array
$event_report = foreach ($event in $events) {
  $event | Foreach-Object {        
        if (!(($events_keys | %{$_.Key}).Contains([string]($_.Key)))) {
            $vm = Get-VM -Name $_.VM.Name    
            if ($vm -and ($vm_report | where {$_.Name -eq $vm.Name})) {
                ## Memory modified
                if ($_.ConfigSpec.MemoryMB) { 
                    $change = "Memory"
                    $operation = "Modify"
                    $new_value = $_.ConfigSpec.MemoryMB    
                    $old_value = [int]($vm_report | where {$_.Name -eq $vm.Name} | %{$_.MemoryGB}) * 1024
                    $value = [string]$old_value + 'MB->' + [string]$new_value + 'MB'
                    if ($new_value -ne $old_value) {
                        ## Update $vm_report array
                        $vm_report = update_report $vm_report $vm $change $operation ($new_value / 1024)
                        ## Save the change to $event_report array
                        report $event $change $operation $value
                ## CPU modified
                if ($_.ConfigSpec.NumCPUs) {
                    $change = "CPU"
                    $operation = "Modify"
                    $new_value = $_.ConfigSpec.NumCPUs
                    $old_value = [int]($vm_report | where {$_.Name -eq $vm.Name} | %{$_.CPU})            
                    $value = [string]$old_value + '->' + [string]$new_value
                    if ($new_value -ne $old_value) {
                        $vm_report = update_report $vm_report $vm $change $operation $new_value
                        report $event $change $operation $value
                ## Hard Disk modified
                if ($_.ConfigSpec.DeviceChange) {
                    ## Use Foreach-Object as there might be more than 1 hard disk changes
                    $_.ConfigSpec.DeviceChange | where { [string]$_.Device -eq "VMware.Vim.VirtualDisk" } | Foreach-Object {
                        if ($_.Device.DeviceInfo.Label) {
                            $change = $_.Device.DeviceInfo.Label
                            $operation = $_.Operation
                            $new_value = $_.Device.CapacityInKB / 1024 / 1024
                            $harddisk = ($vm_report | where {$_.Name -eq $vm.Name} | %{$_."Hard Disk"}) -split ":"
                            $harddisk_capacity = ($vm_report | where {$_.Name -eq $vm.Name} | %{$_."Capacity"}) -split ":"            
                            for ($i = 0; $i -lt $harddisk.length; $i++) {
                                if ($harddisk[$i] -eq $change) {
                                    $old_value = [int]$harddisk_capacity[$i] / 1024 / 1024
                                    $harddisk_capacity[$i] = ("{0:N0}" -f ($new_value * 1024 * 1024))
                            ## If the operation is not Remove, i.e. Edit
                            if ($operation -ne 'remove') {
                                if ($new_value -ne $old_value) {
                                    $value = [string]$old_value + 'GB->' + [string]$new_value + 'GB'
                                    $vm_report = update_report $vm_report $vm $change $operation ([string]::Join(":", ($harddisk_capacity)))
                            ## If the operation is Remove
                            } else {
                                $value = [string]$old_value + 'GB'                            
                                $vm_report = update_report $vm_report $vm $change $operation ""
                            ## Save the output to $event_report
                            report $event $change $operation $value        
                        ## If there is no device label, it means there is a new hard disk
                        } else {
                            $change = "New Hard disk"
                            $operation = $_.Operation
                            $value = $_.Device.CapacityInKB / 1024 / 1024
                            $vm_report = update_report $vm_report $vm $change $operation ""
                            report $event $change $operation ([string]($value) + "GB")                        
            ## Reset parameters after 1 loop
            $change = '';
            $operation = '';
            $old_value = '';
            $new_value = '';
            $value = '';

## If there are no events, send an email with an empty result
if (!$event_report) {
    Write-Warning "No new events found"
    send_email "" $today
## If there are events found, update vm_events_keys.csv to prevent duplicate check
## Update vm_report.csv to ensure the data is up-to-date
## Send an email to the administrators with the changes
} else {
    $events | Select Key | Export-Csv -UseCulture -NoTypeInformation "vm_events_keys.csv"
    $vm_report | Export-Csv -UseCulture -NoTypeInformation -Force -Path "vm_report.csv"
    send_email $event_report $today

Hope this helps!

vRA – Get Reservation Policy ID from vRO


I’ve been dealing with Reservations & Reservation Policies on vRA and found out that instead of using a SQL query, it was able to use vRO to pull Reservation Policy ID information out. In this blog post, I will go through both ways and explain the difference.

If you aren’t familiar with Reservation Policy ID, read the excellent blog by Kushmaro.

SQL Query

The following is the SQL query you could use in order to pull Reservation Policy ID out:

SELECT [id],[name] FROM [vCAC].[dbo].[HostReservationPolicy]

Login to SQL Management Studio and run the query above. If you don’t have access, ask database administrator to run it and get the output for you.

The attached screenshot below is a sample output:


It was quite easy, wasn’t it? Let’s take a look at vRO query.

vRO Query

It’s fairly simple to use vRO to query Reservation Policy ID. First of all, create a workflow and attach two attributes as the following:


For Reservation values, expand Reservations folder and add Reservations you would like to:


For vCACHost, select the vRA server.

Now, click on the Schema tab and drag and drop a scriptable task between start and end:


Edit the scriptable task and finish the Visual Binding like the below:


Navigate to Scripting tab and paste the script:

for each (var r in Reservation) {
  var reservationPolicyId = r.getEntity().getLink(vCACHost, "HostReservationPolicy");
  for each (var p in reservationPolicyId) {
    System.log("Reservation Policy Name is: ");
    System.log("Reservation Policy ID is: ");

Run the workflow and a sample output is shown below:


Comparing both outputs from SQL and vRO, it seems different. There are 5 Reservation Policies from SQL whereas 3 from vRO. Why is this?


Let us first take a look at how many Reservation & Reservation Policies are there in the vRA environment I am connected to:



Even though there are 5 Reservation Policies, it does not mean that Reservations use all of them. In this case there are 3 Reservations and they are using only 2 Reservation Policies.

In summary, if you do a SQL query, it will show you Reservation Policy IDs across all Reservation Policies whereas vRO will only return you the ones being used by Reservations.

Hope this was useful and feel free to leave a comment for any clarifications.

vRO Deepdive Series Part 3 – Presentation Layer


This is the third vRO deep dive series and in this blog post, it will be discussing presentation layer. Before starting, I recommend you to read previous 2 blogs:

Presentation Layer

What is presentation layer? Presentation layer is where you structure and apply logics to inputs. The modified structure and logics will be represented to users who run the workflow. Let’s recap last series’ example. The workflow had 2 inputs defined:

  • VM
  • Cluster

Executing the workflow shows you 2 inputs:


So where do I re-configure presentation layer? Edit the workflow above, “Sample Workflow” and on the 5th tab, you will see Presentation:


In this Presentation tab, there are five options available listed below:

  • Expand All
  • Collapse All
  • Create New Step
  • Create Display Group
  • Delete

Using the workflow above, I will first be going through creating & configuring step.


In the presentation layer, step could be created to allow end-users to give inputs in multiple steps. Think it as a survey, rather than asking all questions in 1 page, survey normally separates out questions in multiple pages. Step is literally the same thing.

Let’s create a step. Edit the workflow, select Presentation and click “Create new step”:


And re-name it to “Step 1” by double clicking it:


Save the change made and close the window. Run the workflow and you will see a new step created:


Click Next and you will be at “Step 1”:


“Step 1” has nothing shown as there are no inputs mapped to it. Let’s allocate an input to it, go back to Presentation tab and drag VM into “Step 1”:



Execute the workflow and in Step 1, it will for VM:


Rather than Common parameters above, it would be better to call it Step 1 and then Step 2. Go back to the Presentation tab and create one more step:


Running the workflow, it will show you 2 separate steps:



This is the way of creating multiple steps and assigning values. I will now start discussing display group.

Display Group

Display group is where you could define multiple steps within a step. It sounds a bit confusing but it’s actually quite simple, go back to the workflow, select Step 1 and click Create display group:



Drag VM into Group 1:


Run the workflow and you will see within the Step 1, there is another step called Group 1:


This time, let’s put 2 inputs VM and Cluster into this group. Drag both inputs to Group 1 and delete Step 2:


Executing the workflow, it will show you Group 1 now asks for 2 inputs:


This time, let’s create 2 groups within a step. Select Step 1, create display group and drag Cluster into the second group:


Run the workflow and you will now see two groups in a single step:



One thing to note is that the display group could only be created within a step, i.e. display group in a display group isn’t possible.

Moving on to properties.


Properties is where you could apply logics to inputs. For instance, you could make an input as a mandatory field that the user must put it in order to submit the request. Let’s try this out, go back to Presentation tab, select VM input and on the bottom, click Add Property:


You will see the full list of properties you can apply to this input. Select Mandatory input and click OK:


Save the workflow and run it. Unlike before, you won’t be able to proceed without defining a cluster value, Submit button will be greyed out:



Instead of using a scriptable task within a workflow to ensure the input is given by user or trying to match it against a regular expression…etc, this is the simpler way.

Next exercise will go through how to predefine list of values, i.e. user will only be able to select a value from the predefined elements. Go back to Presentation, click on the cluster input, click Add property and select Predefined list of elements:


Click on the pencil button:


Wait a minute, there is no parameter available. Why? It’s obvious that we haven’t created any attributes with pre-defined values. Take a close look at the heading of the window “Linked parameter of type Array/VC:VirtualMachine”. This is because the input is defined as VC:VirtualMachine object and since we are trying to predefine values, the final object should be Array/VC:VirtualMachine.


Let’s create an attribute. Go to General tab and Add an attribute called VMList with Array of VC:VirtualMachine:


Once created, click on Not set to insert values. In my case, I pre-defined 4 values:

  • test1
  • test2
  • test3
  • test4



Go back to Presentation tab and click property of VM. You will now see VMList:


Accept it and run the workflow. Select VM and now it will have predefined list:


There are a lot of properties you could play around with, spend some time adding different properties to input values.


Hope this blog post was helpful and the next series will cover “Log Handling, Throwing Exceptions and Fail-back”. Stay tuned 😀