Thursday, February 19, 2009

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

4 comments:

jeff said...

for host based custom fields, populated in the value property when you do a get-view of the host

AlbertWT said...

Hi,

I'm using Office Excel 2007 and i got an error in executing the script ?

Exception calling "Add" with "0" argument(s): "Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"
At :line:12 char:32
+ $Workbook = $Excel.Workbooks.Add <<<< ()

Anonymous said...

Hi albertWT

you should have a look to reflections method because there are some bugs in the zccess to excel object while your UIculture language and serverCulture language not together US-english

#http://msdn.microsoft.com/fr-fr/library/system.reflection.ireflect.invokemember.aspx
function invokeXLSmethod {
param ( $objet, $name, $arguments )

$ci = [System.Globalization.CultureInfo]'en-us'
return $objet.psbase.gettype().InvokeMember($name,[Reflection.BindingFlags]::InvokeMethod,$null, $objet, $arguments, $ci)
}

Anonymous said...

Thanks for the clear and well-documented code--the links to the System.Reflections methods really helped!