Timepicker relative time in dashboard.

[jacobdavis]

Hi all!

What I thought was going to be a fairly simple panel on a dashboard has been giving me fits.  We have a global time picker (Datepkr) for our dashboard, and based on other picker selections from that dashboard would like to display a simple count of events in a timechart for the time window selected by the datepicker, and for the same time window the week prior.  So if someone selected events for the past 4 hours, we would get a line chart of events for the past four hours with a second line of events for events of the last four hours exactly one week prior.  Same deal if someone selected events in the time range Wednesday, t-18 16:00 through Thursday, Oct-19 12:00, they would get events for that range plus a second line for events Wednesday, Oct-11 16:00 through Thursday, Oct-12 12:00.  I think it would get a bit weird as you start selecting increasingly large windows of time larger than one week, but that's ok, for the most part people will be using times less than one week.

I've run into two hurdles so far, one is how to get the second "-7d" time range to be created from the time picker, and then once the two searches can be made, how to effectively merge the two together.

I saw a few posts mentioning using makeresults or addinfo and info_min_time/info_max_time but these don't seem to be resolving correctly (the way I was using them at least), and setting the last week time in the body of the query seems wrong, or at least less useful than having it resolved somewhere that it could be used on other panels.

I tried to add two new tokens to set the past window, but because the time picker can produce times in varying formats this didn't seem to work.  I tried different ways of converting to epoch time and back but didn't get anywhere with that either.

Timepicker config including the eval:

<input type="time" token="Datepkr">
  <label>Time Range Picker</label>
  <default>
    <earliest>-15m</earliest>
    <latest>now</latest>
  </default>
  <change>
    <eval token="date_last_week.earliest">relative_time($Datepkr.earliest$, "-7d")</eval>
    <eval token="date_last_week.latest">relative_time($Datepkr.latest$, "-7d")</eval>
  </change>
</input>

I haven't been able to get as far as to get a search that produces the right results, but assuming I can, I'm not sure how to overlay two the times on top of each other since they are different time ranges.  Wouldn't they display end to end?  I'd like them to overlay.

I saw the timewrap function, but given that a time field is required timewrap as well as a time-span for the chart I don't think that would mesh with the time picker.

Maybe something like:

 Search for stuff from -7d | eval ReportKey=”Last_Week” | modify the “_time” field | append [subsearch for stuff today | eval ReportKey=”Today”] | timechart it based on ReportKey

Thanks in advance for any help!

[bowesmana · Accepted solution]

I've posted a number of solutions for this problem, see a post from yesterday that references some of those

https://community.splunk.com/t5/Splunk-Search/Multiple-time-searches/m-p/669128#M229514

Effectively you have a global search that sees your Datepkr token and does a small search to calculate the relative dates - it needs addinfo, as that makes sure the tokens from the time picker are converted to epoch.

Then in your main search you can do

search (earliest=$Datepkr.earliest$ latest=$Datepkr.latest$) OR (earliest=$my_other_token_earliest$ latest=$my_other_token_latest$)
...
| eval category=if(_time <= $my_other_token_latest$, "PREV", "CURRENT")
| eval _time=if(_time <= $my_other_token_latest$, _time+my_offset, _time)
...
| timechart bla by category

which looks for both date ranges and then sets the category based on which range it's from, and then adjusts the PREV range _time to the current time, so they are overlaid. - my_offset is the amount of time between your two ranges.

This methodology works, so if you're struggling to get something working, post what you've got and we can help.

[jacobdavis]

Thank you so much for the help!  Once I was able to wrap my head around it and do some tinkering your solution worked perfectly!

Here is what I ended up with for the global search:

 <search>
    <query>
      | makeresults
      | addinfo
      | eval last_week_earliest=relative_time(info_min_time,"-7d") 
      | eval last_week_latest=relative_time(info_max_time,"-7d")
  </query>
    <earliest>$Datepkr.earliest$</earliest>
    <latest>$Datepkr.latest$</latest>
    <done>
      <set token="last_week_earliest">$result.last_week_earliest$</set>
      <set token="last_week_latest">$result.last_week_latest$</set>
      <eval token="time_span">($result.last_week_latest$ - $result.last_week_earliest$)/60</eval>
      <eval token="span_value">round($time_span$,0)</eval>
    </done>
  </search>

And here is what is in the main search:

<search>
          <query> index=*apievents* request.org_name=$org$ request.env=$env$ request.api_name=$api$ (earliest=$Datepkr.earliest$ latest=$Datepkr.latest$) OR (earliest=$last_week_earliest$ latest=$last_week_latest$) 
          | eval category=if(_time &lt;= $last_week_latest$, "Last Week Volume", "Current Week Volume")
          | eval _time=if(_time &lt;= $last_week_latest$, _time+(7 * 86400), _time)
          | timechart cont=f span=$span_value$s count by category
          </query>
        </search>

I have it in a place now where it works, and looks like I want it to look, I'm just not sure if there was a much easier path to setting the chart beginning / end time and span fields.

Initially I didn't have the time_span and span_value tokens and just tried to let the timechart function do its thing automated.  It still kept the full time range of seven days when displaying, so all of the timeshifted events were displaying on the seventh (i.e. current) day. 

When I added the cont=f setting things got a bit better, but the chart was displaying in a way that the span field looks like it was still stuck on what it would have been if it were set to a seven day range.  I set it manually to be 1/60 of whatever the user selected time range is in seconds.  That seems to approximate the default behavior of timechart, which looks like it does anywhere from 1/48 to 1/60 depending on what will divide evenly.

If there's a simpler solution to that I'd love to know what it is, but like I said what I have there seems to work perfectly for any time range.

Thanks again for the help @bowesmana !

[bowesmana]

@jacobdavis You've picked it up well. This is how things are done in XML. Have you used fixedrange=f in the timechart - it's similar to cont, but makes timechart trim the empt stuff at either end of the time ranges.