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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...