Splunk Search

Search to only include Business Hours and Exclude weekends

wellsajs
Explorer

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

Tags (1)
1 Solution

lguinn2
Legend

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

View solution in original post

lguinn2
Legend

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
Explorer

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

0 Karma

lguinn2
Legend

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
Communicator

Thank you @lguinn.

0 Karma

wellsajs
Explorer

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
Explorer

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"

0 Karma

wellsajs
Explorer

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

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...