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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...