Excluding holidays and weekends for Alert

[Cheng2Ready]

I have a Holiday.csv file that imports dates for specific holiday dates.
example:

2024-04-01
2026-12-29
2028-06-26

I am working on muting alerts during a day after the dates.

So, if the holiday was on Monday, it shouldn't fire on Tuesday, if the holiday was on Tuesday, it shouldn't fire on Weds, etc.
The weird one is if the holiday is on a Friday, then we actually don't want the alert to fire on Monday

this is what I have for my query.  just not sure how I would add in the Friday scenario if I did 
 strftime(_time+86400,"%Y-%m-%d")  ```to add one day``` 

index=<search>
| eval Date=strftime(_time,"%Y-%m-%d")
| lookup holidays.csv HolidayDate as Date output Holiday
| eval should_alert=if((holidays.csv!="" AND isnull(Holiday)), "Yes", "No")
| table Date should_alert
| where should_alert="Yes"

If something like this is possible in Splunk, I think it would work: if holiday is a Friday, add 3 days, otherwise add 1 day

[gcusello]

Hi @Cheng2Ready ,

please, see this my old answer:

https://community.splunk.com/t5/Splunk-Search/Bank-holiday-exclusion-from-search-query/m-p/491071

Ciao.

Giuseppe

[Cheng2Ready]

@gcusello 
Thank you 
I looked at your post as saw

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.

So now the question is 
I am using this lookup file to say 
Do not alert on these dates in the lookup
but we need to +1 day on them

so lets say the lookup table is 
 2025-02-17th
We would need to add 1 day to it so now its actually Muting on the 18th
if that make sense?

to simplify, the lookup table Dates we just need to +1 day  to it and make sure on those dates, we just mute alert


would it look like this?
your_search | eval date=strftime(_time + 86400,"%Y-%m-%d")
| search NOT [ inputlookup holidays.csv | fields date ]
|...

Also is there a difference using from inputlookup vs lookup?

All the best!

[gcusello]

Hi @Cheng2Ready ,

if you need to exclude only the days following holidays, you approach is correct.

if instead you need to exclude both the holidays and the one followind days, you have to implement a mix between the two solutions with both the checks.

let us know if we can help you more, or, please, accept one answer for the other people of Community.

Ciao and happy splunking

Giuseppe

P.S.: Karma Points are appreciated by all the contributors 😉

[Cheng2Ready]

@gcusello 
I see 

As for you comment
"if instead you need to exclude both the holidays and the one following days, you have to implement a mix between the two solutions "
its a no. its more simply than that.

Just need to add one following day to the lookuptable date. for Muting

Tried my query but doesn't seem like the results are correct.

or how would you go about it?

[gcusello · Accepted solution]

Hi @Cheng2Ready ,

you wave two ways:

insert al the dates to excude in the lookup, in this case you can use the above search;

insert in the lookup only the holydays and run something like this:

your_search
| eval date=strftime(_time,"%Y-%m-%d")
| search NOT ( [ | inputlookup holidays.csv | fields date ] OR [ | inputlookup holidays.csv | eval date=strftime(strptime(date,"%Y-%m-%d")+86400)) | fields date ]
| ...

obviously in the lookup there must be a column called "date" and the format of the values must be "yyyy-mm-dd".

Ciao.

Giuseppe

[Cheng2Ready]

@gcusello 
im getting a Error
Error in 'EvalCommand': The arguments to the 'strftime' function are invalid.

My search
| eval Date=strftime(_time, "%Y-%m-%d")
| search NOT ( [ | inputlookup holidays.csv | eval HolidayDate=strftime(strptime(HolidayDate,"%Y-%m-%d")+86400)) | fields HolidayDate ]

[ITWhisperer]

Try something like this

| eval Date=strftime(_time, "%Y-%m-%d")
| search NOT ( [ | inputlookup holidays.csv | eval HolidayDate=strftime(strptime(HolidayDate,"%Y-%m-%d")+86400,"%Y-%m-%d") | fields HolidayDate ])

[Cheng2Ready]

Thank you @gcusello  I will take look  

[livehybrid]

Hi @Cheng2Ready 

To implement the desired behavior for muting alerts following holidays based on your holiday dates, you can modify your Splunk query to handle the special case where the holiday falls on a Friday. Here's a revised version of your query that checks for Friday holidays and adjusts the day to mute alerts:

index=<search>
| eval Date=strftime(_time, "%Y-%m-%d")
| lookup holidays.csv HolidayDate as Date output Holiday
| eval should_alert = if(isnull(Holiday), "Yes", "No")
| eval day_of_week = strftime(_time, "%A") // Get the day of the week
| eval mute_date = if(day_of_week == "Friday", Date + 3*86400, Date + 86400)
// Mute for Friday holidays
| eval mute_alert = if(mute_date == Date, "No", should_alert) // Adjust mute
based on the calculated mute date
| table Date mute_alert
| where mute_alert = "Yes"

Explanation:

• Day of the Week Calculation: `strftime(_time, "%A")` retrieves the day of
  the week for the given date.
  
  
• Mute Date Calculation: The line:
  `eval mute_date = if(day_of_week == "Friday", Date + 3*86400, Date +
  86400)` determines the mute date based on whether the holiday is on a Friday or
  another day. If it's Friday, it adds 3 days (including the weekend) to the mute
  date; otherwise, it adds only 1 day.
• Mute Alerts Logic: We then check if the current date matches the
  `mute_date`, setting `mute_alert` accordingly.
• Final Filtering: The `where` clause filters results to only keep entries
  where alerts should still fire, aligning with your requirements.
  This should successfully mute alerts on the day following any holiday based on
  the criteria you've established.

Please let me know how you get on and consider accepting this answer or adding karma this answer if it has helped.
Regards

Will

[Cheng2Ready]

Good Morning @livehybrid 

Just wanted to wrap my head around the logic

2025-02-13	Yes
2025-02-14	Yes
2025-02-15	Yes

So is the yes mean that it will alert on those dates? hence returning an result?
Also lets say for example
If an alert fired on the 15th and the lookuptable has the date 2025-02-15
Does it mute the next day? so the 16th ?wont get alerted? (if it falls within mon~thursday)
where Friday it will jump to monday to mute
so it would look like this

2025-02-15

no

and  instead of displaying that in a event it will not actually return any results?



If I want to only add 1 day would I change it like this?

| eval mute_date = if(day_of_week == Date + 86400)

all the best!

[Cheng2Ready]

Thank you @livehybrid livehybrid I will give this try today and let you know the results