Two different earliest values on one dashboard

[dstuder]

I have a time picker on my dashboard called timePicker. There are some pie charts that are populated based on the earliest and latest of the time picker. That part works great.

However, I have on the same dashboard a line graph that shows counts over hour by day for the last 7 days. That earliest and latest is set static like this ...

earliest=-7D@d latest=now

Ideally I would like the line graph to not just show the previous seven days before now, but the previous seven days based on the time picker. The latest part is easy. I could do this ...

earliest=-7D@d latest=$timePicker.latest$

My trouble is with earliest. I need to subtract 604800 from  it (seven days worth of seconds). I tried this, but it doesn't seem to work.

[stats count | eval early=$$timePicker.earliest$$ | eval earliest=early-604800 | fields earliest] latest=$timePicker.latest$

I doubled $ for the earliest token to escape it out because it is used in a dashobard ... I read somewhere that  $ needs to be escaped. Anyway, I get no results. I'm sure my syntax is wrong or maybe I'm going about it wrong. Anyone have any thoughts on how to do this? I guess another thought would be to create a token when the time picker is selected that calculates $timePicker.earliest$ - 604800 but I'm not sure if that is possible or how to do it. Any help would be much appreciated. Thanks.

[ITWhisperer · Accepted solution]

You could set additional tokens in the timepicker change handler - see this solution for similar requirement

https://community.splunk.com/t5/Splunk-Search/Get-the-previous-day-8-9PM-data-based-on-the-date-sele... 

[dstuder]

One question I have. I noticed when I tried to use relative_time with a date range it didn't quite work since the $timePicker.earliest$ was in epoch format. So, I had to use isnum to evaluate if $timePicker.earliest$ was numeric or not. In your example I see that you have not done that. I am curious how the inner relatime_time function would work in a date range scenario vs a relative time scenario. Do, I still need to evaluate that for date ranges?

[ITWhisperer]

It seems to work for me without having to evaluate whether it is numeric or not. Effectively, the inner relative_time() function with now() does the evaluation for you.

[dstuder]

Yeah, it does seem to work ... which is odd. When I do a relative date like Today I see that $timePicker.earliest$ is set to @d. So effectively it is doing this ...

relative_time(relative_time(now(), "@d"), "-7d@d")

I can validate that works like this ...

| stats count
| eval timeTest = relative_time(relative_time(now(), "@d"), "-7d@d")

But I see that when I use a date range the $timePicker.earliest$ is set to an epoch time like 1642492800. Which should effectively be producing this ...

relative_time(relative_time(now(), "1642492800"), "-7d@d")

If I try testing that with this it doesn't work ...

| stats count
| eval timeTest = relative_time(relative_time(now(), "1642492800"), "-7d@d")

But using your solution of this does seem to work.

<change>
        <eval token="daysback7">relative_time(relative_time(now(),$timePicker.earliest$),"-7d@d")</eval>
</change

 I'm not complaining but I am perplexed as to why works.

[ITWhisperer]

Try without the quotes

| stats count
| eval timeTest = relative_time(relative_time(now(), 1642492800), "-7d@d")

[dstuder]

I tried that too. It doesn't work. That's why I'm a bit befuddled.

[dstuder]

My solution worked, but I like yours better. I was looking around for a way to set more tokens on change for the time picker but was not finding it readily. I think this is just a cleaner way to do it. Thanks.

[bowesmana]

A typical way to do this is to have a hidden search (in this example I put it in a visible panel) that will do calculations based on the timepicker value - as you can see from the search it uses addinfo command to get the info_min_time and info_max_time values and then makes tokens based on those so it handles the case where someone creates a strange time range in the picker.

Save this example as a dashboard and you can see that whatever you select in the time picker, the timechart will show a week prior to that also.

<form>
  <label>Time Picker</label>
  <init>
    <set token="earliest">-24h</set>
    <set token="latest">now</set>
  </init>
  <fieldset submitButton="false">
    <input type="time" token="TimePicker">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <title>Simple pie covering $pie_range$</title>
      <chart>
        <search>
          <query>index=_audit
| stats count by user</query>
          <earliest>$TimePicker.earliest$</earliest>
          <latest>$TimePicker.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">pie</option>
        <option name="charting.drilldown">none</option>
      </chart>
    </panel>
    <panel>
      <title>Simple timechart covering $tc_range$</title>
      <chart>
        <search>
          <query>index=_audit
| timechart span=1d count by user</query>
          <earliest>$earliest$</earliest>
          <latest>$TimePicker.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="charting.chart">line</option>
        <option name="charting.drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </chart>
    </panel>
    <panel>
      <title>Calculation panel that determines 7 days prior to TimePicker value</title>
      <table>
        <search>
          <done>
            <set token="earliest">$result.earliest$</set>
            <set token="pie_range">$result.PieRange$</set>
            <set token="tc_range">$result.TCRange$</set>
          </done>
          <query>| makeresults
| addinfo
| eval earliest=info_min_time - 604800
| eval PieRange=strftime(info_min_time, "%F %T")."-".strftime(info_max_time, "%F %T")
| eval TCRange=strftime(earliest, "%F %T")."-".strftime(info_max_time, "%F %T")
| table earliest PieRange TCRange</query>
          <earliest>$TimePicker.earliest$</earliest>
          <latest>$TimePicker.latest$</latest>
        </search>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</form>

[dstuder]

Ok, it looks like this works for date ranges.

[stats count | eval early="$timePicker.earliest$" | eval earliest=early-604800 | fields earliest] latest=$timePicker.latest$

But if I use presets like Today or Yesterday it doesn't work. Thoughts on this?

[dstuder]

I think I got it working. I can test if the earliest value is numeric. If it is I can use that, but if it is a relative date I can convert it to epoch format using the relative_time() function.

[stats count | eval early="$timePicker.earliest$" | eval epoch=if(isnum(early), early, relative_time(now(), early)) | eval earliest=epoch-604800 | fields earliest] latest=$timePicker.latest$