Splunk Search

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

lbogle
Contributor

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

Tags (3)
0 Karma

martin_mueller
SplunkTrust
SplunkTrust

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!

Splunk Training for All: Meet Aspiring Cybersecurity Analyst, Marc Alicea

Splunk Education believes in the value of training and certification in today’s rapidly-changing data-driven ...

Investigate Security and Threat Detection with VirusTotal and Splunk Integration

As security threats and their complexities surge, security analysts deal with increased challenges and ...

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...