nano SIEM
Search Commands

stats

Supported in:Self-hosted

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> ...]]
PartDescription
aggAn 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).
byOne 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

FunctionReturns
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 by values, with the by fields first and then one column per aggregation.
  • Rows are ordered by the first aggregation, descending, then by the by fields. stats count by f therefore lists the largest groups first. Use sort for a different order.
  • Events with a null by value form their own group (shown as null). Filter them out first if you don't want them: user.name=* | stats count by user.name.
  • stats drops every field that isn't a by field 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 > 10

Password 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 >= 10

First 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.name

Command 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.hostname

Outbound 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 20

Error 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_pct

Latency 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_method

CloudTrail API calls per principal and service:

metadata.log_source=aws_cloudtrail | stats count, dc(api.operation) as operations by actor.user.name, api.service.name

Event volume by log source and class (no pipe needed):

stats count by metadata.log_source, class_name

Notes

  • 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 as p95(duration), p99(duration), then collide and the query fails. values(f) and list(f) default to values_<f> / list_<f>.
  • To keep every event and add group statistics to it, use eventstats. For running totals in time order, use streamstats.
  • stats runs after a lookup, prevalence, inputlookup, resolve_identity, or ai stage too.

More references

  • top / rare: most / least common values with percentages

What's next

On this page