Filtering out holidays in Splunk

Question

I am attempting to use a search lookup table csv to filter out holidays for some Splunk queries.

To do this, I created a holidays.csv in the following style:

dateof,dateafter,description
01/17/2022,01/18/2022,MLK Day 22
02/21/2022,02/22/2022,Presidents Day 22
05/30/2022,05/31/2022,Memorial Day 22
[...]

Some of the queries run the day after the holiday, which is why I created a dateof and datefater field. I am trying to pipe the condition onto the end of the existing queries.

environment=staging "message=\"This line would contain the original search query"
| eval date=strftime(_time,"%m/%d/%Y")
| search NOT [ inputlookup holidays.csv | fields dateof ]

Note that an Event from the original query will look something like this:

time=2022-08-31T12:01:39,495Z [...] message="This line would contain the original search query"

Despite giving read access to the csv, the above condition does not filter out anything, regardless of whether it is a holiday listed in the csv file or not.

I suspect something is missing. However, I have a limited knowledge of the Spunk querying language. Would anyone be able to give guidance on this? Thanks in advance!

Answer 1

Subsearches can be tricky. If the result of the subsearch isn't just right, the query will fail. It helps to run the subsearch by itself to confirm the string produced makes sense as part of a query. In this case, check that

| inputlookup holidays.csv | fields dateof | rename dateof as date | format

produces something that works with search NOT.

An alternative to try is to explicitly look for the date field in the lookup.

environment=staging "message=\"This line would contain the original search query"
| eval date=strftime(_time,"%m/%d/%Y")
| where NOT [ | inputlookup holidays.csv | fields dateof | rename dateof as date | format ]

Here is another way to do it without a subsearch. A null description field tells us the date was not found in the lookup file and so is not a holiday.

environment=staging "message=\"This line would contain the original search query"
| eval date=strftime(_time,"%m/%d/%Y")
| lookup holidays.csv dateof as date output description
| where isnull(description)