Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 36 ยท Data Models and Pivot

Splunk Pivot: Build Reports and Charts Without Writing SPL

Use Splunk Pivot to build tables and charts from a dataset: filters, split rows and columns, column values, time buckets, saving pivots as reports, and the pivot command.

Pivot is Splunk's point-and-click report builder. You choose a dataset, then describe a table the way you would in a spreadsheet pivot table: what to filter, what goes in rows and columns, and what to calculate. Splunk writes the search for you, which makes data models useful for people who do not know SPL. This lesson explains the Pivot editor, how its choices map to SPL, and when to use it.

Opening Pivot

Open Pivot from the app's navigation, or from Settings > Data models, select a data model and a dataset, and choose Pivot. You can also open a dataset in Datasets and click Visualize with Pivot. If the dataset is accelerated, Pivot uses the summary and returns quickly.

The editor shows the dataset's name, a Filters area, and the four building blocks of a table:

Area What you pick SPL idea
Filters Time range and field conditions where / base search constraints
Split Rows Fields that become row labels by field
Split Columns Fields that become column labels chart ... over ... by
Column Values Aggregations (count, distinct count, sum, average, min, max, list, values, first, last, earliest, latest, ...) stats functions

Below the editor there are tabs for the result type: Statistics Table, Column, Bar, Line, Area, Scatter, Pie, Single Value, Radial/Filler/Marker Gauge and Map. Pivot also reaches data through Events (listing the raw events) if you open a dataset directly.

Building a pivot step by step

Suppose the Web data model has datasets Web and Server_errors:

  1. Choose Server_errors. The default result is a count of events.
  2. Set the Filter time range to Last 7 days and add a field filter, for example uri_path starts with /api.
  3. Split Rows by _time and choose a period (Hour, Day, ...). Split Rows with a time field makes a time series, with fields like status for categories.
  4. Split Columns by status. Each distinct status becomes a column.
  5. Change Column Values to Count (the default) or to another aggregation, and rename the label.
  6. Click the Column chart tab, then use Format to set stacked or clustered style, axis titles and colors.

Pivot respects the field types in the model. A numeric field offers ranges (for example, group bytes into buckets of 1000), a string field offers limits (top 10 values), and a timestamp field offers time periods. Fields of type child count and object have their own options.

Saving a pivot

Click Save As and pick Report or Dashboard Panel. A saved pivot stores the data model, the dataset and the pivot settings, not a plain search. You can reopen it in the Pivot editor later and keep adjusting. A pivot-based report is scheduled, shared and accelerated like any other report.

Unlike the regular search editor, a saved pivot does not let you edit the search text directly: Open in Search converts it to SPL, after which it is an ordinary (non-pivot) search.

The pivot command

Every pivot is turned into a pivot SPL command, usable in any search:

SPL
| pivot Web Server_errors
    count(Server_errors) AS "Errors"
    SPLITROW _time PERIOD hour
    SPLITCOL status
    FILTER uri_path startswith "/api"

You do not normally write this by hand: build the pivot in the UI, click Open in Search, and copy the generated command. The command is also what Pivot-based dashboard panels run. Since pivot is a generating command, it must start the search (with a leading |).

If you prefer SPL with the same speed, use tstats against the accelerated model and finish with chart or timechart:

SPL
| tstats count from datamodel=Web where Web.status>=500 by _time span=1h, Web.status
| rename Web.status AS status
| xyseries _time status count

Try it: a tiny pivot engine (stand-in)

This stand-in implements split rows, split columns and a count/sum aggregation, which is all that a pivot does conceptually.

Python Stand-in: split rows, split columns, column values

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.

Try it: the generated pivot command (stand-in)

The Pivot editor builds the command from your choices. This stand-in does the same string assembly so you can see the mapping.

Python Stand-in: from pivot settings to SPL

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.

Pivot or SPL?

Use Pivot when Use SPL when
The audience does not know SPL You need commands Pivot lacks (eventstats, streamstats, join)
The data model is accelerated and stable The data is not modeled yet
You want consistent definitions across teams You are exploring and the question changes quickly
You need quick charts with formatting You need exact control over the search

A healthy pattern: analysts explore in SPL, and when a question becomes a regular report, the team models the data and exposes it through Pivot and dashboards.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In Pivot, what does Split Columns do?

    Split Columns creates one column per distinct value of the chosen field, like chart ... by field.
  2. 2. What do you get when you click Open in Search on a pivot?

    The pivot becomes SPL. After that it is an ordinary search and no longer editable in the Pivot editor.
  3. 3. Why is Pivot fast on accelerated data models?

    Acceleration builds summaries that Pivot and tstats read directly.
  4. 4. Which task is a better fit for SPL than for Pivot?

    Commands such as streamstats and eventstats are not available in the Pivot editor.

Summary

  • Pivot builds tables and charts from a dataset using Filters, Split Rows, Split Columns and Column Values.
  • Field types decide the options (time periods, numeric ranges, top values).
  • A pivot is saved as a report or panel and can be turned into a pivot command with Open in Search.
  • Pivot is fastest on accelerated models; SPL stays better for exploration and advanced commands.

Next: CIM and data model acceleration