Splunk Search

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
Explorer

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?

Tags (1)
0 Karma

MuS
SplunkTrust
SplunkTrust

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
Explorer

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

MuS
SplunkTrust
SplunkTrust

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

0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

exmuzzy
Explorer

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

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