Introduction to WMI Basics with PowerShell Part 3 (WQL and Select Data Queries)
Windows Management Instrumentation Query Language also known as WQL is the language we use to express queries against WMI to extract information from it. he language is like a stripped down version of SQL which should make it very familiar for programmers and for most IT/Security professionals. In fact WQL is Microsoft's implementation of the CIM Query Language (CQL), a query language for the Common Information Model (CIM) standard from the Distributed Management Task Force (DMTF). The query language is read only there is no INSERT or UPDATE keywords in it. In PowerShell we are not obligated to use WQL to pull information from WMI like shown in the other blog posts in the series where we queried information using Cmdlets parameters. We find that many of the examples we will see out there in Python, Perl, VBScript, C# and others actually WQL for performing their queries since there is some additional controls on what data to pull and to express the query in a more familiar way for others to understand what it is being done. One of the advantages of using WQL is that the processing of the query and filtering of parameters and methods is done on the target hosts side and only the information that you requested is returned. The ability to have the filtering happen on the target side means that when querying either large amounts of hosts or objects that we only pull the information we want reducing both bandwidth and time for manipulating the data returned. One of the things we have to keep in mind is that WQL differs from the normal PowerShell lexicon in terms of formatting and expression, this means that operators, wildcards and string management is not the same, it is as the name implies a language all on its own.
Basics of a Select WQL Data Query
Lets start with the simples of query where we query information from a class:SELECT [Property Names] FROM [WMI Class]
When we want to apply filtering one uses the "WHERE" Keyword:
SELECT [Property Names] FROM [WMI Class] WHERE [EXPRESSION]SELECT [Property Names] FROM [WMI Class] WHERE [VALUE] LIKE [Wildcard Expression]
SELECT [Property Names] FROM [WMI Class] WHERE [VALUE] [IS|IS NOT] NULL
In PowerShell both the WMI and CIM Cmdlets allow the use of queries. For WMI Cmdlets we use the Get-WMIObject Cmdlets :
Get-WmiObject -Query "SELECT * from Win32_Process"
With CIM cmdlets we use the Get-CimInstance cmdlet, this cmdlet also allows us to specify the Query Dialect we want to use if it is Microsofts WQL or the DMTF CQL:
Get-CimInstance -Query "SELECT * from Win32_Process"Get-CimInstance -Query "SELECT * from CIM_Process" -QueryDialect WQL
When we build our query with the SELECT statement we can only use either the name or comma separated list of names of a property of the object or we can use the * as a wildcard that specifies all properties should be select and brought back.
C:\> Get-CimInstance -Query "Select * from win32_Process" | select -First 5ProcessId Name HandleCount WorkingSetSize VirtualSize
--------- ---- ----------- -------------- -----------
0 System Idle P... 0 20480 65536
4 System 800 2686976 6262784
376 smss.exe 36 331776 4374528
468 csrss.exe 423 2736128 51625984
536 wininit.exe 74 798720 48410624
C:\> Get-CimInstance -Query "Select Name,ProcessId from win32_Process" | select -First 5ProcessId Name HandleCount WorkingSetSize VirtualSize
--------- ---- ----------- -------------- -----------
0 System Idle P...
4 System
376 smss.exe
468 csrss.exe
536 wininit.exe
We can also use the Cmdlet Properties parameter:
Get-WmiObject win32_process -Property name,ProcessId
WQL Operators
When we start looking at filtering either using the WHERE statement we need to know what Operators we can use to perform the comparisons and filtering, lets look at the comparison operators:
The Boolean values in WQL are TRUE and FALSE. We can chain comparisons using the Boolean Operators of AND and OR:
Type and Wildcard Query Keywords:
One thing to keep in mind is that the IS and IS NOT comparison key words are only used to check for NULL, they can not be used to compare content or Class Type. The ISA keyword can be used in queries to check the Class Type for what it is being returned. Now when we use the LIKE keyword for wildcard matching, the Wildcards we can use are not the same we use in PowerShell as stated before but the same ones used in the SQL ANSI Standard:
When working with string values in WQL one may need to scape characters, for this we would use the \, this differs from PowerShell use of the ` character for escaping.
We have covered in this section the most common keywords. WQL has a total of 19 keywords in total, to get a full list checkout http://msdn.microsoft.com/en-us/library/windows/desktop/aa394606(v=vs.85).aspx
Using Comparison Operators
Lets look at several examples of using comparison operators in WQL and then how we would do it using Powershell it self. Lets start with a simple example of looking for services that have a state of running:
Get-WmiObject -Query "SELECT * FROM win32_service WHERE state='running'"
We can negate the query and now get only services that are not running:
Get-WmiObject -Query "SELECT * FROM win32_service WHERE NOT state='running'"
Now lets look at using the OR operator so we can select only the services that are running or paused:
Get-WmiObject -Query "SELECT * FROM win32_service WHERE state='running' OR state='paused'"
We can have more control and mix comparisons using parenthesis, these will be evaluated first and their return use in the next comparison from left to right, lets find all services that are set Manual, are in a State of Running or Paused:
Get-WmiObject -Query "SELECT * FROM win32_service WHERE startmode='manual' and (state='running' OR state='paused')"
If we want to execute the same with the PowerShell Cmdlet we would use the –Filter parameter and just pass it everything after the the WHERE keyword:
Get-WmiObject win32_service -Filter "state='running'"Get-WmiObject win32_service -Filter "state='running' OR state='paused'"
Using Wildcards with the LIKE Operator
The LIKE Operator allow us to use wildcards for matching strings for the value of a property we are trying to use for filtering. Lets start with working with the use of the wildcard character % this is one of the most used ones since it allow us to come up with simple quick expressions, for example I want all the services that start with Microsoft in their name, I can simply express this as microsoft% and it will match anything starting with the word and any characters after it:
Get-WmiObject -Query "SELECT * FROM win32_service WHERE name LIKE 'microsoft%'"
Lets now mix character groups with wildcards to find all services that start with either a letter a or m:
Get-WmiObject -Query "SELECT * FROM win32_service WHERE name LIKE '[am]%'"
We can also use ranges of characters and they are expressed as <first character> = <last character> using the order they are in the English alphabet. A quick note even do on the MS documentation in MSDN says that the = character is the only character for specifying the range I have noticed that – also works. Lets look for any service whose first letter is in the range from a to f in the alphabet:
Get-WmiObject -Query "SELECT * FROM win32_service WHERE name LIKE '[a-f]%'"
We can also negate a group or range by appending at the beginning the ^ character when defining the range or group of characters:
Get-WmiObject -Query "SELECT * FROM win32_service WHERE name LIKE '[^a-f]%'"
For single characters we would use the underscore.
The same Wildcards and Operators are used with the –Filter parameter with the WMI And CIM Cmdlets, this means that we can transform a WQL query that we find on a reference and make it in to a :
Get-WmiObject win32_process -Filter "name LIKE '_md.exe'"Get-WmiObject win32_process -Filter "name LIKE 'power%'"
I hope you have found this blog post informative and useful as always.