Splunk Pivot & Dataset

In this section, we are going to learn about Pivot & Dataset. What is Pivot and how to create Pivot? How do the pivot functions, and what are the attributes associated with it? Also, we are going to learn about the dataset, type of dataset, pivot editor.

Without the Splunk Search Processing Language (SPLTM), the Pivot tool lets us report on a specific data set. First, define a data set we want to say on, and then use a drag-and-drop interface to design and create pivots in the form of tables, maps, and other visualizations that show various aspects of that data.

How do Pivot functions?

Splunk uses data models to define the broad category of event data with which we are working. It then uses hierarchically arranged data model dataset collections to further subdivide the original data set and define the fields on which we want Pivot to return the results. The knowledge managers in our organization, design data models, and their datasets. Hard work is done to help us to concentrate quickly on a particular subset of data from events.

For example, we may have a data model that monitors information from email servers, with data sets representing sent emails and received emails. If we want our sent email to concentrate on trends, pick the data model Email Operation and choose the dataset Emails Sent.

Creating a pivot:

There are two ways to move to the view at Pivots:

  • Via the Datasets page
  • Via the listing page of the data model, through the Settings

The following table describes the steps to create a Pivot

FromWhat to do
Datasets page1. In the Splunk app goto the Search & Reporting app, open the Datasets listing page.
2. Identify the data model dataset for which we want to create a Pivot.
3. In the Actions column, select Explore then Visualize with Pivot.
4. Click Save As... to save our changes as a report or a dashboard panel or as we want.
Settings > Data Models1. Select Settings > Data models
2. Search for the data model, and in the Actions column, click on the Pivot.
3. Click a dataset and create the Pivot.
4. Click Save As... to save our changes as a

Thevot in smaller browser windows, the navigation bar of the Se, if we are viewing Pivot in smaller browser windowsarch & Reporting app, will be hidden. Click on the menu icon at the upper right to use the navigation bar. Slides the navigation bar down.

After selecting a dataset, Splunk Web will take us to the Pivot Editor, where we will be able to create a pivot using the fields available. Our Pivot may take a table or chart shape.

About datasets

The exact composition of a dataset is dictated by the type of dataset we select, and how our data model administrator defined the dataset. There are four types of datasets:

  • Datasets to events represent a series of events. Datasets for the root events are specified by restrictions.
  • Transaction data sets represent transactions ? groups of events that are in some way linked, such as events related to a firewall intrusion accident, or online hotel room reservations by a single client.
  • Search Databases represent inconsistent search results. Search datasets are usually described by searches using transforming or streaming commands to return results in a table format, and the results of those searches are stored.
  • We can add the child datasets to any dataset. They reflect a dataset sub-set adopted by their parent dataset. We will want to base a pivot on a child dataset because it reflects a specific piece of data ? exactly the chunk in which we need to work with a particular study.

Dataset constraints and fields

Constraints are simple searches which define the data set defined by a dataset. They are used to describe the dataset they serve by root event datasets and all the child datasets. All child data sets inherit limitations from their parent datasets, and have their new restriction. This additional restriction ensures that each of them inherits a subset of the data set for their parent dataset.

We might, for example, have a root event dataset called "Error events," where the constraint is simply an error. This dataset would theoretically contain all events that involve the string "error" in our system; it would return the same events as an "error" search.

Most event datasets have more complex, but still not by much, constraints. For example, the sample data model in the "Splunk's Internal Server Logs" contains a child event dataset called "Search Load-Users." This includes events that monitor the number of users running simultaneous searches. The constraints inherited from this dataset boil down to the following search:

index=_internal source=*metrics_log*

This search returns from the internal database metrics log events. Then, the child dataset has this additional limitation:

group=search_concurrency user=*

This command further narrows down the set of events represented by the dataset to metrics log events from the internal database. These have a concomitant group field value and any value user field.

Definitions of the event data set often define the fields that appear in their event data. Fields are connected to a specified dataset. Some fields are mapped directly to the event data of the dataset; others are measured fields or are applied with the aid of lookups and regular expressions to the events of the data collection.

Each child inherits the fields belonging to its parent dataset.

This child datasets can include additional fields that are not part of the description of a parent dataset.

Design pivot tables with the Pivot Editor

In Pivot, once we have chosen a data model, we can come to the Pivot Editor and pick the dataset inside that data model we want to base a pivot on.

When inputting the Pivot Editor first

For example, when we enter the Pivot Editor first after selecting a dataset, we will be in the pivot table mode of the Pivot Editor. Originally, the pivot table will display one row that shows the cumulative result count of the dataset for all time.

What this initial count of results represents depends on what sort of dataset we have chosen.

Type of datasetDoes the initial result count represent?
event dataset (or child dataset)The cumulative number of events overall time chosen by the constraints of the dataset.
transaction datasetThe total number of transactions the dataset has identified over the whole time.
search datasetThe total number of table rows returned overall time by the base search (if the search is not a transforming or streaming search, it reflects the total number of returned events the whole time).

For example, if we go to the data model of Splunk 's Internal Server Logs and click the dataset of Search Load-Users, we will see a pivot table showing the total number of results in the dataset of Search Load-Users.

Now we are ready to start constructing a pivot table or pivot map from these data.

Understanding pivot table elements

To describe a pivot table, the Pivot Editor uses pivot elements. There are four basic types of pivot elements: filters, separating rows, split columns, and column values. Only two elements are specified when we first open the Pivot Editor for a particular dataset:

  • The filter element must be set to All-time by default.
  • The Column Values element (set to the Count_of_<dataset_name> field)

As mentioned in the previous paragraph, this gives us the total number of tests the dataset returns over the entire period.

To define our pivot table, we can add multiple elements from each category of pivot elements. In deciding what details our table will provide, it is simple to add, describe, and delete pivot elements.

The following table of descriptions for pivot elements explains how such elements are used in charts and other visualizations. This information is helpful if we want to build up our pivot table before converting it to a pivot board.

Pivot element basics

This section discusses some of the fundamentals of the use of pivot elements ? how to add, modify, and transfer them around the Pivot Editor while it is in pivot table mode.

To add a pivot element

Select the Symbol +. This opens the dialog for the element, where we select a field and then define how the component uses it. See "Defining a pivot element" for information on the dialog feature, below.

To inspect or edit an element

Click on the item with the "pencil" icon. This opens the dialog on the elements. See "Defining a pivot element" for information on the dialog feature, below.

For reordering pivot elements within a pivot element category.

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

For transferring pivot elements between pivot element categories.

Drag and fall. Have we added page_category as an element of the Column Value only to find that it will work best as a split element? Just drag and drop it over to Split Columns.

We can use any of the following way to delete a pivot element:

  • We can open the dialog with its item and press the Delete button.
  • We can drag and drop the item until it turns red.





Latest Courses