Splunk Search

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

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

0 Karma
1 Solution

sundareshr
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

View solution in original post

0 Karma

sundareshr
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
0 Karma

pavanae
Builder

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

0 Karma

sundareshr
Legend

Try the UPDATED query.

0 Karma

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

0 Karma

sundareshr
Legend

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

0 Karma

pavanae
Builder

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

0 Karma

pavanae
Builder

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

0 Karma

pavanae
Builder

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

0 Karma

sundareshr
Legend

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

0 Karma

pavanae
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

0 Karma

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"
0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...