topic Re: Using single column Holiday table how do I determine whether today is a holiday? in Splunk Search

Using single column Holiday table how do I determine whether today is a holiday?

pm771 — Wed, 09 Sep 2020 13:19:57 GMT

I have a lookup CSV table that lists dates of holidays in a single column.

HolidayTable.csv:

HDate

1/1/2020

...................

I tried to use the following:

search ... | eval currentdate=strftime(now(),"%-m/%-d/%Y") | lookup HolidayTable.csv HDate as currentdate OUTPUT HDate as Holiday | eval Holiday=if(isnull(Holiday), "N", "Y")

My expectation was that when the current date matches a row in the table I will get the date string and otherwise - null.

It does not seem to work this way.

What am I missing here?

Re: Using single column Holiday table how do I determine whether today is a holiday?

bowesmana — Wed, 09 Sep 2020 03:24:14 GMT

Your currentdate date format is wrong based on your example of 01/01/2020 in the CSV. Your format is

%-m/%-d

but it should be %m/%s, so it has leading zeros

Re: Using single column Holiday table how do I determine whether today is a holiday?

pm771 — Wed, 09 Sep 2020 15:05:16 GMT

It was a typo in my question. Month and day in my table are without leading zeroes.

Since the table is going to be maintained via Excel, I had to use the default date format.

I will update the question.

Re: Using single column Holiday table how do I determine whether today is a holiday?

pm771 — Fri, 25 Sep 2020 02:09:00 GMT

[Following up with my own solution]

I ended up with creating a dummy 2nd column.

HDate	Off
1/1/2020	Y
...................	Y

And with a slight change (used new column as returned value) my code started working:

search ... | eval currentdate=strftime(now(),"%-m/%-d/%Y") | lookup HolidayTable.csv HDate as currentdate OUTPUT Off as Holiday | eval Holiday=if(isnull(Holiday), "N", "Y")