Splunk® Enterprise

Data Model and Pivot Tutorial

Download manual as PDF

This documentation does not apply to the most recent version of Splunk. Click here for the latest version.
Download topic as PDF

Create a pivot table

In the previous topic you used pivot to find the total number of purchase requests and saved the single value display as a report. In this topic, you will use the pivot visualization editor to create a pivot table of the Buttercup Games Successful Purchases object.

The Successful Purchases object has attributes for the products purchased from the Buttercup Games website. This includes the automatically extracted attributes (categoryId and productId) as well as the lookup attributes (price and product_name).

The Buttercup Games online store offers hundreds of products, of a variety of categories, 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.

Define a new Pivot

1. From the app navigation bar, select Pivot to enter the "Select a Data Model" page.

6.2tutorial pivot selectdatamodel.png

2. Choose the Buttercup Games data model and select the Successful Purchases child object.

6.2tutorial pivot selectobject.png

The New Pivot editor for Successful Purchases opens.

6.2tutorial pivot newtable.png

Add pivot elements

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.

  • To add a pivot element: Click the + icon. This opens up the element dialog, where you choose an attribute and then define how the element uses that attribute.
  • To inspect or edit an element: Click the "pencil" icon on the element. This opens the element dialog.
  • To reorder and transfer pivot elements: Drag and drop an element within its pivot element category to reorder it. Drag and drop elements between element categories to transfer them.
  • To remove pivot elements from the Pivot Editor: Open its element dialog and click the Remove button, or drag the element up or down until it turns red and drop it.

Under Filters, 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.

Change the time range filter

Currently your Pivot table shows a single value, the total count of Successful Purchases over All time.

Change the time filter to view the Successful Purchases over a different time range:

1. Under Filter, click the pencil next to All time to open the time range picker.

6.2tutorial pivot newfilters.png

2. Under Presets and Relative, click "Last 7 days".

6.2tutorial pivot newfilters2.png

(If this shows no events, you can select "All time" and continue.)

Add a Split Row element

Add Pivot elements to see the Count of Successful Purchases for each product by name:

1. Under Split Rows, click + and select productName, the lookup field that contains the name of each product, based on the productId.

6.2tutorial pivot newsplitrow.png

This opens a dialog box that lets you format the field.

6.1 tutorial pivot editsplitrows.png

2. Rename the field, Product Name and Click Add To Table.

6.2tutorial pivot newtable2.png

Add a Column Value element

Add a Column Value to see total earned for each product that was successfully purchased:

1. Under Column Values, click + and select price.

6.2tutorial pivot newcolumn.png

2. In the dialog box, format the field:

6.2tutorial pivot newcolumn2.png

2.a Enter the label Total Revenue.

2.b Select the Value Sum.

This creates a field called Total Revenue, which is the summation of the price for each successful purchase of the product. (You can add the price values as another Split Row, if you want to see the cost of each individual product in this table.)

3. Click Add To Table.

6.2tutorial pivot newcolumn3.png

Save the Pivot table

Save the Pivot table as a report named Purchases by Product.

1. Click Save as and select Report.

2. In the Save as Report dialog box:

6.2tutorial pivot savetable.png

2.a Enter the Title "Purchases by Product".

2.b (Optional) Add the Description "Table of Product Purchases".

2.c Include a Time Range Picker.

3. Click Save.

4. In the Your Report Has Been Created dialog box, click View.

6.2tutorial pivot report3.png

Next steps

Continue to the next topic to create some simple pivot visualizations.

Create and save a Pivot
Create a pivot chart

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.2.13, 6.2.14, 6.2.15, 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


Why i didn't see the sum option?

April 2, 2016

Was this documentation topic helpful?

Enter your email address, and someone from the documentation team will respond to you:

Please provide your comments here. Ask a question or make a suggestion.

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

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters