Compare Two Time Ranges in One Report

Recently a customer asked me how to show current data vs. historical data in a single report.  It looks like the report below, where one line represents today’s data and another represents yesterday’s data.  This makes it easy to see the relationship between data over any two time ranges.

Here is the basic structure of the two time range search, today vs. yesterday:

Search for stuff yesterday | eval ReportKey=”Yesterday” | modify the “_time” field | append [subsearch for stuff today | eval ReportKey=”Today”] | timechart

If you’re not familiar with the “eval”, “timechart”, and “append” commands used above, and the subsearch syntax, here are links to these commands and their associated functions in Splunk’s online documentation:

Eval Command             Timechart Command             Append Command
Eval Functions               Timechart Functions             Subsearch

The trick to showing two time ranges on one report is to edit the Splunk “_time” field.  Before we continue, take a look at the Splunk documentation on time:

This is the main page: Time modifiers for search

Or go right to the examples on this page: Examples of relative time modifiers

Now let’s build one.  Let’s say that I want to see the five minute average CPU utilization on my system for yesterday, and compare it directly with today’s averages.  My examples use CPU data from the “*NIX” App available on Splunkbase:

https://splunkbase.splunk.com/app/273/

First we write a search to show data from one time range.  We can use the time picker to choose a time range, or type the relative time modifiers directly in the search bar.  Please note that you may not be able to copy and paste these searches into Splunk due to the different double-quotes characters in this document.

This search shows all the CPU pctIdle events for today.  I’m using pctIdle because my utilization is so low that it wouldn’t show up well in the graphs. We’ll add the “multikv” command because the CPU data is columnar, and multikv transforms the column names into field names.  “latest=now” is the default if no “latest” is specified, so you don’t have to include it, but for any time ranges that don’t end “now” you will need to specify a “latest” field

index=”os” sourcetype=”cpu” earliest=-0d@d latest=now | multikv

Now we want to see all the CPU pctIdle events for yesterday, so we use this search:

index=”os” sourcetype=”cpu” earliest=-1d@d latest=-0d@d | multikv

One way Splunk can combine multiple searches at one time is with the “append” command and a subsearch.  The syntax looks like this:

search1 | append [search2]

The search is now:

index=”os” sourcetype=”cpu” earliest=-0d@d latest=now | multikv | append [search index=”os” sourcetype=”cpu” earliest=-1d@d latest=-0d@d | multikv ]

Notice the “search” command just after the left bracket “[” character.  Whenever you include additional searches after a pipe “|” or subsearch bracket “[” you must include the “search” command or you will receive an error.

If you look at your list of events now you’ll see that you’re getting two days of data, beginning with yesterday at 12:00AM and ending “now”.

Next we need to create a way to identify the two different time ranges when we display them on our report.  To do this we’ll create a new field called “ReportKey” using the “eval” command.  This will give us titles to group by in the Report.  You can use any field name you like.  “ReportKey” is not a special field name in Splunk.

index=”os” sourcetype=”cpu” earliest=-0d@d latest=now | multikv | eval ReportKey=”today” | append [search index=”os” sourcetype=”cpu” earliest=-1d@d latest=-0d@d | multikv | eval ReportKey=”yesterday”]

To show the data as a line graph, we need to use the “timechart” command.  Updating our search to show the average CPU pctIdle per five minutes:

index=”os” sourcetype=”cpu” earliest=-0d@d latest=now | multikv | eval ReportKey=”today” | append [search index=”os” sourcetype=”cpu” earliest=-1d@d latest=-0d@d | multikv | eval ReportKey=”yesterday”] | timechart span=5m avg(pctIdle) by ReportKey

Now we see the data from yesterday and today, and each graph line is separate.

In order to see the lines together, one superimposed on the other, we need to edit the special Splunk field of “_time”.  “_time” is the event’s timestamp field, which controls how event data is shown in the Splunk Timeline as well as in Splunk reports.  If we change the _time field for yesterday’s events by adding twenty-four hours to it, yesterday’s events will show up on our report as if they happened today.

index=”os” sourcetype=”cpu” earliest=-0d@d latest=now | multikv | eval ReportKey=”today” | append [search index=”os” sourcetype=”cpu” earliest=-1d@d latest=-0d@d | multikv | eval ReportKey=”yesterday” | eval _time=_time+86400] | timechart span=5m avg(pctIdle) by ReportKey

86400 is the number of seconds in a day.  Rather than calculating, you can also use math in the “eval” command.  “eval _time=_time+60*60*24”  (60 seconds times 60 equals an hour, times 24 hours equals a day) would work just as well.

Now we have our final report:

If we wanted to see today’s data compared to the same day last week, just modify the second search’s time range, ReportKey, and the _time modifier:

index=”os” sourcetype=”cpu” earliest=-0d@d latest=now | multikv | eval ReportKey=”today” | append [search index=”os” sourcetype=”cpu” earliest=-7d@d latest=-6d@d | multikv | eval ReportKey=”same day last week” | eval _time=_time+60*60*24*7] | timechart span=5m avg(pctIdle) by ReportKey

Note: There is a quirk in some versions of Splunk prior to 4.3.1 that when you modify the _time field in a subsearch it may not return all the events you expect from the subsearch. In this case you can create a new field in the subsearch to hold the _time value, and then modify the _time field after the subsearch with an eval. The “If” and “isnotnull” functions below are documented on the Eval Functions page. The changes from the final version above are highlighted below:

index=”os” sourcetype=”cpu” earliest=-15d@d latest=-14d@d | multikv | eval ReportKey=”today” | append [search index=”os” sourcetype=”cpu” earliest=-16d@d latest=-15d@d | multikv | eval ReportKey=”yesterday” | eval new_time=_time+86400] | eval _time=if(isnotnull(new_time), new_time, _time) | timechart span=1h avg(pctIdle) by ReportKey

I hope this was helpful!  If you have any questions please post a comment.

Update: For those of you who are familiar with David Carasso’s excellent book: Exploring Splunk, there is an elegant example of performing this type of search without a subsearch, which avoids hitting the Splunk limitation of how many events can be returned by a subsearch.

His example is in Chapter 6: Recipes for Monitoring and Alerting, page 85, titled: Charting Week Over Week Results.

More information on Exploring Splunk is available at: http://www.splunk.com/goto/book

Happy Splunking!

----------------------------------------------------
Thanks!
Jon Webster