Use PowerShell and Avoid Three Gotcha's with WQL Where Clauses

Doctor Scripto

Summary: Microsoft Scripting Guy, Ed Wilson, shows you how to avoid three potential issues when using WQL data queries with Windows PowerShell.

 Hey, Scripting Guy! Yesterday you said there were three basic WQL keywords: Select, From, and Where. You talked about Select and From. I guess you are going to talk about Where today? Am I right?

—BJ

 Hello BJ,

Microsoft Scripting Guy, Ed Wilson, is here. The good news is that it is not nearly as hot today as it was yesterday. The bad news is that the humidity is hovering around 80 percent, and that makes it 87 degrees Fahrenheit (30.5 degrees Celsius according to my conversion module). Last night, the Scripting Wife, Brian from the Charlotte PowerShell User Group, and I went out to celebrate me turning in the last  chapter of my Windows PowerShell 3.0 Step By Book that will be published by Microsoft Press (preorder from Amazon is available now). Brian volunteered to do a peer review for my next book. Pretty exciting stuff.

Yes, BJ, today we need to talk about the WQL Where statement.

Note   This is the second blog in a series about using the WMI Query language. You should definitely read the first blog in the series before continuing today. Yesterday, I talked about Three Easy Ways to Use PowerShell and WQL to Get WMI Data.

Using the basic WQL Where statement

Typing the Where statement in a WQL query is easy; it is what comes after it that can be a bit of a bother. There are three main gotcha’s when it comes to working with the WQL Where statement. These problem areas are listed here.

1. You need to know the format of the value to use in creating the filter.
2. You need to know the exact property name that you want to use.
3. You need to know the correct operator to use.

With these three concerns, you can surmise that there are three parts in the basic Where statement (four parts if you add in the Where keyword). These parts are shown here.

Gotcha #1: What does that value look like?

The following code provides an example of using the above Where clause in an actual WQL query:

PS C:\> $name = “Select * from win32_Process where name = ‘notepad'”

PS C:\> Get-WmiObject -Query $name

Interestingly enough, this code also illustrates Gotcha #3: You need to know the format of the value to use in creating the filter. For example, when the previous command runs, no data returns. Because the first command starts an instance of Notepad, the query should return data. But it does not.

Note   When working with WMI, and especially with WQL, do not forget that you are also using Windows PowerShell. Often if a WQL query does not work as expected, the results are more easily obtainable via “standard” Windows PowerShell techniques. Unless you are returning massive amounts of data from across bandwidth-constrained remote systems, it is rarely cost effective, in terms of productivity, to waste hours of your time trying to perfect a complicated and convoluted WQL query when there is a perfectly acceptable “pure Windows PowerShell” alternative.

As a quick check, I retrieve all instances of the entire Win32_Process object, and then I use the Select-Object cmdlet to choose only the name of the process. Then I use Where-Object to find processes with a name that contains the letters “notepad” in the process name. The code is shown here:

notepad

$name = “Select * from win32_Process where name = ‘notepad'”

gwmi -Query $name

gwmi win32_process | select name | where { $_.name -match ‘notepad’}

The code and resulting output are shown in the image that follows.

Ah ha! The values that WMI expect for the name property include the file extension. When the query changes to include the .exe extension in the file name, the query works as expected. The revised code is shown here:

$name = “Select * from win32_Process where name = ‘notepad.exe'”

gwmi -Query $name

The query and the associated results are shown in the image that follows.

Gotcha #2: What’s the property name I need?

One problem with using the Where statement to filter the number of returned instances, is that you need to know the property name. In addition to just getting a valid property name, you need to get the property name to return the sort of data that you want. For example, the following query does not return any instances:

PS C:\> $service = “Select name from win32_service where status = ‘running'”

PS C:\> get-wmiobject -Query $service

PS C:\>

It is possible to use the Get-Member cmdlet to view the properties of interest. An example of this technique is shown here:

PS C:\> Get-WmiObject win32_service | Get-Member -MemberType property s*

   TypeName: System.Management.ManagementObject#root\cimv2\Win32_Service

Name                    MemberType Definition

—-                    ———- ———-

ServiceSpecificExitCode Property   System.UInt32 ServiceSpecificExitCode {get;set;}

ServiceType             Property   System.String ServiceType {get;set;}

Started                 Property   System.Boolean Started {get;set;}

StartMode               Property   System.String StartMode {get;set;}

StartName               Property   System.String StartName {get;set;}

State                   Property   System.String State {get;set;}

Status                  Property   System.String Status {get;set;}

SystemCreationClassName Property   System.String SystemCreationClassName {get;set;}

SystemName              Property   System.String SystemName {get;set;}

The problem is distinguishing the difference between State and Status. Although it is true that looking up the WMI class on MSDN will provide the answer, it is also true that Windows PowerShell can often provide answers faster and easier than opening a browser, navigating to MSDN, looking up the WMI class, finding the article, and reviewing the documentation. Sometimes answers are as easy as piping the results of a WMI query to the Format-List cmdlet as shown here:

Get-WmiObject win32_service | Format-List *

The output from the command is shown here:

So, the problem with the previous query was choosing the wrong property. To find out if a service is running or not, use the State property, not the Status property. This is an easy mistake to make, which is alleviated when a sampling of the properties and their associated values is made. Here is the revised query:

$service = “Select name from win32_service where state = ‘running'”

get-wmiobject -Query $service

Gotcha #3: What does the operator look like?

This might not be a gotcha if it were not for the fact that Windows PowerShell uses distinct operators. In fact, the WQL operators appear to be much more “standard” than those used by Windows PowerShell. The issue, of course, is that when using Windows PowerShell to do WMI, using WQL mixed operators often enter the equation. Here is a table of WQL comparison operators:

There are other operators, but these are used for making comparisons. The following query selects the name and the priority from processes (Win32_Process) where the priority of the process is greater than or equal to 11. The Get-WmiObject cmdlet runs the query. The code is shown here:

$highPriority = “Select name, priority from Win32_Process where priority >= 11”

Get-WmiObject -Query $highPriority

The query and the output associated with the query are shown in the following image.

BJ, that is all there is to avoiding three main gotcha’s when using the Where clause in WQL. WQL Week will continue tomorrow when I will talk more about using the Where clause.

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. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy 

Doctor Scripto Scripter, PowerShell, vbScript, BAT, CMD

Follow