Splunk Search

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?

Builder

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

0 Karma
1 Solution

Ultra Champion

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="-"

View solution in original post

Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"

Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Legend

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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Builder

@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
0 Karma

Ultra Champion

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="-"

View solution in original post

SplunkTrust
SplunkTrust

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

0 Karma

Ultra Champion

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.

0 Karma

Ultra Champion

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.

0 Karma

Legend

@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

____________________________________________
| makeresults | eval message= "Happy Splunking!!!"
0 Karma

Builder

@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

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!