We need to get the previous week's results as a second set of results based on the time picker used for current time spans chosen, where last week's earliest and latest is -1w from both.
I have a dashboard that shows, by default, the past 60 minutes of data. It also has, as a separate table joined to it, an average of the past 6 weeks for the time chosen. For example, if today is Thursday, 10:15 am, and it defaults to the past 60 minutes, then it pulls records from current 9:15 to 10:15, but also the 9:15 to 10:15 time frame of the past 6 Thursdays, so I get the historical average to compare to the current average for the same weekday same timespan of the same weekdays.
This all works, but only if I hard code the 60 minutes. i need a time picker so you can choose, for example, the past 30 minutes specific times like from 8:30 to 9:45, or whenever, so that the timespan changes based on the time input dropdown.
My problem is that I need to replicate this time span to the previous 6 weeks so it compares equivalently the week over week averages. For example, I can't compare the previous 2 hours with the 6 week average of only 1 hour. That wouldn't make sense. If the user chooses the past 4 hours of data, then the 6 week average must also pick the same 4 hours of data, but for their respective weeks.
My time dropdown creates 2 sets of earliest and latest, one of them in UNIX time, because i thought it might be easier to do the math.
i've tried this to get just the first previous week.
earliest=relative_time($time.earliest_epoch$, "-1w") latest=relative_time($time.latest_epoch$, "-1w")
But I get an error: invalid value "relative_time" for time term "earliest".
How do i accomplish this?
The way to accomplish this without a search is to create the tokens at the time the input dropdown is updated.
the answer here set the stage:
https://answers.splunk.com/answers/590512/use-timepicker-earliest-and-latest-as-epoch-time.html
rather than doing the math inside the search query, have that completed and saved as separate tokens. there would be more tokens this way, but it works.
<change>
<eval token="time.earliest_epoch">if(isnum('earliest'),'earliest',relative_time(now(),'earliest'))</eval>
<eval token="time.latest_epoch">if(isnum('latest'),'latest',relative_time(now(),'latest'))</eval>
<eval token="time.earliest_epoch_week1">if(isnum('earliest'),'earliest'-604800,relative_time(relative_time(now(),'earliest'),"-1w"))</eval>
<eval token="time.latest_epoch_week1">if(isnum('latest'),'latest'-604800,relative_time(relative_time(now(),'latest'),"-1w"))</eval>
<eval token="time.earliest_epoch_week2">if(isnum('earliest'),'earliest'-1209600,relative_time(relative_time(now(),'earliest'),"-2w"))</eval>
<eval token="time.latest_epoch_week2">if(isnum('latest'),'latest'-1209600,relative_time(relative_time(now(),'latest'),"-2w"))</eval>
<eval token="time.earliest_epoch_week3">if(isnum('earliest'),'earliest'-1814400,relative_time(relative_time(now(),'earliest'),"-3w"))</eval>
<eval token="time.latest_epoch_week3">if(isnum('latest'),'latest'-1814400,relative_time(relative_time(now(),'latest'),"-3w"))</eval>
<eval token="time.earliest_epoch_week4">if(isnum('earliest'),'earliest'-2419200,relative_time(relative_time(now(),'earliest'),"-4w"))</eval>
<eval token="time.latest_epoch_week4">if(isnum('latest'),'latest'-2419200,relative_time(relative_time(now(),'latest'),"-4w"))</eval>
<eval token="time.earliest_epoch_week5">if(isnum('earliest'),'earliest'-3024000,relative_time(relative_time(now(),'earliest'),"-5w"))</eval>
<eval token="time.latest_epoch_week5">if(isnum('latest'),'latest'-3024000,relative_time(relative_time(now(),'latest'),"-5w"))</eval>
<eval token="time.earliest_epoch_week6">if(isnum('earliest'),'earliest'-3628800,relative_time(relative_time(now(),'earliest'),"-6w"))</eval>
<eval token="time.latest_epoch_week6">if(isnum('latest'),'latest'-3628800,relative_time(relative_time(now(),'latest'),"-6w"))</eval>
</change>
then you can do
earliest=$time.earliest_epoch_week1$
The way to accomplish this without a search is to create the tokens at the time the input dropdown is updated.
the answer here set the stage:
https://answers.splunk.com/answers/590512/use-timepicker-earliest-and-latest-as-epoch-time.html
rather than doing the math inside the search query, have that completed and saved as separate tokens. there would be more tokens this way, but it works.
<change>
<eval token="time.earliest_epoch">if(isnum('earliest'),'earliest',relative_time(now(),'earliest'))</eval>
<eval token="time.latest_epoch">if(isnum('latest'),'latest',relative_time(now(),'latest'))</eval>
<eval token="time.earliest_epoch_week1">if(isnum('earliest'),'earliest'-604800,relative_time(relative_time(now(),'earliest'),"-1w"))</eval>
<eval token="time.latest_epoch_week1">if(isnum('latest'),'latest'-604800,relative_time(relative_time(now(),'latest'),"-1w"))</eval>
<eval token="time.earliest_epoch_week2">if(isnum('earliest'),'earliest'-1209600,relative_time(relative_time(now(),'earliest'),"-2w"))</eval>
<eval token="time.latest_epoch_week2">if(isnum('latest'),'latest'-1209600,relative_time(relative_time(now(),'latest'),"-2w"))</eval>
<eval token="time.earliest_epoch_week3">if(isnum('earliest'),'earliest'-1814400,relative_time(relative_time(now(),'earliest'),"-3w"))</eval>
<eval token="time.latest_epoch_week3">if(isnum('latest'),'latest'-1814400,relative_time(relative_time(now(),'latest'),"-3w"))</eval>
<eval token="time.earliest_epoch_week4">if(isnum('earliest'),'earliest'-2419200,relative_time(relative_time(now(),'earliest'),"-4w"))</eval>
<eval token="time.latest_epoch_week4">if(isnum('latest'),'latest'-2419200,relative_time(relative_time(now(),'latest'),"-4w"))</eval>
<eval token="time.earliest_epoch_week5">if(isnum('earliest'),'earliest'-3024000,relative_time(relative_time(now(),'earliest'),"-5w"))</eval>
<eval token="time.latest_epoch_week5">if(isnum('latest'),'latest'-3024000,relative_time(relative_time(now(),'latest'),"-5w"))</eval>
<eval token="time.earliest_epoch_week6">if(isnum('earliest'),'earliest'-3628800,relative_time(relative_time(now(),'earliest'),"-6w"))</eval>
<eval token="time.latest_epoch_week6">if(isnum('latest'),'latest'-3628800,relative_time(relative_time(now(),'latest'),"-6w"))</eval>
</change>
then you can do
earliest=$time.earliest_epoch_week1$
Keep in mind that you can't call a relative_time in a base search, it's only for things such as evals. So, you are probably better off using relative time in a separate search that then sets that epoch value to a token, to which your earliest= and latest= values use to compare against.
thanks, i looked up the docs and they do indeed state.
https://docs.splunk.com/Documentation/Splunk/7.2.3/SearchReference/DateandTimeFunctions
does this really have to be done in a whole other search? all i need is the calculation, not a search. the query cannot perform that well with all that i'm tasking it to do, so i can't afford to broaden my search and then narrow down later.