topic Re: Search to only include Business Hours and Exclude weekends in Splunk Search

Search to only include Business Hours and Exclude weekends

wellsajs — Mon, 28 Sep 2020 12:59:42 GMT

Hi All

I am trying to generate a search that only includes Business hours and also excludes weekends.
I have tried any combinations and still cannot get it working.

I have tried the below which I thought would work but it did not give me the results I wanted

((date_hour<=18 AND date_hour>=6) OR (date_wday!="sunday" AND date_wday!="saturday"))

Cheers for any help
J

Re: Search to only include Business Hours and Exclude weekends

GKC — Mon, 28 Sep 2020 12:59:47 GMT

The best way to create your search strings is step by step.
in the search string the AND is always implied unless you say something different, so no need for that.

Try this first without any date_hour restriction:
date_wday!="sunday" date_wday!="saturday"

You will see it will work because you are telling Splunk to search all the fields but the ones that have the value Sunday or Saturday

For the time restriction, I'd recommend you used the eval function. http://docs.splunk.com/Documentation/Splunk/5.0/searchreference/eval

anyhow, you can always have this working

date_hour>=6 date_hour<=18 date_wday!="sunday" date_wday!="saturday"

Re: Search to only include Business Hours and Exclude weekends

wellsajs — Mon, 28 Sep 2020 12:59:50 GMT

When I run the below

date_hour>=6 date_hour<=18 date_wday!="sunday" date_wday!="saturday"

I get nothing as Splunk tells me "No matching events found" my timepicker is the last 7 days and with out this filter i get thousands of events over each of the last 7 days.

So not sure why that is not working

Cheers
J

Re: Search to only include Business Hours and Exclude weekends

lguinn2 — Thu, 20 Dec 2012 05:04:00 GMT

Not all events have date_* fields. But all events have a timestamp. So I would do it this way

yoursearchhere
| eval hour = tonumber(strftime(_time,"%H"))
| eval dow = tonumber(strftime(_time,"%w"))
| where hour>=6 AND hour<=18 AND dow!=0 AND dow!=6

If this doesn't seem to be working, try this as a debugging step

yoursearchhere
| eval hour = tonumber(strftime(_time,"%H"))
| eval dow = tonumber(strftime(_time,"%w"))
| eval timestamp = strftime(_time,"%x %X")
| table timestamp dow hour

Re: Search to only include Business Hours and Exclude weekends

wellsajs — Mon, 28 Sep 2020 12:59:53 GMT

Thanks, this worked like a charm. I started to try different methods and it would seem the below is slightly faster is there a reason for this ?

date_wday!="saturday" AND date_wday!="sunday"
| eval myHour=strftime(_time, "%H")
| eval myMinute=strftime(_time, "%M")
| where ( (myHour <= 18 AND myMinute >= 00) AND (myHour > 5 AND myMinute <= 59) )

Re: Search to only include Business Hours and Exclude weekends

lguinn2 — Thu, 20 Dec 2012 06:25:36 GMT

When there is a date_wday field in the events, it probably is faster to filter the events in the initial search. It's a rule of thumb: "the earlier in the search pipeline that you can filter out events, the faster the search will run"

BTW, you can completely drop the myMinute field and the related tests from your search - it will work just the same and run even faster

date_wday!="saturday" AND date_wday!="sunday" | eval myHour=strftime(_time, "%H") | where ( myHour <= 18 AND myHour > 5 )

Re: Search to only include Business Hours and Exclude weekends

wellsajs — Thu, 20 Dec 2012 06:53:11 GMT

Thanks very much for taken the time to explain things and provide a solution, much appreciated

Re: Search to only include Business Hours and Exclude weekends

saurabh_tek — Sat, 24 Sep 2016 10:43:52 GMT

Thank you @lguinn.