The other day I wrote a blog post for the MS OMS blog titled Perform system update assessment with #MSOMS<\/a>. 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<\/b> tool in MS OMS is reliable, produces cool reports, is quick, and it can export a CSV file. <\/p>\n The first thing I do is in MS OMS. I click the Export<\/b> button at the bottom of the System Update Assessment tool:<\/p>\n After I save the data in a location I can find, I open the data in a Microsoft Excel spreadsheet:<\/p>\n 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.<\/p>\n Importing the CSV file is easy, I use the Import-CSV<\/b> cmdlet and store the returned objects in a variable that I name $csv<\/b>:<\/p>\n $csv = Import-Csv C:\\fso\\SearchResults.csv<\/p>\n When I call the $csv<\/b> 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:<\/p>\n 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:<\/p>\n PS C:\\> $csv.Count<\/p>\n 522<\/p>\n 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<\/b> is an alias for Format-Table<\/b>):<\/p>\n $csv | group title -NoElement | sort count -Descending | ft count, name<\/p>\n Here is the output:<\/p>\n 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:<\/p>\n $csv | select computer, kbid, msrcseverity | sort msrcseverity, computer<\/p>\n The output will be something like the following:<\/p>\n Maybe what I want to know is how many updates require a reboot. I can obtain this information by using the following command:<\/p>\n $csv | sort rebootBehavior -Descending | where rebootbehavior -eq 'CanRequestReboot' |<\/p>\n select computer, kbid, rebootbehavior, MSRCSeverity<\/p>\n The command and its output are shown here:<\/p>\n One thing that is kind of fun is to see how long it will take to install of these updates. There is an InstallTimePredictionSeconds<\/b> 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:<\/p>\n PS C:\\> $a = 0<\/p>\n PS C:\\> $csv | % {$a+=$_.installtimepredictionseconds}<\/p>\n PS C:\\> $a<\/p>\n 12343.6000473499<\/p>\n Dividing by 60 gives me the number of minutes:<\/p>\n PS C:\\> $a \/ 60<\/p>\n 205.726667455832<\/p>\n 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<\/a>. Join me tomorrow when I will talk about more cool stuff.<\/p>\n I invite you to follow me on Twitter<\/a> and Facebook<\/a>. If you have any questions, send email to me at scripter@microsoft.com<\/a>, or post your questions on the Official Scripting Guys Forum<\/a>. Also check out my Microsoft Operations Management Suite Blog<\/a>. See you tomorrow. Until then, peace.<\/p>\n Ed Wilson, Microsoft Scripting Guy<\/b> <\/p>\n","protected":false},"excerpt":{"rendered":" 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 […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[169,663,3,4,45],"class_list":["post-76251","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-csv-and-other-delimited-files","tag-msoms","tag-scripting-guy","tag-scripting-techniques","tag-windows-powershell"],"acf":[],"blog_post_summary":" 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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/76251","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=76251"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/76251\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=76251"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=76251"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=76251"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Parsing MS OMS data with PowerShell<\/h2>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-1-14-16-01.png\" "\\"Image")
<\/a><\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-1-14-16-02.png\" "\\"Image")
<\/a><\/p>\nImport the CSV file using PowerShell<\/h2>\n
![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-1-14-16-03.png\" "\\"Image")
<\/a><\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-1-14-16-04.png\" "\\"Image")
<\/a><\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-1-14-16-05.png\" "\\"Image")
<\/a><\/p>\n![\"Image](\"https:\/\/devblogs.microsoft.com\/wp-content\/uploads\/sites\/29\/2019\/02\/HSG-1-14-16-06.png\" "\\"Image")
<\/a><\/p>\n