Splunk® Enterprise

Data Model and Pivot Tutorial

Download manual as PDF

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 dataset.

The Successful Purchases dataset has fields for the products purchased from the Buttercup Games website. This includes the automatically extracted fields, such as categoryId and productId, as well as the lookup fields, price and product_name, that you added when you built the dataset.

The Buttercup Games online store sells hundreds products, in of a variety of categories. 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. Using the report, you can quickly see which of the products were the top sellers for that period.

Define a new pivot

  1. From the Splunk bar, select Settings > Data models.
  2. Select the Buttercup Games data model.
  3. In the Datasets editor page, click Pivot.
  4. In the Select a Dataset page, select the Successful Purchases child dataset.

    The New Pivot editor for Successful Purchases opens.

    7.0 dmtutorial successful purchases pivot.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 a field and then define how the element uses that field.
  • 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 range to view the Successful Purchases over a different time range:

  1. Under Filters, click the pencil icon to open the time range picker.
  2. 6.2tutorial pivot newfilters.png

  3. In the Presets list under the Relative column, click Last 7 days.
  4. 6.2tutorial pivot newfilters2.png

    If no events are returned, it simply means that you downloaded the tutorialdata.zip file more than a week ago. Select a longer time range, such as Previous month or you can keep the default time range, All time.

Add a Split Row element

You can add pivot elements to see the Count of Successful Purchases for each product by name:

  1. Under Split Rows, click + and select productName.
    This is the lookup field that contains the name of each product. This field is based on the productId field in the events.
    6.2tutorial pivot newsplitrow.png
    A dialog box opens where you can format the field.
  2. For Label, type Product Name.
  3. 6.1 tutorial pivot editsplitrows.png
  4. Click Add To Table.
  5. 7.0 dmtutorial pivot newtable2.png
    The table shows the count of successful purchases for the past 7 days for each product.

Add a Column Value element

Now you want the table to show the total revenue 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, specify how you want the information to appear in the table.
  3. For Label, type Total Revenue.
  4. For Value, select Sum.
  5. 6.2tutorial pivot newcolumn2.png
    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.
  6. Click Add To Table.
    7.0 dmtutorial pivot newcolumn3.png
  7. The table shows the new Total Revenue column with the total amount earned from the purchases of each product.

Save the pivot table

Now it is time to save the pivot table as a report.

  1. Click Save As and select Report.
  2. 6.2tutorial pivot savetable.png
  3. For Title, type Purchases by Product.
  4. Optional. For Description type Table of Product Purchases.
  5. For Time Range Picker, the Yes setting should already be selected.
  6. Click Save.
  7. In the Your Report Has Been Created dialog box, click View.
    7.0 dmtutorial pivot report3.png
  8. Next steps

    Continue to the next topic to create some simple pivot visualizations.
PREVIOUS
Create and save a pivot report
  NEXT
Create a pivot chart

This documentation applies to the following versions of Splunk® Enterprise: 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.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


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