topic How to dynamically compare two time ranges? in Dashboards & Visualizations

How to dynamically compare two time ranges?

naty — Mon, 14 Nov 2016 17:15:56 GMT

Hi,

i have a dashboard with panels comparing data of historical data and showing it graphically.
i manipulate the _time for all the searches to be in the same time in the panel.
example - i take data from today between 10:00-11:00, and data from yesterday between 10:00-11:00.
then I manipulate the _time of the second search so that in the panel the two graphs will be on the same time frame, but they are originally from different dates

the problem that i am experiencing is that i want to change the time frame dynamically.
i'm doing so using a timepicker.

this is my timepicker:

fieldset submitButton="false"
   input type="time" token="timeField"
      label:/label
      default
        earliest:-1h:earliest
        latest:now:latest
      default
    input
  fieldset

and this is my search:

index=myapp source="mysource" NOT DATETIME earliest=$timeField.earliest$ latest=$timeField.latest$ ID=000 | eval ReportKey="ID0 Today" | append [search index=myapp source="mysource" NOT DATETIME earliest=$timeField.earliest$-86400 latest=$timeField.latest$-1d@m ID=000 | eval ReportKey="ID0 Yesterday" | eval _time=_time+86400| append [search index=myapp source="mysource" NOT DATETIME earliest=$timeField.earliest$-604800 latest=$timeField.latest$-7d@m ID=000 | eval ReportKey="ID0 Last week" | eval _time=_time+7*86400 | append [search index=myapp source="mysource" NOT DATETIME earliest=$timeField.earliest$-2419200 latest=$timeField.latest$-28d@m ID=000 | eval ReportKey="ID0 Last Month" | eval _time=_time+28*86400]]] |  timechart span=1m max(field1) by ReportKey

this search will work perfect if the time i'm picking in the timepicker is a relative time, for example: Last 1 hour/Last 1 day/etc..
but if i'm picking a specific time, for example: 10/13/2016 09:00:00.000 10/13/2016 10:00:00.000 then the search will fail, because i get an epoch time from the second choice.

how can i overcome this?

Thank you!

Re: How to dynamically compare two time ranges?

masonmorales — Mon, 14 Nov 2016 18:07:19 GMT

I haven't seen a good way to do this yet. Most people resort to using the Timewrap TA: https://splunkbase.splunk.com/app/1645/

Re: How to dynamically compare two time ranges?

naty — Tue, 13 Dec 2016 15:54:35 GMT

hi,
thank you for your answer.
sadly, i don't have the ability to change the Splunk infrastructure in our business, we can only use it.
my problem is with the now() - if the latest is 'now' then all of the searches will work.
but, if i'm picking a specific time then i get an EPOCH time, and with EPOCH time i get for example -
"invalid latest time 14756941647-1d@m"

so i need to either change "-1d@m" to EPOCH, or to know when i get "now" or EPOCH for latest time.

Re: How to dynamically compare two time ranges?

sundareshr — Tue, 13 Dec 2016 17:07:43 GMT

Add this to the Timepicker control to always return epoch time

<input type="time" token="time">
...
<change>
<eval token="e">if(isnum($time.earliest$),  relative_time($time.earliest$, "-30d@d), relative_time(relative_time(now(), $time.earliest$), "-30d@d")</eval>
</change>

And for you search, your could try this to avoid the sub-searches (append)

index=foo earliest=$e$ | eval ReportKey=case(_time>relative_time(now(), "@d"), "Today", _time>relative_time(now(), "-1d@d") AND _time<relative_time(now(), "@d"), "Yesterday", _time>relative_time(now(), "-30d@d") AND _time<relative_time(now(), "-29d@d"), "Last Month" | ... rest of your query from any one of the segments

Re: How to dynamically compare two time ranges?

naty — Tue, 13 Dec 2016 17:27:25 GMT

hi, thank you!
i understood the change to the query to avoid the sub-searches, but i didn't understand the you added from the time input.
why -30d@d? you meant 30 days earlier?
can't i just check if time.latest == now?

also, can you please elaborate on what relative_time does and what it returns? the documentation is not very good about this function..

Re: How to dynamically compare two time ranges?

sundareshr — Tue, 13 Dec 2016 18:15:09 GMT

relative_time(x, y) accepts two params. The x represent time value (epoch) and y represents offset from x. For example relative_time(now(), "-1h@h") would mean 1 hour prior to now.

So in query above, -30d represents 30d prior to epoch time selected in the timepicker.