I need to get a count of possible days an event could be happening while excluding weekends and holidays, for example
index=physical date_wday!="saturday" date_wday!="sunday"
| WHERE holiday!=*
| eval day_of_swipe=(date_month + " "+ date_mday + " "+ date_wday )
| stats count(dvc_name) as swipes_per_day by user,day_of_swipe
| eventstats dc(day_of_swipe) as work_days
| eventstats dc(day_of_swipe) as days_swiped by nick
| eval perc_present=round((days_swiped/work_days*100))
eventstats is giving me the total number of days being search and not the excluded, I need to get the total number of possible working days m-f and excluding any holidays are being identified with a lookup table and filtered
Any ideas?
Try this
index=physical date_wday!="saturday" date_wday!="sunday"
| eval Date=strftime(_time,"%d-%m-%Y") | search NOT [| inputlookup holidays.csv | table holiday | rename holiday as Date]
| eval day_of_swipe=(date_month + " "+ date_mday + " "+ date_wday )
| stats count(dvc_name) as swipes_per_day by user,day_of_swipe
| eventstats dc(day_of_swipe) as work_days
| eventstats dc(day_of_swipe) as days_swiped by nick
| eval perc_present=round((days_swiped/work_days*100))
EXCELLENT! Thank you kind sir.
Bonus points if you can solve this one.
I have my results which are correct for a single month span. I would like to run this against the previous year and see the results in 1 month buckets. Can you point me to a solution?
Thank you!!!
Do you have the holiday lookup containing dates of holidays??
I do and it is working correctly.
holidays.csv
holiday,type,holiday_description
25-12-2015,global,christmas
16-02-2015,global,presidents day
transforms.conf
[holidays]
filename = holidays.csv
min_matches = 1
props.conf
EVAL-holiday = strftime(_time,"%d-%m-%Y")
LOOKUP-isaholiday = holidays holiday OUTPUTNEW