image/svg+xml image/svg+xml Layer 1 Layer 2 background powershell.one

    WQL Queries

    WMI supports the WQL query syntax to select class instances. WQL works like SQL database queries.

    The WQL Query Syntax used by WMI is an adaption of the common SQL query that you may know from databases. With WQL, you can select the WMI class instances you want to retrieve.

    Let’s explore WQL by looking at Get-CimInstance. A typical query uses the parameters -ClassName, -Filter, and -Property.

    Querying WMI with individual parameters

    The code below looks for all instances of the class Win32_Service (Windows Services) that are not running but have a StartMode set to Auto.

    # WMI class name to query
$className = 'Win32_Service'

# properties to return
$properties = 'Name', 'StartName', 'Description', 'PathName', 'ExitCode'

# conditions that must be met
$filter = 'Started = FALSE AND StartMode = "Auto"'

# perform query...
Get-CimInstance -ClassName $className -Filter $filter -Property $properties |
  # keep only the requested properties 
  # (because all other properties are empty but still present)
  Select-Object -Property $properties

    Note that Get-CimInstance returns only the property values specified in -Property. Any property not specified in -Property is empty but still present.

    To remove all unwanted properties, the result needs to be piped to Select-Object.

    The same can be achieved with a single WQL Query.

    Querying WMI with WQL

    WQL combines the values of -ClassName, -Filter, and -Property in a SQL-like syntax:

    Select Name, StartName, Description, PathName, ExitCode 
  from Win32_Service 
  Where Started = FALSE AND StartMode = "Auto"

    Here is the script:

    # WMI class name to query
$className = 'Win32_Service'

# properties to return
$properties = 'Name', 'StartName', 'Description', 'PathName', 'ExitCode'

# conditions that must be met
$filter = 'Started = FALSE AND StartMode = "Auto"'

# combine everything in a WQL query:
$wql = "Select $($Properties -join ', ') from $className Where $filter"

# perform query...
Get-CimInstance -Query $wql |
  # keep only the requested properties 
  # (because all other properties are empty but still present)
  Select-Object -Property $properties

# return WQL used:
Write-Warning $wql
    Finding running services that were manually started 
    $wql = 'SELECT * FROM _InstanceModificationEvent within 5 where Target Instance isa "Win32Service" AND TargetInstance.State="Stopped"'

# perform query...
Get-CimInstance -Query $wql

    Data Types

    WQL supports all WMI Datatypes. Note that string and char types need to be quoted. All numeric types must not be quoted.

    Filtering String Values

    String values must be quoted. Below code lists all instances of Win32_Service with a StartMode set to Auto. Since this property is a string data type, Auto needs to be put in quotes:

    Get-CimInstance -Query 'Select * From Win32_Service Where StartMode = "Auto"'

    You can use the WQL Escape Character (“\”) to use double-quotes inside double-quoted strings:

    Get-CimInstance -Query "Select * From Win32_Service Where StartMode = \"Auto\""

    IMPORTANT: always make sure you escape the escape character when you want to use it literally, i.e. in paths:

    Get-CimInstance -Query 'Select * From Win32_AutochkSetting Where SettingID LIKE "%\\Device\\Harddisk0\\Partition3"'
    Filtering Numeric Values

    Numeric values must not be quoted.

    Boolean values are considered numeric values and represented by the key words TRUE and FALSE.

    Below code identifies suspicious services: it finds all services that are set to start automatically but are not running, with a return code other than 0 (which typically indicates some sort of error condition).

    • StartMode is a string value and must be quoted.
    • Started is a boolean value and must not be quoted. Either use the keywords TRUE and FALSE, or the numeric alternatives 1 and 0.
    • ExitCode is a numeric value and must not be quoted.
    # wql
$wql = 'Select Name, StartName, PathName from Win32_Service Where Started=FALSE AND StartMode="Auto" AND ExitCode<>0'

# perform query...
Get-CimInstance -Query $wql |
  # remove empty properties:
  Select-Object -Property Name, StartName, PathName

    If the code below does not yield any results, then that is actually a good thing: no suspicious services were found.

    ReplaceExitCode<>0 with ExitCode=0 in this case to see what the output would look like.

    Operators

    WQL is not PowerShell code and uses its own set of operators:

    WQL Operator PowerShell Description
    = -eq equals
    < -lt lower than
    > -gt greater than
    <= -le lower equals
    >= -ge greater equals
    != -ne not equal
    <> -ne not equal
    LIKE -like equals (including wildcards)
    the WQL wildcard character is %.
    IS NULL -eq $null is undefined
    IS NOT NULL -ne $null is defined
    ISA -is is of type

    Supported Wildcards

    The operator LIKE supports the following wildcards:

    Wildcard Example Description
    % %test%
    “test” anywhere in a string    		 string of zero or more characters
    _ “Windows__“
    any text starting with Windows, followed by exactly two characters    		 any one character. To use “_” literally, enclose it in square brackets
    [] [a-f0-9]
    a hex number    		 Character in a range [a-f] or a list of characters [aix]. The order of characters does not matter.
    ^[] ^[a]%
    any text that does not start with a    		 Characters not in a range

    Logical Operators

    To combine comparisons, WQL supports case-insensitive logical operators:

    WQL Operator PowerShell Description
    And -and both expressions must be met
    Or -or either one expression must be met

    WQL supports parens to group conditions. While not always necessary, parens make expressions more readable and easier to understand.

    Key Words

    WQL supports the following case-insensitive default key words that work very similar to SQL in database queries:

    Keyword Description
    Select Properties to return. CIM Cmdlets will still return all properties, but properties not specified by Select are empty.
    From WMI Class name to query
    Where Condition to be met. Use any WMI Class property with any of the supported WQL Operators. If the condition evaluates to true, the class instance is returned.
    WQL Example: Installed Hotfixes starting with KB45

    Returns all installed Hotfixes with an ID starting with KB45.

    Get-CimInstance -Query 'Select InstalledOn, HotFixId, Description, InstalledBy From Win32_QuickFixEngineering Where HotfixId LIKE "KB45%"' | 
Select-Object -Property InstalledOn, HotFixId, Description, InstalledBy

    If you omit Select-Object, you get the same information but a lot of empty properties.

    Win32_QuickFixEngineering basically returns the same information that you get from Get-Hotfix, except with WMI you can filter with wildcards and access remote systems.

    In addition, WQL supports keywords to query class relationships. You can identify ancestors and child inheritance as well as relationships:

    Keyword Description
    Group Groups events to simplify event handling: when a group of events fires within a certain time frame, this is treated as one event
    Having Number of events until an event is fired
    Within Used for event subscribers and specifies the time intervals (in seconds) to monitor for changes. The shorter the interval, the quicker will WMI respond to property changes, but the more CPU load will be created.

    Finally, there are specific keywords for event handling:

    Keyword Description
    Associators Of Returns associated classes
    References Of Returns inherited classes

    Limitations with Arrays

    WQL is a very powerful query language but has limited support for Array data types. It misses operators to check for individual array elements or whether an array is empty.

    Example for limitations with arrays

    The WMI Class Win32_NetworkAdapterConfiguration represents the configuration settings of network adapters. It includes a wide range of adapters including pseudo network adapters:

    $wql = 'SELECT Caption, IPAddress FROM Win32_NetworkAdapterConfiguration'

Get-CimInstance -Query $wql |
  Select-Object -Property Caption, IPAddress

    Array Content can not be Filtered

    To filter out instances with no assigned IP address, you would have to check whether the property IPAddress is empty.

    Since IPAddress is an array (a network adapter can have more than one assigned IP address), and since WQL Operators do not support array data types, this query fails:

    $wql = 'SELECT Caption, IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPAddress=NULL'

Get-CimInstance -Query $wql |
  Select-Object -Property Caption, IPAddress

    Workaround

    To work around this limitation, try and identify a non-array property that describes the condition you want to use for filtering. As it turns out, the property IPEnabled is set to TRUE whenever at least one IP address is assigned:

    $wql = 'SELECT IPAddress FROM Win32_NetworkAdapterConfiguration WHERE IPEnabled=TRUE'

Get-CimInstance -Query $wql |
  Select-Object -Property Caption, IPAddress

    Alternatives

    If you can’t get queries to work, you can always resort to PowerShell and client-side filtering:

    $wql = 'SELECT IPAddress FROM Win32_NetworkAdapterConfiguration'

Get-CimInstance -Query $wql |
  # filter out all instances where "IPAddress" is empty:
  Where-Object IPAddress |
  Select-Object -Property Caption, IPAddress

    Client-side filtering is less effective and uses more network bandwidth when querying remote machines.

    Over time, PowerShell and the Windows operating system started to natively support many of the areas originally covered by WMI. If you are running at least Windows 8/Server 2012, to list assigned IP addresses, all you probably need is something like this:

    Get-NetIPConfiguration | Select-Object -Property InterfaceAlias, IPv4Address

    Event Queries

    WQL statements can be used to define temporary and permanent WMI Event Subscribers: the query defines the type of event you are listening to, and what event conditions you want to monitor.

    Waiting For Events

    There are three generic WMI Classes that can trigger events:

    Class Description
    __InstanceCreationEvent fires whenever a new WMI Instance is created, i.e. a new process was launched
    __InstanceModificationEvent fires whenever an existing WMI Instance changed, i.e. a running service has stopped, or a notebook battery was disconnected from the charger.
    __InstanceDeletionEvent fires whenever an existing WMI Instance is removed, i.e. a process exited.

    While the three classes above implement a generic event wrapper that can be used with any WMI Class, specific event-enabled WMI Classes exist such as RegistryTreeChangeEvent, RegistryKeyChangeEvent, RegistryValueChangeEvent, Win32_ComputerSystemEvent, Win32_ComputerShutdownEvent, Win32_IPv4RouteTableEvent, Win32_ProcessStartTrace, Win32_ProcessStopTrace, Win32_SystemConfigurationChangeEvent, or Win32_PowerManagementEvent (to mention a few) that you can use in place of any of the three generic event-enabled WMI Classes above.

    Waiting for New Process Launch

    The PowerShell code below waits for new processes to be launched, and responds within 5 seconds. The names and start times of all processes are written to the PowerShell console untol you press CTRL+C.

    # respond within a maximum delay of 5 seconds to __InstanceCreationEvents
# if the instance that triggered this is of type "Win32_Process" 
# (a newly launched process)
$wql = 'Select * From __InstanceCreationEvent within 5 where TargetInstance isa "Win32_Process"'

Write-Warning "Monitoring enabled. Waiting for process starts."
Write-Warning "Press CTRL+C to abort monitoring."

# important: submit a source identifier that *you* know
# so you can later clean up and remove event subscribers
$id = (New-Guid).Guid

# register the WMI event. A PowerShell background job is returned:
$job = Register-WmiEvent -Query $wql -SourceIdentifier $id 

# wait endless for events. Embed this in a try...finally block
# so you can respond when the user aborts the script via CTRL+C,
# and clean up in the finally block:
try
{
  do
  {
    # Wait-Event waits for the event to fire, or a maximum of 1 second,
    # whichever comes first. This allows the user to abort the script every second
    # when PowerShell temporarily regains control:
    $eventData = Wait-Event -Timeout 1

    # $eventData is either $null (when no event occured within the timeout period)
    # or returns the information about the event that triggered
    if ($eventData -ne $null)
    {
      # for __InstanceModificationEvents, there is both a 
      # NewEvent and OldEvent property that can be used to compare
      # the before/after configuration of an object.
      $eventData.SourceEventArgs.NewEvent.TargetInstance | 
        Select-Object -Property TimeGenerated, Name, ProcessId, CommandLine

      # Important: you MUST remove the event from the queue, or else it
      # will be returned by the next call to Wait-Event again and again:
      $eventData | Remove-Event
    }
  # run forever (until the user aborts the script via CTRL+C or closes PowerShell)
  } while ($true)
}
finally
{
  # runs when the script ends. Remove the WMI event handler here:

  # unregister event:
  Get-EventSubscriber -SourceIdentifier $id | Unregister-Event
   
  # remove background job:
  Remove-Job -Name $id

  Write-Warning "Monitoring disabled."
}

    Aggregating Events

    Events can be aggregated for more efficiency. Instead of handling every single event, all events of a given type are grouped over a certain period of time, and when there were enough events to meet a threshold, one single aggregate event is fired.

    WQL uses the keywords GROUP (to define the time frame) and HAVING (to define the minimum number of events threshold).

    Combining Events with HAVING and GROUP

    Events can be grouped and treated as one event with a WQL Query like this:

    SELECT * FROM EventClass [WHERE property = value] 
  GROUP WITHIN interval [BY property_list]
  HAVING NumberOfEvents operator constant

    A real-life example could look like this:

    $wql = 'SELECT * FROM __InstanceCreationEvent 
WHERE TargetInstance ISA "Win32_NTLogEvent" 
GROUP WITHIN 600 BY TargetInstance.SourceName 
HAVING NumberOfEvents > 25'

    This query would group all events of type __InstanceCreationEvent of type Win32_NTLogEvent within 600 seconds (10 minutes) after the first event was received, and would trigger an own event only when there have been more than 25 events received.

    In other words, rather than firing an event for every new eventlog entry, this query would aggregate events for 10 minutes, then look whether there was a critical mass of events, and only then fire an event.

    © powershell.one 2020 - Legal - Contact Us