Splunk Search

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

Explorer

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

Tags (3)
0 Karma

Splunk Employee
Splunk Employee

@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!

0 Karma

SplunkTrust
SplunkTrust

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

Explorer

It got this error : Unknown search command 'makeresults'

0 Karma

SplunkTrust
SplunkTrust

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

---
If this reply helps you, an upvote would be appreciated.

Ultra Champion

thats much nicer than my suggestion.

SplunkTrust
SplunkTrust

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

0 Karma

SplunkTrust
SplunkTrust

appId or appid?

0 Karma

Ultra Champion

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.

0 Karma

Explorer

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.

0 Karma

Ultra Champion

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

0 Karma

SplunkTrust
SplunkTrust

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".

---
If this reply helps you, an upvote would be appreciated.
0 Karma

Explorer

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.

0 Karma

Influencer

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

0 Karma

Explorer

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.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!