Alert trigger condition on field sum

[diconium]

Hi.

I created the following search which reports events of Active Directory users being locked aggregated by username:

index="active_directory" sourcetype=XmlWinEventLog source="XmlWinEventLog:Security" EventCode=4740
| stats count BY user

To be notified if the overall amount is above a threshold I want to create an alert on it. Of course I could extend this base search to only have a result if the number of events is above the threshold and trigger the alert if the number of results is greater than one:

index="active_directory" sourcetype=XmlWinEventLog source="XmlWinEventLog:Security" EventCode=4740
| stats count BY user
| stats sum(count) AS sum
| search sum > 100

But in this case the alert result would only consist of the number of events. To get the list of the events one would then need to manually run the base search with correct time range.

So I came to the custom trigger condition. As the documentation doesn't tell if it should work, I just tried to use the last two lines as trigger condition:

stats sum(count) AS sum | search sum > 100

Unfortunately this doesn't seem to work. Does anyone have an idea how this could be solved alternatively?

[diconium · Accepted solution]

Finally I solved it using eventstats which creates new field for the total sum:

index="active_directory" sourcetype=XmlWinEventLog source="XmlWinEventLog:Security" EventCode=4740
| stats count BY user
| eventstats sum(count) AS total_amount
| search total_amount > 100

The result of this search looks like this:

user	count	total_amount
user1	45	103
user2	27	103
user3	31	103

Afterwards I can filter by its value using the threshold value (e.g. 100). So there will be results only if the total_amount exceeds the theshold.

[diconium]

All those solutions have only one row in the search result. But I want the search result to consist the amount of events for each user and the alert should only be triggered if the overall sum of events is above the threshold.

[ITWhisperer]

You just need to make your query retrieve all the events without the stats clauses and change your alert so it triggers on the number of results (rather than custom)

[diconium]

That's not what I need. The search result should look like this:

user	sum
user1	45
user2	27
user3	31

The alert should be triggered if the summary of the second column is above the threshold. But the Splunk users should be able to see the search result to check analyze it without running the search again on their own.

So triggering on the number of results won't work as needed because the threshold will mostly be reached with less results. Applied to the example result set the threshold of 100 is reached but there are only 3 results.

[ITWhisperer]

You could add a running total column and trigger on that exceeding 100

...
| streamstats sum(sum) as total

[ITWhisperer]

Have you tried your query as

index="active_directory" sourcetype=XmlWinEventLog source="XmlWinEventLog:Security" EventCode=4740
| stats count BY user
| stats sum(count) AS sum, values(_raw) as events

and your custom trigger as

search sum > 100

[isoutamo]

Hi

just change the 

| stats count
| search count > 100

to

| stats count as locked_accounts
| where locked_accounts > 100

 And then in alert definitions alert if results > 0

r. Ismo