Dashboard Visualization alert

[auzark]

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???

[auzark]

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

[ITWhisperer]

1. | append [ - appends the events generated by the [subsearch] to the existing pipeline of events
2. | makeresults - starts a set of events
3. | eval host=split("host1,host2,host3,host4",",") - creates a multi-value field with each of the hostnames you are interested in
4. | mvexpand host - expands the mv field so that there is one event per host
5. | eval count=0 - sets the count to zero for each event (host)
6. | table host count - sets the fields to be returned by the subsearch
7. ] - completes the subsearch
8. |stats sum(count) as count by host - adds zero to the count for all the hosts

Now you will have a minimum of zero if a host didn't exist in the first search.

[ITWhisperer]

|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, "🗸", "⚠")

[auzark]

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

[ITWhisperer]

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.

[auzark]

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&gt;3, "🗸", "⚠")</query>