Skip to main content
Skip table of contents

NQL summarize by

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

Syntax

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

Examples

Display the average Confluence backend page load time per device.

CODE
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

Period

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

When the time frame for the root table is expressed in minutes or hours, you can apply the duration expressed in multiple of 15 minutes or in hours.

When the time frame for the root table is expressed in days, you can apply the duration expressed in days.

This is a correct query:

CODE
web.errors during past 24h
| summarize number_of_crashes = count() by 1h, device.operating_system.platform 
| sort number_of_crashes desc

This is an incorrect query:

CODE
web.errors during past 7d
| summarize number_of_crashes = count() by 1h, device.operating_system.platform 
| sort number_of_crashes desc

Syntax

Description

Example

summarize <aggregate> by <duration>

The aggregate is calculated over the specified duration, expressed in minutes, hours and days.

Valid duration 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.

Windows can be mixed with other groupings. The time period can occur anywhere in the list of fields, but only one period selector may occur.

CODE
web.page_views past 7d 
| where vpn_status == enabled 
| summarize c = count() by 1d, vpn_status

Example

CODE
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

Windows

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

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.