topic Re: Excluding holidays and weekends for Alert in Splunk Search

Excluding holidays and weekends for Alert

Cheng2Ready — Thu, 13 Feb 2025 21:20:22 GMT

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

Re: Excluding holidays and weekends for Alert

livehybrid — Thu, 13 Feb 2025 21:26:02 GMT

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

Re: Excluding holidays and weekends for Alert

gcusello — Fri, 14 Feb 2025 07:15:50 GMT

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

Re: Excluding holidays and weekends for Alert

Cheng2Ready — Fri, 14 Feb 2025 17:21:27 GMT

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

Re: Excluding holidays and weekends for Alert

Cheng2Ready — Fri, 14 Feb 2025 17:21:48 GMT

Thank you @gcusello I will take look

Re: Excluding holidays and weekends for Alert

Cheng2Ready — Tue, 18 Feb 2025 22:22:49 GMT

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

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!

Re: Excluding holidays and weekends for Alert

Cheng2Ready — Thu, 20 Feb 2025 23:24:05 GMT

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

Re: Excluding holidays and weekends for Alert

gcusello — Fri, 21 Feb 2025 06:48:15 GMT

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 😉

Re: Excluding holidays and weekends for Alert

Cheng2Ready — Fri, 21 Feb 2025 17:19:54 GMT

@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?

Re: Excluding holidays and weekends for Alert

gcusello — Sat, 22 Feb 2025 06:38:14 GMT

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:

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

Ciao.

Giuseppe

Re: Excluding holidays and weekends for Alert

Cheng2Ready — Wed, 26 Feb 2025 20:53:31 GMT

@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 ]

Re: Excluding holidays and weekends for Alert

ITWhisperer — Wed, 26 Feb 2025 22:20:56 GMT

Try something like this