How to alert when we are receiving data from hosts that are not in a lookup, or are not receiving data from hosts in the lookup?

[mlevsh]

Hi,

We have a lookup table "hostlist" of hosts that need to be present in Splunk. For example

   host
   dns1
   dns2
   dns3

We need to create an alert that will be triggered by two conditions and the result's presented in a form of
"host count":
1) 1st trigger condition: we get data from host that is not in lookup table.
For example:

  host count 
     dns   1  

2) 2nd trigger condition: we don't have data from one of the hosts in lookup table. For example

   host count
   dns3  0

When I use the following command it gives me required hosts (dns is not in the lookup "hostlist" and dns3 from the list is not in Splunk), but not sure how to add count to it (by using 0 if no events found)

|set diff [search index=index1 |stats count by host|fields host] [|inputlookup hostlist]

Result:

   host
   dns
   dns3

Thank you in advance for any suggestions

[niketn]

@mlevsh, as illustrated by Somesh Soni in his answer https://answers.splunk.com/answers/612603/how-to-search-what-values-are-missing-in-my-lookup.html . You can create a query like the following for your scenario.

| tstats count WHERE index=index1 BY host
| eval from="data"
| append 
    [| inputlookup hostlist.csv 
    | table host 
    | eval count=0 
    | eval from="lookup"] 
| stats values(from) as from sum(count) as Total by host 
| where mvcount(from)=1

Please try out and confirm.

[niketn]

@mlevsh is your issue resolved? If it is please accept the answer to mark this question as answered.

[niketn]

@mvlesh was your issue resolved? If it was then please accept the answer to mark this question as answered and help others facing similar issue.

[mlevsh]

@niketnilay ,
I used the following approach: I have hosts.csv lookup table which available to users via dashboard so they can delete or add hosts to the lookup table to keep it up-to-date.
and use the following search:

|inputlookup hosts.csv| stats count by host|eval count=0|join host type=outer [ search index="ourindex"|stats count by host]|fillnull|where count=0|fields  host count

[nickhills · Accepted solution]

I do something similar in my environment.

except my "target" list is something I build on the fly from a number of sources to build an "asset list" - assets.csv
I then run a scheduled search to populate a splunked_hosts.csv

|metadata type=hosts index=* index=_*| eval hostname=lower(host) | where (now()-((60*60*24)*10) < recentTime) |table recentTime hostname|outputlookup splunked_hosts.csv

(restrict only to hosts which have sent events in the last 10 days)

To perform the reporting I now join the two lists together:

|inputlookup assets.csv|join type=left hostname [|inputlookup splunked_hosts.csv|eval Splunked="x"]|fillnull value="-" Splunked

This adds the recentTime a host was last reported in Splunk, and adds a field called Splunked which contains "x" if its working, or "-" if its not.
You can now do counts like:

|inputlookup assets.csv|join type=left hostname [|inputlookup splunked_hosts.csv|eval Splunked="x"]|fillnull value="-" Splunked |stats count by Splunked

Or to highlight hosts which are not reporting into splunk you could use:

|inputlookup assets.csv|join type=left hostname [|inputlookup splunked_hosts.csv|eval Splunked="x"]|fillnull value="-" Splunked |search Splunked="-"

[starcher]

I downvoted this post because don't join on lookups. just use the lookup normally.

[nickhills]

Hi George, I'm interested on your comment on this - where do you see the issue? Joining on any lookup, or just between 2? In my particular case joining on lookups (which are 20k rows) seems way more performant than the alternatives.

[nickhills]

Just a follow up to note that although i have only answered part 2 of your question if you reverse the order you load the lookup files and a few minor changes, you can answer part 1 in the same way.

[niketn]

@mlevsh, refer to @somesoni2's answer for a similar query: https://answers.splunk.com/answers/612603/how-to-search-what-values-are-missing-in-my-lookup.html

[mlevsh]

@niketnilay, I have a search (right in my question), but not sure how to add count to it.
I need to get the following result:
host count
dns 1
dns3 0