How to run a search using a lookup to find all hosts that have no events in the index?

[xdp4]

I have a device matrix of all the hosts I want to receive data from configured in a lookup file. I'm trying to run a report that shows all the devices that I haven't received events from. This is proving to be much more difficult than I anticipated. I thought it would be as simple as running a search, deduping the hosts, outer join on the intersecting column, and isnull() on _time or _raw or some other field that wouldn't be present on hosts that don't have events.

Challenge: My lookup file has a field called host. This is the IP address of the device. Find all devices (host) in the lookup file that don't have events in the index. Doable? (I realize this is asking splunk to tell me what it doesn't know, but I would think a lookup file solves that)

[xdp4]

The similar answer isn't really relevant because it's trying to ascertain if indexers/forwarders have stopped. The thruput command doesn't appear to calculate stats on hosts forwarding events via syslog.

You would think something like this would work: | inputlookup VDC_Lookup | join type=left host [ search index=* | stats count by host ] where count < 1 but it doesn't. Neither does where isnull(count). I think it has to do with the field created by stats not being accessible by the rest of the search pipe or something. (BTW I have over 455 hosts I need to report on, which may affect certain search types)

[xdp4]

Despite my tweaks, I could not get this report to run and return valid results. After spending too long trying to make it work, I finally gave up and exported the results of |metadata into a csv and used Excel. I might continue fighting with it in my spare time simply because I don't want to continue doing it this way, but I'm not optimistic based on my experience thus far.

[cphair]

@xdp4: You've probably given up on this by now, but what exactly doesn't work--are the matching hosts in each search not joining together, or is there a count field in both the lookup and the live search that's breaking isnull, or something else? I got a working search very similar to the one you posted in your own answer, so fundamentally this technique is fine; it's likely a problem with your particular data set. Since the join field is IPs, it's probably not an issue of mismatching capitalization, but there might be stray newlines or something that's causing the join to break.

Also, since you're searching all indexes for the past day, it might be as simple as the subsearch timing out before it can return any data. Running a live search over index=* just to see what hosts are reporting in gives me the heebie jeebies. If you're on a moderately recent version of Splunk, try this instead: | tstats count where index=* by host.

[xdp4]

I'm running the search you recommend now, we'll see if it brings anything in. Hosts are just IP addresses, and I've validated it many times. ( | inputlookup VDC_Lookup) just to see the columns. The lookup file is a csv converted from and Excel file, but it works correctly if I try to find hosts reporting events, so I don't believe it's a lookup file issue. I'm curious as to what the "precious_time" stat is in your search, is that a typo?

[yannK]

Maybe missing a pipe before the "where".

Double check your lookup table columns name.
Example (with the time in epochtime)

host previous_count previous_time
AA.domain.com 10 1410539225

and try to change the search to :

index=* earliest=-1d@d | stats count AS today_count by host | join type=left host [| inputlookup VDC_Lookup | table host previous_count precious_time ] | fillnull previous_count value=0 | where previous_count > today_count

and be consistent in the way you name the hosts and update the lookup.

[yannK]

create a lookup that you update once a while with the list of hosts and latest udpate.
and use it to compare to the current host sending data.

see this similar answer http://answers.splunk.com/answers/23628/how-to-compare-a-list-of-hosts-from-a-week-to-another