How to prevent duplicate alerts using field conditions and time?

[PDXKiel]

I have an alert that searches every 15 mins for the count of events >150 (|where Count>150) for the same routing prefix and merchant name. There are 6 fields we list in the results: Routing prefix, merchant ID, bank ID, merchant name, and merchant category code, and Count. I want to stop duplicate emails/alerts when it's for the same merchant category, bank ID, and merchant name that's already been alerted on in the past 8 hours. Is there an optimal way to build the search to do this or a way to setup the trigger conditions that would allow for this?

[PDXKiel · Accepted solution]

Just wanted to provide an update showing the solution I found with the help of a co-worker.

I did a count BY all the fields I wanted then I created a new field called uniqueID that is a md5 hash of the fields MERCHANTNAME, LOGO, MERCHANTID. Then I set the alert to send an email for each result and throttled it for 8 hours based on the new field "uniqueID" that way when the same combination of fields I wanted came out within 8 hours the md5 hash would be the same and no alert would trigger but if a new one came out we'd still get alerted because the md5 hash would change.

I'm also going to add another field called alertState that would still trigger if all of a sudden the count went above 1500 within that same 8 hour throttle. (| eval alertState=if(Count>1500, "OVER1500", "OVER150")) that I will add to the md5 conditions. Below is a sample of the search and I hope this helps someone else!~

index=card sourcetype=epoctd RESPCODE=77 RESPSOURCE=0
| lookup AnalystByLogo.CSV "LOGO" OUTPUT "AnalystName"
| stats count by LOGO, MERCHANTID, CARDACCEPTNAME, MERCHANTTYPE, ROUTINGPREFIX, AnalystName
| eval uniqueID=md5(MERCHANTID . LOGO . CARDACCEPTNAME)
| where Count>150

[damo66a]

this is a pretty good solution to something i am having experience with. 

how do you get the alert to recognise a different MD5 hash? i dont see anywhere in the settings of the alert that i can specify that (or i cant see the wood for the trees). 

[skoelpin]

Yep, bake all the logic into the alert with some conditional logic and have the output reflect each field. Then use a custom alert trigger for the output. It's impossible to help until I see your query

[PDXKiel]

Thanks, here is a basic version of the alert. I have a couple variations running for different scenarios but this is the simplest. So just to make sure since I have the specific field names now: I want to only alert one time in 8 hours if it's the same MERCHANTTYPE, LOGO, & MERCHANTID.

Thanks for your help!

index=card sourcetype=epoctd RESPCODE=77 RESPSOURCE=0
| lookup AnalystByLogo.CSV "LOGO" OUTPUT "AnalystName"
| stats list(LOGO) as LOGO list(MERCHANTID) as MERCHANTNAME list(CARDACCEPTNAME) as MERCHANTID list(MERCHANTTYPE) as MCC Count by ROUTINGPREFIX, AnalystName
| where Count>150

[skoelpin]

It's gunna look something like this. I don't think my logic is right on the throttle field and its difficult to format this without having a Splunk search bar in front of me. But you get the jist, you're gunna have to massage that field and it should give you the right output

index=card sourcetype=epoctd RESPCODE=77 RESPSOURCE=0
| lookup AnalystByLogo.CSV "LOGO" OUTPUT "AnalystName"
| bin _time span=8h
| stats list(LOGO) as LOGO list(MERCHANTID) as MERCHANTNAME list(CARDACCEPTNAME) as MERCHANTID list(MERCHANTTYPE) as MCC Count by ROUTINGPREFIX, AnalystName by _time
| eval throttle=now()-28800
| eval flag=if(Count>150 AND _time<throttle,1,0)
| where flag=1

[richgalloway]

Have you tried throttling the alert?

[PDXKiel]

No, because throttling is blanket for whole alert no matter the results. We still want the alert to trigger, just not for the same merchant category, bank ID, and merchant name.

[skoelpin]

It's possible to use SPL to throttle by field. Need to incorporate _time into your results then use an eval to flag results if they should be throttled. If the flag is set to false AND it results in a trigger condition then fire the alert

[richgalloway]

I would swear there was a way to throttle by field, but I can't find it now.