Fun Tricks with Format-Table and Select

Caution: This is a great example of how PowerShell is awesome... if you can keep track of all the punctuation it uses! But it's also a great example of how PowerShell isn't a scripting language - it's a shell that has scripting capabilities (subtle difference).

Here's my scenario: I want to display information about some remote computers. Specifically, I want the OS version and build number, service pack version, computer name, and BIOS serial number (because my company puts asset tag information in the BIOS serial number). I can get that information from two WMI classes, Win32_OperatingSystem and Win32_BIOS, but I want the final output to be a single table.

Format-Table to the rescue!

The trick is called a hashtable or dictionary object, something I've probably written about before. I prefer the term dictionary, because these things basically let you add a bunch of "words" and "definitions." If you know the "word," it'll give you the corresponding "definition." The official terms for those are keys and values. Just keep that in mind.

So, Format-Table is designed to accept a list of properties that you want displayed. When you query something like Win32_OperatingSystem, you basically get back a big table of information. It has one row (called an object), because computers only have one operating system. It has many columns (called properties), detailing the various bits of information about the operating system. If you run this:

<code style="color: #006699; font-weight: bold; ">div>Get-WmiObject -class Win32_OperatingSystem -computername Server-R2 | Format-List *div><code style="color: #006699; font-weight: bold; ">div>div>

You can see all of those properties in list form. I only want a few, so I'll ask Format-Table to trim the list down for me:

Get-WmiObject -class Win32_OperatingSystem -computername Server-R2 | Format-Table -property __Server,Caption,BuildNumber,ServicePackMajorVersion -autosize -wrap

Two problems: I want the "__SERVER" property (which is attached to every WMI object and contains the name of the computer that WMI info came from) displayed as "ComputerName." And I need that BIOS information.

Here's where those hashtables come into play: Format-Table is designed to accept a hashtable in that list of properties. The hashtable has to be very specifically constructed: It needs a key called "Name" or "Label" (which can be abbreviated as "n" or "l"), with a value of the column header I want used. It needs a second key called "Expression" (abbreviate as "e"), and its value must be a script block that tells the shell how to derive the value I want used for that table column. Within that script block, I can use the $_ placeholder to access the current row's information. So, to rename __SERVER to ComputerName:

Get-WmiObject -class Win32_OperatingSystem -computername Server-R2 |
 Format-Table -property @{n='ComputerName';e={$_.__SERVER}},
  Caption,BuildNumber,ServicePackMajorVersion
  -autosize -wrap

I've broken that up across several lines for readability; you'd type all that on one line. You can see where I've used $_ to access the current row; following it with a period lets me access columns from that row of the table - specifically, that awkwardly-named __SERVER property.

By the way, you'll want to change the -computername to something, like localhost, if you're trying this.

And yes, I realize this syntax is a little weird and hard to grasp. Think of how your grandparents would feel if you told them to run "Dir *.* /s" - you've learned weird, complex syntax before, and this is just more of it that you'll have to start memorizing. Price of entry, so to speak.

Anyway, now I want to go on and get that BIOS information. The neat thing about those script blocks is that they can contain anything, including other commands. So I'm going to start with a simple command:

Get-WmiObject -class Win32_BIOS -computername Server-R2

That does what I want, but it's too much information. I JUST want the SerialNumber property. So I'll use Select-Object to trim the list down to just the one I want - it has the same basic capabilities as Format-Table in this regard.

Get-WmiObject -class Win32_BIOS -computername Server-R2 | Select-Object -property SerialNumber

Hmm, not quite it. It's creating a whole new table with just that one column. I really just want the serial number as a simple string, not as part of a whole new table. Fortunately, Select-Object can handle that: Its -expand parameter will take whatever property ("table column") I specify, and just grab the property's value (e.g., the contents of that table column).

Get-WmiObject -class Win32_BIOS -computername Server-R2 | Select-Object -expand SerialNumber

Perfect. Now I need to combine that with my earlier statement:

Get-WmiObject -class Win32_OperatingSystem -computername Server-R2 |
 Format-Table -property @{n='ComputerName';e={$_.__SERVER}},
  @{n='BIOSSerialNumber';e={
   Get-WmiObject -class Win32_BIOS -computername $_.__SERVER | Select-Object -expand SerialNumber
  }},
  Caption,BuildNumber,ServicePackMajorVersion
  -autosize -wrap

Now, that second command runs and becomes the contents of my new BIOSSerialNumber column. Perfect!!! 

Wait, not so perfect. I might not always want this in a table. Sometimes I might want to hit several computers at once and have the results in a CSV file. Well, unfortunately, you can't pipe the output of a Format- command to pretty much anything else. Try adding | Export-CSV to the end of that command and you'll get junk. Fortunately, Select-Object can pretty much replace Format-Table, and it produces perfectly normal information that can be piped anywhere.

Get-WmiObject -class Win32_OperatingSystem -computername Server-R2 |
 Select-Object -property @{n='ComputerName';e={$_.__SERVER}},
  @{n='BIOSSerialNumber';e={
   Get-WmiObject -class Win32_BIOS -computername $_.__SERVER | Select-Object -expand SerialNumber
  }},
  Caption,BuildNumber,ServicePackMajorVersion

Now, I can get a table or a CSV - or anything else:

Get-WmiObject -class Win32_OperatingSystem -computername Server-R2 |
 Select-Object -property @{n='ComputerName';e={$_.__SERVER}},
  @{n='BIOSSerialNumber';e={
   Get-WmiObject -class Win32_BIOS -computername $_.__SERVER | Select-Object -expand SerialNumber
  }},
  Caption,BuildNumber,ServicePackMajorVersion <strong>|</strong>
<strong>  </strong><strong>Format-Table -auto -wrap</strong>
 
Get-WmiObject -class Win32_OperatingSystem -computername Server-R2 |
 Select-Object -property @{n='ComputerName';e={$_.__SERVER}},
  @{n='BIOSSerialNumber';e={
   Get-WmiObject -class Win32_BIOS -computername $_.__SERVER | Select-Object -expand SerialNumber
  }},
  Caption,BuildNumber,ServicePackMajorVersion <strong>|</strong>
<strong>  </strong><strong>Export-CSV inventory.csv</strong>

A handy trick to keep in your pocket.

Discuss this Blog Entry 1

on Dec 21, 2011
Good stuff, man. Renaming headers helps the end user. When I used format-table with compare-object in my script I was getting errors (don't remember the error now) until I added out-string. Thank you.

Please or Register to post comments.

What's PowerShell with a Purpose Blog?

Don Jones demystifies Windows PowerShell.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×