How to compare the last 7 days' average with yesterday's average?

[pavanae]

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.

[sundareshr · Accepted solution]

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

[pavanae]

| 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.

[sundareshr]

Try the UPDATED query.

[pavanae]

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. )

[sundareshr]

Your time table has 2 values - start & end. What is stdev calculated on?

[pavanae]

on both. something like if a user start and end time deviation. @sundareshr

[pavanae]

| 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.

[pavanae]

haven't seen any results for the field "7d_accessTimerange" could you please check if anything wrong

[sundareshr]

I updated the query to fix the error. Try it now.

[pavanae]

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

[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"