How to eliminate duplicate alerts from being generated if search time is greater than the alert interval?

[mendesjo]

I have an alert that looks like this:

index=test Operation="Add member to role." | eval lag_sec=_indextime-_time | table UserId,Operation,lag_sec,_time  

Normally, if I was receiving and indexing the data in near real time, I would setup the alert to run say every 15 minutes, and set the search window to 15 minutes, and I wouldn't get any duplicate alerts. In this case, however, the search is scheduled to run every 15 minutes, but the search window is 12hrs, because the system sending Splunk the data to be indexed can be delayed up to 12 hours.

I created a Splunk case for this but they were not helpful. The only thing I could think of was maybe to use lookup tables. Where, I could save the result of a search to a lookup table and then have the search look at the lookup table for that userid and timestamp and if they match, it's a duplicate and don't alert on it.

Any ideas how else I could do this? I'm trying to eliminate duplicate alerts, and again the whole problem is that the data being indexed can be delayed up to 12hrs. Thank you in advance.

[jblemieux]

i have the same issue generating alerts from AWS Cloudtrail events using the Splunk App. I check for alerts every 5 minutes, and the shortest length to look back is 15 minutes since any shorter would miss critical events. I have gotten it down to just 1 duplicate alert. Where would I put the code in my search query (sample below)?

aws-cloudtrail-sourcetype eventName=AuthorizeSecurityGroupEgress | dedup eventID | eval discovered_date=ceil(_time) * 1000 | fields eventID, eventTime, userIdentity.principalId, userIdentity.accessKeyId, discovered_date, eventSource, eventName, recipientAccountId, requestParameters.groupId

[martin_mueller]

See my answer from 2016 about primarily filtering by index time. That way you can search a large time range but only look at each event once.

[frobinson_splun]

Hi @mendesjo,
You might want to review the alert scheduling best practices here:
http://docs.splunk.com/Documentation/Splunk/6.5.0/Alert/AlertSchedulingBestPractices#Best_practices

In particular, it discusses building delay into the alert schedule to deal with overlaps or gaps in data.

Hope this helps!

[martin_mueller]

You eliminate generating alerts twice by not searching events twice 😉

Run this over a time range of, say, -24h to +24h every 15 minutes:

_index_earliest=-16m@m _index_latest=-m@m index=test Operation=...

That will filter on two time ranges, one based on _time and one based on _indextime. The index time one is corresponding to your search frequency, the event time one is corresponding to your expected indexing delay. Be generous there, missed events are usually big trouble.

[aamelyan]

Can you do the same when you have a SQL query?

| dbxquery connection=MyDB query=usp_Splunk_GetDataForAlert shortnames=true output=csv

[martin_mueller]

DB Connect doesn't know time ranges as Splunk search itself knows, nor does it know index time.
Use DB Connect's tailing feature to only load new rows.

[mendesjo]

Simple, brilliant! Let me give it a shot...

[aaraneta_splunk]

Hi @mendesjo - Did @martin_mueller's answer help you out? If yes, please don't forget to click "Accept" below his answer. If no, feel free to leave a comment with more feedback. Thanks!