Summary: Ed Wilson, Microsoft Scripting Guy, talks about using Windows PowerShell to parse data obtained from Microsoft Operations Management Suite.

Microsoft Scripting Guy, Ed Wilson, is here. One of the things I have been working on a lot the past month is the new Microsoft Operations Management Suite Blog. For one thing, it is on the new blogging platform we using at Microsoft, so the blog is like a new toy with lots of fun things for a geek to play with. In addition, Microsoft Operations Management Suite (MS OMS) is a tremendously powerful platform that uses Windows PowerShell in the background, so it is also way cool to play with. With these two sets of givens, it was only a matter of time before I begin to merge the two.

The other day I wrote a blog post for the MS OMS blog titled Perform system update assessment with #MSOMS. I have written such articles before for the Hey, Scripting Guy! Blog, but they were always a bit annoying to write because update information has a tendency to be reported in different ways, in different places, depending on the level of the operating system and so on. It can be done, but I am never too sure of the results, for instance, as compared to what the Security Update tool reports in Control Panel. The System Update Assessment tool in MS OMS is reliable, produces cool reports, is quick, and it can export a CSV file. 

Parsing MS OMS data with PowerShell

The first thing I do is in MS OMS. I click the Export button at the bottom of the System Update Assessment tool:

After I save the data in a location I can find, I open the data in a Microsoft Excel spreadsheet:

Import the CSV file using PowerShell

The way cool thing about importing the CSV file with Windows PowerShell is that I obtain objects that represent each row that appears in my Excel sheet. This makes the data easier to read and manipulate.

Importing the CSV file is easy, I use the Import-CSV cmdlet and store the returned objects in a variable that I name $csv:

$csv = Import-Csv C:\fso\SearchResults.csv

When I call the $csv variable, the objects that I stored appear in my Windows PowerShell console…and scroll and scroll and scroll. The commands and a portion of the output are shown here:

Each of the column headings now appear as a property associated with each instance of the object. So how many objects (rows) do I have? The following command shows me that there are 522:

PS C:\> $csv.Count

522

I may be interested in how many servers need which update. I can do this by grouping the updates by title, and then sorting the output. I use the following command (ft is an alias for Format-Table):

$csv | group title -NoElement | sort count -Descending | ft count, name

Here is the output:

Perhaps I am interested in the KB ID, the severity, and the computers to which the update applies. I can easily use a command such as the following to obtain the information:

$csv | select computer, kbid, msrcseverity | sort msrcseverity, computer

The output will be something like the following:

Maybe what I want to know is how many updates require a reboot. I can obtain this information by using the following command:

$csv | sort rebootBehavior -Descending | where rebootbehavior -eq 'CanRequestReboot' |

select computer, kbid, rebootbehavior, MSRCSeverity

The command and its output are shown here:

One thing that is kind of fun is to see how long it will take to install of these updates. There is an InstallTimePredictionSeconds property that I can use. All I need to do is add all the values together. (Note that some of the updates do not include a prediction time.) I use the following command:

PS C:\> $a = 0

PS C:\> $csv | % {$a+=$_.installtimepredictionseconds}

PS C:\> $a

12343.6000473499

Dividing by 60 gives me the number of minutes:

PS C:\> $a / 60

205.726667455832

This is just scratching the surface of how I can use Windows PowerShell to parse the data exported from Microsoft Operations Management Suite. Remember, it is Windows PowerShell and CSV manipulation. I have written several posts about using PowerShell with CSV files. Join me tomorrow when I will talk about more cool stuff.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. Also check out my Microsoft Operations Management Suite Blog. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy