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