Splunk Search

Bank holiday exclusion from search query

ashrafsj
Path Finder

HI All,

I have a search query that needs to be excluded to run on a bank holiday.

I have created a holidays.csv file as a lookup table with Date,Description

Entries as below,

Date,Description
05/08/2020,Early May bank holiday
05/25/2020,Spring bank holiday
08/31/2020,Summer bank holiday

I'm struggling to get my search to exclude the bank holidays from this list

inputlookup holidays.csv 
| eval holiday = strftime(now(),"%m/%d/%Y") 
| where Date==holiday 

if this condition meets my search query should not be run. I tried to test with todays date but still the results are getting returned.

 search query 
| search NOT [ inputlookup holidays.csv 
| eval holiday = strftime(now(),"%m/%d/%Y") 
| where Date==holiday 
]

Any help is much appreciated.

0 Karma
1 Solution

gcusello
Legend

Hi @ashrafsj,
you could extract a date from your timestamp in the same format of your lookup and compare this field, something like this:

your_search
| eval date=strftime(_time,"%Y-%m-%d")
| search NOT [ inputlookup holidays.csv | fields date ]
| ...

in this way you exclude all the events in the days contained in the lookup.

if instead you would to have the list of all events outside the working hours and you want to manage all the kinds of days: holydays, saturdays, mondays, all days from 18.00 to 9.00, mid working days (from 13.00 to 9.00), you should create a little more complex lookup:

  • in your lookup you have to insert a row for each day of the year (called date), associating to each day a code (called type):
    • 2 for saturdays, mondays and holidays,
    • 1 for mid working days,
    • 0 for working days;
  • insert in your search:

.

| lookup holidays.csv date  OUTPUT type
| search type=2 OR (type=1 (time_hour>13 OR time_hour<9))

.
if you have to insert this condition in many searches, it could be a good idea to create a macro and call it in your searches, in this way you'll have a slimmer code and you can modify working hours in an easily way (only in the macro)

Ciao.
Giuseppe

View solution in original post

0 Karma

gcusello
Legend

Hi @ashrafsj,
you could extract a date from your timestamp in the same format of your lookup and compare this field, something like this:

your_search
| eval date=strftime(_time,"%Y-%m-%d")
| search NOT [ inputlookup holidays.csv | fields date ]
| ...

in this way you exclude all the events in the days contained in the lookup.

if instead you would to have the list of all events outside the working hours and you want to manage all the kinds of days: holydays, saturdays, mondays, all days from 18.00 to 9.00, mid working days (from 13.00 to 9.00), you should create a little more complex lookup:

  • in your lookup you have to insert a row for each day of the year (called date), associating to each day a code (called type):
    • 2 for saturdays, mondays and holidays,
    • 1 for mid working days,
    • 0 for working days;
  • insert in your search:

.

| lookup holidays.csv date  OUTPUT type
| search type=2 OR (type=1 (time_hour>13 OR time_hour<9))

.
if you have to insert this condition in many searches, it could be a good idea to create a macro and call it in your searches, in this way you'll have a slimmer code and you can modify working hours in an easily way (only in the macro)

Ciao.
Giuseppe

0 Karma

ashrafsj
Path Finder

Thanks a lot @gcusello, I will also check out the other option that you have highlighted and get back to you on it. Much appreciated for a quick response.

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...