Find missing hosts after referencing a lookup file.

[jason_hotchkiss]

Hello I have the following search which produces  statistics(746) in Splunk:

index=my_index sourcetype=my_st id=100 host!=10.* earliest=-1d@d
| stats values(repot) as repot dc(repot) as repost_count values(ip) as ip_address dc(ip) as ip_count by host
|table host ip_count ip_address repot_count repot

  
I am then using a lookup file to filter out unwanted hosts from the above search (which produces statitics(676) in Splunk.

| search
 [ |inputlookup my_host_list
   |table host ip_address ]
   |dedup host
   |table host ip_count ip_address repot_count repot

How would I determine the host names of the 70 missing hosts from the my_host_list lookup?

[gcusello]

Hi @jason_hotchkiss,

you could run something like this:

index=my_index sourcetype=my_st id=100 host!=10.* earliest=-1d@d
| stats 
   values(repot) as repot 
   dc(repot) as repost_count 
   values(ip) as ip_address 
   dc(ip) as ip_count 
   count
   BY host
| append [ |inputlookup my_host_list
   | eval count=0
   | fields host ip_address count ]
| stats 
   values(repot) as repot 
   dc(repot) as repost_count 
   values(ip) as ip_address 
   dc(ip) as ip_count 
   sum(count) As total
   BY host
| eval status=if(total=0,"missing","present"
| table host ip_count ip_address repot_count repot status

Ciao.

Giuseppe

[richgalloway · Accepted solution]

You're looking for hosts not in the lookup file so use the NOT keyword in the search.

| search NOT
 [ |inputlookup my_host_list
   |table host ip_address ]
   |dedup host
   |table host ip_count ip_address repot_count repot

[jason_hotchkiss]

@richgalloway - thank you.  I think I have been staring at this screen too long....