Use timepicker earliest and latest as epoch time

[christoffertoft]

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?ut...]

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.

[niketn]

@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

[christoffertoft]

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

[niketn]

@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 🙂

[rjthibod · Accepted solution]

@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$

[andrewtrobec]

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

[christoffertoft]

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.

[christoffertoft]

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

[rjthibod]

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.

[christoffertoft]

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

[kamlesh_vaghela]

Hi

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

[christoffertoft]

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.

[kamlesh_vaghela]

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