2015-September Scripting Games Wrap-Up

The September puzzler wasn't intended to break your brain - but it was intended to highlight an extremely important pipeline technique. Let's begin with our Celebrity Entry, from Stephen Owen.

Celebrity Entry

Alright guys, buckle yourselfs (selfs?  Selves?  I'm not sure which one it is..and this is supposed to be a stream of consciousness thing, so I'll just leave it as is)

So, we've got a CSV, with either computernames or IP addresses.  The fact that RPC is already open and all of my communcations works..and it's the same domain?  There goes like half of what I normally have to fix, right there! 

This one shouldn't be too bad.  We need to iterate through each computer in the list, using either it's IP address or name--whatever we've got listed, grab some info, then return back the OS version and the machine name.  Not too bad!

Let's get coding!

I've got a few VMs, all joined to my local domain FoxDeploy, (FoxDeploy, named after the wonderful PowerShell Blog FoxDeploy.com, which I happen to write.  Quick, go there right now and follow me!), and I've already got an account I can use, so I'll just test this in my own environment.

Alright, I've fired up PowerShell and have a connection to Hyper-V.  I'll only perform this puzzle/test  on VMs that are online.

Inline image 17

There are all of my VMs.  Now let me filter down to only those that are Running:

Inline image 16

So far so good!  I know I need to have a column called MachineName, so I'll just select the Name column and rename it to MachineName using a calculated property (it's one of these funky things, like when you select a property but do some editing to it in real time.  For example

Get-Process | select Name,@{Name='Full Name';Expression={$_.Description}}  

Don't just read it, try it!  See, we selected the Name property as it was, but also made a new property/column called 'Full Name', and then populated it with the .Description property for each object.  Anyway, the whole point of this was that I'm using a Calculated Property.  With me?  Great!

To change the name of the column from Name to machinename, I'll just do this: Get-VM | ? State -ne Off | Select @{N='MachineName';Exp={$_.Name}} | convertto-csv

 Inline image 15

Oops, should have used Export-CSV, not ConvertTo.  I literally always make that mistake.  Still, I can see that this is looking pretty much like what I need it to look like, I'll preserve this one-liner and just add out-file to the end.

 Inline image 14

Protip : you can use the start command to open webpages, or open whatever file in their default application.  The more you can do from the command line, the more like a wizard you'll seem to mere mortals.  Don't forget!

For one last thing, I'll go ahead and open it in Excel and add my own IP address,

 Inline image 13

Ugh, I forgot to use the -NoTypeInformation field when I exported this file.  No biggie, it shouldn't hamper me in this case.  

 Inline image 12

OK, I've got my input file.  Now, the task was to find the OS name.  I don't off the top of my head know where to find that, other than the SystemInfo.exe DOS command.  However, stuff like OS Captions and other frilly and sometimes useful data normally live in WMI, so I'll start by stabbing around to find it in WMI first.

My normal approach to WMI is to first use the Get-CimClass cmdlet as a searching tool, to find classes that might have good info.  If I find a juicy looking one, I'll then use Get-CimInstance to look inside the class and see what I find.

If you're confused about why I'm talking CIM now when I was saying WMI earlier, just picture WMI and CIM as two different gateways to the same juicy goodness database that is the WMI Repository.  Two doors - One database.  Got it? 

Get-CimClass is cool becasuse you can do a wildcard search, so I'll start by looking for things that have the word OS in them.  

Get-CimClass *os*

 Inline image 11

Uh, that was way too much.  How about Operating System instead?

Get-CimClass *Operatingsys*

Inline image 10 

Oh this is much better.  Normally, if you look at WMI/CIM and see classes that are otherwise the same but for the letters CIM/Win32 in the beginning, you can safely assume they'll be duplicates.  

To recap,  first I found the Class that might have juicy stuff using Get-CimClass.  Now I look at Get-CimInstance to actually pull the instances of that class and see what it contains.  CIM_OperatingSystem has some properties called Caption,Description etc, so I'll start there:

Get-CimInstance CIM_OperatingSystem

 Inline image 9

This looks good but there is probably more.  When in doubt, FL*  it out.  ( I pronounce that as FLº it out, acting like it's a little 'o' character.  Cool?  No?  Not cool…oh well)

Get-CimInstance CIM_OperatingSystem | FL * 

Inline image 8

Ding ding ding, we have a winner!  So, I've found the info I want, which is the Caption property of the CIM_OperatingSystem Class.  

Now to get some stuff from other PCs. Let's start simple with importing the machines:

 Inline image 7

Alright, now, let me try on one of these PCs.  Out of habit, Pretty much always type GWMI, which is shorthand for Get-WMIObject

Get-WmiObject Win32_OperatingSystem -ComputerName VM01 | select Caption

Inline image 6

I'm pretty sure there is a field somewhere in OperatingSystem that gives you the computer name too, so I'll just find it with a lazy wildcard for *name*

Get-WmiObject Win32_OperatingSystem -ComputerName VM01 | select caption,*name*

Inline image 5

Got it, CSName!  Almost done, now, I just need to nest an Import-CSV in here with parenthesis for the ComputerName Property

Get-WmiObject Win32_OperatingSystem -ComputerName (import-csv .\MachineNames.csv) | select caption,CSname

Inline image 4

As Jason Helmick would say 'blood in the water!'.  If we drilled down into any of these errors, we'd see that it choked trying to find our computers, a sure sign that I'm not giving PowerShell the input that it wants.  The problem is probably the mismatched properties, I'm giving PowerShell a $_.MachineName, but it wants a $_.ComputerName.  The easiest way around this is to just Pipe my Import-CSV into a Select MachineName, using the -Expand property to toss out the header/property name.

Get-WmiObject Win32_OperatingSystem -ComputerName (import-csv .\MachineNames.csv | select -expand MachineName) | select caption,Csname

Inline image 3

Winner winner, chicken dinner!  My last step is to export this as a CSC and change the column names AGAIN, this time to MachineName and OSVersion.  More calculated properties!

PS L:\> Get-WmiObject Win32_OperatingSystem -ComputerName (import-csv .\MachineNames.csv |
select -expand MachineName) | select @{N='MachineName';Exp={$_.CSName}},`

 Inline image 2

Now, to dump it into a CSV file.

PS L:\> Get-WmiObject Win32_OperatingSystem -ComputerName (import-csv .\MachineNames.csv |
select -expand MachineName) | select @{N='MachineName';Exp={$_.CSName}},`
@{N='OSVersion';Exp={$_.Caption}} | Export-Csv .\Output.csv

 Inline image 1

This was fun!  I left my errors here so you can see that even those of us who pretty much do PowerShell for a living are just normal folks who typo from time to time, and rely on the tools we have built into the console to succeed. 

Official Answer

While there's no one right way to accomplish this task, our puzzle author obviously has an answer in mind. Here it is:

Import-CSV Input.CSV |
Select-Object -Property @{n='ComputerName';e={$_.MachineName}} |
Get-CimInstance -Class Win32_OperatingSystem |
Select-Object -Property @{n='MACHINENAME';e={$_.CSName}},
 @{n='OSVERSION';e={$_.Caption}} |
Export-CSV Output.CSV

The only real difference is that this example assumes Get-CimInstance will accept its -ComputerName parameter from the pipeline ByPropertyName. By renaming the CSV file's column (by using Select-Object), we can make that work. This will run somewhat differently than Stephen's example, because computer names will be able to stream through the pipeline one at a time. In Stephen's example, the computer names will all be read in one batch, and passed to Get-CimInstance in one batch.

Your Answers

Kirill Pashkov has what is probably the best attempt at shortening the command line as much as possible. He dug out more command aliases than even our puzzle author realized existed!

Many, many, many of you - Joe Kirby, looking at you - came up with substantially the same thing as the official answer, some in short form, some in long form. Good work! Others took very close approaches.

Jeff Bunting came up with an answer that is worth looking at. In running some tests with really really really big CSV files, Jeff's solution ran slower, in part we think due to his use of ForEach-Object, rather than Select-Object. That's just a guess, of course, and it still arrives at the correct result, but it's always interesting to see the directions other folks take.

And Flynn Bundy, thanks for the nice comments - we will keep it up, but only because you asked! And, interesting take on the challenge - adding in the Test-Connection is a cool extra.

Rob Campbell had a really interesting approach, managing to eschew curly brackets entirely by using some fairly esoteric syntax. Worth a look if you want to see how weird PowerShell can get when you try!

This challenge had some of the most, and the best, variations yet - we hope you all keep playing!

Posted in:
About the Author

PowerShell.org Announcer

Profile photo of PowerShell.org Announcer

This is the official account for PowerShell.org and sponsor announcements.