Splunk Search
Highlighted

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

Path Finder

I need to get a count of possible days an event could be happening while excluding weekends and holidays, for example

index=physical datewday!="saturday" datewday!="sunday"

| WHERE holiday!=*
| eval dayofswipe=(datemonth + " "+ datemday + " "+ datewday )

| stats count(dvc
name) as swipesperday by user,dayofswipe

| eventstats dc(dayofswipe) as workdays
| eventstats dc(day
ofswipe) as daysswiped by nick
| eval percpresent=round((daysswiped/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?

Tags (2)
0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

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

Path Finder

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

0 Karma
Highlighted

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

SplunkTrust
SplunkTrust

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

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

Path Finder

EXCELLENT! Thank you kind sir.

0 Karma
Highlighted

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

Path Finder

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

0 Karma