Windows PowerShell Filter Left, Format Right

Don Jones


Filter Left—at the Source
Format Right—and You're Done with It
Under the Hood

On the Windows PowerShell Most Valuable Professional (MVP) mailing list, we’ve recently been discussing—and attempting to document—Windows PowerShell “gotchas.” You know what we mean: those little things that don’t work exactly the way you’d think they would— and that can really cause problems when you’re learning the shell.

Two of the biggest gotchas involve filtering and formatting. I mentioned on the list that I've tried to help folks avoid problems in those areas by remembering to "filter left, format right." It occurred to me that the phrase might be a good basis for a column on more effective use of Windows PowerShell—so here we are.

Filter Left—at the Source

I use Quest's free Active Directory (AD) management cmdlets (you can find them at a lot, and I encourage others to use them, too. One cmdlet, Get-QADUser, is designed to retrieve users from AD. Seeing people run a command like this one isn't that uncommon:

Get-QADUser | Where-Object { $_.Department -eq "Sales" } | Set-QADUser -department "Inside Sales"

This would change everyone whose AD Department attribute is set to Sales, so that the attribute was set to Inside Sales instead. The problem is where the filtering is happening. The first cmdlet, Get-QADUser, is going to retrieve every single user from the entire domain.

Fortunately, it won't retrieve every attribute from every user by default, but in a large domain, it will do more than enough damage as the poor domain controller (DC) struggles to transmit user-account information to your client computer. Your client must then examine each account, one at a time, and discard those that don't have a Department attribute containing the value Sales. What a waste!

That's why I like to "filter left." Always move filtering criteria as far to the left of he command line as possible.In this case, the Get-QADUser cmdlet itself happens to support filtering:

Get-QADUser -Department "Sales" | Set-QADUser -department "Inside Sales"

There's no longer any need for the Where-Object cmdlet. Better still, by using this syntax, Get-QADUser is transmitting the filter criteria to the DC, and the DC is retrieving and transmitting only those accounts that match this criteria. Domain controllers are fantastic at filtering—they do it all day long, really—so we're putting the filtering in the best possible place for fast execution. Your client won't have to deal with as many objects coming from the DC, so your command will execute faster and more efficiently.

Here's another example, using Windows Management Instrumentation (WMI):

Get-WmiObject CIM_DataFile -computerName Server2 | Where { $_.FileName -like "*.dll" }

This will contact WMI on Server2, get all instances of the CIM_DataFile class—which represents files on the hard drive—and filter out those that aren't DLL files. So you're left with a list of DLL files on that server. But this is going to take time to run—you're bringing all of the server's file information over to your computer. Again, moving filtering further left would help and, in this case, it's possible to do so:

Get-WmiObject CIM_DataFile -computerName Server2 -filter "FileName LIKE '*.dll'"

You need to use slightly different filter syntax because this syntax isn't being executed by Windows PowerShell. Instead, it's being transmitted to WMI on the remote computer. It will still take time to execute, but it will involve less network traffic and, ultimately, less overhead on both Server2 and your computer. Get in the habit of reading cmdlets' help files (Help Get-WmiObject, in this case) to see what filtering options a cmdlet offers. Only rely on Where-Object when the Get-* cmdlet you're using doesn't support the filtering that you need.

Format Right—and You're Done with It

The other gotcha relates to the Format-* cmdlets, such as Format-Table and Format-List. I'll see newcomers run something like this:

Get-Process | Format-Table ID,VM,Name | Export-CSV c:\processes.csv

It won't work. Oh, it will run, but you won't like the results. And if you take a second to really read that command, you may wonder exactly what you expected to happen anyway. You've gotten a bunch of processes, formatted them as a table—and then you somehow want the table to become a .CSV file? If the end goal is to get a .CSV file that only contains certain object properties, you would run this:

Get-Process | Select-Object ID,VM,Name | Export-CSV c:\processes.csv

If the goal is to get a formatted, columnar table into a text file, you would do this:

Get-Process | Format-Table ID,VM,Name | Out-File c:\processes.txt

But you can't mix and match these two approaches. Understanding why that's so requires some background on how those Format cmdlets work, along with how the Out-* cmdlets work.

The Windows PowerShell pipeline—which is what your commands run in-is hard-coded to end in the Out-Default cmdlet. This cmdlet does little more than redirect objects to Out-Host, which is responsible for displaying output on the screen.

Any command line—or pipeline, to use the correct term—that doesn't end in an Out-* cmdlet will, by default, end in Out-Default, which is the same as saying it ends in Out-Host. The trick is that the Out-* cmdlets don't know what to do with objects—they need those objects turned into formatting instructions. So when Out-Host gets Process objects, it sends those objects to one of the Format-* cmdlets. Which one it uses—Table, Wide, Custom or List—is based upon a set of rules that I won't be able to cover in this column. But it sends the objects to one of them, and the Format-* cmdlet takes the objects and produces formatting instructions. The Out-* cmdlets know how to consume those formatting instructions, and they use them to create whatever display is necessary.

So let's put that into practical terms: There are three or four main Out-* cmdlets that you'll encounter and use regularly: Out-Host, Out-File, Out-Printer and Out-String.

These are designed to consume formatting instructions only, and then take those instructions to construct a screen display, a text file, a printer page or a string, respectively. The only way to get those formatting instructions is to run objects through a Format-* cmdlet—and formatting instructions are the only thing the Format-* cmdlets produce. So when you run this:

Get-Process | Format-Table ID,VM,Name |Export-CSV c:\processes.csv

what goes into the .CSV file are the formatting instructions produced by Format-Table—probably not at all what you expected. After executing a Format-* cmdlet, your original objects are lost, and you're left with formatting instructions. Here's another simple rule for you: Format right. In other words, push the formatting cmdlet to the extreme right of your command line. The only thing that can come after a Format-* cmdlet is a carriage return or an Out-* cmdlet. That's why this is legal:

Get-Process | Format-Table ID,VM,Name | Out-File c:\processes.txt

Out-File knows how to read those formatting instructions and use them to create a text file. This is also legal:

Get-Process | Format-Table ID,VM,Name 

Here, you're relying on the hard-coded Out-Default, which redirects to Out-Host, which knows how to consume formatting instructions and create a text display in the host, or console, window. Keep your formatting as far to the right as possible, followed either by nothing or by an Out-* cmdlet, and you'll be fine.

One more tip: Nothing should follow an Out-* cmdlet because, with only one exception, none of them emit objects to the pipeline. There's no sense in having anything follow an Out-* cmdlet, because nothing will come of it.

Under the Hood

I hope that I've helped you understand why these gotchas are the way they are. But, at the very least, remember "filter left, format right" and you should be able to stay out of trouble by avoiding those gotchas entirely.

Many of Windows PowerShell's little quirks become easier to grasp once you really understand how and why the shell works the way it does. Taking the time to learn what's happening under the hood—and applying that knowledge whenever you're getting unexpected results in the shell—will quickly make you a Windows PowerShell guru.

Don Jones is one of the nation's most experienced Windows PowerShell trainers and writers. He blogs weekly Windows PowerShell tips at; you may also contact him or ask him questions there.