Search to only include Business Hours and Exclude weekends

[wellsajs]

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

[lguinn2 · Accepted solution]

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

[wellsajs]

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

[lguinn2]

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 )

[saurabh_tek]

Thank you @lguinn.

[wellsajs]

Hi

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

[GKC]

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"

[wellsajs]

Hi

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