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 the following numeric data types:

• Date time, for example last_seen
• Byte, for example hardware.memory

To group by a time interval instead of a property, refer to Grouping by period.

Syntax

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

Examples

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
…	…

Rank the public IP addresses that the highest number of devices present. Devices that share an address usually reach the internet through the same egress point, such as a corporate firewall or a proxy service. A shift in these clusters indicates a change in the network.

Grouping by an IP address field groups by the exact address value. The system does not support grouping by an IP range.

devices
| summarize total = count() by public_ip.ip_address
| sort total desc

Public IP address	total
203.0.113.17	1,284
203.0.113.42	976
198.51.100.8	412
198.51.100.63	87
…	…

List the URLs that generate the most web errors, broken down by error code, in the last 7 days. Grouping by an integer field such as code returns one row per distinct value.

web.errors during past 7d
| summarize total = number_of_errors.sum() by code, url
| sort total desc

code	url	total
503	https://intranet.example.com/api	2,140
404	https://intranet.example.com/help	1,509
500	https://portal.example.com/login	883
403	https://portal.example.com/reports	216
…	…	…

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
…	…	…	…	…