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