I've created the following query for a range of -12h...+12h with data 1,2,3,4 weeks ago and the data of the last 12h creating a timechart with 10m span and snapping to 10min.
The query works ok with minor caveats such as ignoring summer/winter time changes.
index=x somesearchfields earliest=-684h latest=+12h
| eval interval_sec=600
| eval week1_end = relative_time(now(), "-156h")
| eval week1_start = relative_time(now(), "-180h")
| eval week2_end = relative_time(now(), "-324h")
| eval week2_start = relative_time(now(), "-348h")
| eval week3_end = relative_time(now(), "-492h")
| eval week3_start = relative_time(now(), "-516h")
| eval week4_end = relative_time(now(), "-660h")
| eval today_start = relative_time(now(), "-12h")
| eval dataset = case(
_time < week4_end,"4 weeks ago",
_time > week3_start AND _time < week3_end, "3 weeks ago",
_time > week2_start AND _time < week2_end, "2 weeks ago",
_time > week1_start AND _time < week1_end, "1 week ago",
_time > today_start AND _time<=now(),"last 12h",
1==1, null)
| eval stop=interval_sec*floor(now()+(12*3600)/interval_sec)
| eval start=interval_sec*ceil((now()-(12*3600))/interval_sec)
| eval _time = if(dataset=="1 week ago", _time+(3600*24*7), _time)
| eval _time = if(dataset=="2 weeks ago", _time+(3600*24*14), _time)
| eval _time = if(dataset=="3 weeks ago", _time+(3600*24*21), _time)
| eval _time = if(dataset=="4 weeks ago", _time+(3600*24*28), _time)
| where _time>start AND _time<stop
| timechart span=600s fixedrange=f count by dataset
The Question:
How do I modify the query to get a timechart displaying the following:
- the last 12h of data (as currently showing as 'last 12h')
- the data for the interval a week earlier (as currently showing as '1 week ago')
- the average of the data 1,2,3 and 4 weeks earlier (i.e. avg('1 week ago' + '2 weeks ago' + '3 weeks ago' + '4 weeks ago'))
