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 name	backendTime
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_time	end_time	bucket_duration	number_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 platform	start_time	end_time	bucket_duration	number_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
…	…	…	…	…