Buttercup Games: Further Dashboarding Techniques (Part 7)

[ITWhisperer]

This series of blogs assumes you have already completed the Splunk Enterprise Search Tutorial as it uses the same dataset that you will have already downloaded and ingested into Splunk. If not, please go to the Tutorial and complete it (or at least download and ingest the dataset).

This is the seventh blog in the series, and builds on the dashboard created in the previous blogs.

Drilling down to the events

From the details chart, we now want to look at events from the time period which match the various status values. This is done by enabling the drilldown capability of the chart. Depending on which part of the chart is clicked, the resulting table should show various events matching the drilldown criteria.

Request events table

First, you can create a table that displays the events from the details chart. Later, you will add filtering based on which area of the chart is clicked.

• Click on Edit.
• Click on + Add Panel
• Expand New, and then click on Statistics Table.
• Change the panel title to Requests $zoom.period$
• Edit the search for the panel as below

sourcetype=access_combined_wcookie earliest=$zoom.earliest$ latest=$zoom.latest$
| eventstats count as _count
| streamstats count as _row
| eval Event=_row."/"._count
| table Event _time clientip method file action productId itemId categoryId status

This screen image shows the events statistics table being added to the dashboard.

This search also overrides the earliest and latest values so there is no need to change the Time Range settings.

• Click Add to Dashboard
• Save the updated dashboard, and try selecting the same time window.This screen image shows the events statistics table.

  You may have noticed that the statistics table is still visible even when no time period has been selected, and that there is an error message in the table instead of some events. You will be fixed in the next section.

Filtering the request events table

The events statistics table will be filtered depending on what the user has clicked on in the details chart. This capability is called drilldown. From the chart, the user has a number of options to modify the events filter; they can click on a bar in the details chart; they can click on an item in the legend of the chart; or, they can click on one of the overlay lines.

• by clicking on a bar in the chart, the corresponding status value will be added to the filter. If it is already in the filter it will be removed
• by clicking on an item in the legend, the filter will be set to just that value (removing all the other values)
• by clicking on the 200 line, the successful events will be added (or removed) from the filter
• by clicking on the threshold line or legend item, the filter will be cleared

You will implement these options in the drilldown section of the details chart in the following way.

• Click on Edit.
• Click on the Source button.
• Insert the following lines into the chart stanza for the details chart:

<drilldown>
    <eval token="drilldown.series">if(isnull($click.name$),null(),$drilldown.series$)</eval>
    <eval token="drilldown.series">case($click.name2$=="threshold" or $drilldown.series$ == $click.name2$,null(),isnull($drilldown.series$),$click.name2$,isnull(mvfind(split($drilldown.series$,","),$click.name2$)),mvjoin(mvdedup(mvappend(split($drilldown.series$,","),$click.name2$)),","),true(),replace(replace($drilldown.series$,",".$click.name2$,""),$click.name2$.",",""))</eval>
    <eval token="drilldown.choice">if(isnull($drilldown.series$),"","status IN (".$drilldown.series$.")")</eval>
    <set token="drilldown.status">$click.name2$</set>
</drilldown>​

"This screen image shows the drilldown handling for the details chart.

The drilldown handler works in the following manner

1. Firstly, if $click.name$ token is null, the user has clicked in the legend, so clear the current selection of series from the filter.
2. Secondly, set the $drilldown.series$ filter token based on the following choices (It is worth noting that, in SimpleXML dashboard code, the case evaluation function should be completed in a single line.): 
   1. if "threshold" has been clicked (as seen in the $click.name2$ token), or the current filter token only contains the clicked item, then clear the selection (by setting it to null);
   2. if the current selection is empty, set it to the clicked item;
   3. if the clicked item is not currently in the filter list (mvfind() equates to null), add it to the list;
   4. otherwise, the clicked item must currently be in the filter list, so remove it from the list; this is done by replacing the clicked item and a preceding comma, or the clicked item and a following comma, with an empty string.
3. Thirdly, set the $drilldown.choice$ token to an empty string (no filter) if no choices remain in the $drilldown.series$ token (it is important to note that an empty string is used here rather than a null to prevent the search from stalling if no filtering is required), or to "status IN" followed by a comma-separated list of required status values. Note that since these are all numeric, they do not have to be enclosed in double-quotes.
4. Finally, set a token to display the events table.

• Enable drilldown for the details chart:

<option name="charting.drilldown">all</option>​

This screen image shows the drilldown enabled for the details chart.

Having set up the tokens in the drilldown handler, you now need to use the tokens in the events statistics table

• Still in Edit Source mode, update the row for the statistics panel:

<row depends="$drilldown.status$">

• Update the table title to show the choice being used in the table

<title>Requests $zoom.period$ $drilldown.choice$</title>​

• Update the search for the panel to include the choice made by the drilldown, as below

sourcetype=access_combined_wcookie earliest=$zoom.earliest$ latest=$zoom.latest$ $drilldown.choice$
| eventstats count as _count
| streamstats count as _row
| eval Event=_row."/"._count
| table Event _time clientip method file action productId itemId categoryId status​

This screen image shows the events statistics table being amended to use the drilldown tokens.

Since we are now using some tokens to determine when to show the event statistics table, you should unset these whenever the time period selection is changed.

• Still in Edit Source mode, update the zoom selection handler for the hourly rates panel:

<selection>
    <eval token="zoom.earliest">if($start$ = $beginning$ and $end$ = $ending$, null(), $start$)</eval>
    <eval token="zoom.latest">if($start$ = $beginning$ and $end$ = $ending$, null(), $end$)</eval>
    <eval token="zoom.period">"from ".strftime($zoom.earliest$,"%F %H:%M")." to ".strftime($zoom.latest$,"%F %H:%M")</eval>
    <unset token="drilldown.series"></unset>
    <unset token="drilldown.status"></unset>
</selection>​

This screen image shows the updated selection handler unsetting drilldown tokens.

• Save the updated dashboard, and try selecting the same time window, and click on one of the status bars, e.g. the 400 status.This screen image shows the events statistics table with 400 status events.

  Try clicking on other status bars, lines and the chart legend to see which statistics are shown in the table. 

Next step is to go on to part 8 where you create an alternative way of comparing hourly rates with the previous few days.