Check current date against lookup

[aohls]

I have a lookup table with a list of dates which I want to use in my alerts. If the alert triggers I want a where clause to check if the current date, MM/DD/YYYY, is in the lookup table; if it is the alert trigger should be ignored.

This is my idea of the search but it does not work as I am not sure how to get the formatting right to actually lookup the date.

| eval Date=strftime(_time,"%m/%d/%Y")
| lookup HolidayDates.csv HolidayDate as Date OUTPUT HolidayDate as HolidayDate

The lookup table has simple dates in it; 07/04/2019, 9/2/2019, 10/14/2019.....

[cshora]

For those who don't like the idea of a lookup file, due to maintenance,  I created a SQL query, which will calculate the date values (see below).

NOTE:  This SQL is written for Oracle, and may need to be modified for your purposes.

select Holiday
/* CALCULATE HOLIDAY VALUES ... RAW *********************************** */
,to_char(date_val,'dd-Mon-yyyy') date_val
,to_char(date_val,'Dy') day_val
/* CALCULATE FEDERAL HOLIDAY VALUES (IF SHIFTED TO ANOTHER DAY) ******* */
,case when to_char(date_val, 'Dy') in ('Sat') then 'prev day'
when to_char(date_val, 'Dy') in ('Sun') then 'next day'
else '' end as fed_calc
,case when to_char(date_val, 'Dy') in ('Sat') then date_val-1
when to_char(date_val, 'Dy') in ('Sun') then date_val+1
else date_val end as fed_recog_date
,case when to_char(date_val, 'Dy') in ('Sat') then to_char(date_val-1,'Dy')
when to_char(date_val, 'Dy') in ('Sun') then to_char(date_val+1,'Dy')
else to_char(date_val, 'Dy') end as fed_recog_day
from (/* CURRENT YEAR -2 ******************************************************************************************************************** */
SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 0) + 0 as date_val FROM DUAL union
SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365*-2),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union
SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 5)+18 as date_val FROM DUAL
where sysdate+(365*-2) > '01-JAN-2022' union
SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 6) + 3 as date_val FROM DUAL union
SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union
SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union
SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'),10) +10 as date_val FROM DUAL union
SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union
SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-2),'YYYY'),11) +24 as date_val FROM DUAL union
/* CURRENT YEAR -1 ******************************************************************************************************************** */
SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 0) + 0 as date_val FROM DUAL union
SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365*-1),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union
SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 5)+18 as date_val FROM DUAL
where sysdate+(365*-1) > '01-JAN-2022' union
SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 6) + 3 as date_val FROM DUAL union
SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union
SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union
SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'),10) +10 as date_val FROM DUAL union
SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union
SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*-1),'YYYY'),11) +24 as date_val FROM DUAL union
/* CURRENT YEAR -0 ******************************************************************************************************************** */
SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 0) + 0 as date_val FROM DUAL union
SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365* 0),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union
SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 5)+18 as date_val FROM DUAL
where sysdate+(365* 0) > '01-JAN-2022' union
SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 6) + 3 as date_val FROM DUAL union
SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union
SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union
SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'),10) +10 as date_val FROM DUAL union
SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union
SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365* 0),'YYYY'),11) +24 as date_val FROM DUAL union
/* CURRENT YEAR +1 ******************************************************************************************************************** */
SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 0) + 0 as date_val FROM DUAL union
SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365*+1),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union
SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 5)+18 as date_val FROM DUAL
where sysdate+(365*+1) > '01-JAN-2022' union
SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 6) + 3 as date_val FROM DUAL union
SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union
SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union
SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'),10) +10 as date_val FROM DUAL union
SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union
SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+1),'YYYY'),11) +24 as date_val FROM DUAL union
/* CURRENT YEAR +2 ******************************************************************************************************************** */
SELECT 'New Years' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 0) + 0 as date_val FROM DUAL union
SELECT 'M.L.K Jr.' as Holiday, NEXT_DAY( TRUNC(sysdate+(365*+2),'YYYY') - 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'President''s' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 1)- 1,'MONDAY') +14 as date_val FROM DUAL union
SELECT 'Memorial' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 5)- 1,'MONDAY') - 7 as date_val FROM DUAL union
SELECT 'Juneteenth' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 5)+18 as date_val FROM DUAL
where sysdate+(365*+2) > '01-JAN-2022' union
SELECT 'Independence' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 6) + 3 as date_val FROM DUAL union
SELECT 'Labor' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 8)- 1,'MONDAY') as date_val FROM DUAL union
SELECT 'Columbus' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'), 9)- 1,'MONDAY') + 7 as date_val FROM DUAL union
SELECT 'Veterans' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'),10) +10 as date_val FROM DUAL union
SELECT 'Thanksgiving' as Holiday, NEXT_DAY(ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'),10)- 1,'THURSDAY') +21 as date_val FROM DUAL union
SELECT 'Christmas' as Holiday, ADD_MONTHS(TRUNC(sysdate+(365*+2),'YYYY'),11) +24 as date_val FROM DUAL
) calc
order by date_val

[justinatpnnl]

You should be able to accomplish this with a subquery:

| eval Date=strftime(_time,"%m/%d/%Y")
| search NOT [ | inputlookup HolidayDates.csv | rename HolidayDate as Date | fields Date]

[jaime_ramirez · Accepted solution]

You are missing a column in you lookup table so you can tell apart when the where clause applies:

sample_lookup_alerts_skip.csv:
HolidayDates,Skip
"07/4/2019","Yes"
"09/2/2019","Yes"
"10/14/2019","Yes"

and then use as below:

...
| eval Date=strftime(_time,"%m/%d/%Y")
| lookup sample_lookup_alerts_skip.csv HolidayDate as Date OUTPUT Skip as Skip
| where Skip != "Yes"

Cheers!!!

[aohls]

One minor change I needed to make was to add

 | fillnull value=No Skip

If this was not applied I did not get values. Thanks!

[Vijeta]

@aohls can you share some sample data for date format from your lookup.

[aohls]

@Vijeta The data is just a single column of dates like the following; 07/4/2019, 09/2/2019, 10/14/2019....