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.
Two time ranges on one graph
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:

http://splunk-base.splunk.com/apps/22314/splunk-for-unix-and-linux

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!

Is this now possible with real time searches? In the past, I have had requests for a real time reports of today v last week. With previous versions of splunk, the only way I could find of doing this was by using a summary search and outputlookup to create a csv. Then use a time based lookup to pull in last weeks data.

February 20, 2012

Cheers Jon, a very useful article that we’ve just put to good use on a dashboard. Just had a look and 4.3.1 hasn’t been officially released yet – thanks for the subsearch note at the end which we needed to get it working.

Steve T
February 20, 2012

There is a litte part of yesterday which is not displayed in your charts?
How could this be fixed?

Jens
February 20, 2012

Hi Bob: You can make the “today” search real-time by using the earliesttime=rt latesttime=rt parameters instead of earliest & latest, but you may need to edit the dashboard in simple XML to set the parameters correctly.

Note that by default, only users with the Admin role can run and save real-time searches. This can be modified in the Manager->Access controls->Roles by granting the “rtsearch” capability.

Here is some documentation that should help:
http://docs.splunk.com/Documentation/Splunk/latest/Developer/RealTimeDashboardIntro
http://docs.splunk.com/Documentation/Splunk/latest/User/RealtimeSearch

Be sure to review the documentation on as new versions of Splunk are released to keep up-to-date on real-time search enhancements.

February 20, 2012

Hi Jen: Regarding your question about “a little bit of yesterday”, I had probably shut down my computer that night. Are you having any problem creating your own charts?

February 21, 2012

Hi Steve: Happy to hear you’ve used this successfully. Thanks for commenting!

February 21, 2012

Great post! I was just working on this type of search with a customer.

David Maislin
February 27, 2012

Hi Jon,

i don’t have any problems while crating the charts but in my and in your charts the yesterday line ends to early. Look in the final report, the yellow line ends before the blue one.

Jens
March 2, 2012

Hi how should I modify my search to make it work?

host=”javaserver1″ source=”/var/log/javastuff.log” earliest=-1d@d latest=-0d@d Sending failed | multikv | eval ReportKey=”yesterday” | append [search host="javaserver1" source="/var/log/javastuff.log" earliest=-0d@d latest=now Sending failed | multikv | eval ReportKey="today"] |timechart span=”1h” count by ReportKey

While today’s results are displayed, yesterday’s results are zero for every hour, then when yesterday’s results begin displaying other values than zero, today’s results are displayed all as zeroes.

Any solution to this?

Thank you

John Mal
March 15, 2012

I am having a similar problem to John Mal’s.

Before modifying the _time parameter, my values for “LastWeek” and “Today” both display correct, albeit next to each other instead of superimposed.

When I modify the _time parameter, it does not superimpose my data. Instead, it simply shifts the viewable timeframe, so that only the “Today” data is displayed.

Here is my search:

sourcetype=”Perfmon:Available Memory” earliest=-0d@d latest=now | multikv | eval ReportKey=”Today” | append [search sourcetype="Perfmon:Available Memory" earliest=-7d@d latest=-6d@d | multikv | eval ReportKey="LastWeek" | eval _time=_time+60*60*24*7] | timechart span=5m avg(Value) by ReportKey

Dan Russo
May 8, 2012

This post is awesome. It completely answered my question, with a thorough walkthrough of the query. Thanks for providing the details.

Of course, now I need to swap in some $param$ stuff and make this a macro, so my coworkers don’t have to go through this same work to build the query.

May 31, 2012

Hi Dan:
First I think you probably don’t need ‘multikv’ for perfmon data.
Second if you are on a pre-4.3.1 version of Splunk, please re-read and retry this section:
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

June 10, 2012

Hi John:
First I think you probably don’t need ‘multikv’ for your data, since you’re not specifying any fields for output. It seems like you just want a count of ‘sending failed’ events per hour.
Try each search alone with the timechart and see that they both work. If you get zeros in any hour, make sure you have ‘sending failed’ events during that hour.
I don’t see you changing the ‘_time’ value anywhere. Perhaps you haven’t gotten to that part yet.

June 10, 2012

Hopefully this will save folks a ton of time: You’ll need that if(isnotnull()) trick even on Splunk 4.3.2 (build 123586). You’ll know you need it if all you get is one full day of data and then a tiny subset of yesterday’s data overlaid on the very end.

dpk
September 6, 2012

If you add this to the end of the search it won’t plot any 0 values:

| eval Today=if(Today == “0″,”",Today) | eval Yesterday=if(Yesterday == “0″,”",Yesterday)

October 26, 2012

I am trying to use the example above and it doesn’t seem to be rendering correctly for me. We are running 4.2.5.

My goal is to have a chart that compared the median time for all saved searches for today and a week ago. When the chart renders I do’t get an overlayed chart.

My query is:

index=_audit earliest=-0d@d latest=now savedsearch_name=*
| eval searchStartTime=strptime(apiStartTime, “‘%a %B %d %H:%M:%S %Y’”)
| eval searchEndTime=strptime(apiEndTime, “‘%a %B %d %H:%M:%S %Y’”)
| eval searchExecuteTime=_time
| eval deltaFromEnd=searchExecuteTime – searchStartTime
| eval ReportKey=”Today”
| append [search index=_audit earliest=-7d@d latest=-6d@d savedsearch_name=*
| eval searchStartTime2=strptime(apiStartTime, "'%a %B %d %H:%M:%S %Y'")
| eval searchEndTime2=strptime(apiEndTime, "'%a %B %d %H:%M:%S %Y'")
| eval searchExecuteTime2=_time
| eval deltaFromEnd=searchExecuteTime2 - searchStartTime2
| eval ReportKey="Last Week"
| eval new_time=_time+60*60*24*7 ]
| eval _time=if(isnotnull(new_time), new_time, _time)
| timechart span=15m median(deltaFromEnd) by ReportKey

Randy
November 14, 2012

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*