Summarize Operator

Overview

Aggregate findings with summarize.

| summarize [[<dest>=]aggregation[, ...]] by [<dest>=]<expression>[, ...] 

Many aggregations are supported, including:

• agg.avg()

• agg.count()

• agg.count_distinct()

• agg.make_set()

• agg.max()

• agg.min()

• agg.percentile_cont()

• agg.stddev()

• agg.sum()

• agg.take_any()

See a full list of available aggregations on Aggregation Functions.

Examples

Count

The following query displays the count of all events in the last day stored in field num_connections:

panther_logs.public.aws_alb
| where p_event_time > time.ago(1d)
| summarize num_connections=agg.count()

num_connections
993

Group count by single field

The following query counts the number of connections for each clientIp and orders results with the largest number of connections first:

panther_logs.public.aws_alb
| where p_event_time > time.ago(1d)
| summarize num_connections=agg.count() by clientIp
| sort num_connections

clientIp	num_connections
192.167.7.55	979
10.145.4.26	130
10.99.231.15	31
...

Group count by multiple fields

The following query displays num_connections grouped by both the clientIp and clientPort fields:

panther_logs.public.aws_alb
| where p_event_time > time.ago(1d)
| summarize num_connections=agg.count() by clientIp, clientPort
| sort num_connections

clientIp	clientPort	num_connections
192.167.7.55	50160	7
10.145.4.26	63335	5
10.145.4.26	60845	4
192.167.7.55	52138	4
10.99.231.15	58704	3
…

Group count by arbitrary expressions

panther_logs.public.aws_alb
| where p_event_time > time.ago(1d)
| summarize num_connections=agg.count() by isOK = elbStatusCode == 200
| sort num_connections

isOK	num_connections
true	1114
false	324

Count distinct

The query below stores the distinct number of clientIps in num_distinct_clients:

panther_logs.public.aws_alb
| where p_event_time > time.ago(1d)
| summarize num_distinct_clients=agg.count_distinct(clientIp)

num_distinct_clients
121

Refer to an aggregation later

You can refer to an aggregation in a subsequent query expression:

panther_logs.public.aws_alb
| where p_event_time > time.ago(1d)
| summarize num_connections=agg.count() by clientIp, clientPort
| where num_connections >= 5

clientIp	clientPort	num_connections
192.167.7.55	50160	7
10.145.4.26	63335	5
...

Summarize by field (without aggregation)

The query below displays each unique userAgent:

panther_logs.public.aws_alb
| where p_event_time > time.ago(1d)
| summarize by userAgent

userAgent
Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/31.0.1650.63 Safari/537.36
curl/8.1.2
Mozilla/5.0 (Linux; Android 7.0; LG-H918 Build/NRD90M) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Mobile Safari/537.36
…