topic Re: Two different earliest values on one dashboard in Dashboards & Visualizations

Two different earliest values on one dashboard

dstuder — Mon, 17 Jan 2022 19:04:14 GMT

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.

Re: Two different earliest values on one dashboard

dstuder — Mon, 17 Jan 2022 22:29:04 GMT

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?

Re: Two different earliest values on one dashboard

dstuder — Mon, 17 Jan 2022 23:56:16 GMT

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$

Re: Two different earliest values on one dashboard

bowesmana — Tue, 18 Jan 2022 00:09:50 GMT

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>

Re: Two different earliest values on one dashboard

ITWhisperer — Tue, 18 Jan 2022 06:23:51 GMT

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-selected-in/m-p/556978/highlight/true?search-action-id=62999991367&search-result-uid=556978#M158182

Re: Two different earliest values on one dashboard

dstuder — Tue, 18 Jan 2022 17:17:47 GMT

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.

Re: Two different earliest values on one dashboard

dstuder — Tue, 18 Jan 2022 17:29:45 GMT

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?

Re: Two different earliest values on one dashboard

ITWhisperer — Tue, 18 Jan 2022 18:03:14 GMT

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.

Re: Two different earliest values on one dashboard

dstuder — Tue, 18 Jan 2022 18:19:29 GMT

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.

Re: Two different earliest values on one dashboard

ITWhisperer — Tue, 18 Jan 2022 18:25:18 GMT

Try without the quotes

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

Re: Two different earliest values on one dashboard

dstuder — Tue, 18 Jan 2022 18:32:34 GMT

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