How to identify the hosts (Primary or Secondary) that have stopped reporting to Splunk using lookup table

[gauravu_14]

All,

I am looking for a solution to identify the hosts that have stopped reporting to Splunk using lookup table. 
However, the condition is there are Primary and Secondary hosts for some data types. I do not want to get alerted if either of the hosts (Primary or Secondary) is reporting.
At the same time I would like to map these hosts to their respective index. So if a host(both primary and secondary in some cases) from a particular index stops reporting an alert should trigger (will probably have another column for index mapping the hosts).

Any solution would be highly appreciated!! 

[PickleRick]

Assuming you have a lookup containing three columns (index, host, sourceid) so that you can have multiple index/host pairs matching a single sourceid and you want to find situations where none of the index/host pairs for a given sourceid report indexed events, you can do it like this:

Count the events you have (preferably with tstats if you can)

| tstats count where <your conditions> by index host

Now you want to append your table

| inputlookup append=t yourlookup
| fillnull count value=0

So you have to do check the overall count

| stats sum(count) as count by index host sourceid

This is not much different from your "single source check". But as you want to have it checked against a "multisourced" id.

So do

| eventstats sum(count) as combined_count by sourceid

This will give you additional field containing a combined count of events across all index/host pairs for a given sourceid.

So the ones you're interested in are those which didn't have any events in any of those index/host pairs

| where combined_count=0

[gcusello]

Hi @gauravu_14 ,

in general, having a lookup containing the host to monitor list, you can use a search like this:

| tstats count WHERE index=* BY host
| append [ | inputlookup your_lookup.csv | eval count=0 | fields host count ]
| stats sum(count) AS total BY host
| where total=0

if you are monitoring some clusters, you should have in the lookup the indication of the clusters, something like this:

primary_host secondary_host
host1        host1bis
host2
host3        host3bis
host4

and run a little different search:

| tstats count WHERE index=* BY host
| lookup your_lookup.csv primary_host AS host OUTPUT secondary_host
| lookup your_lookup.csv seondary_host AS host OUTPUT primary_host
| append [ 
   | inputlookup your_lookup.csv 
   | rename primary_host AS host 
   | eval count=0 
   | fields host count ]
| append [ 
   | inputlookup your_lookup.csv 
   | rename secondary_host AS host 
   | eval count=0 
   | fields host count ]
| stats 
   sum(count) AS total 
   values(primary_host) AS primary_host
   values(secondary_host) AS secondary_host 
   BY host
| where total=0 AND NOT (primary_host=* secondary_host=*)

About the indexes related to the not sending hosts, it's more difficoult because you don't have, in this search the information about the indexes, the only way is to store in the lookup also the information about the indexes usually used, in this case you can add this information in the stats commands:

| tstats count WHERE index=* BY host
| lookup your_lookup.csv primary_host AS host OUTPUT secondary_host indexes
| lookup your_lookup.csv seondary_host AS host OUTPUT primary_host indexes
| append [ 
   | inputlookup your_lookup.csv 
   | rename primary_host AS host 
   | eval count=0 
   | fields host count ]
| append [ 
   | inputlookup your_lookup.csv 
   | rename secondary_host AS host 
   | eval count=0 
   | fields host count ]
| stats 
   sum(count) AS total 
   values(primary_host) AS primary_host
   values(secondary_host) AS secondary_host 
   values(indexes) AS indexes
   BY host
| where total=0 AND NOT (primary_host=* secondary_host=*)

Ciao.

Giuseppe

[ITWhisperer]

You could normalise the hostname using a lookup such that the primary and secondary of a pair resolve to the same name. Then you can look to see when the last time either pair had an event.