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.
You could set additional tokens in the timepicker change handler - see this solution for similar requirement
You could set additional tokens in the timepicker change handler - see this solution for similar requirement
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?
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.
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.
Try without the quotes
| stats count
| eval timeTest = relative_time(relative_time(now(), 1642492800), "-7d@d")
I tried that too. It doesn't work. That's why I'm a bit befuddled.
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.
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>
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?
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$