Design pivot tables with the Pivot Editor

In Pivot, you'll come to the Pivot Editor after you've selected a data model and have chosen the dataset within that data model that you want to base a pivot on. (If you're not sure how to get to select a data model or chose a dataset, see Introduction to Pivot.)

When you first enter the Pivot Editor

When you first enter the Pivot Editor after selecting a dataset, you'll be in the Pivot Editor's pivot table mode by default. The pivot table will initially display one row that presents the dataset's total result count over all time.

What this initial result count represents depends on the type of dataset you've selected.

Type of dataset	Initial result count represents?
event dataset (or child dataset)	The total number of events selected by the dataset constraints over all time.
transaction dataset	The total number of transactions identified by the dataset over all time.
search dataset	The total number of table rows returned by the base search over all time (if the search is not a transforming or streaming search, it represents the total number of events returned over all time).

For example, if you go to the "Splunk's Internal Server Logs" data model and click the "Search Load - Users" dataset, you'll see a pivot table that presents the total result count in the "Search Load - Users" dataset over all time.

Now you're ready to start building a pivot table or pivot chart out of this data.

Understanding pivot table elements

The Pivot Editor uses pivot elements to define a pivot table. There are four basic pivot element categories: Filters, split rows, split columns, and column values. When you first open the Pivot Editor for a specific dataset, only two elements will be defined:

• A Filter element (set to All time)
• A Column Values element (set to the Count_of_<dataset_name> field)

As described in the preceding subsection, this gives you the total count of results returned by the dataset over all time.

You can add multiple elements from each pivot element category to define your pivot table. It's easy to add, define, and remove pivot elements in the process of determining what information your table should provide.

The following table of pivot element definitions describes how these elements are used in charts and other visualizations. This knowledge is helpful if you intend to build out your pivot table before converting it to a pivot chart. For more information about how charts and visualizations use tabular data, see "Data structure requirements for visualizations" in the Splunk Data Visualizations Manual.

Pivot element type	How does it work?	How is it used in data visualizations?
filter	Used to cut down the result count for the dataset. They are restrictions in addition to those that might be applied via constraints or other means in the dataset's definition. All pivots are filtered by time range. You can optionally add one or more filters by fields. For example, if you were viewing a "Page Views" dataset that contained page view events for a website, you could set up a filter that would cause the pivot table to display only those page view events from the past week that were successful (they have an http_status value of 2*).	All of the chart and data visualization options include time ranges, and they can make use of additional filter elements.
split row	Splits out the pivot results by row. For example, you could use this element to configure a Page View dataset to display a row for each month of the past year, thus breaking out the page view count by month.	Column, and bar charts use the first split row element in pivot table definitions to provide their x-axis values. Area and line charts use the first pivot table split row element for their x-axis values, but only when it uses the _time field. When you switch to an area or line chart, Pivot will populate x-axis with _time whether _time is being used in a split row element or not. Scatter charts use the first two split row elements in a pivot table definition. The first split row element is required for scatter charts; it creates a "mark" on the scatter chart for each unique value of the chosen field. The second split row element is optional for scatter charts; it ensures that each mark with the same value of its field has the same color. Pie charts use the values from the first split row element to determine the number and colors of their slices. Single value visualizations do not use split row elements.
split column	Breaks out field values by column. For example, you could design a pivot table for a Page View event-based dataset that breaks out its returned events by the page_category of the pages viewed (product info, blog, store, support, etc.).	Column, bar, line, and area charts use the values from the first split column element in pivot table definitions to provide their colors (or series). In other words, when you see a line chart in Pivot with three lines, each a different color, it means that the corresponding pivot table definition includes a split column element that breaks the results out into a table with three field value columns. Scatter charts, pie charts, and single value visualizations do not use split column elements.
column value	Is usually numeric in nature and represents aggregates like result counts, sums, and averages (in the case of non-numeric fields you can do things like list distinct field values). When you first enter a pivot table, you'll find a default column value element that uses the "Count of <name of dataset>" field. It represents the a count of events, results, or transactions, depending on the type of dataset you're working with. You could use this element type to configure a pivot table for a Page View dataset to show the average number of page views for a given row/column combination.	Column, bar, line, and area charts use the first column value element in pivot table definitions to provide their y-axis values. Scatter charts use the first two column value elements in a pivot table definition, when both are defined. The first column value element provides the scatter chart's x-axis values. The second column value element provides the scatter chart's y-axis values. Pie charts use the first column value element to determine the relative sizes of their slices. Single value visualizations (single value, the three gauge types) use the first column value element to get their single value, while ignoring any existing split row and split column elements.

Pivot element basics

This section covers some of the basics of pivot element usage--how to add them, edit them, and move them around the Pivot Editor when it is in pivot table mode.

To add a pivot element

Click the + icon. This opens up the element dialog, where you choose a field and then define how the element uses that field. For details on the element dialog, see "Define a pivot element," below.

To inspect or edit an element

Click the "pencil" icon on the element. This opens the element dialog. For details on the element dialog, see "Define a pivot element," below.

To reorder pivot elements within a pivot element category

Drag and drop an element within its pivot element category to reorder it. For example, if you have page_category and department elements in the Split Rows pivot element category, but you want to reorder them so that department comes before page_category, you can simply drag and drop them around to the correct order.

To transfer pivot elements between pivot element categories

Drag and drop them. Did you add page_category as a Column Value element only to realize it would work better as a split column element? Just drag it over to Split Columns and drop it there.

Element transfer caveats:

• You cannot transfer elements to or from the Filters category.
• You cannot transfer a Column Value element featuring the Count_of_<dataset_name> field to any other category. Similarly, if you are working with a dataset that has children, you cannot transfer a Column Value element featuring the is_<child_dataset_name> or is_not_<child_dataset_name> fields to any other element category. Here's an example of how these Column Value choices appear for a dataset with children:

To remove pivot elements from the Pivot Editor

You can remove a pivot element in one of two ways:

• You can open its element dialog and click the Remove button.
• You can drag the element up or down until it turns red and drop it.

Configure elements for your pivot table

When you add or edit a pivot element you use the element dialog to define it. The element dialog is broken up into two steps. In one step, you choose (or change) the element field. In the other step, you define (or update) the element configuration.

• When you add an element, you choose the element field first, then move on to configure the element.
• When you edit an existing element, you start at the element configuration step. You click a back arrow within the dialog to go to the element field step, where you can change the field.

Configure a filter element

You can define three kinds of filter elements for a pivot:

• The time filter is always present when you build a pivot; you cannot remove it. It defines the time range for which the pivot returns results. It operates exactly like the time range menu that is in use throughout Splunk Web. For more information, see "Select time ranges to apply to your search" in the Search Manual.
• Match filters enable you to set up matching for strings, numbers, timestamps, booleans, and IPv4 addresses. For example, you can find all online store purchase events where the price is greater than or equal to $19.99. Or you can find all website hits where the IPv4 value for the site visitor starts with 192.168.

Note: Currently match filters can be used to set up "AND" boolean operations, but not "OR" boolean operations. So you can set up a pair of filters that when combined say customer_country = Spain AND France but not customer_country = Spain OR France.

• Limit filters enable you to restrict in some manner the number of results returned by the pivot.

Here's a limit filter example: Say you have an online store that offers hundreds of products, and you want to know more about the items that were purchased over the past week. You can create a pivot report that breaks down the total number of purchase events by product name, and through that quickly see which of your products were the top sellers for that period. But what if you want to see which ten products were your top earners for that same period? All you need to do is add a limit filter element that ensures that the report only displays the 10 products with the highest price sums for their purchase events. This way, a product with just 10 purchase events in the past week but a price of $100 (for a total sum of $1000) might be at the top of the list, while a product with 500 purchase events but a price of $1 ($500) could be much lower on the list, and potentially not within the top 10 results returned.

To make the table easier to read you can add a split row column that shows the price and a Column Value column that shows the sum of the price (the total amount of revenue returned for the listed products for the given time range).

Here's how this example looks with data similar to that of the Data Model Tutorial, sorted by "sum of price". Note that the products with the most successful purchases are not at the top of the list.

Match filter options

The configuration options for a match filter element depend on the type of field that you've chosen for the element.

If you're basing the filter on a string type field, you specify a filter rule (your options are is, contains, is not, does not contain, starts with, ends with, is null, and is not null) and the field that the filter rule should be applied to (not applicable to the is null and is not null filter rules).

If you're basing the filter on an IPv4 field, you can specify the same choices as you can for string fields, with the exception of the ends with filter rule.

If you're basing the filter on a number type field, you specify a filter rule (your options are =, !=, <, <=, >=, >, is null, and is not null) and the field value that the filter rule should be applied to (not applicable to the is null and is not null filter rules).

If you're basing the filter on a Boolean type field you can specify whether the filter should select events where the field value is true, false, null, or not null.

If you're basing the filter on a timestamp type field, you can use the time range menu to choose an earliest and latest time.

Limit filter options

You can base a limit filter element on string and number type fields. You can specify:

• The field to limit by--this can be any string, number, or Count_of_<dataset_name> field available in the current dataset, including the field that the filter element is filtering.
• How to limit: your options are Highest and Lowest
• The maximum number of results to return--this can be any number.
• The stats function to apply for limiting. The available functions here depend on the type of the field you want to limit by.
  • For string fields, you can select counts and distinct counts.
  • For number fields, you can select counts, distinct counts, sums, and averages.
  • For Count_of_<dataset_name> fields, counts is the only possible choice.

Note: Selecting distinct counts for a field with high cardinality (such as Name or Phone_Number) can slow pivot performance.

Configure a split row or split column element

The configuration options available for split row and split column elements depend on the type of field you choose for them. A few configuration options are specific to either split row or split column elements, while other options are available to either element type no matter what field type you've chosen.

Configuration options common to both split row and split column elements regardless of field type

• Max Rows or Max Columns - The maximum number of rows or columns that can appear in the results table. Must be non-negative; a value of 0 means no maximum is set. A few special cases exist where the Max Rows/Max Columns option is unavailable:
  • If there is only one split and it is based on a timestamp or Boolean field type.
  • If there is only one split and it is based on a numeric field type and is configured to use numeric ranges (see below for the configuration options available for numeric field types).
• Totals - Indicates whether to include a row or column that represents the total of all others in an field called ALL.
  • Set to No by default.
  • The ALL row/column does not count toward the Max Rows/Max Columns limit.

Configuration options specific to split row elements regardless of field type

• Label - Use this to override the field name with a different text string for reporting purposes. For example, you can use it to ensure that an field titled "product name" displays as "Product" in the pivot.
• Sort - How the split rows that the element creates should be sorted. Available values are Default, Descending, and Ascending. The default is Default.
  • When the Sort value is Default Splunk software sorts the rows naturally by the field type of the first split. In other words, if the first split is on uri (a string field), the rows will be sorted alphabetically by the value of uri. If it is on _time (a timestamp field) the rows will be sorted in ascending chronological order.
  • When the Sort value is Descending or Ascending, the rows will be sorted by the value of the first Column Values element that outputs a metric value (via an aggregation operation like count, sum, average, and so on).

Configuration options specific to split column elements regardless of field type

• Group Others - Indicates whether to group any results excluded by the Max Columns limit into a separate OTHER column.
  • Available values are Group Others and Hide Others. The default is Group Others.
  • The OTHER column does not count towards the Max Columns limit.

If you've chosen a string field for your split row or split column element

There are no configuration options specific to string fields that are common to both split row and split column elements.

If you've chosen a numeric field for your split row or split column element

• Create ranges - Indicates whether or not you want your numeric values represented as ranges (Yes) or listed separately (No). Set to Yes by default, but if you leave the other value range fields blank, it will behave as if set to No. When Yes is selected you can optionally identify:
  • the maximum number of ranges to sort results into
  • the maximum size each range should be
  • the range start and end values

If you've chosen a Boolean field for your split row or split column element

• You can provide alternate labels for true and false values.

If you've chosen a timestamp field for your split row or split column element

• Period - Use this to bucket your timestamp results by Year, Month, Day, Hour, Minute, or Second. Set to Auto by default.

Configure a column value element

When you first enter the Pivot Editor, you'll find a column value element that provides the count of results returned by the dataset over all time. The only aspect of the "Count of <name of dataset>" element that you can change is its Label--to change its name in the pivot table. You can opt to keep this element, or remove it in favor or a different column value element.

New column value elements can be based on string, numeric, and timestamp field types. In all cases, you can update the Label for the element if you wish.

When you add a string, numeric, or timestamp event, you indicate the calculation that should be used to calculate the Value that is displayed in the column cells.

• For string fields the options are List Distinct Values, First Value, Last Value, Count, and Distinct Count.
• For numeric fields the options are Sum, Count, Average, Max, Min, Standard Deviation, and List Distinct Values.
• For timestamp fields the options are Duration, Earliest, and Latest.

Note: Selecting Distinct Count for a field with high cardinality (such as Name or Phone_Number) can slow pivot performance.

Manage the pivot table display and format

You can control the pagination of the pivot table results via a dropdown. Pivot can be set up to display 10, 20, and 50 results per page (20 is the default).

A Format dropdown enables you to control other aspects of table appearance and behavior. For example you can determine whether the table wraps results and displays row numbers. You can also determine drilldown and data overlay behavior.

Drilldown in Pivot Tables

Pivot table drilldown mechanics are similar to those described in Use drilldown for dashboard interactivity in Dashboards and Visualizations. You can review that topic for an overview of drilldown functionality.

The Row drilldown mode

Choosing the Row drilldown mode causes the drilldown action to select an entire row of the pivot table. Clicking on a specific row launches a search that focuses on the split row element values that belong to the row. If there isn't a split row element in the pivot definition the drilldown search will show all events in the table. For each split row element in the definition, Splunk software adds a field-value constraint to the resulting drilldown search.

For example, say you have a pivot table of web intelligence data where the rows have been split by URI and then again by HTTP_status. If you click on a row where the URI value is index.php and the HTTP_status is 200, you'll get a search that brings back only those events where URI = index.php AND HTTP_status = 200.

Note: The exception to this mechanic is triggered when the first split row element is _time. Instead of adding constraints to the drilldown search, the search will be restricted with the earliest and latest time of the row. For example, if you have a pivot table where the rows are split by _time with an hour between each row, clicking on the row at 9:00AM will generate a search that returns events between 9 and 10AM.

The Cell drilldown mode

Choosing the Cell drilldown mode causes the drilldown action to select a specific cell of the pivot table. Clicking on a specific cell launches a search that takes into account the values of the split row and split column elements that affect the cell.

If no split row or split column elements have been chosen for the pivot table definition, the search will encompass all of the events returned for the table.

If split row elements have been defined but there are no split column elements, the search will operate like a Row drilldown search (see above).

For each split row and split column element in the pivot table definition, a field/value constraint will be added to the resulting drilldown search.

For example, say you have a pivot table of web intelligence data where the rows have been split by URI and the columns have been split by HTTP_status. Clicking on a cell where the row URI value is index.html and the column header value is 404 should generate a search that brings back events where URI = index.html and HTTP_status = 404.

When _time is the first split row element, the behavior is the same as for row drilldowns (see above).

Note: In the case of multivalue fields, each individual field value is clickable. A field/value constant will be added to the resulting drilldown search with the field name of the corresponding cell element equal to that of the clicked value. For example, in a pivot table of web intelligence data where a cell is displaying all of the distinct values of HTTP_status, clicking on the 303 value will generate a search that returns events where HTTP_status=303.