Splunk Search

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

Builder

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(datehour) and latest(datehour) for each day in last 7 days.

Tags (5)
1 Solution
Legend

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
``````
Legend

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
``````
Builder

| stats earliest(datehour) as 7dFirstHour latest(datehour) as 7dLastHour - 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.

Legend

Try the UPDATED query.

Builder

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

Legend

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

Builder

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

Builder

| stats earliest(datehour) as 7dFirstHour latest(datehour) as 7dLastHour - 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.

Builder

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

Legend

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

Builder

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

Legend

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