I have a Splunk search as below:
earliest=-1d@d latest=@d index="abc" sourcetype="def" | stats earliest(date_hour) as FirstHour latest(date_hour) as LastHour by student | eval accessTimerange=FirstHour+" -- "+LastHour | fields - FirstHour - LastHour | table student accessTimerange
Which Displays the results as follows:
student accessTimerange
A123 22--24
B456 02--08
Now, how can I modify my search to Compare last 7 days' average accessTimerange with yesterday's accessTimerange
Hint: average of accessTimerange is average of earliest(date_hour) and latest(date_hour) for each day in last 7 days.
This assumed "yesterday" is included in the last 7 days.
earliest=-7d@d latest=@d index="abc" sourcetype="def" | eval when=if(_time>relative_time(now(), "-1d@d") AND _time<relative_time(now(), "@d"), "Yesterday", "Last 7 days") | stats earliest(date_hour) as 7d_FirstHour latest(date_hour) as 7d_LastHour earliest(eval(if(when="Yesterday", date_hour, null()))) as yesterday_FirstHour latest(eval(if(when="Yesterday", date_hour, null()))) as yesterday_LastHour by student | eval 7d_accessTimerange=7d_FirstHour+" -- "+7d_LastHour | eval Yesterday_accessTimerange=yesterday_FirstHour+" -- "+yesterday_LastHour | fields - FirstHour - LastHour | table student 7d_accessTimerange Yesterday_accessTimerange
*UPDATED*
| eval days=round((now()-_time)/86400, 0) | eval when=if(_time>relative_time(now(), "-1d@d") AND _time<relative_time(now(), "@d"), "Yesterday", "Last 7 days") | stats earliest(_time) as e latest(_time) as l by user days | stats avg(e) as a_e avg(l) as a_l values(eval(if(days=1, e, null()))) as y_e values(eval(if(days=1, l, null()))) as y_l by user | convert ctime(a*) timeformat="%H:%M" | convert ctime(y*) timeformat="%H:%M" | eval 7d=a_e."--".a_l | eval yd=y_e."--".y_l | table user 7d yd
This assumed "yesterday" is included in the last 7 days.
earliest=-7d@d latest=@d index="abc" sourcetype="def" | eval when=if(_time>relative_time(now(), "-1d@d") AND _time<relative_time(now(), "@d"), "Yesterday", "Last 7 days") | stats earliest(date_hour) as 7d_FirstHour latest(date_hour) as 7d_LastHour earliest(eval(if(when="Yesterday", date_hour, null()))) as yesterday_FirstHour latest(eval(if(when="Yesterday", date_hour, null()))) as yesterday_LastHour by student | eval 7d_accessTimerange=7d_FirstHour+" -- "+7d_LastHour | eval Yesterday_accessTimerange=yesterday_FirstHour+" -- "+yesterday_LastHour | fields - FirstHour - LastHour | table student 7d_accessTimerange Yesterday_accessTimerange
*UPDATED*
| eval days=round((now()-_time)/86400, 0) | eval when=if(_time>relative_time(now(), "-1d@d") AND _time<relative_time(now(), "@d"), "Yesterday", "Last 7 days") | stats earliest(_time) as e latest(_time) as l by user days | stats avg(e) as a_e avg(l) as a_l values(eval(if(days=1, e, null()))) as y_e values(eval(if(days=1, l, null()))) as y_l by user | convert ctime(a*) timeformat="%H:%M" | convert ctime(y*) timeformat="%H:%M" | eval 7d=a_e."--".a_l | eval yd=y_e."--".y_l | table user 7d yd
| stats earliest(date_hour) as 7d_FirstHour latest(date_hour) as 7d_LastHour - get the earliest and latest _time values for all data.
If I am correct From the above query it's displaying the earliest and latest time for all the 7 days. But I am looking for the average earliest time and average latest time for all the 7 days. which is average of day 1 to day 7. Please suggest how to modify the query to get like that.
Try the UPDATED query.
Thank you very much. @sundareshr . I would really appriciate if you can also provide me how to display a chart which shows stdev of yesterday's time while compared with the last 7 days average(add stdev to the updated Query. )
Your time table has 2 values - start & end. What is stdev calculated on?
on both. something like if a user start and end time deviation. @sundareshr
| stats earliest(date_hour) as 7d_FirstHour latest(date_hour) as 7d_LastHour - get the earliest and latest _time values for all data.
If I am correct From the above query it's displaying the earliest and latest time for all the 7 days. But I am looking for the average earliest time and average latest time for all the 7 days. which is average of day 1 to day 7. Please suggest how to modify the query to get like that.
haven't seen any results for the field "7d_accessTimerange" could you please check if anything wrong
I updated the query to fix the error. Try it now.
Thank you very much and Could you please explain. what have done in the above search to just understand the query..Also have'nt seen any results under 7d_accessTimerange.. @sundareshr
Here's the breakdown of the SPL
earliest=-7d@d latest=@d index="abc" sourcetype="def" - retrieves last 7 days' data
| eval when=if(_time>relative_time(now(), "-1d@d") AND _time<relative_time(now(), "@d"), "Yesterday", "Last 7 days") - checks the _time value for each event, if its greater than midnight yesterday (relative_time(now(), "-1d@d") AND less than midnight today (relative_time(now, "@d"), then set the value of when to "Yesterday", else sets it to "Last 7 days"
| stats earliest(date_hour) as 7d_FirstHour latest(date_hour) as 7d_LastHour - get the earliest and latest _time values for all data.
earliest(eval(if(when="Yesterday", date_hour, null()))) as yesterday_FirstHour latest(eval(if(when="Yesterday", date_hour, null()))) as yesterday_LastHour by student - gets earliest and latest date_hour values for events where when="Yesterday"