stats
Aggregate events into one row per group: counts, distinct counts, sums, averages, percentiles, value lists and first/last seen.
Use stats to summarize your events into one row per group. You can count events, calculate numeric aggregates, and collect distinct values.
Syntax
... | stats <agg> [as <name>] [, <agg> [as <name>] ...] [by <field> [, <field> ...]]| Part | Description |
|---|---|
agg | An aggregation such as count, dc(f), sum(f). Separate several with commas or spaces. |
as <name> | Name the output column. Recommended for every aggregation except count (see Notes). |
by | One or more grouping fields, separated by commas or spaces. Without by, stats returns a single row. |
You can start a query with stats and no pipe: stats count by metadata.log_source is the same as * | stats count by metadata.log_source.
Aggregations
| Function | Returns |
|---|---|
count, count(f) | Number of events. count(f) counts events where f is not null. count needs no parentheses. |
dc(f), distinct_count(f) | Exact number of distinct values. |
estdc(f) | Approximate distinct count. Faster than dc on high-cardinality fields. |
sum(f), avg(f), min(f), max(f) | Sum, mean, minimum, maximum. |
median(f), mode(f) | Median; most frequent value. |
stdev(f), var(f) | Population standard deviation and variance. |
range(f) | max(f) - min(f). |
p95(f), p99(f), perc95(f) | 95th / 99th percentile. |
percentile(f, N) | Nth percentile, for example percentile(duration, 90). |
values(f) | Distinct non-empty values, as a comma-separated string (up to 100). |
list(f) | All non-empty values in event order, as a comma-separated string (up to 100). |
first(f), last(f) | Value of f on the earliest / latest event in the group (by time). |
earliest(time), latest(time) | First seen / last seen time. On other fields these return the smallest / largest value, not the value at the earliest event; use first(f) / last(f) for that. |
sparkline, sparkline(f) | A small per-group time series of event counts. |
rate(f) | Per-second rate of a monotonically increasing counter: (max - min) / seconds. Meant for OTLP metric counters. |
histogram_quantile(f, N) | Approximate Nth percentile (N is a whole number, 0–100) of the values of f. Meant for OTLP histogram metrics; on log searches it returns about the same as percentile(f, N). |
Conditional aggregation. Wrap a condition in eval() to count or sum only matching events: count(eval(http_response.code>=500)) as errors.
Expressions. An aggregation can take an expression: sum(traffic.bytes_in + traffic.bytes_out) as total_bytes.
Every eval function is listed on Functions.
How it works
- Output has one row per distinct combination of
byvalues, with thebyfields first and then one column per aggregation. - Rows are ordered by the first aggregation, descending, then by the
byfields.stats count by ftherefore lists the largest groups first. Use sort for a different order. - Events with a null
byvalue form their own group (shown as null). Filter them out first if you don't want them:user.name=* | stats count by user.name. statsdrops every field that isn't abyfield or an aggregation. Later commands can only use those columns.
Examples
Failed logons per user and source IP, worst first:
class_uid=3002 status_id=2 | stats count as failures by user.name, src_endpoint.ip | where failures > 10Password spraying: one source trying many accounts:
class_uid=3002 status_id=2 | stats dc(user.name) as users, count as attempts by src_endpoint.ip | where users >= 10First and last time each process was seen on each host:
class_uid=1007 | stats earliest(time) as first_seen, latest(time) as last_seen, count by device.hostname, process.nameCommand lines run by an unusual parent process:
class_uid=1007 actor.process.name="winword.exe" | stats values(process.cmd_line) as cmds, count by device.hostnameOutbound volume per internal host:
class_uid=4002 | stats sum(traffic.bytes_out) as sent, sum(traffic.bytes_in) as received by src_endpoint.ip | sort -sent | head 20Error rate per web host with a conditional count:
class_uid=4002 | stats count as total, count(eval(http_response.code>=500)) as errors by http_request.url.hostname | eval error_pct=round(errors*100/total, 2) | sort -error_pctLatency percentiles per HTTP method:
class_uid=4002 | stats avg(duration) as avg_ms, p95(duration) as p95_ms, p99(duration) as p99_ms by http_request.http_methodCloudTrail API calls per principal and service:
metadata.log_source=aws_cloudtrail | stats count, dc(api.operation) as operations by actor.user.name, api.service.nameEvent volume by log source and class (no pipe needed):
stats count by metadata.log_source, class_nameNotes
- Name your aggregations. Without
as, the column is named after the function (sum,avg,dc,percentile), not the field. Two aggregations of the same kind, such asp95(duration), p99(duration), then collide and the query fails.values(f)andlist(f)default tovalues_<f>/list_<f>. - To keep every event and add group statistics to it, use eventstats. For running totals in time order, use streamstats.
statsruns after alookup,prevalence,inputlookup,resolve_identity, oraistage too.
More references
What's next
- chart:
statswithover/bysplit fields - timechart: aggregate per time bucket
- eventstats: add aggregates to each event
- streamstats: running aggregates