I have a main search that returns to a table output of "IP,MAC,Host,Location"
I would like to do a subsearch with the MAC address, but cannot pass the MAC to the subsearch to work properly. I want to output just a simple "Yes" if it exists in the separate source.
I have looked at the documentation on fields and format, multiple questions here, however I cannot get what I think should be a simple query to work properly. Below is just a simple example...
The first search field return is MAC as you see, the subsearch field is DMAC
source=* | lookup IPInfo IP | stats values(IP), values(MAC), values(Host), values(Location) | appendcols [search=othersource where MAC=DMAC | eval MACExists="Yes" | table MAC MACExists]
Looking for an output similar to this...
IP MAC Host Location MACExists
Note that you don't actually have to use a subsearch to do this. And because subsearches are limited to returning only 100 rows, and they will self-finalize after 20 or so seconds, you probably should not be using a subsearch here.
Instead you want to try the more splunkish approach of matching both sides of the equation in the initial search, and then we stitch them together (or not, as appropriate) at searchtime.
* | lookup IPInfo IP | eval normalizedMac=if(source=="othersource",DMAC,MAC) | stats values(source) as source values(Host) as Host values(Location) as Location values(IP) as IP by normalizedMac | eval macExists=if(source=="othersource","True","False") | rename normalizedMac as MAC | table MAC HOST Location IP macExists
First, the way you have written your stats function doesn't return a table with one row per MAC address, instead it returns 4 cells, each of which contains a list of values. So it is impossible to effectively join or append subsearch results to the first search.
source=* | lookup IPInfo IP | stats count by IP MAC Host Location | eval maxExists=false | join type=outer MAC [search=othersource | eval macExists=true | fields + MAC maxExists ]
Alternately (and possibly faster)
source=* | lookup IPInfo IP | fields IP MAC Host Location | dedup IP MAC Host Location | eval maxExists=false | join type=outer MAC [search=othersource | eval macExists=true | fields + MAC macExists ] | table IP MAC Host Location macExists