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?
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
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
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
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.
it find values for all hours before current, but I need only the same hour in the same day of week