PowerShell, Scripting, wmi, WQL
comments 17

WMI Query Language (WQL) – Data Queries: SELECT, FROM, and WHERE

These posts in the form of an ebook now available [download id=”25″ format=”4″]

Here are the links to all articles in this series of posts on WQL.

1. WMI query language – An introduction
2. WMI query language – Keywords and Operators
3. WMI query language – Data Queries: SELECT, FROM, and WHERE (this post)
4. WMI query language – Data Queries: Associators Of
5. WMI query language – Data Queries: References Of
6. WMI query language – Event Queries: Introduction
7. WMI query language – Event Queries: Syntax
8. WMI query language – Event Queries: Intrinsic Events
9. WMI query language – Event Queries: Extrinsic Events
10. WMI query language – Schema queries

In this part of the series on WQL, we will look at what are data queries and how some of the WQL keywords & operators can be used to retrieve information from WMI repository. Also, as mentioned earlier, there are many other tools that consume WQL queries to retrieve information from WMI. However, in this series, I shall use only PowerShell to demostrate WQL.

WQL data queries are the most simplet form of querying for WMI data. Data queries are used to retrieve class instances and data associations. For example,

gives us a list of all processes with a handle count above 5500.

Note
You can use -filter parameter to Get-WMIObject instead of -Query. Whatever you pass as a value to -filter will be used within the WHERE clause of a WQL statement. For example, the above PowerShell example can be re-written as

So, here is the difference between using -Query or  -Filter. You can pass any valid WQL statement as a value to -Query parameter. However, whatever value you pass as a value to -filter will always be used within the WHERE clause. So, the value to -filter must be a valid WHERE clause value with proper use of WQL operators. For example, using a PowerShell comparison operator such as -eq is not valid within -Filter.

Let us use this example and discuss a few keywords.

SELECT

In the above WQL query,  we used SELECT statement. This statement returns instances of the specified class and any of its subclasses. As a general practice, many people retrieve WMI data by specifying SELECT * FROM <WMI CLASS>. By using *, we retrieve all possible properties of a given WMI class. This type of query can take a while to execute and consume more bandwidth to retrieve the result set. One method to reduce the bandwidth required to retrieve the result set is to replace * with selected set of property names.

OR

FROM

FROM statement is used to specify the class from which we need to create the instances. Remember that you can perform data queries only from one class at a time. For example, the following query will produce an invalid query error:

WHERE

As you may be familiar by now, WHERE keyword is used to narrow the scope of retrieved data based on a filter. This keyword can be used in all of the three query types. In general, WHERE clause when used with SELECT statement can take one of the following forms:

SELECT * FROM class WHERE property operator constant
SELECT * FROM class WHERE constant operator property

In the above two forms, property denotes a valid property of a WMI instance, operator is any valid WQL operator and constant must be of the correct type for the propert. We have already seen an example of the first form of using WHERE. Here is an example for the second form. The following query retrieve all services in stopped state.

Multiple groups of properties, operators, and constants can be combined in a WHERE clause using logical operators such as AND, OR, and NOT. Here are a few examples to demonstrate these.

The above three examples show using AND, OR, and NOT logical operators with WHERE clause to perform multiple filters. In the third example, we can replace NOT with other WQL operators. For example, the same query can be performed in the following ways:

You can also use IS, IS NOT operators within WHERE clause. However, the query will be valid only if the constant is NULL. For example,

is valid and will return the disk drive information with no file system information. However, the following example,

will result in an invalid query error.

There are many other keywords such as REFERENCES OF, ASSOCIATORS OF within the context of data queries. To keep these posts short, I will end today’s post here and discuss a few more keywords in the next post. As usual, please leave your feedback here on what can be improved and what else you want to see.

Filed under: PowerShell, Scripting, wmi, WQL

by

Ravikanth is a principal engineer and the lead architect for Microsoft and VMware virtualized and hybrid cloud solutions within the Infrastructure Solutions Group at Dell EMC. He is a multi-year recipient of Microsoft Most Valuable Professional (MVP) award in Windows PowerShell (CDM) and Microsoft Azure. Ravikanth is the author of Windows PowerShell Desired State Configuration Revealed (Apress) and leads Bangalore PowerShell and Bangalore IT Pro user groups. He can be seen speaking regularly at local user group events and conferences in India and abroad.