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