Splunk Search

Check current date against lookup

Contributor

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.....

Tags (3)
0 Karma
1 Solution

Communicator

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!!!

View solution in original post

0 Karma

Communicator

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]

Communicator

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!!!

View solution in original post

0 Karma

Contributor

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!

0 Karma

Influencer

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

0 Karma

Contributor

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

0 Karma