How to Mute Alert using Lookup table

[Cheng2Ready]

This is what I have setup

index=xxxxxx
| eval HDate=strftime(_time,"%Y-%m-%d")
| search NOT [ | inputlookup Date_Test.csv | fields HDate ]

The search always returns 1 event.
The Alert Condition is:
if it see's  more than 1 event OR  0 event trigger an alert.

Issue I'm facing now is on the Lookup table dates
Lets say I have it setup on April 14th in my Lookup table file "Date_Test.csv"
On April the 14th Still fired an alert, I'm not sure if its because it see 0 events ?  It suppose to Mute on that day.
any insight and help would be much appreciated

[yuanliu]

This is not a good use of inputlookup.  The better command to use is lookup.  You then count how many events do not match

index=xxxxxx
| eval HDate=strftime(_time,"%Y-%m-%d")
| lookup Date_Test.csv HDate output HDate as match
| where isnull(match)
| stats count values(Hdate)
| where count != 1

 I added values(Hdate) in speculation.  Don't include it in your alert if the values are not useful.

[Cheng2Ready]

@yuanliu 
I still get alerted, I added today's date so it should of Muted the Alert, but it didnt

[yuanliu]

I am confused.  You say that you only want to suppress alert when count is 1.  If count is greater than 1 or if count is 0, you want to send alert.  In your screenshot, you get count 0 - so the alert is valid.  No?

[Cheng2Ready]

@yuanliu  hi so I only want to suppress alert on the dates of the lookup table.

The condition for the alert is to fire if the resulted does not equal to 1

If your pointing out my screen shot please let me know which to adjust to the correct format so I can try implement it correctly.

[yuanliu]

To clarify, there are two distinct aspects in your requirements:

1. If the date of the event matches that in the lookup, do not send alert no matter what search result is.
2. Only on days that do not match any date in the lookup, send alert if search result is 0 or greater than 1.

If this is true, event count must be before date match or together with date match.

index=xxxxxx
| eval HDate=strftime(_time,"%Y-%m-%d")
| lookup Date_Test.csv HDate output HDate as match
| stats count values(match) as match by HDate
| where isnull(match) AND count != 1

The by HDate clause is to validate event date in case the search crosses calendar dates.

[Cheng2Ready]

@yuanliu 
Good Morning I've updated the Search query, let me know if anything needs to be adjusted.

So far the Alert is not firing. My index search is looking for something  that doesn't exist so it should always alert unless I update the Lookup table to today's date(5/27/2025) to mute.

[gcusello]

Hi @Cheng2Ready ,

at first check that the date format is the same both in events (after eval command) and in lookup,

then try inserting in the lookup a test date that you're sure to have events.

At least, don't use this condition in the alert: put the condition inside the alert search and not in the alert definition, in other words: in alert definition use results>0 and use this search:

index=xxxxxx
| eval HDate=strftime(_time,"%Y-%m-%d")
| search NOT [ | inputlookup Date_Test.csv | fields HDate ]
| stats count
| append [ | makeresults | eval count=0 | fields count)
| stats sum(count) AS total
| where total>1 OR total=0

in ths way, removing the final condition, you can check your search results before the alerting.

Ciao.

Giuseppe

[Cheng2Ready]

@gcusello the Alert still fired

[gcusello]

Hi @Cheng2Ready ,

please try this:

index=xxxxxx
| eval HDate=strftime(_time,"%Y-%m-%d")
| stats count BY HDate
| eval type="events"
| append [ | inputlookup Date_Test.csv | eval count=0, type="lookup" | fields HDate count type ]
| stats 
     sum(count) AS total 
     values(type) AS type
     dc(type) AS type_count
     BY HDate 
| where total=0 OR (total>1 AND type_count=1 AND type="events"

in this way, with the first condition (total=0) you check if there's some date without events and with the scond one (total>1 AND type_count=1 AND type="events") you check that there are events with dates not present in the lookup.

The solution has only one issue, that's inside the requirement: you need to continously update the lookup otherwise you'll have false positives created by the old dates in the lookup.

Only for discussing: what do you want to check?

maybe there's another easier solution.

Ciao.

Giuseppe

[Cheng2Ready]

@gcusello 
tried this

is it suppose to return the lookup table?

and it Still Alerted

Only for discussing: what do you want to check?
So 
the Goal here is to check
if there is More than 1 Event Alert
if there is 0 Event Alert

Issue currently facing
Currently the Search is look at 0 event so on default it will always alert because there is 0 event
What I am trying to test is the Mute in effect.
on the Lookuptable Ive added Today's date to see if it will take in effect and looks like I am still being Alerted.
Looking for answers to fix the Alert to MUTE on the Dates ive included in the lookuptable

[livehybrid]

Hi @Cheng2Ready 

In the SPL you have shared you are appending a makeresult with count=0, then stats sum(count) as total, but then in your WHERE clause you have total>1 OR total=0

If total=0 which I guess it will, then you will still get 1 result returned, even if the returned result says total=0 it will still match the criteria of No. of events >0.

I think you meant to close the append after the eval count=0. Can you try the following instead?

index=xxxxxx
| eval HDate=strftime(_time,"%Y-%m-%d")
| search NOT [ | inputlookup Date_Test.csv | fields HDate ]
| stats count
| append [ | makeresults | eval count=0 | fields count) ]
| stats sum(count) AS total
| where total>1

Although I'm confused as to why you couldnt do this?

index=xxxxxx
| eval HDate=strftime(_time,"%Y-%m-%d")
| search NOT [ | inputlookup Date_Test.csv | fields HDate ]
| stats count
| where count>0

🌟 Did this answer help you? If so, please consider:

• Adding karma to show it was useful
• Marking it as the solution if it resolved your issue
• Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

[Cheng2Ready]

@livehybrid 
Thank you so much for the feedback

as to answering your question

"Although I'm confused as to why you couldn't do this?
index=xxxxxx | eval HDate=strftime(_time,"%Y-%m-%d") | search NOT [ | inputlookup Date_Test.csv | fields HDate ] | stats count | where count>0"




Would this also help capture if there was 0 events?  The Goal is to have the Alert Trigger anything except 1 event , so !=1  .
It needs to alert if  there is 0 events found OR more than 1 event.

Either way I have a scenario where there is 0 events BUT! Its a Mute date on my Lookup table and it still fired an alert.
Its either that or because its was a Mute date that there might have been 1 event but since its a Mute date it changed it to 0 event Still causing the Alert to fire.

Let me know if you need more clarification and I can post what I have setup

[Cheng2Ready]

@gcusello "at first check that the date format is the same both in events (after eval command) and in lookup"

This is what I have in the look up