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