topic Re: Use timepicker earliest and latest as epoch time in Splunk Search

Use timepicker earliest and latest as epoch time

christoffertoft — Fri, 10 Nov 2017 12:49:56 GMT

I have the same problem as in the link below:

[https://answers.splunk.com/answers/336929/how-can-i-get-time-picker-earliest-and-latest-epoc.html?utm_source=typeahead&utm_medium=newquestion&utm_campaign=no_votes_sort_relev][1]

Where i need the times to be either human-formatted (Y-M-D H:M:S) or epoch (1510317953) .

However when i do a search that relies on the timepicker.earliest and latest values I get an error.
My search query is ... | where _time > $time.earliest$ AND _time < $time.latest$

This gives the Error in 'where' command: The operator at 'd@d AND _time < now ' is invalid.

as we can see the timepicker defaults the 30 days earliest and latest values to -30d@d and now

Speed is kind of of the essence here so I need a way to format the input values from the timepicker or to allow the search to accept the specific formats the timepicker gives me.

Also a random question; why isn't this being done automatically.. Seems very inefficient to keep converting every event between epoch, human readable and other arbitrary splunk time formats.

Re: Use timepicker earliest and latest as epoch time

kamlesh_vaghela — Fri, 10 Nov 2017 13:04:03 GMT

Can you please try this one??

YOUR_SEARCH |  addinfo| where _time > info_min_time AND _time < info_max_time

I have used addinfo command for earliest & latest time. Please check below link for more info.

http://docs.splunk.com/Documentation/Splunk/7.0.0/SearchReference/Addinfo

Thanks

Re: Use timepicker earliest and latest as epoch time

rjthibod — Fri, 10 Nov 2017 13:18:03 GMT

@kamlesh_vaghela's answer should work. Here is another solution that will give you two tokens you can use anywhere.

Basically, update the timepicker XML to include a change element that looks like this:

    <input type="time" token="time">
      ...
      <change>
        <eval token="time.earliest_epoch">if(isnum('earliest'),'earliest',relative_time(now(),'earliest')</eval>
        <eval token="time.latest_epoch">if(isnum('latest'),'latest',relative_time(now(),'latest')</eval>
      </change>
    </input>

That will give you the tokens time.earliest_epoch and time.latest_epoch that you can use for the filtering: ... | where _time > $time.earliest_epoch$ AND _time < $time.latest_epoch$

Re: Use timepicker earliest and latest as epoch time

niketn — Fri, 10 Nov 2017 13:42:02 GMT

@christoffertoft, refer to two options of getting earliest and latest time from Time Picker using dummy search i.e.

1) addinfo and
2) $job.earliestTime$ and $job.latestTime$

https://answers.splunk.com/answers/578984/running-one-of-two-searches-based-on-time-picker-s.html

Re: Use timepicker earliest and latest as epoch time

christoffertoft — Tue, 29 Sep 2020 16:45:28 GMT

This does not work unfortunately. The data i pull using a base search is accelerated and indexed from 0 to +infinity. As such, any events have the infity or 0.0000 values for their mix and max times respectively.

I have gotten the time as |eval _time=report_date

but the events stretch from 0 to infinity. This means i need to be able to use the timepicker to only chose events where the report_date (_time) are in the range selected in the timepicker.

Re: Use timepicker earliest and latest as epoch time

christoffertoft — Fri, 10 Nov 2017 14:05:57 GMT

Hi, and thank you very much for your help. @kamlesh_vaghela's answer does not work (please see my reply above). I will try your approach next to see if it works.

Re: Use timepicker earliest and latest as epoch time

christoffertoft — Fri, 10 Nov 2017 14:06:38 GMT

Hi, and thanks for your reply. This unfortunately does not work. See the comment i posted to @kamlesh_vaghela's answer.

Re: Use timepicker earliest and latest as epoch time

kamlesh_vaghela — Fri, 10 Nov 2017 14:09:35 GMT

HI @christoffertoft,

A missed search in "All Time". Can you please try below search ?

YOUR_SEARCH |  addinfo | where ((NOT (isnum(info_max_time) AND info_max_time!="+Infinity")) OR _time > info_min_time AND _time < info_max_time)

Thanks

Re: Use timepicker earliest and latest as epoch time

christoffertoft — Fri, 10 Nov 2017 14:21:48 GMT

Hi, your approach with converting the values to epoch times seems to have worked. Can you explain the logic behind it?

Re: Use timepicker earliest and latest as epoch time

rjthibod — Fri, 10 Nov 2017 14:53:01 GMT

As of Splunk 6.3, many of the form inputs can be extended to set / unset / eval tokens based on other tokens or their new values. So this answer looks at the new value of the timepicker whenever it changes, and figures out how to convert that value to epoch time. The isnum function in the first condition checks to see if the new time values is already in epoch time form. If it is false, that means the timepicker value is set to a relative time (like -30d@d), the the relative_time function converts that to epoch time.

Re: Use timepicker earliest and latest as epoch time

christoffertoft — Fri, 10 Nov 2017 14:56:01 GMT

Epic, thanks alot. It was the final piece in the puzzle for my dashboard. Credits to you!

Re: Use timepicker earliest and latest as epoch time

niketn — Fri, 10 Nov 2017 15:36:24 GMT

@christoffertoft, the option with job.earliestTime and job.latestTime should have worked. I am not sure if you tried. I was able to display results for All Time using tokens those two as earliest and latest:

Following is the additional code I used to test

  <row>
    <panel>
      <chart>
        <search>
          <query>index=_internal sourcetype=splunkd
| timechart count
        </query>
          <earliest>$tokEarliestTime1$</earliest>
          <latest>$tokLatestTime1$</latest>
        </search>
        <option name="charting.chart">column</option>
      </chart>
    </panel>
  </row>

But no need to stress as you already have an working solution 🙂

Re: Use timepicker earliest and latest as epoch time

andrewtrobec — Mon, 06 Jul 2020 12:57:50 GMT

Simple, elegant, and still valid 3 years later! Thanks 🙂