Splunk Search

Two query results overlay on same graph

ngvella
Explorer

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
Tags (2)
1 Solution

lguinn2
Legend

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

View solution in original post

lguinn2
Legend

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
Splunk Employee
Splunk Employee

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
Splunk Employee
Splunk Employee

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

0 Karma

ngvella
Explorer

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

1==1

0 Karma
Get Updates on the Splunk Community!

Optimize Cloud Monitoring

  TECH TALKS Optimize Cloud Monitoring Tuesday, August 13, 2024  |  11:00AM–12:00PM PST   Register to ...

What's New in Splunk Cloud Platform 9.2.2403?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.2.2403! Analysts can ...

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...