Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 36 ยท Search Processing Language

SPL stats, chart, timechart, top and rare Explained

Summarize data with SPL transforming commands: stats with count, sum, avg, dc and values, chart over and by, timechart span, top, rare, eventstats and streamstats.

Transforming commands turn many events into a small table of numbers. They are the heart of SPL: almost every dashboard panel ends with one. Learn stats thoroughly and the others follow naturally.

stats

SPL
index=web | stats count
index=web | stats count by status
index=web | stats count AS requests, avg(bytes) AS avg_bytes, max(bytes) AS biggest by host

The syntax is stats <function>(<field>) [AS name] ... [by <fields>]. Without by you get one row; with by you get one row per distinct combination of the by-fields.

Common functions:

Function Result
count, count(field) Number of events, or events where the field exists
sum, avg, min, max, median, stdev Numeric aggregates
dc(field) Distinct count (number of unique values)
values(field) List of unique values (sorted)
list(field) All values in order, including duplicates
first, last, earliest, latest Specific events' values
perc95(field), p50(field) Percentiles
rate, range, mode Additional statistics
SPL
index=web | stats dc(clientip) AS unique_visitors, sum(bytes) AS total_bytes by uri_path
Python Stand-in: count, dc, avg, values by field

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

chart

chart is stats arranged for a visualization. It takes over (the X axis) and optionally by (the series):

SPL
index=web | chart count over host by status
index=web | chart avg(bytes) over uri_path

The first search makes a table with one row per host and one column per status, ready for a stacked bar chart. By default chart shows 10 series and groups the rest into OTHER; use limit=0 to keep all, or useother=f to hide OTHER.

timechart

timechart is chart with _time as the X axis, bucketed automatically:

SPL
index=web | timechart count
index=web | timechart span=1h count by status
index=web | timechart span=5m avg(response_ms) AS avg_ms, perc95(response_ms) AS p95
  • span= sets the bucket size (30s, 5m, 1h, 1d). Omit it and Splunk picks a size that gives about 100 buckets.
  • by field creates a series per value (top 10 plus OTHER by default).
  • Empty buckets show as zero for count, so gaps are visible.
  • Select the Visualization tab and choose line, column or area charts.
Python Stand-in: timechart span=5m count by status

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

top and rare

SPL
index=web | top limit=5 uri_path
index=web | top status by host
index=web | rare limit=3 useragent

top returns the most common values, with count and percent columns. rare returns the least common. limit=0 returns all values. They are shortcuts for a stats count followed by sort and a percentage calculation.

eventstats and streamstats

stats throws the events away. When you want to keep events and add a statistic as a new field, use:

  • eventstats computes the statistic over all results and adds it to every event.
  • streamstats computes a running (cumulative or windowed) statistic in event order.
SPL
index=web
| eventstats avg(bytes) AS avg_bytes by host
| where bytes > 3 * avg_bytes

index=web
| sort 0 _time
| streamstats count AS request_number, sum(bytes) AS running_bytes by host
| streamstats window=5 avg(response_ms) AS moving_avg

The first search finds requests more than three times larger than their host's average. The second numbers requests per host and computes a 5-event moving average.

Python Stand-in: eventstats (add average to each event) and streamstats (running count)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Putting it together: error rate per host

SPL
index=web
| eval is_error=if(status>=500, 1, 0)
| stats count AS requests, sum(is_error) AS errors by host
| eval error_rate=round(errors / requests * 100, 2)
| sort - error_rate

Count requests and errors in one pass, then compute the rate with eval (next lesson, eval and functions). This "count with a flag" pattern appears everywhere.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which command returns the number of distinct client IPs per URI path?

    dc() is the distinct count; count(field) counts events that have the field.
  2. 2. What is the difference between stats and eventstats?

    stats replaces events with a summary table; eventstats adds the result as a new field on every event.
  3. 3. Which command draws a time series with automatic bucketing?

    timechart uses _time as the X axis and chooses a span unless you set span=.
  4. 4. What does top limit=5 uri_path return?

    top counts values and shows the most common, with count and percent columns.

Summary

  • stats ... by is the core aggregation command; know count, sum, avg, dc, values, perc.
  • chart splits by a field, timechart by time; both accept by.
  • top and rare are quick frequency tables.
  • eventstats and streamstats add statistics while keeping events.
  • Count with flags (if(...)) then compute rates with eval.

Next: eval and functions