Thursday, October 29, 2009

Using F# BigInt in PowerShell

Occasionally I have the need to utilize large precise numbers within PowerShell. Unfortunately, the native data types that we can use are limited in their precision.
  • Float: 7 digits
  • Double: 16 digits
  • Decimal: 29 digits

I have been researching F# and came across a data type called BigInt. I now knew that I could go back to PowerShell and take advantage of this type.

The process to do so is straight forward:
First add a reference to the FSharp.Core assembly

Add-Type -Path 'C:\Program Files (x86)\FSharp-\bin\fSharp.Core.dll'

Now you do things like:
PS C:> [Microsoft.FSharp.Math.BigInt]::Pow(17,71).toString()
PS C:>2300771122759378216336589429524308

So if you are a fan of you now have another tool you can utilize.


Friday, October 2, 2009

Defining Object Formatting or "How to always show Military time"

A former colleague of mine at the OSU Medical Center recently asked a PowerShell related question.

Wait - Stop - Let's start over. I must digress a moment, please be patient as I give this former colleague of mine an appropriate introduction.

This Buckeye transplant to the state of Texas is much more then a former workmate, his legacy persists in innumerable ways. I won't take your time listing many of them, but I do have to share at least one. Notice the name of my blog? It isn't named after a South Park character, nor is it named after some pirate fetish. No this transplanted Buckeye is the source of FatBeard. He has a peculiar ability to derive nicknames that persist way beyond any code or script he has ever written. I fell prey to his moniker making over 3 years ago.

OK - now that you know how FatBeard came to be, lets move on with the PowerShell related post.

Texas asked me the following:
When I type Get-ChildItem (dir, ls) and get a file listing I want to see the LastWriteTime in Military format. Easy enough! I sent him this:

Get-ChildItem | select @{Name="LWT";Expression={"{0:d} {0:HH}:{0:mm}" -f ($_.LastWriteTime)}}

His response went something like this (paraphrased for the children in the audience) . "You obtuse gluteus maximus, why would I want to type that each and every time I need a directory listing." Admittedly, he had a point.

Thus begins my first foray into extending types. Using this as my guide (, I was able to quickly accomplish the task.

My steps were as follows:

  1. Navigate to C:\Windows\System32\WindowsPowerShell\v1.0 and make a copy of FileSystem.format.ps1xml
  2. Rename the copied file (CustomFileSystem.format.ps1xml)
  3. Open the renamed file and look for the tag.
  4. You should find something like this -
    [String]::Format("{0,10} {1,8}", $_.LastWriteTime.ToString("d"), $_.LastWriteTime.ToString("t"))
  5. As you can see, this handles the LastWriteTime formating for FileSystem related information. All we need to do is change it to meet our need. This should do -
  6. [String]::Format("{0,10} {1,2}:{2,2}:{3,2}", $_.LastWriteTime.ToString("d"),
    $_.LastWriteTime.ToString("HH"), $_.LastWriteTime.ToString("mm"),
  7. Save the file.
  8. We now need to load the new formatting. Easy enough!
    Update-FormatData -prependPath 'C:\WINDOWS\system32\windowspowershell\v1.0\ CustomFileSystem.format.ps1xml'
  9. Verify your custom formatting by typing a few Get-ChildItem commands.
Now that wasn't to hard was it! Well Texas is happy now and you know how to modify object formatting (and how FatBeard came to be).


Tuesday, August 25, 2009

PowerShell and ZedGraph – Example 3

Welcome to part three in our PowerShell/ZedGraph series. Make sure you take a look at Part 1 & Part 2 before you continue on. Before we jump into more advanced features, I thought I would demonstrate how to populate the chart with real data. Apparently some were not impressed by the Yuengling consumption example (a fictitious one at that!).

We will use the ever diligent Get-Process cmdlet for our "real" data. The end result will look something like this:

Pretty no?

The code:
#Load the zedgraph dll
$ZedGraphDll = "C:\zedgraph_dll_v5.1.5\ZedGraph.dll"
[System.Reflection.Assembly]::LoadFrom($ZedGraphDll) | out-null

# Create a WinForm to serve as a container
$global:form = new-object Windows.Forms.form
$form.Size = new-object System.Drawing.Size @(500,400)

# Create a ZedGraphControl
$zgc = new-object -typename ZedGraph.ZedGraphControl
$zgc.GraphPane.Title.Text = "Processes"
$zgc.GraphPane.XAxis.Title.Text = "Process"
$zgc.GraphPane.YAxis.Title.Text = "WS(MB)"

# Go fetch me some processes data
$WS = @{Name='WS';Expression={"{0:#}" -f ($_.WorkingSet/1KB)}}
$processes = Get-Process | where {$_.WorkingSet -gt 30MB} | Sort Name | select $WS, Name

$a = $b = @()
foreach($Process in $Processes)
$a+= $Process.WS
$b+= $Process.Name

$curve = $zgc.GraphPane.AddBar("Test",$null,$a,[System.Drawing.Color]::Red)
$zgc.GraphPane.XAxis.Type = 'Text'
$zgc.GraphPane.XAxis.Scale.FontSpec.Angle = 65
$zgc.GraphPane.XAxis.Scale.MajorStep = 1
$zgc.GraphPane.X2Axis.Scale.FontSpec.Size = 8
$zgc.GraphPane.XAxis.Scale.TextLabels = $b

# Hide the legend
$zgc.GraphPane.Legend.IsVisible = $False

# Make me pretty...
$zgc.GraphPane.Fill = New-Object ZedGraph.Fill([System.Drawing.Color]::WhiteSmoke,[System.Drawing.Color]::Lavender,0)
$zgc.GraphPane.Chart.Fill = New-Object ZedGraph.Fill([System.Drawing.Color]::FromArgb(255,255,245), [System.Drawing.Color]::FromArgb(255,255,190),90)

# Calculate the Axis Scale Ranges

# Add our graph to the form
$zgc.dock = [System.Windows.Forms.DockStyle]::Fill

# Show the form
The only difference in this example is we create and populate a couple arrays (forgive my naming conventions) and use them as our data points.

I hope I have answered some of the questions about "real" data utilization with ZedGraph.


Monday, August 24, 2009

PowerShell and ZedGraph – Example 2

If you haven't already, take a look at Example 1 before you move forward!
Let's continuing with our charting example. At this point we have created a simple bar chart. Let's make a couple of modifications.
The first thing we will do is to remove the legend.
All we need to do is set the Legend.IsVisible property:
$zgc.GraphPane.Legend.IsVisible = $False
Next, let's apply some color. We will set the pane background and the Axis background with a gradient:
$zgc.GraphPane.Fill = New-Object ZedGraph.Fill([System.Drawing.Color]::WhiteSmoke,[System.Drawing.Color]::Lavender,0)

$zgc.GraphPane.Chart.Fill = New-Object ZedGraph.Fill([System.Drawing.Color]::FromArgb(255,255,245), [System.Drawing.Color]::FromArgb(255,255,190),90)

Lastly, lets add some text to the graph:
$text = New-Object ZedGraph.TextObj("Supply`nDepleted",5,30)
$arrow = New-Object ZedGraph.ArrowObj([System.Drawing.Color]::Black,10,5,26,5,15)

So our end result should look something like this:

The code...
#Load the zedgraph dll
$ZedGraphDll = "C:\zedgraph_dll_v5.1.5\ZedGraph.dll"
[System.Reflection.Assembly]::LoadFrom($ZedGraphDll) | out-null

# Create a WinForm to serve as a container
$global:form = new-object Windows.Forms.form
$form.Size = new-object System.Drawing.Size @(500,400)

# Create a ZedGraphControl
$zgc = new-object -typename ZedGraph.ZedGraphControl
$zgc.GraphPane.Title.Text = "Yuengling Consumption"
$zgc.GraphPane.XAxis.Title.Text = "Month"
$zgc.GraphPane.YAxis.Title.Text = "Bottles"

$xLabels = "April", "May", "June","July", "August"
$yLabels = 60, 60, 75, 70, 15

$zgc.GraphPane.XAxis.Type = 'Text'
$zgc.GraphPane.XAxis.Scale.TextLabels = $xLabels

# Hide the legend
$zgc.GraphPane.Legend.IsVisible = $False

# Fill the pane background with a gradient
$zgc.GraphPane.Fill = New-Object ZedGraph.Fill([System.Drawing.Color]::WhiteSmoke,[System.Drawing.Color]::Lavender,0)

# Fill the Axis Background with a gradient
$zgc.GraphPane.Chart.Fill = New-Object ZedGraph.Fill([System.Drawing.Color]::FromArgb(255,255,245), [System.Drawing.Color]::FromArgb(255,255,190),90)

# Add text item to decorate the graph
$text = New-Object ZedGraph.TextObj("Supply`nDepleted",5,30)

# Add an arrow pointer for the above text line
$arrow = New-Object ZedGraph.ArrowObj([System.Drawing.Color]::Black,10,5,26,5,15)

# Calculate the Axis Scale Ranges

# Add our graph to the form
$zgc.dock = [System.Windows.Forms.DockStyle]::Fill

# Show the form
Till next time - Enjoy!

Sunday, August 23, 2009

PowerShell and ZedGraph – Example 1

Was searching for a PowerShell Mathematics library when I stumbled upon a cool .NET drawing library that can be easily consumed in PowerShell –ZedGraph. This is a fairly robust library that will enable you to graph multiple types of charts, including overlays. I plan on writing a series of posts that will demonstrate these capabilities.

Lets get started!

For the first chart, we are going to keep it simple. We will create a standard Bar chart with some fictitious data. The end result will look something like this.

Now to the code:

# Load the ZedGraph dll   
$ZedGraphDll = "C:\zedgraph_dll_v515\zedgraph_dll_v5.1.5\ZedGraph.dll"
[System.Reflection.Assembly]::LoadFrom($ZedGraphDll) | out-null

# Create a WinForm to serve as a container
$global:form = new-object Windows.Forms.form
$form.Size = new-object System.Drawing.Size @(500,400)

# Create a ZedGraphControl
$zgc = new-object -typename ZedGraph.ZedGraphControl
$zgc.GraphPane.Title.Text = "Yuengling Consumption"
$zgc.GraphPane.XAxis.Title.Text = "Month"
$zgc.GraphPane.YAxis.Title.Text = "Bottles"

$xLabels = "April", "May", "June","July", "August"
$yLabels = 60, 60, 75, 70, 15

$zgc.GraphPane.XAxis.Type = 'Text'
$zgc.GraphPane.XAxis.Scale.TextLabels = $xLabels

# Calculate the Axis Scale Ranges

# Add our graph to the form
$zgc.dock = [System.Windows.Forms.DockStyle]::Fill

# Show the form

As you can see, it is fairly simple to get a standard graph output. The class documentation on the ZedGraph site was very helpfull. Make sure you spend some time looking over it as you start to explore this dll. I will continue to build on this example over the next few days.


Wednesday, August 12, 2009

2009 Summer Scripting Games – Beginner Event 3

What a pleasant surprise! Came into work this morning and saw that @makovec sent me a note indicating that this mornings Scripting Guys Blog referenced one of my submissions posted during the 2009 Summer Scripting Games!

The posting sited the use of the “undocumented” parameter –delimiter.

Their post is here.

It isn’t very often that Stahler and elegant are used in the same sentence…

Wednesday, August 5, 2009

Removing Header/Footer lines from CSV files

I was recently tasked with scheduling a script to read in a TSM.out file and create a usable CSV file from it. The TSM *.out file has at least 9 header lines that are not relevant to the end file as well as some extra footer lines. I also noticed that the column headers are not descriptive either. So, to automate the file transformation, we look to PowerShell!
function Create-CSV {
param( [int]$HeaderLines,
[string]$NewColumnNames )

if(Test-Path -literalPath $SourceFilePath) {
$a = Get-Content -path $SourceFilePath
if ($NewColumnNames -ne $null) {
Add-Content -path $DestinationFilePath `
-value $NewColumnNames
# Grab only the lines we need
$a[$HeaderLines..($a.count - $FooterLines)] | `
foreach{Add-Content -path $DestinationFilePath -value $_}
else {
Write-Error "$SourceFilePath does not exist."
} # End of function

Create-CSV -HeaderLines 9 `
-FooterLines 4 `
-SourceFilePath 'c:\temp\test.out' `
-DestinationFilePath 'c:\temp\test.csv' `
-NewColumnNames "Nodename,hostname,tcpipaddress"


Monday, July 27, 2009

Central Ohio PowerShell Users Group

Please join us for our first Central Ohio PowerShell Users Group on Thursday, July 30th, 2009 at the Ohio State University Medical Center. Jeffery Hicks, Microsoft MVP will be joining us! For more information, visit the Central Ohio PowerShell Users Group site.

Thursday, June 4, 2009

Searching for files remotely

We are looking to possibly centralize the management of our OPT files. Thought maybe a quick script in PowerShell would do the trick. Ran the following line:

Get-childitem \\server1-vp01\c$ -Include *.opt -Recurse

and waited, waited, waited...... 6 minutes later the console displayed my results.
Knowing a little bit about WMI, I decided to approach this from a different angle:
Get-WmiObject `
-class CIM_DATAFile `
-computername 'server-vp01' `
-filter "extension='opt' and drive='c:'"
This gave me back the results in 2.73 seconds!

Comparing the times generated the following:
measure-command {`
get-childitem \\server-vp01\c$ `
-Include *.opt `
Days              : 0
Hours :
Minutes :
Seconds :
Milliseconds :
Ticks :
TotalDays : 0.00420690061458333
TotalHours : 0.10096561475
TotalMinutes : 6.057936885
TotalSeconds : 363.4762131
TotalMilliseconds : 363476.2131
measure-command {`
Get-WmiObject `
-class CIM_DATAFile `
-computername 'server-vp01' `
-filter "extension='opt' and drive='c:'"}

Days :
Hours :
Minutes :
Seconds :
Milliseconds :
Ticks :
TotalDays : 3.17014293981481E
TotalHours : 0.000760834305555556
TotalMinutes : 0.0456500583333333
TotalSeconds : 2.7390035
TotalMilliseconds : 2739.0035
Pretty obvious which method to use.
Once again, PowerShell and WMI save the day.

Wednesday, June 3, 2009

PowerShell Formatting Error

I was looking throught the PowerGUI forum yesterday when I saw a post similar to this:
$processes = Get-Process -name m*
$drives = Get-Wmiobject `
-class win32_logicaldisk `
-Filter "DriveType=3"

$driveinfo | Format-Table -autosize

When you run this code in an editor, you are likely to get the following error:
out-lineoutput : Object of type "Microsoft.PowerShell.Commands.Internal.Format.FormatStartData" is not legal or not in the correct sequence. This is likely caused by a user-specified "format-tabl e" command which is conflicting with the default formatting.

After a few minutes of research, I see that this has been registered on Microsoft Connect as a bug. A quick, easy fix is to pass the $processes object down the pipline to Out-Default. ( $processes | Out-Default )

Hope this helps!

Sunday, May 31, 2009

TechED 2009 (Pics)

Had a great time at TechED. I met a lot of great folks including quite a few from the PowerScript community. Following are a few pics from TechED - specifically the Birds of a Feather Session that Hal and Steven hosted (Practical PowerShell: Best practices from the field - Check out the recorded podcast of the session!).

Hal Rottenberg, PowerShell MVP & Steven Murawski hosting the BOF.

Hal fielding some questions.

Kirk Munro, PowerShell MVP (

From left to right, Steven Murawski, the back of Ed Wilson (1/2 of the Scripting Guys) and John Merrill (IT content evangelist and publishing manager in the Windows Server and Solutions Division User Assistance group).

Steven Murawski getting ready to work the PowerShell booth.

Ed Wilson evangelizing the merits of PowerShell.

Had a great time at the TechED, especially at the BOF and at the PowerShell Dinner.

Thursday, May 28, 2009

File Migration with PowerShell

Was recently tasked with assisting our file migration project. Until the following script (assumes you are using Quest AD Commandlets), this was a manual process.
$Users = get-content -path c:\users.txt
foreach ($User in $Users) {
$SourceFolder = "\\HumanResources\vol10\Users\$User"
$NewFolderName = "$SourceFolder-Migrated"

$homeDir = "\\personal-p01\users$\" + $User.substring(0,1) + "\$User"
Copy-Item $SourceFolder -Destination $homeDir -Recurse

$User -ObjectAttributes @{'HomeDirectory'=$homeDir; 'HomeDrive'= 'P:'}
$rule=new-object System.Security.AccessControl.FileSystemAccessRule("OSUMC\$User","FullControl","Allow")

foreach ($file in $(Get-ChildItem $homeDir -recurse)) {
$acl=get-acl $file.FullName
set-acl $File.Fullname $acl
# set the acl on the root folder
set-acl $homeDir $acl

# Rename-item doesn't work, so copy and delete
Copy-item $SourceFolder -Destination "$SourceFolder-migrated" -Recurse
Remove-Item $SourceFolder -Recurse -Force

Friday, April 17, 2009

Using PowerShell, LogParser and PowerGadgets to get Exchange 2003 storage information – Part 1

Was asked this week to write up a script that would graphically show how much free space was available in our Exchange Databases. My twitter colleague Alan Renouf made me aware of Jonathan Medds's post about Exchange free space. This gave me a good starting point. I now knew to look for EventID 1221 in the Exchange server(s) application event log. Being a big fan of LogParser, I decided to query the event logs using it instead of Get-WMIObject Win32_NTLogEvent.  My reasoning for this is two-fold, I have a lot of LogParser scripts that I am able to reuse in PowerShell.  Additionally, for many log parsing tasks, LogParser still is faster then using Select-String or other parsing methods. 

I highly suggest that if you are entertaining the idea of using call LogParser within PowerShell, you should copy David Muegge’s LogParser library into your code base.  Check out his posting for an explanation on how his library works (it is slick).

function Get-ExchangeEventLogRecords {

# Include our LogParser library
# Check out David Muegge's Blog
# for more info on his library

# This is the query we will be passing
# to LogParser
# Let's hear it for Here-Strings!
$query = @"
SELECT ComputerName, timewritten, Message
FROM \\Exchange01\application
WHERE eventID = 1221 and
SourceName = 'MSExchangeIS Mailbox Store' and
to_date(timewritten) = System_Date()
$inputformat = Get-LPInputFormat "evt"
$records = Get-LPRecordSet $query $inputformat
return $records

The function Get-ExchangeEventLogRecords returns all the records that I care about (in this example I have included only one Exchange Server). In order to get the exact fields I want, I need to parse the Message field from the event log.  The field looks something like this:

The database "MSXC01SG4\MSXC01SG4DB4" has 4626 megabytes of free space after online defragmentation has terminated…

The next function takes care of the parsing and creates a custom object for me.

function Format-ExchangeEventLogObject {
Begin {
$regex = [regex] '(?:MSXC\w+\\MSXC\w+)'
$regex1 = [regex] '(?:\s\d+)' }
Process {
$Store = $regex.Match($_.message) | select Value
$Mem = $regex1.Match($_.message) | select Value
$Mem = $Mem.value.ToString().Trim()
$obj = New-Object psObject
$obj | Add-Member NoteProperty TimeWritten $_.TimeWritten
$obj | Add-Member NoteProperty ExchangeServer $_.ComputerName
$obj | Add-Member NoteProperty Store $Store.Value
$obj | Add-Member NoteProperty AvailableMem $Mem
Write-Output $obj }

One thing to note, so far, we have only looked at the available space within the *.EDB file.  In the next post, I will take this information and gather a few more relevant fields (total size of the EDB file, size of STM file, estimated availability in the STM file) and show a graphical representation of the data.


Thursday, April 9, 2009

Quest Management Shell 1.2 – Updated AD cmdlets!

I just installed Quest’s latest version of their free AD cmdlets. After installing, I wanted to see what new cmdlets have been provided. The first thing I noticed in looking for the updated cmdlets was that you can no longer use Get-QADCommand to see the Quest Commands, that has been replaced with Get-QCommand. Looks like there are 9 new cmdlets:

  • Add-QADMemberOf – I see this one getting some use! Allows you to add a single object to one or many groups.
  • Approve-QARSApprovalTask – An ActiveRoles Server cmdlet. Looks like a workflow approval cmdlet
  • Get-QADMemberOf – This will be handy for auditing purposes! This cmdlet allows you to retrieve the groups that an object belongs to.
  • Get-QADPasswordSettingsObjectAppliesTo – This new cmdlet is specific to Windows Server 2008 Active Directory. Allows you to retrieve groups affected by a particular password settings object.
  • Get-QARSApprovalTask – Another ActiveRoles Server cmdlet. You can use this one to grab approval task records.
  • Get-QARSOperation - Another ActiveRoles Server cmdlet. Used to retrieve the operations records.
  • Reject-QARSApprovalTask – Like the Approve-QARSApprovalTask accept this one is used to reject the workflow task.
  • Remove-QADMemberOf – The opposite of Add-QADMemberOf. Use the cmdlet to remove an object from one or more groups. This one will get some use in our environment!
  • Restore-QADDeletedObject – Wish we had this one a few months ago! This cmdlet will allow you to undelete objects in AD by restoring tombstones into normal objects. An example from the help file demonstrates the power of this cmdlet:
    Get-QADUser -Tombstone -LastKnownParent '<DN of container>' -Name 'John Smith*' Restore-QADDeletedObject

Nice collection of new functionality!

When looking at the example in the help on Restore-QADDeletedObject, I saw the parameter –Tombstone used with Get-QADUser. Further investigation yields that Quest has added quite a few parameters to various QAD cmdlets.

This is a list of new parameters from the documentation supplied with the new version:

ParametersCmdlet added to

Can’t wait to dive in a bit more and use the newest version.


HIMSS - MSHUG Presentation

I had the opportunity to evangelize about PowerShell at the Healthcare Information and Management Systems Society (HIMSS) - Microsoft Health Users Group (MSHUG) last Saturday (4/4/2009). Other then my laptop freezing in the first ten minutes of the presentation, all went well. Given that I only had about 50 minutes, I decided to focus on "what" PowerShell can do versus "how". So I demonstrated the following:

There were not nearly as many questions as I had anticipate (perhaps following lunch had a little to do with that).  Of the questions asked, a couple stood out.

  • Why not continue to use VBScript and/or Perl
  • Can scripts be compiled

Was able to reiterate the point that if you are moving forward with Microsoft, you really have no choice.  Throughout the session I tried to emphasize the power of objects and how utilizing them makes a systems admin job so much easier. As far as compiling is concerned, my response was “if compiling them is a requirement, then you should look at writing it in C# instead”.  Would be interested in seeing how others would have responded.

I want to thank the following people for sending PowerShell related materials for distribution at the session:

  • Susan Roper (Quest Software)
  • SoftwareFX sales staff
  • Jeffery Hicks (Sapien Technologies)


Friday, February 27, 2009

PowerShell and WSUS

Was recently tasked with writing an audit script of what servers we control that were not currently in WSUS. Following is a script that generates 2 text files. The first one will be a list of servers in the specific OU that we are concerned with are in WSUS and the second will be a listing of those that are not.

Shout out to Jonathan Medd for showing the way with his WSUS PowerPack.

function Get-WSUSComputers()
    $wsus = [Microsoft.UpdateServices.Administration.AdminProxy]::getUpdateServer('WSUS_Server',$false)
$ServerList = 'c:\ProductionScripts\Servers.txt'
$WSUSList = 'c:\ProductionScripts\WSUS.txt'
$InWSUS = 'c:\ProductionScripts\FirstInSecond.txt'
$NotInWSUS = 'c:\ProductionScripts\FirstNotInSecond.txt'
# Export out a list of all servers
Get-QADComputer -OSName *Server* `
    -SearchRoot 'OU=Your OU,OU=Your Company,DC=Company,DC=com' |`
    Sort-Object DNSName | `
    Select-Object DNSName | `
    Out-File -FilePath $ServerList -Force
# Export out a list of all servers
# listed in WSUS
Get-WSUSComputers | Sort FullDomainName |`
    Select FullDomainName | `
    Out-File -FilePath $WSUSList -Force
$Servers = get-content $ServerList
$WSUS = get-content $WSUSList 
New-Item $InWSUS -Type file -Force
New-Item $NotInWSUS -Type file -Force
Foreach ($Server in $Servers)
    If ($WSUS -contains $Server)
        Add-content $InWSUS $Server
        Add-content $NotInWSUS $Server


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
   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 
   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)
  15: # Create our column headers
  16: $Info.Cells.Item(1,1) = "VM Name"
  17: $Info.Cells.Item(1,2) = "PowerState"
  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
  25: # Data starts at row 2
  26: $intRow = 2
  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:     }    
  40:     $Style.Cells.Item($intRow, 1) = $VirtualServer.Name
  41:     $Style.Cells.Item($intRow, 2) = $PoweredState 
  42:     $intRow++
  43: }
  45: ## Now for some formatting 
  46: #  For more data on the enumeration values look at the following:
  47: #  XlFormatConditionType Enumeration: 
  48: #  XlFormatConditionOperator Enumeration:
  49: #  XlIconSet Enumeration        
  50: #  XlConditionValueTypes Enumeration
  51: ## The constants that we will use
  53: $xlCenter = -4108
  54: $xlCellValue = 1
  55: $xlEqual = 3
  56: $xl3TrafficLights2 = 5
  57: $xl3Symbols = 7
  58: $xlConditionValueNumber = 0
  59: $xlIconSet = 6
  61: # Make Column "A" wide enough for content
  62: $info.Range("A:A").entireColumn.Autofit()
  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 
  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
  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
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!