I have found a dashboard visualisation where we are using eyes on glass alert when one of the hosts is not being ingested into Splunk.
I have found out (the hard way) that the below query will of course never alert on a zero count because if one of the hosts is NOT ingesting, it does not return a value.
|tstats count where index=prod_s3 sourcetype=WinEventLog:Security (host=host1 OR host=host2 OR host=host3 OR host=host4 ) by host |stats min(count) as count | eval unit=if(count>0, "🗸", "⚠")
Any suggestions as to a better way to phrase this statement???
Hi ITWhisperer,
I will test this out when I get back to work next Monday. In the mean time can you explain how this steps thru? (ie...each of the pipe's following the append)
The Dashboard that this panel belongs to refreshes every 20 minutes, but the spl query for this panel looks back 1/2 an hour.
Much appreciated...
Now you will have a minimum of zero if a host didn't exist in the first search.
|tstats count where index=prod_s3 sourcetype=WinEventLog:Security (host=host1 OR host=host2 OR host=host3 OR host=host4 ) by host
| append [
| makeresults
| eval host=split("host1,host2,host3,host4",",")
| mvexpand host
| eval count=0
| table host count
]
|stats sum(count) as count by host
|stats min(count) as count
| eval unit=if(count>0, "🗸", "⚠")
Hi ITWhisperer,
My apologies for the slow response. Here is what I found running the code you supplied.
The returned results are a count for the 4 different hosts wit count, followed by the same hosts with a count =0. the minimum is then applied and the returned result is = 0 and always displays the red warning sign.
I need to return the green tick if the min host of the 4 hosts is still > 0. and only return the red warning if one of the hosts does not appear in the original search (ie... ingested events = 0). Does that make sense?
Returned/reported data.
Host Count
Host1 299
Host2 366
Host3 382
Host4 319
Host1 0
Host2 0
Host3 0
Host4 0
It doesn't make sense
|stats sum(count) as count by host
should reduce it to one line per host - having said that, you might appear to get hosts repeated if there are differences such as trailing spaces or misspellings.
Thanks for your help.
I have taken a different approach and am now counting the hosts currently ingesting into Splunk and drill down from there to work out which host is not ingesting
| tstats dc(host) AS hostOKCount where index=prod_s3 sourcetype=WinEventLog:Security (host=host1 OR host=host2 OR host=host3 OR host=host4)
| eval unit=if(hostOKCount>3, "🗸", "⚠")</query>