Splunk Search

Alert trigger condition on field sum

diconium
Explorer

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?

Labels (1)
Tags (2)
0 Karma
1 Solution

diconium
Explorer

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:

usercounttotal_amount
user145103
user227103
user331103

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.

View solution in original post

diconium
Explorer

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:

usercounttotal_amount
user145103
user227103
user331103

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
Explorer

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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)

0 Karma

diconium
Explorer

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

usersum
user145
user227
user331

 

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.

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

...
| streamstats sum(sum) as total

 

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

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

 

 

 

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...