topic Re: excluding holidays and weekends a count of days with events and a count of total days excluding weekends and holidays in Splunk Search https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231951#M68817 <P>Do you have the holiday lookup containing dates of holidays??</P> Wed, 13 Jan 2016 21:38:11 GMT somesoni2 2016-01-13T21:38:11Z excluding holidays and weekends a count of days with events and a count of total days excluding weekends and holidays https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231950#M68816 <P>I need to get a count of possible days an event could be happening while excluding weekends and holidays, for example</P> <P>index=physical date_wday!="saturday" date_wday!="sunday"<BR /><BR /> | WHERE holiday!=*<BR /> | eval day_of_swipe=(date_month + " "+ date_mday + " "+ date_wday )<BR /><BR /> | stats count(dvc_name) as swipes_per_day by user,day_of_swipe<BR /><BR /> | eventstats dc(day_of_swipe) as work_days<BR /> | eventstats dc(day_of_swipe) as days_swiped by nick <BR /> | eval perc_present=round((days_swiped/work_days*100))</P> <P>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 </P> <P>Any ideas?</P> Tue, 29 Sep 2020 08:25:33 GMT https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231950#M68816 omgwut56k 2020-09-29T08:25:33Z Re: excluding holidays and weekends a count of days with events and a count of total days excluding weekends and holidays https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231951#M68817 <P>Do you have the holiday lookup containing dates of holidays??</P> Wed, 13 Jan 2016 21:38:11 GMT https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231951#M68817 somesoni2 2016-01-13T21:38:11Z Re: excluding holidays and weekends a count of days with events and a count of total days excluding weekends and holidays https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231952#M68818 <P>I do and it is working correctly.</P> <P>holidays.csv</P> <P>holiday,type,holiday_description<BR /> 25-12-2015,global,christmas<BR /> 16-02-2015,global,presidents day</P> <P>transforms.conf<BR /> [holidays]<BR /> filename = holidays.csv<BR /> min_matches = 1</P> <P>props.conf<BR /> EVAL-holiday = strftime(_time,"%d-%m-%Y")<BR /> LOOKUP-isaholiday = holidays holiday OUTPUTNEW</P> Wed, 13 Jan 2016 22:29:51 GMT https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231952#M68818 omgwut56k 2016-01-13T22:29:51Z Re: excluding holidays and weekends a count of days with events and a count of total days excluding weekends and holidays https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231953#M68819 <P>Try this</P> <PRE><CODE>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)) </CODE></PRE> Wed, 13 Jan 2016 22:51:59 GMT https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231953#M68819 somesoni2 2016-01-13T22:51:59Z Re: excluding holidays and weekends a count of days with events and a count of total days excluding weekends and holidays https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231954#M68820 <P>EXCELLENT! Thank you kind sir.</P> Thu, 14 Jan 2016 14:41:48 GMT https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231954#M68820 omgwut56k 2016-01-14T14:41:48Z Re: excluding holidays and weekends a count of days with events and a count of total days excluding weekends and holidays https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231955#M68821 <P>Bonus points if you can solve this one.</P> <P>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?</P> <P>Thank you!!!</P> Thu, 14 Jan 2016 16:40:44 GMT https://community.splunk.com/t5/Splunk-Search/excluding-holidays-and-weekends-a-count-of-days-with-events-and/m-p/231955#M68821 omgwut56k 2016-01-14T16:40:44Z