NQL summarize by

The summarize by statement condenses the information into aggregated results grouped by properties or time interval.

Grouping by property

Enter the field name after by to create a breakdown by a property. Enter additional field names separated by a comma to create more breakdown dimensions.

The summarize by clause does not support grouping by properties with numeric data types such as days_since_last_seen (integer), last_seen (date time) or hardware.memory (byte).

Syntax

...
| summarize <new metric name> = <metric>.<aggregation function> by <field_1>, <field_2> ...

Example

Display the average Confluence backend page load time per device in the last 7 days.

web.page_views during past 7d
| where application.name == "Confluence"
| summarize backendTime = page_load_time.backend.avg() by device.name
| list device.name, backendTime
| sort backendTime desc
Device namebackendTime

device-10d267d2

508.2 ms

device-d1d5abc9

498.9 ms

device-5117c4c3

432.1 ms

device-16834449

431.9 ms

device-b634ce84

429.4 ms

device-731db075

349.8 ms

device-7fb313ef

293.9 ms

device-a834a720

277.6 ms

Grouping by period

The summarize by statement when used in combination with a time period, groups the metric values into time buckets.

Syntax

...
| summarize <new metric name> = <metric>.<aggregation function> by <time period>

Valid period values are:

  • 15 min 30 min 45 min … The value must be a multiple of 15.

  • 1 h 2 h 3 h ... The value must be a whole number.

  • 1 d 2 d 3 d ... The value must be a whole number.

Example

Display daily number of crashes in the last 7 days in chronological order.

execution.crashes during past 7d
| summarize total_number_of_crashes = count() by 1d
| sort start_time asc
start_timeend_timebucket_durationnumber_of_crashes

2021-03-05 00:00:00

2021-03-06 00:00:00

1 d

758

2021-03-06 00:00:00

2021-03-07 00:00:00

1 d

700

2021-03-07 00:00:00

2021-03-08 00:00:00

1 d

954

2021-03-08 00:00:00

2021-03-09 00:00:00

1 d

493

2021-03-09 00:00:00

2021-03-10 00:00:00

1 d

344

2021-03-10 00:00:00

2021-03-11 00:00:00

1 d

765

2021-03-11 00:00:00

2021-03-12 00:00:00

1 d

857

Grouping by property and period

Combine properties and time period to generate time buckets with additional breakdowns. You can use multiple fields, but only one time period selector. The sequence of items is arbitrary; the time period selector can be positioned anywhere within the list of fields.

Syntax

...
| summarize <new metric name> = <metric>.<aggregation function> by <field_1>, <field_2>, ... <time period>, ...

Example

Display daily number of crashes in the last 30 days broken down by operating system platform and sorted starting from the highest number of crashes.

execution.crashes during past 30d
| summarize total_number_of_crashes = count() by 1d, device.operating_system.platform 
| sort total_number_of_crashes desc
Device platformstart_timeend_timebucket_durationnumber_of_crashes

Windows

2021-12-07 00:00:00

2021-12-08 00:00:00

1 d

690

Windows

2021-12-08 00:00:00

2021-12-09 00:00:00

1 d

533

macOS

2021-12-20 00:00:00

2021-12-21 00:00:00

1 d

511

Windows

2021-12-17 00:00:00

2021-12-18 00:00:00

1 d

493

Windows

2021-12-08 00:00:00

2021-12-09 00:00:00

1d

356

macOS

2021-12-20 00:00:00

2021-12-21 00:00:00

1d

325

Last updated