topic Re: How to edit my search so the alert triggers when the count=0? in Splunk Search

How to edit my search so the alert triggers when the count=0?

nithin204 — Mon, 13 Feb 2017 19:07:48 GMT

I'm looking for a query which write count=0 in the stats result when there are no events for that app and host.

My search query:

index=XYZ (appid=A OR appId=B) ( host=123 OR host =234 ) | stats count by appid,host

An alert should be triggered when the count is 0 from the result. I have tried using appendpipe but it didn't work for me.

Example: I have added a new host=000 in the above search

index=XYZ (appid=A OR appId=B) ( host=123 OR host =234 OR host=000) | stats count by appid,host | appendpipe [ stats count by appid,host | count=0 | where count==0]

The result is same as the result from first query. I was expecting two extra rows in the result ,something like appId A host=000 count=0 and appid=B host=000 count=0

Is there any other way I can trigger an alert when count=0 for the above scenario.
Thanks

Re: How to edit my search so the alert triggers when the count=0?

pradeepkumarg — Mon, 13 Feb 2017 19:32:38 GMT

You can use your 1st search itself and when setting up the alert, use the alert condition if number of events - equals to - 0

Re: How to edit my search so the alert triggers when the count=0?

richgalloway — Mon, 13 Feb 2017 19:33:26 GMT

Rather than force the query to return "count=0" I prefer to let my search return what it will and set the alert condition to "if number of events", "is equal to", "0".

Re: How to edit my search so the alert triggers when the count=0?

nithin204 — Mon, 13 Feb 2017 19:42:28 GMT

The count=0 will not be displayed in the stats table(result). I guess it works only when there is atleast one event. If host=123 is down, the result will be app A host 234 count and app B host 234 count. There will not be any records for host=123 I believe.

Re: How to edit my search so the alert triggers when the count=0?

nithin204 — Mon, 13 Feb 2017 19:42:35 GMT

Re: How to edit my search so the alert triggers when the count=0?

nickhills — Mon, 13 Feb 2017 19:49:40 GMT

The problem with your approach is that if any of your app/host combos stop sending events they will be dropped from your search.

If I interpret your question correctly - you have two apps, both running on two hosts. You want to know if either app stops sending events on either server?

Your stats table from:

index=XYZ (appid=A OR appId=B) ( host=123 OR host =234 ) | stats count by appid,host

should be returning you 4 rows?

If I follow correctly so far, you will want to build your alert to trigger when your event count !=4
If any of the apps/servers stop sending logs, your event count will be below 4, and your alert will fire.

Re: How to edit my search so the alert triggers when the count=0?

nithin204 — Mon, 13 Feb 2017 19:55:18 GMT

Thanks for your comment. Yes, This was my approach as well. But I have 4 apps and 4 hosts. So the result will have 16 rows. If I set up a condition if number of events < 16 the alert will trigger but I don't want the users to go and find what server is missing from the lists. I want to send the details of the appId and host in the alert rather than the complete list of results when alert was triggered. Is this possible with lookup's ? Appreciate your help on this.

Re: How to edit my search so the alert triggers when the count=0?

nickhills — Mon, 13 Feb 2017 20:25:04 GMT

If you added your desired hosts and appIds into a lookup file, you could start your search with an inputlookup. This would ensure you always have at least 1 event for each host/app combo in your search then you could run stats on the results, and finally a where count=1 would show up just the events which are in the lookup, but not the query.

i have not tested this, or perhaps even fully thought it through, but I think this could work.

I'll try and test and give you a full example if I get a chance

Re: How to edit my search so the alert triggers when the count=0?

DalJeanis — Mon, 13 Feb 2017 20:33:45 GMT

appId or appid?

Re: How to edit my search so the alert triggers when the count=0?

DalJeanis — Mon, 13 Feb 2017 20:39:21 GMT

Try this

index=XYZ (appid=A OR appId=B) ( host=123 OR host =234 ) | stats count as mycount by host appid 
| append  
[| makeresults | eval host="123 234" | eval appid="A B" | makemv host | makemv appid | mvexpand host | mvexpand appid | eval mycount = 0 ]
| stats sum(mycount) as mycount by appid host
| where mycount = 0

Re: How to edit my search so the alert triggers when the count=0?

nickhills — Mon, 13 Feb 2017 21:00:18 GMT

thats much nicer than my suggestion.

Re: How to edit my search so the alert triggers when the count=0?

nithin204 — Mon, 13 Feb 2017 21:10:28 GMT

It got this error : Unknown search command 'makeresults'

Re: How to edit my search so the alert triggers when the count=0?

richgalloway — Mon, 13 Feb 2017 21:17:27 GMT

makeresults is a relatively new command. If your version of Splunk doesn't have it, try metadata type=sources | head 1.

Re: How to edit my search so the alert triggers when the count=0?

DalJeanis — Mon, 13 Feb 2017 21:28:20 GMT

Actually, the lookup table is a more maintainable solution in the long run. This is a good one for a quick throwaway, though.

Re: How to edit my search so the alert triggers when the count=0?

aaraneta_splunk — Mon, 20 Mar 2017 00:28:39 GMT

@nithin204 - Looks like you have a few possible solutions to your question. If one of them provided a working solution, please don't forget to click "Accept" below the best answer to resolve this post. If you still need help, please leave a comment. Don’t forget to upvote anything that was helpful too. Thanks!