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
NameOS 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 crashesSum of freezesBinary nameMD5 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
Nametotal_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

Last updated

#451: 2024.8-Overview of integration DOC

Change request updated