excluding holidays and weekends a count of days with events and a count of total days excluding weekends and holidays

[omgwut56k]

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?

[somesoni2]

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

[omgwut56k]

EXCELLENT! Thank you kind sir.

[omgwut56k]

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

[somesoni2]

Do you have the holiday lookup containing dates of holidays??

[omgwut56k]

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