Windows PowerShell Filtering and Formatting Data

Don Jones

There’s no question that Windows PowerShell can, without a lot of work on your part, return a great deal of valuable information. For example, consider the simple Get-WMIObject cmdlet, which can be used to return a list of services on the local computer. The default view, which lists the status, name, and startup mode of

the services running, is essentially a command-line view of the Services console. Windows PowerShell™ just makes it much easier to get to.

But there’s a lot more useful information available for services than just what is shown in the default view. Try running this command:

$s = get-wmiobject win32_service
$s[0] | gm

The first line of the command returns a collection of all services (or, more specifically, all instances of the Win32_Service class from Windows® Management Instrumentation, or WMI), storing them in the $s variable. The second line takes the first service in that collection (ordinal number zero, indicated in square brackets) and pipes it to the Get-Mem­ber cmdlet, which I’ve abbreviated using its alias, gm. The resulting output shows all the properties and methods available for that data type. I’ve decided to use the Win32_Service class in WMI, rather than the built-in Get-Service cmdlet in Win­dows PowerShell, because WMI actually exposes more information than the Microsoft® .NET Service­Controller object, including the Start­Name property. That property tells me the name of the user account that the service is running under. You can check the name of a single instance, such as the first one, as follows:

PS C:\> $s[0].StartName
LocalSystem

However, referring to services by their ordinal number within the collection isn’t very useful since the services aren’t in any particular order. (They are usually listed alphabetically, but not always.) From a management perspective, you’ll probably be most interested in a list of all services and the account each is using to log in with. This would be handy for, say, a compliance audit. So let’s back up a second and look at what WMI is providing by default. I’ll use Get-WMIObject’s shorter alias, gwmi (see Figure 1).

Figure 1 Using gwmi

PS C:\> gwmi win32_service

ExitCode  : 0
Name      : AcrSch2Svc
ProcessId : 1712
StartMode : Auto
State     : Running
Status    : OK

ExitCode  : 1077
Name      : Adobe LM Service
ProcessId : 0
StartMode : Manual
State     : Stopped
Status    : OK

This is obviously just a sample, but you can see that the output isn’t really great for a management report. What I want to know is the name of the service and the StartName, which is the account the service is running under. I’d also like the report in a more readable format, rather than this somewhat awkward-looking list. That’s where the formidable formatting and data-filtering cmdlets of Windows PowerShell come into play.

Get the Data You Need

I’m going to start by filtering the data from Get-WMIObject so that the properties I’m interested in are being displayed. The best way to do this is to use the Windows PowerShell Select-Object cmdlet or its simple alias, select. Select is designed to accept a collection of objects—such as the collection returned by Get-WMIObject—and display just the desired properties of those objects. This means I can pipe the output of gwmi to select and specify the two properties I’m interested in. Figure 2 shows the results, which include the Name and StartName properties formatted nicely as a table.

Figure 2 Viewing just the Name and StartName properties in a table

Figure 2 Viewing just the Name and StartName properties in a table (Click the image for a larger view)

If I were producing this report for a compliance audit, however, it really contains too much information. Some of the services are disabled, so it most likely wouldn’t matter to anyone reading the output what account the disabled services would theoretically use. Thus, I’d like to filter out all services that have a startup type of Disabled—that’s the StartMode property of the Win32_Service class.

Windows PowerShell performs object filtering using the Where-Object cmdlet or its shorter alias, where. The where cmdlet accepts a collection of input objects and runs each through a scriptblock to determine whether each object is going to be present in the cmdlet’s output based on a set of defined criteria. Each object that meets the criteria produces a True comparison and is included in the output; objects that produce a False value are not included.

So, I determined that I only want objects whose StartMode property equals Disabled. In Windows PowerShell terms, that evaluation looks like this:

$object.StartMode –eq “Disabled”

Of course, $object is just an example. I’m not actually writing a script and so I don’t have a variable named $object. Within the scriptblock used by where, however, I do have the use of a special variable named $_, which represents the object currently being evaluated by the cmdlet. So my where scriptblock might look like this:

$_.StartMode –eq “Disabled”

I can test that easily enough:

PS C:\> gwmi win32_service | where 
{$_.StartMode -eq “Disabled”}

ExitCode  : 1077
Name      : Alerter
ProcessId : 0
StartMode : Disabled
State     : Stopped
Status    : OK

Of course, in this quick test, the output is back to appearing in the default list-style. And, to conserve space, I’ve only included one service here. But you’ll notice that this output is backwards. I’ve included disabled services, as opposed to excluding them. That’s because I’ve got my scriptblock backwards: I need to include all services where the StartMode is not equal to Disabled, as shown by this modification to my example:

PS C:\> gwmi win32_service | where 
{$_.StartMode -ne “Disabled”}

ExitCode  : 0
Name      : AcrSch2Svc
ProcessId : 1712
StartMode : Auto
State     : Running
Status    : OK

That’s better! Now that the output contains only the services I’m really interested in, I can once again pipe the output to select and specify just the properties I want:

gwmi win32_service | where {$_.StartMode -ne “Disabled”} | select name,startname

This business of piping data from one cmdlet to another and to yet another really illustrates the capabilities of Windows PowerShell. I still haven’t written a script, yet I’ve managed to filter through a large data set to get just the output I’m looking for.

The Right Look

You should keep in mind that the output of select is still a bunch of objects. When I retrieve a nicely formatted table using Windows PowerShell, that’s really the PowerShell.exe rendition of those objects. In other words, Windows PowerShell knows that I, as a mere human, can’t see the objects, so it renders the objects as text. In this case, it rendered the objects as a table, with a column for each property, as illustrated in Figure 2.

This might be sufficient for my auditing purposes. But then again, it might not. Everyone has different needs and Windows PowerShell doesn’t presume to know what your specific needs are. Instead, it gives you the tools to format the output in whatever way works best for you. Four built-in cmdlets—Format-List, Format-Custom, Format-Table, and Format-Wide—are designed to accept a collection of objects (such as the collection returned by select) and format those objects in various ways. Format-Table is basically what Windows PowerShell is already using to format the output of my select cmdlet. For a different look, let’s try Format-List:

gwmi win32_service | where {$_.StartMode -ne “Disabled”} | 
select name,startname | format-list

The result looks a bit like this sample:

name      : AcrSch2Svc
startname : LocalSystem

name      : Adobe LM Service
startname : LocalSystem

The Format-Wide cmdlet is designed to produce a multi-column list of, by default, the first property from each object. Take this line, for example:

gwmi win32_service | where {$_.StartMode -ne “Disabled”} |
 select name,startname | format-wide

It produces a list of service names, which isn’t what I want. The output is missing the StartName (see Figure 3), which is an important piece of information I need in my auditing report.

Figure 3 Output displayed using the Format-Wide cmdlet omits key information--the StartName

Figure 3** Output displayed using the Format-Wide cmdlet omits key information--the StartName **(Click the image for a larger view)

Since I’m only dealing with two properties, it’s likely that either Format-Table or Format-List would be acceptable. But it’s unlikely that an auditor would be happy looking at this information on the screen. She would probably prefer a file of some kind.

Exporting Data

So how might an auditor want to view the data? Outputting the list of services and login names to a CSV (comma-separated values) file might be sufficient since the file can then be easily opened in Microsoft Excel®. To create a CSV file, simply pipe your output to the Windows PowerShell Export-CSV cmdlet:

gwmi win32_service | where {$_.StartMode -ne “Disabled”} | 
select name,startname | export-csv c:\services.csv

Of course, in today’s world, CSV seems a bit outdated. Perhaps your auditors would prefer the data to be displayed as a Web page on an intranet server. To do this, start by converting the output to HTML, using the ConvertTo-HTML cmdlet:

gwmi win32_service | where {$_.StartMode -ne “Disabled”} | 
select name,startname | convertto-html

Raw HTML is hard to view, so you’ll need to write the output to a file:

gwmi win32_service | where {$_.StartMode -ne “Disabled”} | 
select name,startname | convertto-html | out-file c:\services.html

The result, shown in Figure 4, is a well-formatted HTML page that can be posted on any Web server without further modification.

Figure 4 Output displayed as a well-formatted HTML page

Figure 4 Output displayed as a well-formatted HTML page (Click the image for a larger view)

Just the (Relevant) Facts

Windows PowerShell provides you with quick access to a broad range of management data. However, that data in its raw state isn’t always useful in a business sense.

By filtering the data (using where), choosing the desired object properties (using select), and applying an appropriate formatting option (such as Format-Table or Format-List), you can quickly turn this management data into useful information with very little effort. And then, by exporting the data to a file format that can be easily shared, you can take this information beyond your own desktop and communicate valuable information to other people in your organization.

Don Jones is the Director of Projects and Services for SAPIEN Technologies, and coauthor of Windows PowerShell: TFM (SAPIEN Press). Contact Don through his Web site at www.ScriptingAnswers.com.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.