Windows PowerShell
Cool Pipeline Tricks, Redux
Don Jones
Last month, I showed you a great pipeline trick that takes advantage of pipeline parameter binding to turn a somewhat complex task into a simple Windows PowerShell one-liner.
Here’s a quick recap: I created a comma-separated values (CSV) file containing information about new users I needed to create in an Active Directory domain. I made sure that the CSV file’s column headers matched the parameter names of the New-ADUser cmdlet (which comes with Windows Server 2008 R2, and can be used with Windows Server 2003 domains as well). So the column headers were items like “Department,” “City,” “Name,” “GivenName,” “Surname,” and so forth. Because New-ADUser binds pipeline input by property name, selecting those column names let me create the users with just two cmdlets:
Import-CSV c:\new-users.csv | New-ADUser
The Import-CSV cmdlet outputs an object for each line of the CSV file, and those objects have properties that correspond to the CSV column names. Because my CSV file contained all therequiredparameters of New-ADUser, I didn’t need to manually specify any parameters, though I could have specified any parameters not in the file that would be valid for every new user—an organization, for example:
Import-CSV c:\new-users.csv | New-ADUser –organization "OurCompany"
I think this example really highlights the power of Windows PowerShell: In the VBScript days, this task would have required a multi-line script (and you could do it that way in Windows PowerShell, too). But by taking the time to understand how the pipeline works, and with the assistance of a couple of well-written cmdlets, this “script” becomes a couple of simple commands.
So What’s the Problem?
A couple of astute readers posted questions on my Web site at www.ConcentratedTech.com, pointing out that my cool trick only worked if the CSV file’s column names exactly match the parameter names in New-ADUser. If an admin is creating the CSV file, you can probably rely on that exact match; if the file is coming from someone in Human Resources, the column names are more likely to be things like “First Name,” “Last Name,” and so on—not the exact match we need for New-ADUser to automatically bind those properties to its parameters.
Of course, you could always just edit whatever file or database the Human Resources folks sent you—but isn’t that kind of drudgery exactly why we invented computers? Windows PowerShell, of course, offers a quick and easy fix for “renaming” properties: The Select-Object cmdlet.
Select-Object is one of those cmdlets that can be a bit tricky to learn to use, because it does so much. For example, one of its primary tasks is to simplify objects by eliminating the properties you don’t need at the time. Simply specify the properties you do want, and the cmdlet will output a trimmed-down object. This can be useful for making output displays and text files a bit clearer, for example:
Get-WmiObject Win32_OperatingSystem | Select-Object BuildNumber,ServicePackMajorVersion
Another use of Select-Object is to select a subset of the objects in the pipeline, by using the –first or –last parameters:
Get-Process | Sort VM –desc | Select –first 10
Notice I’ve switched to the alias Select rather than the full cmdlet name.
Here’s the Solution
Another often overlooked capability of Select-Object is its ability to not only select the properties you want, but to rename them. If you’ve ever read the full help for the cmdlet (Help Select –full—something I absolutely recommend you do with every cmdlet), you’ll have discovered this capability and even seen examples of how to use it. Here’s an example:
Import-CSV c:\new-users.csv | Select @{Name="Surname";Expression={$_."Last Name"}}
If the input CSV file has a “Last Name” column, this will rename it to “Surname,” allowing the output property to match the parameter name of New-ADUser. What I’ve done is created a hashtable, also called a dictionary or associative array. The @ sign is an array operator in Windows PowerShell; the hashtable is an array that consists of key-value pairs. In this case, the key is the name of the property I want to create, and the value is the content I want assigned to that property. Within the expression portion of the hashtable, I can use the $_ variable to refer to the current pipeline object—in this example, I’ve retrieved its “Last Name” property (I had to use quotes around the property name because the property name contains a space).
You can submit as many of these as you like to Select-Object:
Import-CSV c:\new-users.csv | Select @{Name="Surname";Expression={$_."Last Name"}}, @{Name="GivenName";Expression={$_."First Name"}}
The catch is that Select-Object will only output exactly what you tell it to, so you can’t just tell it to rename one or two columns and have it output those two plus everything you didn’t need to rename. You generally need to specify every property you want output to the next cmdlet. Of course, if you don’t need to rename every column from your CSV file, you can just list them as is:
Import-CSV c:\new-users.csv | Select @{Name="Surname";Expression={$_."Last Name"}}, @{Name="GivenName";Expression={$_."First Name"}},Department,Organization,Name
In this case, “Department,” “Organization,” and “Name” would be passed along without being renamed, which is fine—they exactly match the associated parameters of New-ADUser.
This might seem painful if you have a lot of columns to specify, so here’s a shortcut:
Import-CSV c:\new-users.csv | Select @{Name="Surname";Expression={$_."Last Name"}}, @{Name="GivenName";Expression={$_."First Name"}},*
The “*” will output all of the properties from the input object, in addition to the “Surname” and “GivenName” properties I’ve created. So let’s say I have a CSV file that looks like this:
First Name,Last Name,Department,Name
Don,Jones,IT,DonJ
Greg,Shields,Janitorial,GregS
Jeff,Hicks,IT,JeffH
I can then use these two cmdlets:
Import-CSV c:\new-users.csv | Select @{Name="Surname";Expression={$_."Last Name"}}, @{Name="GivenName";Expression={$_."First Name"}},*
And my output objects will each have six properties: “First Name,” “Last Name,” “Department,” “Name,” “Surname,” and “GivenName.” In other words, all of the original properties, plus my additions. I then just tack on the New-ADUser cmdlet:
Import-CSV c:\new-users.csv | Select @{Name="Surname";Expression={$_."Last Name"}}, @{Name="GivenName";Expression={$_."First Name"}},* | New-ADUser
The New-ADUser cmdlet won’t be able to do anything with the “First Name” and “Last Name” properties, since those don’t match any of its parameters. That’s okay—the cmdlet will simply ignore any properties it can’t bind. It will, however, bind the remaining four—“Surname,” “GivenName,” “Department,” and “Name,” in this case—and create the new user accounts for me.
Changing Your Mindset
I find that one of the biggest challenges in using Windows PowerShell is to never give up. In other words, if you can’t use a provided CSV file just because the column names don’t match what you need, don’t assume that the answer is to change the CSV file! Windows PowerShell can almost always do what you need—with just a little investigation on your part.
So what sticky challenges have you run into with Windows PowerShell? I’d love to know—and to maybe base a future article on the answer! Post questions at www.ConcentratedTech.com, where I’ll be able to answer immediately online, gather additional details from you and possibly build a future article for Microsoft TechNet Magazine.
Don Jones is one of the nation’s most experienced Windows PowerShell trainers and writers. He blogs weekly Windows PowerShell tips at ConcentratedTech.com; you may also contact him or ask him questions there.