Splunk Search
Highlighted

How to only return results where values for a field in one search (subsearch) are NOT found in another search?

Path Finder

Hello all,

I'm somewhat new to Splunk as a consistent user and am trying to master the magic of subsearches. I come from a SQL background and am looking to replicate the function of a correlated sub-query using a composite key. I can get simple subsearches to work, but I can't seem to understand more complex scenarios where matching the two datasets on two keys is necessary.

I have two working searches which work independently and I'm hoping I can get a good working example as a model.

UserName=Bob OR UserName=Eduardo earliest=-3d latest=-2d | table UserName EmpNum ComputerName

Bob | 1234 | FileServer.domain.com
Eduardo | 4567 | File
Server.domain.com
Bob | 1234 | WebServer.domain.com
Eduardo | 4567 | Web
Server.domain.com
Eduardo | 4567 | Email_Server.domain.com

UserName=Bob OR UserName=Eduardo earliest=-1d latest=now()| table UserName EmpNum ComputerName

Bob | 1234 | FileServer.domain.com
Eduardo | 4567 | File
Server.domain.com
Bob | 1234 | WebServer.domain.com
Eduardo | 4567 | Web
Server.domain.com
Eduardo | 4567 | EmailServer.domain.com
Bob | 1234 | Print
Server.domain.com

Essentially I need to find from a subsearch where ComputerName from list 2 does NOT exist in list 1 for the user / empnum combination. Therefore, I would expect output as:

Bob | 1234 | Print_Server.domain.com

Is it as simple as just concatenating the keys together in both sides? (e.g. UserName.Empnum) If so, how do I indicate to Splunk to use the derived field?

0 Karma
Highlighted

Re: How to only return results where values for a field in one search (subsearch) are NOT found in another search?

Esteemed Legend

Like this:

UserName=Bob OR UserName=Eduardo earliest=-3d latest=-2d NOT [search UserName=Bob OR UserName=Eduardo earliest=-1d latest=now() | fields UserName EmpNum ComputerName]| table UserName EmpNum ComputerName

View solution in original post

Highlighted

Re: How to only return results where values for a field in one search (subsearch) are NOT found in another search?

Path Finder

Does Splunk just automatically know to map the sub-search to the main search based off of field names?

0 Karma
Highlighted

Re: How to only return results where values for a field in one search (subsearch) are NOT found in another search?

Esteemed Legend

YES! Splunk is so cool! To see exactly what it does, take your subsearch, run it as the main search and tack on | format to the end of it.

0 Karma