Skip to main content
Splunk Lantern

Trends in database query counts

You might want to visualize how database queries change over time when doing the following:

Prerequisites 

In order to execute this procedure in your environment, the following data, services, or apps are required:

Example

While the number of queries executed against the database varies by hour of day and day of week, you want to monitor for growth in the overall number of calls to the database, either gradual or sudden. 

To optimize the search shown below, you should specify an index and a time range.

  1. Run the following search:
|timechart span=1h count
|`lineartrend(_time,count)`
|rename newY AS lineartrend
|fields _time count lineartrend

Search explanation

The table provides an explanation of what each part of this search achieves. You can adjust this query based on the specifics of your environment.

Splunk Search Explanation

|timechart span=1h count

Graph the count of database queries over time in 1-hour increments.

|`lineartrend(_time,count)`

Run a macro to plot a linear trendline (regression) of the counts. The SPL for this macro is given here, along with an explanation of the function and sample output.

The punctuation surrounding a Splunk macro is always a back tick (`), not a single quote ('). 

|rename newY AS lineartrend

Rename the field as shown for better readability.

|fields _timecount lineartrend

Display only the fields shown.

Result

The results are displayed as a timechart where the query count is plotted over time with a second overlaid line representing the trend. This trendline is the linear regression of the count values and makes it easier to see overall trending of the counts. This is helpful with randomly distributed data or data that is bursty. In those cases, the regression line will clearly show positive, negative, or flat trends in the data. 

Unanticipated growth may occur for several reasons, such as increased demand for the data, addition of new workloads, or changes to the client software due to patching or new releases. If unanticipated growth is detected, review upstream changes between the database and the client to determine if the growth is appropriate.

Another use of the trendline is to correlate query counts with other changes in the environment.  For example, increasing query counts can positively affect other metrics, such as query response time or the query duration, such that response time trends up as query counts go up. You can use the steepness of the trend line to predict when query counts might affect other metrics, such as response time or transaction duration. A simple way to correlate these is to have the related metrics displayed side by side on the same dashboard. 

Since trend data is used to predict the value of a metric at a future time, you can also use the predict command in Splunk SPL:

| timechart span=1h count(query) AS count 
| predict count

By default, the predict command displays a line for the actual query counts, the predicted query counts, and bands that represent the upper and lower 95th percentile predictions. The predict command has many options including different predict algorithms and future timespan length.  Note that the lineartrend macro, fields, and rename commands are not needed with the predict command.

  • Was this article helpful?