PowerShell, VM & Excel (Conditional Formatting)

Some of our support staff recently asked if they could get access to the VM Virtual Center in order to see the status of VM servers. We recognized the importance of giving them this information, but did not like the idea of giving them access to Virtual Center. So I was asked for suggestions: Yep you guessed it PowerShell to the rescue!

I wrote a script that gets all the Virtual machines and then populates an Excel spreadsheet. But wait there's more! A colleague asked if it was possible to create a dashboard (you know Green for good, Red for bad). Being quite familiar with Excel, I knew conditional formatting was the answer.

This is a simplified version of the output from the script (only 2 properties):


How did we make this work? Well, the first thing I do when automating Excel from PowerShell is to open up Excel and record a macro. I find it helpful to do this in very meticulous fashion. I record individual tasks then hit Alt-F11 to look at the generated code. If you have no experience with VBA and Excel, you should take a few minutes to get acquainted. Once I see the VBA code, I have a good idea how to convert it into PowerShell.

Here is the script:

   1: # Create $cred variable (only if you need to pass different creds)

   2: $cred = Get-Credential 'osu\fatbeard'

   3: Connect-VIServer -Server 'VM-Server' -Credential $cred

   4:  

   5: # Get all VM Boxes, we are interested in this

   6: # point only in the name and PowerState

   7: $VirtualServers = Get-VM | sort Name | select Name, PowerState 

   8:  

   9: # Create an instance of Excel

  10: $Excel = New-Object -comobject Excel.Application

  11: $Excel.visible = $True 

  12: $Workbook = $Excel.Workbooks.Add()

  13: $Info = $Workbook.Worksheets.Item(1)

  14:  

  15: # Create our column headers

  16: $Info.Cells.Item(1,1) = "VM Name"

  17: $Info.Cells.Item(1,2) = "PowerState"

  18:  

  19: # Add a little formatting to the column header

  20: $Style = $Info.UsedRange

  21: $Style.Interior.ColorIndex = 19

  22: $Style.Font.ColorIndex = 11

  23: $Style.Font.Bold = $True

  24:  

  25: # Data starts at row 2

  26: $intRow = 2

  27:  

  28: # iterate over each VM

  29: foreach ($VirtualServer in $VirtualServers)

  30: {

  31:     if ($VirtualServer.PowerState -eq "PoweredOn")

  32:     {

  33:         $PoweredState = 1

  34:     }

  35:     else

  36:     {

  37:         $PoweredState = 0

  38:     }    

  39:     

  40:     $Style.Cells.Item($intRow, 1) = $VirtualServer.Name

  41:     $Style.Cells.Item($intRow, 2) = $PoweredState 

  42:     $intRow++

  43: }

  44:  

  45: ## Now for some formatting 

  46: #  For more data on the enumeration values look at the following:

  47: #  XlFormatConditionType Enumeration:       http://msdn.microsoft.com/en-us/library/bb241301.aspx 

  48: #  XlFormatConditionOperator Enumeration: http://msdn.microsoft.com/en-us/library/bb241299.aspx

  49: #  XlIconSet Enumeration                  http://msdn.microsoft.com/en-us/library/bb241324.aspx

  50: #  XlConditionValueTypes Enumeration      http://msdn.microsoft.com/en-us/library/bb241028.aspx

  51: ## The constants that we will use

  52:  

  53: $xlCenter = -4108

  54: $xlCellValue = 1

  55: $xlEqual = 3

  56: $xl3TrafficLights2 = 5

  57: $xl3Symbols = 7

  58: $xlConditionValueNumber = 0

  59: $xlIconSet = 6

  60:  

  61: # Make Column "A" wide enough for content

  62: $info.Range("A:A").entireColumn.Autofit()

  63:  

  64: # Make Column "B" wide enough for content and Center the contents

  65: $info.Range("B:B").entireColumn.Autofit()

  66: $info.Range("B:B").HorizontalAlignment = $xlCenter 

  67:  

  68: # This will fill the cell

  69: #$Info.Range("B:B").FormatConditions.Add($xlCellValue,$xlEqual,'="Y"')

  70: #$Info.Range("B:B").FormatConditions.Item(1).Interior.Color = 5296274

  71:  

  72: $Info.Range("B:B").FormatConditions.Add($xlIconSet)

  73: $Info.Range("B:B").FormatConditions.AddIconSetCondition

  74: $Info.Range("B:B").FormatConditions.Item(1).ShowIconOnly = $true

  75: $Info.Range("B:B").FormatConditions.Item(1).IconSet = $Excel.ActiveWorkbook.IconSets.Item($xl3Symbols)

  76: $Info.Range("B:B").FormatConditions.Item(1).IconCriteria.Item(2).Value = 0

  77: $Info.Range("B:B").FormatConditions.Item(1).IconCriteria.Item(2).Operator = 5

  78: $Info.Range("B:B").FormatConditions.Item(1).IconCriteria.Item(3).Value = 1

  79: $Info.Range("B:B").FormatConditions.Item(1).IconCriteria.Item(3).Operator = 7

  80:  

  81:  

  82:  

  83:  

I have included MSDN links in the code to the Excel enumerations used to write this script.
All that is left to do is to schedule it and save it somewhere accessible to the Support Center staff. Another todo from a VM scripting perspective is figure out a way to get the custom fields. Leave me a comment if you have already figured that one out!

Enjoy!!