{"id":12351,"date":"2011-10-19T00:01:00","date_gmt":"2011-10-19T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2011\/10\/19\/use-powershell-to-find-the-top-values-returned-by-wmi\/"},"modified":"2011-10-19T00:01:00","modified_gmt":"2011-10-19T00:01:00","slug":"use-powershell-to-find-the-top-values-returned-by-wmi","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/use-powershell-to-find-the-top-values-returned-by-wmi\/","title":{"rendered":"Use PowerShell to Find the Top Values Returned by WMI"},"content":{"rendered":"

Summary:<\/strong> Learn how to use Windows PowerShell to slice and dice WMI data in an easy, SQL-like fashion.<\/span><\/span><\/p>\n

 <\/span><\/p>\n

\"Hey,Hey, Scripting Guy! Does the WMI select<\/b> statement have any other clauses like SQL does? For example, can I just select top 10 * from<\/b> to get a sample of the collection rather than doing a *<\/b> for the entire collection?<\/span><\/p>\n

—UJ<\/span><\/p>\n

 <\/span><\/p>\n

\"Hey,Hello UJ, <\/span><\/p>\n

Microsoft Scripting Guy Ed Wilson here. This is actually a rather common request. People see WMI queries that sort of look like Structured Query Language (SQL), and they immediately want to know if they can use other language statements instead of just the select<\/b> <\/i>statement. <\/span><\/p>\n

UJ, the first thing you need to know about querying WMI is that it does not use SQL. It uses a query language called WQL (WMI Query Language). If the name WQL sort of looks like SQL, that is a good thing because WQL is sort of like SQL. And it sort of is not like SQL. WQL is actually a subset of ANSI SQL, with a few additions. The language keywords and their meanings <\/span>are documented on MSDN<\/span><\/a><\/span>. They are also covered in my <\/span>WMI book<\/span><\/a><\/span>. <\/span><\/span><\/p>\n

So, to directly answer your question, no. WMI does not have a top<\/b> <\/i>keyword, a sortby<\/b> <\/i>keyword, or any of the other more sophisticated features of SQL. However, all is not lost because, using Windows PowerShell, it is very easy to accomplish these tasks. I simply pipe the results of the WMI query to other Windows PowerShell cmdlets. <\/span><\/p>\n

For example, if I want to query the Win32_Process<\/b> WMI class and find the top 10 processes that are creating the most pagefaults<\/b>, I can use the following command (gwmi<\/b> is an alias for the Get-WmiObject<\/b> cmdlet; sort<\/b> is an alias for the Sort-Object<\/b> cmdlet; and select<\/b> is an alias for the Select-Object<\/b> cmdlet):<\/span><\/span><\/p>\n

gwmi win32_process | sort pagefaults -des | select name, pagefaults -First 10<\/span><\/p>\n

The command and associated output are shown in the following figure.<\/span><\/p>\n

\"Image<\/a><\/span><\/p>\n

If I am concerned about reducing the amount of data that is returned by the WMI query, I can save a decent amount of space by only choosing the two properties I am displaying. This modified command is shown here:<\/span><\/p>\n

gwmi win32_process -prop name,pagefaults | sort pagefaults -des | select name, pagefaults -First 10<\/span><\/p>\n

If I want to see the most efficient processes (in terms of the number of pagefaults<\/b> generated), I use the last <\/i>parameter of the Select-Object<\/b> cmdlet instead of the first <\/i>parameter. This command is shown here:<\/span><\/p>\n

gwmi win32_process -prop name,pagefaults | sort pagefaults -des | select name, pagefaults -Last 10<\/span><\/p>\n

The command and associated output are shown in the following figure.<\/span><\/p>\n

\"Image<\/a><\/span><\/p>\n

Using Windows PowerShell, some really cool queries are produced. For example, I decided I wanted to see the top 20 processes that are producing the most page faults. When I ran that command, I noticed that several processes appeared multiple times. I then decided to group by name so that I could see how many processes occupied the top 20 slot. To do this, I introduced a new cmdlet into the mix: Group-Object<\/b> (the alias is group<\/b>). The output was actually a bit surprising. First, my command; it is shown here: <\/span><\/p>\n

gwmi win32_process -prop name,pagefaults | sort pagefaults -des | select name, pagefaults -First 20 | group name | sort count –Des<\/span><\/p>\n

The command and associated output are shown in the following figure.<\/span><\/p>\n

\"Image<\/a><\/span><\/p>\n

For the last little bit, I have been running the same WMI command over and over again. If this were a WMI command that took a decent amount of time to run, it would be much better to store the results in a variable and work through the same offline data. In fact, from a decision perspective, my data keeps changing each time I run the command. To store and process, my commands would look like the following:<\/span><\/p>\n

$wmi = gwmi win32_process -prop name,pagefaults<\/span><\/p>\n

$wmi | sort pagefaults -des | select name, pagefaults -First 20 | group name| sort count –Des<\/span><\/p>\n

Of course, for analyzing data it is hard to beat the Out-Gridview<\/b> cmdlet because it makes it really easy to add conditions that permit quick slicing and dicing. I like to pass the results through the Select-Object<\/b> cmdlet to ensure I only have the data I want to examine. Here is the command I used:<\/span><\/p>\n

$wmi = gwmi win32_process -prop name,pagefaults | select name, pagefaults<\/span><\/p>\n

$wmi | Out-GridView<\/span><\/p>\n

The Out-Gridview<\/b> cmdlet produces a gridview tool that is shown in the following figure.<\/span><\/p>\n

\"Image<\/a><\/span><\/p>\n

I add a criterion by clicking Add criteria<\/b> and selecting the property I want to examine. Next, I choose the operator and type the value I want to see. The cool thing is that, as I type zeroes on my number, the list of processes changes dynamically. I ended up with 500,000 page faults and found six processes that had more than that number. It would have taken me a while to discover this information without using this tool. The gridview with the associated filter is shown in the following figure.<\/span><\/p>\n

\"Image<\/a><\/span><\/p>\n

 <\/span><\/p>\n

UJ, the short answer to your question is no. The longer answer is that, by using Windows PowerShell, I have an extremely powerful tool set that actually makes it easy to slice and to dice the WMI data in a very logical manner. Well, that is it for today. Join me tomorrow for more cool Windows PowerShell stuff. <\/span><\/p>\n

I invite you to follow me on <\/span>Twitter<\/span><\/a><\/span> and <\/span>Facebook<\/span><\/a><\/span>. If you have any questions, send email to me at <\/span>scripter@microsoft.com<\/span><\/a><\/span>, or post your questions on the <\/span>Official Scripting Guys Forum<\/span><\/a><\/span>. See you tomorrow. Until then, peace.<\/span><\/span><\/p>\n

 <\/span><\/p>\n

Ed Wilson, Microsoft Scripting Guy<\/b><\/span><\/p>\n

<\/span> <\/p>\n

 <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Summary: Learn how to use Windows PowerShell to slice and dice WMI data in an easy, SQL-like fashion.   Hey, Scripting Guy! Does the WMI select statement have any other clauses like SQL does? For example, can I just select top 10 * from to get a sample of the collection rather than doing a […]<\/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":[3,4,45,6],"class_list":["post-12351","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-scripting-guy","tag-scripting-techniques","tag-windows-powershell","tag-wmi"],"acf":[],"blog_post_summary":"

Summary: Learn how to use Windows PowerShell to slice and dice WMI data in an easy, SQL-like fashion.   Hey, Scripting Guy! Does the WMI select statement have any other clauses like SQL does? For example, can I just select top 10 * from to get a sample of the collection rather than doing a […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12351","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=12351"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12351\/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=12351"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=12351"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=12351"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}