Subtracting time from timepicker

[kishan2356]

Hi

Im trying to create a statistical table on a dashboard, (bucketed into 5 min bins) that tries to do the following.

_time Count today | Count Same Day last week | Avg of last 4 weeks same day

6:00 100 98 75
6:05 23 56 99
6:10 89 45 23
6:15

6:20

I try to find 'count same day last week' by using earliest and latest (-7d@d) the problem is that I need a timepicker to filter the dates. For example today is 10/18/19 but someone wants to see the data for 10/17/19 I want them to be able to use the timepicker and select yesterday, but what happens is that the earliest=-7d@d and latest=-6d@d overrides what gets selected on the timepicker. What should happen is when 10/17/19 is selected "Count today" needs to reflect 10/17/19 and "Count Same Day last week" needs to show data from 10/10/19 , extaclty one week prior. I understand that the earliest and latest method is not compatiable with timepicker. Is there a way to use the timepicker token and subtract from it? For example, if the token for my timepicker is called timeFinder could I do something like $timeFinder$-7d@d? Or $timeFinder.earliest$-7d@d? If anyone has a solution(s) to this I would appreciate it. Thanks

[woodcock]

You simply need to add | addinfo to your search. This will give you info_min_time and info_max_time from your Time picker and then you can do whatever logic you would like to do (probably using the relative_time() and now() functions).

[aaronbarry73]

Hi @kishan2356, this code worked for me when I pasted it into an empty dashboard. It is assumed that the user enters relative time modifiers for earliest and latest times. Additional code would have to be added to account for different ways the user might use the time input, such as epoch time values, use of "now", etc. This uses a subsearch to pull last weeks data. So the search uses the time input values and the subsearch uses modified time input values.

<form>
  <label></label>
  <fieldset submitButton="false">
    <input type="time" token="field1">
      <label></label>
      <default>
        <earliest>-24h@h</earliest>
        <latest>now</latest>
      </default>
    </input>
  </fieldset>
  <row>
    <panel>
      <table>
        <search>
          <query>index=some_index | stats count | addinfo | eval info_min_time_string=strftime(info_min_time,"%Y-%m-%d %H:%M:%S") | eval info_max_time_string=strftime(info_max_time,"%Y-%m-%d %H:%M:%S") | eval time_input_min_time_string=strftime(relative_time(now(),$field1.earliest|s$),"%Y-%m-%d %H:%M:%S") | fields time_input_min_time_string info_min_time_string info_max_time_string count
| append [search index=some_index earliest=$field1.earliest$-7d latest=$field1.latest$-7d | stats count | addinfo | eval info_min_time_string=strftime(info_min_time,"%Y-%m-%d %H:%M:%S") | eval info_max_time_string=strftime(info_max_time,"%Y-%m-%d %H:%M:%S") | eval time_input_min_time_string=strftime(relative_time(now(),$field1.earliest|s$),"%Y-%m-%d %H:%M:%S") | fields time_input_min_time_string info_min_time_string info_max_time_string count]</query>
          <earliest>$field1.earliest$</earliest>
          <latest>$field1.latest$</latest>
          <sampleRatio>1</sampleRatio>
        </search>
        <option name="drilldown">none</option>
        <option name="refresh.display">progressbar</option>
      </table>
    </panel>
  </row>
</form>

[kishan2356]

This did not work for me. Is there a way to get this to work with Time (+Add input) instead of text? I would like the user(s) to be able to select from the Presets and Date Range.