Two query results overlay on same graph

[ngvella]

Trying to display a timechart with results for a time frame for a certain timespan from today, and then a day in the past, and overlay them on the same graph. I found a few examples and was able to do this when displaying continuous results like today vs yesterday, but in this case i just want to do, for this example, results from today 7am-9am, and 7am-9am 7 days ago.

first search earliest="02/10/2014:07:00:00" latest="02/10/2014:09:00:00"| eval timeVal=seconds+microsec/1000000| eval ReportKey="today" | append [search secondSearch earliest="02/03/2014:07:00:00" latest="02/03/2014:09:00:00"| eval timeVal=seconds+microsec/1000000| eval ReportKey="7 days ago"]| timechart avg(timeVal) as "InsertTime" span=10m by ReportKey

[lguinn2 · Accepted solution]

Try this

firstSearch earliest=-7d@d+7h latest=-7d@d+9h
| eval timeVal=seconds+microsec/1000000
| eval ReportKey="7 days ago" 
| eval _time = _time + (7 * 86400)
| append [search secondSearch earliest=@d+7h latest=@d+9h
     | eval timeVal=seconds+microsec/1000000 | eval ReportKey="Today" ]
| timechart avg(timeVal) as "InsertTime" span=10m by ReportKey

Two things: first, I have more luck with the older time range as the outer search. Also, you need to re-calculate the _time for the older time range so that it aligns with the new time range. | eval _time = _time + (7 * 86400) does that.

Finally, you could do this without the append, which would probably be more efficient - IF the first search and the second search are the same:

yourSearch earliest=-7d@d+7h latest=@d+9h
| eval ReportKey = case (_time <= relative_time(now(),"-7d@d+9h"), "7 days ago",
                         _time >= relative_time(now(),"@d+7h"), "Today",
                         1==1, "skip")
| where ReportKey != "skip"
| eval _time = if(ReportKey=="Today",_time,_time + (7 * 86400))
| eval timeVal=seconds+microsec/1000000
| timechart avg(timeVal) as "InsertTime" span=10m by ReportKey

[carasso]

Comparing week-over-week results used to a pain in Splunk, with complex date calculations. No more. Now there is a better way.

I wrote a convenient search command called "timewrap" that does it all, for arbitrary time periods.

... | timechart count span=1d | timewrap w

That's it!

http://apps.splunk.com/app/1645/

[sowings]

It's the "default" statement for case(). It's a true condition that will always match.

[ngvella]

Works great, thank you. Can you explain to me what this bit is doing though?

1==1