How to achieve an alert for when the count is 0?

[bapun18]

I am running a query |tstats count latest(_time) where index=abcd by host, my requirement is to create an alert when the count is 0, when there would be no event in the index. 

My problem is when there is no event I am not getting the count field as 0.

[richgalloway · Accepted solution]

appendpipe to the rescue!  This is a common method used to create a event where one otherwise would not exist.

| tstats count latest(_time) where index=abcd by host 
| appendpipe [ stats count as count2 
  | eval count=0, host="N/A" 
  | where count2=0 
  | fields - count2
]

[bapun18]

what if I have 2 hosts rly01 and rly02 and I want to print 2 lines as below in case of no event for one host or two
host               count
rly01                0

rly02              1456

or vice versa

[richgalloway]

Then we're looking at a different solution, one at which Splunk does not excel.  See https://www.duanewaddle.com/proving-a-negative/

[bapun18]

Thanks a lot