Use the Like Operator to Simplify Your WQL Queries

Use the Like Operator to Simplify Your WQL Queries

  • Comments 7
  • Likes

Summary: Learn how to simplify your WQL queries by using the like operator and special wildcard characters.

Hey, Scripting Guy! Question Hey, Scripting Guy! OK, I can see that using WMI queries might be cool. But I do not like having to always specify exact properties and values. Is it possible to use wildcard characters for some of this stuff?”

—SC

Hey, Scripting Guy! Answer Hello SC,

Microsoft Scripting Guy, Ed Wilson, is here. The Scripting Wife was rummaging around, and she decided to use some of our hotel points to go to Myrtle Beach, South Carolina for the weekend. I guess she did not think it was hot enough in Charlotte, North Carolina. I said, “OK,” because basically, I am going to be working on the finishing touches for my TechReady presentation in Seattle. I find working while I am looking out the window at the beach relaxing, and I can feel the stress ebbing away with each passing wave. The cool thing about Windows Server 2012 is that is has the real Hyper-V built into it, so I can run multiple virtual machines on my laptop. Sweet!

So, SC, you want to use some wildcards in your WQL queries…

Using the like operator

Symbol

Meaning

[ ]

Character in a range [a-f] or a set of characters [abcdef]

^

Character not in a range [^a-f] or not in a set [^abcdef]

%

Any string of zero or more characters

_ (underscore character)

Any one character.
Note  A literal underscore used in the query string must  be escaped by placing it inside square brackets [_]. 

Use like in a WQL query and look for a range

To use the like operator in a WQL query and look for a range of characters, use the square brackets. In the following example, the notepad process starts. Next, a WQL query is created that uses the like operator and the range. A range of letters from H through N is created by using the WQL range characters [H-N]. The square brackets, are placed inside the string that like uses in the search. Here any process from hotpad.exe through notepad.exe will be returned by the query.  

PS C:\> notepad

PS C:\> $query = "Select * from win32_Process where name like '[H-N]otepad.exe'"

PS C:\> Get-WmiObject -Query $query | select name, processID

 

name                                                                       processID

----                                                                       ---------

notepad.exe                                                                     1740

You can combine the range operation with the percent wildcard character to create simple, yet powerful filters. In the code that follows, everything is selected from the Win32_Process WMI class where the name begins with a letter in the range from A – P, and is followed by zero or more letters in any combination. The Get-WmiObject cmdlet executes the query, and chooses only the name and the process ID. The output is sorted by name. The code is shown here:

$query = "Select * from win32_Process where name like '[A-P]%'"

Get-WmiObject -Query $query | select name, processID | sort name

The code and the output associated with the commands are shown in the image that follows.

Image of command output

Use like in a WQL query and look for characters not in a range

Sometimes you know what you do not want to know, but you are not certain what you really want to know. The not in a range character is the caret (^). You do not need to create a consecutive range of characters. In fact, you do not need to even supply the characters in order. In the following query, no process that  begins with one of the letters ASWPRCU or N followed by any other letter will be retrieved. The Get-WmiObject cmdlet is used to execute the query, and the name and processed properties are selected. Next they are sorted by name.  

$query = "Select * from win32_Process where name like '[^ASWPRCUN]%'"

Get-WmiObject -Query $query | select name, processID | sort name

The output appearing in the following image illustrates running this query and the output associated with the query.

Image of command output

Use a wildcard character with your WQL query

The most useful character to use with the like operator is the percent character “%.” The percent character will match zero or more characters. It is equivalent to the asterisk “*” character. In the following example, a WQL query first looks for a process named calculator.exe, but nothing returns from the query. Next, the wildcard character is used and the WMI query returns the requested data.

PS C:\> $query = "Select * from win32_Process where name like 'calculator.exe'"

PS C:\> Get-WmiObject -Query $query | select name, processID

PS C:\> $query = "Select * from win32_Process where name like 'calc%'"

PS C:\> Get-WmiObject -Query $query | select name, processID

 

name                                                                       processID

----                                                                       ---------

calc.exe                                                                        4424

Using a single character wildcard with WQL

Sometimes, you want a very focused filter. In the query shown here, any process named anything from calc.exe through czlc.exe will match, in addition to other characters that may appear in process names.

PS C:\> $query = "Select * from win32_Process where name like 'c_lc.exe'"

PS C:\> Get-WmiObject -Query $query | select name, processID

 

name                                                                       processID

----                                                                       ---------

calc.exe                                                                        4424

In the following query, the process with the name WLIDSVC.exe produces one match.

Note   When using the like operator without any wildcards or range operators, you basically have an equality operation; and therefore, you will only get an exact match.

$query = "Select * from win32_Process where name like 'WLIDSVC.exe'"

Get-WmiObject -Query $query | select name, processID

By using the underscore character for a one-letter wildcard like operation, you do not match the WLIDSVC.exe process, but rather you match the WLIDSVCM.exe process.

$query = "Select * from win32_Process where name like 'WLIDSVC_.exe'"

Get-WmiObject -Query $query | select name, processID

By using the percentage sign wildcard character, you match both of the processes.

$query = "Select * from win32_Process where name like 'WLIDSVC%.exe'"

Get-WmiObject -Query $query | select name, processID

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

Image of command output

SC, that is all there is to using the like operator in a WQL query. WMI Week will continue tomorrow when I will talk some more about performing WMI data queries.

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 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Excellent article Ed.  I have never paid much attention to the match patterens other than the %.  Your article made me realize that I should not have ignored these pattern tools.  Unfortinately, way back whne, the pattenrs did not work reliably and I never thought to revisit them .  I will now.

    Thanks.

  • @JRV I am glad my posting got you thinking ... that is the point of a good blog post (at least in my mind). Yes, there are still a few WMI classes that do not seem to properly implement the wild card characters, but they are largely the exception ... Something I have not done ... that you might want to do ... is to do a literal filter, and a wild card filter ... and pipe the results to measure-object to see the performance difference. You will need to reboot between tests, to eliminate any caching ... In fact, I think I will write an article about that later.

  • Hi Ed,

    this is pretty much like SQL! And it is undoubtfully a feature to remember!

    I would go for having regular expressions to extend the possibilities.

    A match operator would have been great!

    Klaus.

  • @K_Schulte Yes, the syntax is actually called WQL --- even looks like SQL. Yes, it would be nice to have regular expressions, but WQL does not have the support. What you can do is run the WQL query, and then pipeline the results to a Where-Object and THERE you can use the Regex.

  • I am glad you posted this information. I was completely stuck with my scripts, I could not get the syntax right until I saw your article. Thanks

  • @Robin I am glad the post helped you. Thank you so much for the feedback.

  • Would like to note that Windows 2000 does not recognise the WQL "Like" operator