Splunk Search

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


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?

Tags (3)
0 Karma


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.

Get Updates on the Splunk Community!

.conf23 Registration is Now Open!

Time to toss the .conf-etti 🎉 —  .conf23 registration is open!   Join us in Las Vegas July 17-20 for ...

Don't wait! Accept the Mission Possible: Splunk Adoption Challenge Now and Win ...

Attention everyone! We have exciting news to share! We are recruiting new members for the Mission Possible: ...

Unify Your SecOps with Splunk Mission Control

In today’s post, I'm excited to share some recent Splunk Mission Control innovations. With Splunk Mission ...