Related Answers
Download topic as PDF
pivot
Description
The pivot command makes simple pivot operations fairly straightforward, but can be pretty complex for more sophisticated pivot operations. Fundamentally this command is a wrapper around the stats and xyseries commands.
The pivot command does not add new behavior, but it might be easier to use if you are already familiar with how Pivot works. See the Pivot Manual. Also, read how to open non-transforming searches in Pivot.
Run pivot searches against a particular data model object. This requires a large number of inputs: the data model, the data model object, and pivot elements.
Syntax
| pivot <datamodel-name> <object-name> <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
- <cellvalue>
- Syntax: <function>(fieldname) [AS <label>]
- Description: Define the values of a cell and optionally rename it. Here,
labelis the name of the cell in the report.
The set of allowed functions depend on the data type of the fieldname:
- Strings: list, values, first, last, count, and distinct_count (dc)
- Numbers: sum, count, avg, max, min, stdev, list, and values
- Timestamps: duration, earliest, latest, list, and values
- Object or child counts: count
Descriptions for row split-by elements
- SPLITROW <rowvalue>
- Syntax: SPLITROW <field> [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 specify one or more of these options on each SPLITROW. The options can 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:
- RANGE applies only for numbers. You do not need to specify all of the options (start, end, max, and size).
- 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.
Descriptions for column split-by elements
- 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 SPLITCOL. 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.
Descriptions for filter elements
- Filter <filter expression>
- Syntax: <fieldname> <comparison-operator> <value>
- Description: The expression used to identify values in a field. The comparison operator that you use depends on the type of field value.
- Strings: is, contains, in, isNot, doesNotContain, startsWith, endsWith, isNull, isNotNull
- For example: ... filter fieldname in (value1, value2, ...)
- ipv4: is, contains, isNot, doesNotContain, startsWith, isNull, isNotNull
- Numbers: =, !=, <, <=, >, >=, isNull, isNotNull
- Booleans: is, isNull, isNotNull
Descriptions for limit elements
- Limit <limit expression>
- Syntax: LIMIT <fieldname> BY <limittype> <number> <stats-function>(<fieldname>)
- Description: Use to limit the number of elements in the pivot. The
limittypeargument specifies where to place the limit. The valid values aretoporbottom. Thenumberargument must be a positive integer. You can use any stats function, such asmin,max,avg, andsum. - Example: LIMIT foo BY TOP 10 avg(bar)
Usage
The pivot command is a report-generating command. See Command types.
Generating commands use a leading pipe character and should be the first command in a search.
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:
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
See also
Answers
Have questions? Visit Splunk Answers and see what questions and answers the Splunk community has about using the pivot command.
|
PREVIOUS overlap |
NEXT predict |
This documentation applies to the following versions of Splunk® Enterprise: 6.2.0, 6.2.1, 6.2.2, 6.2.3, 6.2.4, 6.2.5, 6.2.6, 6.2.7, 6.2.8, 6.2.9, 6.2.10, 6.2.11, 6.2.12, 6.3.0, 6.3.1, 6.3.2, 6.3.3, 6.3.4, 6.3.5, 6.3.6, 6.3.7, 6.3.8, 6.3.9, 6.3.10, 6.3.11, 6.3.12, 6.3.13, 6.3.14, 6.4.0, 6.4.1, 6.4.2, 6.4.3, 6.4.4, 6.4.5, 6.4.6, 6.4.7, 6.4.8, 6.4.9, 6.4.10, 6.4.11, 6.5.0, 6.5.1, 6.5.1612 (Splunk Cloud only), 6.5.2, 6.5.3, 6.5.4, 6.5.5, 6.5.6, 6.5.7, 6.5.8, 6.5.9, 6.5.10, 6.6.0, 6.6.1, 6.6.2, 6.6.3, 6.6.4, 6.6.5, 6.6.6, 6.6.7, 6.6.8, 6.6.9, 6.6.10, 6.6.11, 6.6.12, 7.0.0, 7.0.1, 7.0.2, 7.0.3, 7.0.4, 7.0.5, 7.0.6, 7.0.7, 7.0.8, 7.0.9, 7.0.10, 7.0.11, 7.1.0, 7.1.1, 7.1.2, 7.1.3, 7.1.4, 7.1.5, 7.1.6, 7.1.7, 7.1.8, 7.2.0, 7.2.1, 7.2.2, 7.2.3, 7.2.4, 7.2.5, 7.2.6, 7.2.7, 7.3.0
Comments
Could you enhance the filter argument to allow multiple conditions filtering, like ... filter filedA in ("a1", "B1") AND FieldB in ("b1", "A2")
This is sth Excel Pivot table can do.
nvrmnd i was able to get this to work:
| pivot Mobile Mobile count(Mobile) AS "Hits" SPLITCOL host SPLITROW _time AS "_time" PERIOD hour
Can you provide an example of pivot with splitrow used with PERIOD as seen in the section "Descriptions for row split-by elements"? The closest example i found was in the splunk answers which was posted by some other user as a question
https://answers.splunk.com/answers/149284/pivot-ignoring-some-period-values.html
Is there any other documentation on the LIMIT feature of Pivot? It is not fully documented here.
Please update the 6.3.X documentation to include the "inList" FILTER option. Please clarify its usage in the SPL (e.g., FILTER <<FIELD>> in ("<<value1>>", "<<value2>>", ...)
Is there a documentation on how to use token with Pivot? which depends on data models. Please share a link if you have one.
Leonjxtan - I will pass along your suggestion to our development team.