How to compare the number of events in an hour of the current day with the average number of events of the same hour of the same day of the week for 6 weeks ago?

[exmuzzy]

I want to show count of events for each hour of the current day in one column, min, max and avg count of events in the same hour same week_day during 4 weeks ago.
How to do this?

[MuS]

Hi exmuzzy,

if Timewrap is not an option for you (maybe it will, after you read this answer 😉 ) try this run everywhere search (if you have the admin role assigned) :

index=_audit earliest=-6w-1d@d latest=-6w-0d@d OR earliest=-1d@d latest=-0d@d 
| timechart span=1h count 
| eval weeks_ago = if(_time > exact(relative_time(now(),"-6w-1d@d")) AND _time <= exact(relative_time(now(),"-6w-0d@d")) , count, "0"), today = if(_time > exact(relative_time(now(),"-1d@d")) AND _time <= exact(relative_time(now(),"-0d@d")) , count, "0") 
| where count!="0" 
| eval hour=strftime(_time, "%H") 
| stats max(today) AS today avg(weeks_ago) AS weeks_ago by hour

What this search does if the following:

index=_audit earliest=-6w-1d@d latest=-6w-0d@d OR earliest=-1d@d latest=-0d@d 

Only gets events for yesterday and the same day 6 weeks before

| timechart span=1h count 

run a timechart on a hourly span to count the events

| eval weeks_ago = if(_time > exact(relative_time(now(),"-6w-1d@d")) AND _time <= exact(relative_time(now(),"-6w-0d@d")) , count, "0"), today = if(_time > exact(relative_time(now(),"-1d@d")) AND _time <= exact(relative_time(now(),"-0d@d")) , count, "0") 

set values based on the time of the events

| where count!="0" 

discard empty events so we can compare them

| eval hour=strftime(_time, "%H") 

create a hour field which finally will be used in

| stats max(today) AS today avg(weeks_ago) AS weeks_ago by hour

a stats to get the two values per hour and as next step you can do the comparison of the values.

Hope this helps ...

cheers, MuS

PS: you could also just use the base search with the two time ranges and use timewrap

[exmuzzy]

Hi, MuS!

Thanks for example!
Would you like to improve you variant to use not all days in -6week to calculate avg for hour but exact hour from -6w, -5w, ... -1w?

My variant is:

index=_audit earliest="-7d@h"
 | timechart count as hits span=1h
 | eval current_day_start=relative_time(now(),"@d"), day = case(_time >= current_day_start,"current", true(), "d".ceiling((current_day_start-_time)/604800*7)), Time = strftime(_time,"%H:%M")
 | stats sum(hits) as hits by day, Time
  | appendpipe [| where like(day,"d%") | stats avg(hits) as hits by Time | eval  day="d_avg", hits=ceiling(hits)]
     | eval {day} = hits
       | stats sum(*) as * by  Time
         | table Time, current, d_avg

[MuS]

sure easy as this:

index=_audit ( earliest=-0d@d latest=+d@d ) OR ( earliest=-1w@-0d@d latest=-1w@+d@d ) OR ( earliest=-2w@-0d@d latest=-2w@+d@d ) 
    OR ( earliest=-3w@-0d@d latest=-3w@+d@d ) OR ( earliest=-4w@-0d@d latest=-4w@+d@d ) 
    OR ( earliest=-5w@-0d@d latest=-5w@+d@d ) OR ( earliest=-6w@-0d@d latest=-6w@+d@d ) 
| timechart span=1h count 
| eval 6weeks_ago = if(_time > exact(relative_time(now(),"-6w@-0d@d")) AND _time <= exact(relative_time(now(),"-6w@+d@d")) , count, "0"), 
    5weeks_ago = if(_time > exact(relative_time(now(),"-5w@-0d@d")) AND _time <= exact(relative_time(now(),"-5w@+d@d")) , count, "0"), 
    4weeks_ago = if(_time > exact(relative_time(now(),"-4w@-0d@d")) AND _time <= exact(relative_time(now(),"-4w@+d@d")) , count, "0"), 
    3weeks_ago = if(_time > exact(relative_time(now(),"-3w@-0d@d")) AND _time <= exact(relative_time(now(),"-3w@+d@d")) , count, "0"), 
    2weeks_ago = if(_time > exact(relative_time(now(),"-2w@-0d@d")) AND _time <= exact(relative_time(now(),"-2w@+d@d")) , count, "0"), 
    1week_ago = if(_time > exact(relative_time(now(),"-1w@-0d@d")) AND _time <= exact(relative_time(now(),"-1w@+d@d")) , count, "0"), 
    today = if(_time > exact(relative_time(now(),"-0d@d")) AND _time <= exact(relative_time(now(),"+d@d")) , count, "0") 
| where count!="0" 
| eval hour=strftime(_time, "%H") 
| stats max(today) AS today avg(1week_ago) AS 1week_ago avg(2weeks_ago) AS 2weeks_ago avg(3weeks_ago) AS 3weeks_ago avg(4weeks_ago)  AS 4weeks_ago avg(5weeks_ago)  AS 5weeks_ago avg(6weeks_ago)  AS 6weeks_ago by hour

After the final stats you can process the results further and compare them.

cheers, MuS

[richgalloway]

Create the chart for today and use the Timewrap app (https://splunkbase.splunk.com/app/1645) to display the same information for 4 weeks ago.

[exmuzzy]

it find values for all hours before current, but I need only the same hour in the same day of week