How To Trigger Event Based On Value Of Sorted Search Ouput

[mallem]

I have already setup a saved search to alert on license breaches, but I'm trying to setup a more proactive search that will run every hour to alert on hosts whose throughput is over X number of megabytes. The idea is to be turned onto message floods before we reach our license ceiling. The search criteria below returns the top offending host and the sum of indexed megabytes. I need to setup a scheduled search that will trigger an e-mail with the offending host and sum in the body of the e-mail if the value of the "sum(MB)" field is greater than some number. I'm sure there are advanced alert conditions that can do this, but I haven't been able to figure it out. Any help is appreciated.

index=_internal source=*metrics.log splunk_server="*" NOT "*splunk*" starthoursago = 1 | search group="per_host_thruput" | eval MB=kb/1024 | chart sum(MB) by series | sort - sum(MB) | head 1

[mallem]

This search has been particularly helpful to me to nail down top offending servers. It can easily be modified to show indexing volume by source and sourcetype too.

index=_internal source=*metrics.log splunk_server="*" NOT "splunk" | search group="per_host_thruput" | eval MB=kb/1024 | stats sum(MB) as sumMB by series | sort sumMB | tail 10

[mallem]

Sorry it took me so long to respond. The saved search to alert on license breaches is below:

index=_internal source=*license_audit.log LicenseManager-Audit | delta quotaExceededCount as quotadiff | stats first(quotadiff) as quotadiff | search quotadiff>0

[mallem · Accepted solution]

That worked! Thank you! Interesting that piping to "head" pulls the bottom offending hosts, but changing to the "tail" pulls in the top offending hosts in the below example. Opposite of the behavior I expected.

index=_internal source=*metrics.log splunk_server="*" NOT "*splunk*" startminutesago=60 | search group="per_host_thruput" | eval MB=kb/1024 | stats sum(MB) as sumMB by series | sort sumMB | tail 10

[dwaddle]

If someone provided an answer that solved your issue, kindly click the "accept" checkbox next to so that it is marked as answered and the answerer receives their rep points for the help. Thanks!

[ftk]

I updated the answer. Check it out.

[ftk]

After scheduling your search you will want to select "if custom condition is met" as your alarm action. Then put the following into your Custom condition search:

search sum(MB) > 50

where 50 is the "some number" you are referring to in your question.

I personally would remove the sort and head off of your search and replace chart with stats as such:

index=_internal source=*metrics.log splunk_server="*" NOT "*splunk*" starthoursago = 1 | search group="per_host_thruput" | eval MB=kb/1024 | stats sum(MB) by series

and then use the custom condition search

search sum(MB) > 50

as this will alert you on all violating hosts, not just the top violating host.

For more info on alert conditions see http://www.splunk.com/base/Documentation/latest/User/SetAlertConditionsFromScheduledSearches

[EDIT]: Yeah looks like the search on the sum(MB) isn't happy. You can work around it by giving the sum a name, as such: Using your query:

index=_internal source=*metrics.log splunk_server="*" NOT "*splunk*" starthoursago = 1 | search group="per_host_thruput" | eval MB=kb/1024| chart sum(MB) as sumMBby series | sort - sumMB | head 1

or my version

index=_internal source=*metrics.log splunk_server="*" NOT "*splunk*" | search group="per_host_thruput" | eval MB=kb/1024 | stats sum(MB) as sumMB by series

And then setting your custom condition to

search sumMB > 50

This will work.

[eclypse]

mallem, I'd love if you could share the search you are using to alert on license breaches.

[ftk]

You are right. The search on sum(MB) isn't happy -- work around it by renaming the sum field. I edited the answer.

[mallem]

I tried using your recommended expression, along with "search sum(MB) > 50" as the custom condition search, but it doesn't trigger any e-mails. I reduced the "search sum(MB)" to "> 1", but still nothing. I retried using my search expression. Still nothing.