How to compare lookup to an index?

[Manik_]

Hey all,
Looking for some assistance on this splunk search. I've looked at other examples but for some reason I'm unable to replicate that with our data set.

Currently have:

index=DB DNS="*aws.amazon.com*"
| dedup DNS
| stats count by DNS
| lookup dataFile hostname AS DNS OUTPUT hostname as matched
| eval matched=if(isnull(matched), "No Match", "Matched")
| stats sum(count) BY matched

So what this is doing is matching the Index and lookup file name DataFile by the DNS name and it just gives me the count of what matches and the count of what doesn't have a match in dataFile.

However, I'm looking for this but essentially flipped. I need the results of the lookup table "dataFile" to be the base set of data and compare that to the index named DB so that it displays the count of assets not matched in the index.

I've tried something like this:

index=DB DNS="*aws.amazon.com*"
 [|inputlookup dataFile
  | rename hostname as host
  | fields host]
| lookup dataFile hostname as DNS output hostname
| stats values(hostname) as host

but no it just keeps parsing so something is wrong here. Not sure what may be the best approach here.

[bowesmana · Accepted solution]

Use this

index=DB DNS="*aws.amazon.com*"
| dedup DNS
| stats count by DNS
| eval isIx=1
| append [
  | inputlookup dataFile
  | rename hostname as DNS
  | eval isIx=0
]
| stats max(isIx) as isIx values(count) by DNS
| eval matched=if(isIx=0, "No Match", "Matched")
| stats sum(count) BY matched

What you are doing is searching the index data, marking those as finds_in_index (isIx) and then appending the lookup file and marking them as not index finds.

Then join them together and those with isIx set to 1 were index finds, the others not.

[Manik_]

Thank you, this was great!

All I did was add a where clause for the inputlookup and it worked great to compare and filter by column

|inputlookup dataFile where hostname="*aws.amazon.com*"
| fields hostname

[bowesmana]

If the question is solved, please accept the solution so others can benefit