topic Re: How to edit my search to find the time frame window with the least amount of events? in Splunk Search

How to edit my search to find the time frame window with the least amount of events?

bamalone — Tue, 11 Jul 2017 15:30:15 GMT

Hi there,

I am trying to return the top 3 results of three hour windows where an event is least likely to happen based on the past 30 days during working hours (Monday - Friday 9am - 5pm).

So far I have

event name here
 | eval day_of_week = strftime(_time,"%A")
 | where NOT (day_of_week="Saturday" OR day_of_week="Sunday")
 | bin span=1d _time
 | stats count dc(_time) as days by day_of_week
 | eval average_count = count / days
 | eventstats avg(average_count)
 | sort Average_Count | head 3
 | fields day_of_week, count

I am looking to return something like:
Example: Monday 9am - 12pm, Monday 2pm - 5pm and Friday 2pm - 5pm.

An ideas how to improve my search and return what I am looking for? Cheers

Re: How to edit my search to find the time frame window with the least amount of events?

somesoni2 — Tue, 11 Jul 2017 15:58:38 GMT

Try like this

your base search
| eval day_of_week = strftime(_time,"%A")
| eval hour=strftime(_time,"%H")
| where NOT (day_of_week="Saturday" OR day_of_week="Sunday") AND (hour>=9 AND hour<17)
| eval period=case(hour>=9 AND hour<12,"9 AM to 12 PM",hour>=12 AND hour<14,"12 PM to 2 PM",1=1,"2 PM to 5 PM")
| bin span=1d _time
| stats count dc(_time) as days by day_of_week period
| eval average_count = count/days
| sort 3 average_count
| eval day_of_week, period, count

Re: How to edit my search to find the time frame window with the least amount of events?

bamalone — Wed, 12 Jul 2017 08:44:45 GMT

Thanks so much.

However, I would like to return the top 3 x 3 hour time slots with the least amount of events not specific to line 5 in your suggestion.

Something like this instead:

 your base search
 | eval day_of_week = strftime(_time,"%A")
 | eval time= strftime(_time,"%m/%a")." ".strftime(_time,"%H %p")." - ".strftime(Max,"%H %p") 
 | fieldformat Max=strftime(Max,"%m/%a %H:%M")
 | tstats count latest(_time) as Max WHERE index=_internal BY _time span=3h
 | where NOT (day_of_week="Saturday" OR day_of_week="Sunday") AND (hour>=9 AND hour<17)
 | stats count dc(_time) as days by day_of_week period
 | eval average_count = count/days
 | sort 3 average_count
 | fields day_of_week, period, count

However, the above does not seem to work, can you help me out? Cheers