topic Re: Find missing hosts after referencing a lookup file. in Splunk Search

Find missing hosts after referencing a lookup file.

jason_hotchkiss — Fri, 10 Mar 2023 14:13:10 GMT

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.

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

Re: Find missing hosts after referencing a lookup file.

richgalloway — Fri, 10 Mar 2023 15:08:42 GMT

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

Re: Find missing hosts after referencing a lookup file.

jason_hotchkiss — Fri, 10 Mar 2023 15:41:04 GMT

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

Re: Find missing hosts after referencing a lookup file.

gcusello — Fri, 10 Mar 2023 16:04:05 GMT

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