Splunk - Pivot and Datasets
Splunk can ingest many kinds of data sources and build tables which are similar to relational tables. Theses are called table dataset or just tables. They provide easier ways to analyze the data and doing filtering, lookups etc. These table data sets are also used in creating pivot analysis which we will see in this chapter.
Creating a Dataset
We use a Splunk Add-on named Splunk Datasets Add-on to create and manage the datasets. It can be downloaded from the Splunk website here . It has to be installed by following the details tab available in this link. On successful installation we see a button named create a new table dataset.
Select Dataset
Next, we click the Create New Table Dataset button and it gives us the option to choose from the below three options.
Indexes and Source Types - Choose from an existing index or source type which are already added to Splunk through Add Data app.
Existing Datasets – You might have already created some dataset previously which you want to modify by creating a new dataset form it.
Search – Write a search query and the result can be used to create a new dataset.
In our example we choose an index to be our source of data set as shown in the diagram below.
Choose Dataset Fields
On clicking OK in the above screen we are presented with an option to choose the various fields we want to be finally get into the Table Dataset. The _time field is selected by default and this filed can not be dropped. We choose the fields – bytes,categoryID,clientIP and files.
On clicking done in the above screen we get the final dataset table with all the selected fields, as seen below. Here the dataset has become similar to a relational table. We save the dataset with save as option available in the top right corner.
Creating Pivot
We use the above dataset to create a pivot report. The pivot report reflects aggregation of values of one column with respect to the values in another column. In other words, one columns values are made into rows and another columns values are made into columns.
Choose Dataset Action
To achieve this we first select the dataset using the dataset tab and then choose the option visualize with pivot from the Actions column for that data set.
Choose the Pivot Fields
Next, we choose the appropriate fields for creating the pivot table. We choose category ID in the split columns option as this is the filed whose values we want to appear as different columns in the report. Then we choose File in the Split Rows option as this is the field whose values we want to be present in rows. The result shows count of each categoryid values for each value in the file field.
Next, we can save the pivot table as a Report or a panel in an existing dashboard for future reference.
