Search Reference

 


pivot

pivot

This page is currently a work in progress; expect frequent near-term updates.

The pivot command makes simple pivot operations fairly simple, but can be pretty complex for more sophisticated pivot operations. Fundamentally this command is a wrapper around stats and xyseries. It does not add new behavior, but it may be easier to use if you are already familiar with how Pivot works. Read more in the Pivot Manual. Also, read how to open non-transforming searches in Pivot.

This command is a generating command and must be first in a search pipeline. It requires a large number of inputs: the data model, the data model object, and pivot elements.

Synopsis

Run pivot searches against a particular data model object.

Syntax

| pivot <datamodel-name> <objectname> <pivot element>

Required arguments

datamodel-name
Syntax: <string>
Description: The name of the data model to search.
objectname
Syntax: <string>
Description: The name of a data model object to search.
pivot element
Syntax: (<cellvalue>)* (SPLITROW <rowvalue>)* (SPLITCOL colvalue [options])* (FILTER <filter expression>)* (LIMIT <limit expression>)* (ROWSUMMARY <true|false>)* (COLSUMMARY <true|false>)* (SHOWOTHER <true|false>)* (NUMCOLS <num>)* (rowsort [options])*
Description: Use pivot elements to define your pivot table or chart. Pivot elements include cell values, split rows, split columns, filters, limits, row and column formatting, and row sort options. Cell values always come first. They are followed by split rows and split columns, which can be interleaved, for example: avg(val), SPLITCOL foo, SPLITROW bar, SPLITCOL baz.

Cell value element

<cellvalue>
Syntax: <function>(fieldname) [AS <label>]
Description: Define the values of a cell and optionally rename it. Here, "label" is the name of the cell in the report.

The set of allowed functions depend on the data type of field specified (fieldname):

  • for strings: list, values, first, last, count, and distinct_count (dc).
  • for numbers: sum, count, avg, max, min, stdev, list, and values.
  • for timestamps: duration, earliest, latest, list, and values.
  • for object or child counts: count.

Row split-by element

SPLITROW <rowvalue>
Syntax: fieldname [AS <label>] [RANGE start=<value> end=<value> max=<value> size=<value>] [PERIOD (auto | year | month| day | hour | minute | second)] [TRUELABEL <label>] [FALSELABEL <label>]
Description: You can have none, some, or all of these options on each SPLITROW. They may appear in any order. You can rename the field using "AS <label>", where "label" is the name of the row in the report.

Other options depend on the data type of the field specified (fieldname):

  • RANGE applies only for numbers. The options (start, end, max, and size) do not all have to be specified.
  • PERIOD applies only for timestamps. Use it to specify the period to bucket by.
  • TRUELABEL applies only for booleans. Use it to specify the label for true values.
  • FALSELABEL applies only for booleans. Use it to specify the label for false values.

Column split-by element

SPLITCOL colvalue <options>
Syntax: fieldname [ RANGE start=<value> end=<value> max=<value> size=<value>] [PERIOD (auto | year | month| day | hour | minute | second)] [TRUELABEL <label>] [FALSELABEL <label>]
Description: You can have none, some, or all of these options on each SPLITROW. They may appear in any order.

Other options depend on the data type of the field specified (fieldname):

  • RANGE applies only for numbers. The options (start, end, max, and size) do not all have to be specified.
  • PERIOD applies only for timestamps. Use it to specify the period to bucket by.
  • TRUELABEL applies only for booleans. Use it to specify the label for true values.
  • FALSELABEL applies only for booleans. Use it to specify the label for false values.

Filter element

Filter <filter expression>
Syntax: <fieldname> <comparator> <value>
Description: The comparator you can use for value expressions depends on the the type of field value.
  • for strings: is, contains, isNot, doesNotContain, startsWith, endsWith,isNull, isNotNull, and regex
  • ipv4: is, contains, isNot, doesNotContain, startsWith, isNull, isNotNull
  • for numbers: =, !=, <, <=, >, >=, isNull, isNotNull
  • for booleans: isNull, isNotNull, = or is

Limit element

Limit <limit expression>
Syntax: LIMIT <fieldname> BY <limittype> <number> <statsfn>(<fieldname>)

Examples

Example 1: This command counts the number of events in the "HTTP Requests" object in the "Tutorial" data model.

| pivot Tutorial HTTP_requests count(HTTP_requests) AS "Count of HTTP requests"

This can be formatted as a single value report in the dashboard panel:

Pivot command single panel.png


Example 2: Using the Tutorial data model, create a pivot table for the count of "HTTP Requests" per host.

| pivot Tutorial HTTP_requests count(HTTP_requests) AS "Count" SPLITROW host AS "Server" SORT 100 host

Pivot command table.png


See also

datamodel, stats, xyseries

Answers

Have questions? Visit Splunk Answers and see what questions and answers the Splunk community has about using the pivot command.

This documentation applies to the following versions of Splunk: 6.0 , 6.0.1 , 6.0.2 , 6.0.3 , 6.0.4 , 6.0.5 , 6.0.6 , 6.0.7 , 6.1 , 6.1.1 , 6.1.2 , 6.1.3 , 6.1.4 , 6.1.5 , 6.2.0 , 6.2.1 View the Article History for its revisions.


Comments

When I try to use the FILTER regex on a string field, I get the following error message : "Error in 'PivotUtil': Cannot filter using 'regex' on field type 'string'.". This page says that it is possible...

TaZe78
December 11, 2014

You must be logged into splunk.com in order to post comments. Log in now.

Was this documentation topic helpful?

If you'd like to hear back from us, please provide your email address:

We'd love to hear what you think about this topic or the documentation as a whole. Feedback you enter here will be delivered to the documentation team.

Feedback submitted, thanks!