How do I combine two searches and only return results from Search2 that are missing from Search1?

[lbogle]

Hello Splunkers,

I am running two separate searches, both of which are running fine. The results of these two searches are two lists of host names that match partially, which is expected. There are more in list A than in list B. I am trying to mash them together in a way that tells me which are missing in list B.

|inputlookup DNS.csv | eval hostname=upper(hostname) | join hostname type=inner[|search index=dns |stats count by host | rename host AS hostname | fields + hostname]

I also tried an outer on the join, but that's not quite doing it either.
Any suggestions on how I show which are missing from column B?
Thanks,
Lindsay

[martin_mueller]

How 'bout this?

| tstats count where index=dns by host | eval from_search = 1
| inputlookup append=t DNS.csv | fillnull from_search
| eval host = upper(coalesce(host, hostname))
| stats values(from_search) as from_search dc(from_search) as dc by host | search dc=1 from_search=1

The first row executes your subsearch, but faster, and marks the results as source B (I guess).
The second row appends your CSV to this, and marks as source A.
The third row sanitizes the host/hostname field, and implicitly handles the rename.
The fourth row checks which host is present where, and only keeps those that are present in the search but not in the CSV.

For an entirely different approach:

| tstats count where index=dns NOT [inputlookup DNS.csv | rename hostname as host | dedup host | fields host] by host

This will turn your CSV file into a negated OR'd list of host values, and only grab different hosts from the dns index. Might be trouble if your list is very long, but will be very fast if it's not.