NQL with
A with
clause allows you to join an inventory object table with an event table. It returns data per object only when there is at least one event recorded for a specific object. Use it to query inventory objects with conditions on events.
Syntax
<object table> ...
| with <event table> ...
Example
Select all the devices with at least one error during the last seven days.
devices
| with web.errors during past 7d
| list device.name, operating_system.name
Name | OS name |
---|---|
device-54304276 | Windows 10 Pro 21H1 (64 bits) |
device-c0b53b3f | Windows 10 Enterprise 21H1 (64 bits) |
device-71cedc8f | Windows 10 Enterprise 21H1 (64 bits) |
device-dc98cd15 | Windows 10 Enterprise 21H1 (64 bits) |
device-b5d55bd0 | Windows 10 Pro 21H1 (64 bits) |
device-706d3c09 | Windows 10 Pro 21H1 (64 bits) |
device-a56b63f1 | Windows 10 Enterprise 21H1 (64 bits) |
device-259c7017 | Windows 10 Pro 20H2 (64 bits) |
device-d0ce2109 | Windows 10 Enterprise 21H1 (64 bits) |
Computing new metric
The with
clause can be used along with a compute
clause that appends the object table with a new column with metric per object. Refer to the NQL compute keyword documentation page for more information.
Using multiple ‘with’ clauses
An NQL query can contain multiple with
clauses.
binary.binaries
| with execution.crashes during past 1d
| compute total_number_of_crashes = count()
| with execution.events during past 1d
| compute sum_of_freezes = number_of_freezes.sum()
| list total_number_of_crashes, sum_of_freezes, name
Number of crashes | Sum of freezes | Binary name | MD5 hash |
---|---|---|---|
7 | 0 | odio.exe | f32bd724cb4b8593c9789ec584eb38dc |
12 | 0 | volutpat.exe | 5ec62b81e594367fa20a3fbdf4e4e7f3 |
24 | 0 | eget.exe | dc182b7939eba5ca8b1d64396b88fcd2 |
3 | 0 | euismod.exe | 2d0c540521f7e5683487c42c6ff52479 |
9 | 0 | euismod.exe | 2d0c540521f7e5683487c42c6ff52479 |
17 | 0 | aliquet.exe | f4c4ad04db18ff1d225cbc43e864748a |
Filtering data
Only the computed values are available outside of the with
clause. When you start with devices
, only the fields of that table are available for other statements. Adding a with
and a compute
makes new fields available.
devices
| with web.errors during past 7d
| compute total_errors_device = number_of_errors.sum()
| where total_errors_device > 10
| list device.name, total_errors_device
| sort total_errors_device desc
Name | total_errors_device |
---|---|
device-741da9be | 125 |
device-c91fa737 | 120 |
device-08469fee | 62 |
device-f2301dea | 51 |
device-9e07abe9 | 45 |
device-03680882 | 42 |
device-25c67269 | 42 |
device-f8586bb6 | 41 |
device-b5d55bd0 | 39 |
device-60ea7a88 | 39 |