topic Re: Time Token Parsing to unix epoch in Dashboards & Visualizations

Time Token Parsing to unix epoch

momoXD — Wed, 29 Nov 2017 12:35:38 GMT

I am building a dashboard where I want to overlay data from user choosen time period with another user choosen time period. For example yesterday's average transaction duration compared to today's average transaction duration. As an orientation I use the
Splunk Book( https://www.splunk.com/goto/book) chapter "Charting Week Over Week Results" (pages 85ff.).

So I created a dashboard with two time picker named past and present so the four tokens are accessible using statements like "$past.earliest$". However my code only works if the time tokens are set to a relative value like "1d@d" or "now" and don't work if a user uses a concrete point time (which means a unix-epoch time). Here is my current search:

index=some_index some_field="someValue" earliest=$past.earliest$ latest=$present.latest$
 | eval dur_sec=duration/1000000 
 | eval marker = if ( (_time > relative_time(now(), "$past.earliest$") and _time < relative_time(now(),"$past.latest$")), "past", "today")
 | eval _time = if (marker=="past", _time + relative_time(now(),"$present.earliest$")-relative_time(now(),"$past.earliest$"), _time)
 | timechart span=30min max(dur_sec) by marker 
 | trendline sma5("past") as trend_last_week 
 | eval upperBound=if( isnotnull(trend_last_week), 'trend_last_week'*1.5,'past'*1.5),lowerBound=0 
 | eval isOutlier=if('today'>upperBound or 'today'<lowerBound,1,0)| where _time >= relative_time(now(),"$present.earliest$")| fields _time,"today",lowerBound,upperBound,isOutlier,*

As explained above this only works for relative time spans like "-1d@d" and "now". In simpler words this solution is not flexible enough. I also suspect that there might be an error in the line were I am recalculating the "_time" values, but I'm not sure yet.

Because of these problems, I thought about converting all values from my tokens to Unix Epoch Times with the following code:

 | eval past_earliest=if(isnum(tonumber("$past.earliest$",10)),"$past.earliest$",relative_time(now(),"$past.earliest$"))
    | eval past_latest=if(isnum(tonumber("$past.latest$",10)),"$past.latest$",relative_time(now(),"$past.latest$"))
    | eval present_earliest=if(isnum(tonumber("$present.earliest$",10)),"$present.earliest$",relative_time(now(),"$present.earliest$"))
    | eval present_latest=if(isnum(tonumber("$present.latest$",10)),"$present.latest$",relative_time(now(),"$present.latest$"))

This does not work if one of the tokens contains a non-numeric value. In this case the whole search can't be run. But I guess this code can be fixed but I don't know how.

Maybe somehow can help me complete this workaround or even has an idea fro improvement of the original search.

Any help is appreciated.

Re: Time Token Parsing to unix epoch

somesoni2 — Wed, 29 Nov 2017 15:39:03 GMT

Add these search stanza to your dashboard (no visualization, searches to define tokens) which will get the epoch time ranges from each of the time range picker in new tokens.

<search>
   <query>| makeresults | addinfo | table info_min_time info_max_time</query>
   <!-- use the value of earliest_tok -->
   <earliest>$past.earliest$</earliest>
   <latest>$past.latest$</latest>
   <finalized>        
        <condition>
            <set token="past_earliest">$result.info_min_time$</set>
            <set token="past_latest">$result.info_max_time$</set>
        </condition>
   </finalized>
</search>
<search>
   <query>| makeresults | addinfo | table info_min_time info_max_time</query>
   <!-- use the value of earliest_tok -->
   <earliest>$present.earliest$</earliest>
   <latest>$present.latest$</latest>
   <finalized>        
        <condition>
            <set token="preset_earliest">$result.info_min_time$</set>
            <set token="present_latest">$result.info_max_time$</set>
        </condition>
   </finalized>
</search>

Re: Time Token Parsing to unix epoch

niketn — Wed, 29 Nov 2017 17:06:02 GMT

@momoXD, refer to my answer on two options to get epoch time timepicker control.

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

Once you always get epoch time from Time Picker, you will have to change from relative_time() to direct epoch time difference, so that epoch times i.e. instead of _time > relative_time(now(), "$past.earliest$" use this _time > $past.earliest$

Re: Time Token Parsing to unix epoch

niketn — Wed, 29 Nov 2017 17:08:05 GMT

@somesoni2, I have added the note to my answer that since this method gets epoch earliest and latest time from Time Picker, the SPL should be modified to remove relative_time() and compare _time with epoch earliest or latest time directly.

Re: Time Token Parsing to unix epoch

momoXD — Tue, 29 Sep 2020 16:59:16 GMT

there's a typo in line 20 it say "preset_earliest" but I assume you mean "present_earliest". If this is not a typo please give reasons for changing my naming convention.

Re: Time Token Parsing to unix epoch

niketn — Thu, 30 Nov 2017 08:33:56 GMT

@momoXD, since there are two time inputs, you would need to have four separate tokens.

Re: Time Token Parsing to unix epoch

momoXD — Tue, 29 Sep 2020 16:59:22 GMT

That is totally clear.
So let me rephrase my question why did somesoni2 rename one token to "preset_earliest" which is a synonym for "predefined" and didn't stick with the more meaningful name "present_earliest" where "present" means a point in time.
Did he just forget an "n" or is there more to it?