Add sparklines to search results

If you are working with stats and chart searches, you can increase their usefulness and overall information density by adding sparklines to their result tables. Sparklines are inline charts that appear within table cells in search results, and are designed to display time-based trends associated with the primary key of each row.

The size of the sparkline is defined by settings in the limits.conf file. The sparkline_maxsize setting defines the maximum number of elements to emit for a sparkline.

For example, say you have this search, set to run over events from the Last 15 minutes:

index=_internal | chart count by sourcetype

This search returns a two-column results table that shows event counts for the source types that have been indexed to _internal in the last 15 minutes. The first column lists each sourcetype found in the past hour's set of _internal index events; this is the primary key for the table. The second column, count, displays the event counts for each listed source type:

You can add sparklines to the results of this search by adding the sparkline function to the search itself:

index=_internal | chart sparkline count by sourcetype

This results in a table that is almost the same as the preceding one, except that now, for each row you have a sparkline chart that shows the event count trend for each listed source type over the past 15 minutes.

Now you can easily see patterns in your data that may have been invisible before. Some search activity apparently caused a bump in most index=_internal source types about three quarters into the 15 minute span. And splunkd has what almost looks like a regular heartbeat running over the entire span of time.

Each sparkline in a table displays information in relation to the other events represented in that sparkline, but not in relation to the other sparklines. A peak in one sparkline does not necessarily have the same value as a peak in another sparkline.

Using sparklines with the stats and chart commands

You always use the sparklines feature in conjunction with chart and stats searches, because it is a function of those two search commands. It is not a command by itself. The functionality of sparklines is the same for both search commands.

Sparklines are not available as a dashboard chart visualization by themselves, but you can set up a dashboard panel with a table visualization that displays sparklines. For more information, see the "Visualization reference" topic in the Splunk Data Visualizations Manual.

For more information about the chart and stats commands, including details on the syntax around the sparkline function, see chart and stats in the Search Reference.

Example: Stats, sparklines, and earthquake data

Here are some examples of stats searches that use sparklines to provide additional information about earthquake data.

This example uses recent earthquake data downloaded from the USGS Earthquakes website. The data is a comma separated ASCII text file that contains magnitude (mag), coordinates (latitude, longitude), region (place), etc., for each earthquake recorded. You can download a current CSV file from the USGS Earthquake Feeds and add it as an input.

Let's say you want to use the USGS Earthquakes data to show the locations that had the most earthquakes over the past month, a column that shows the average quake magnitude for each location. You could use the following search:

source="all_month.csv" | stats sparkline count, avg(mag) by locationSource | sort count

This search returns the following table, with sparklines that illustrate the quake count over the course of the month for each of the top earthquake locations:

Right away you can see differences in quake distribution between the different locations.

You can click on a sourceLocation to see the actual events that are included in the location calculations.

For the avg(mag) column, you can use the Format icon to change the number formatting in that column.

You can easily get the minimum and maximum count for a particular region by mousing over the sparkline; in this example you can see that in Southern Alaska, the minimum count of quakes experienced in a single day during the 7-day period was 1, while the maximum count per day was 6.

But what if you want your sparkline to represent not only the earthquake count, but also the relative average magnitude of the quakes affecting each region? In other words, how can you make the sparkline line chart represent average quake magnitude for each "time bucket" (segment) of the chart?

Try a search like this:

source="all_month.csv"| stats sparkline(avg(mag),6h) as magnitude_trend count, avg(mag) by locationSource | sort - count

This search produces a sparkline for each region that shows the average quake magnitude for the quake events that fall into each segment of the sparkline. By specifying a dash ( - ) after sort, the results are sorted in descending order.

But it does a bit more than that. It also asks that the sparkline divide itself up into smaller chunks of time. The preceding table had a sparkline that was divided up by day, so each data point in the sparkline represented an event count for a full 24 hour period. This is why those sparklines were so short.

The addition of the 6h to the search language overrides this default and displays sparklines that are broken up into discrete six-hour chunks, which makes it easier to see the distribution of events along the sparkline for the chosen time range.

The search also renames the sparkline column as magnitude_trend to make it easier to understand.

Now you can see that the quakes for the nc location are spread out more evenly than the previous search suggested.