Bank holiday exclusion from search query

[ashrafsj]

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.

[gcusello · Accepted solution]

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

[ashrafsj]

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.