Why is my Lookup table returning both matching and unmatched data corresponding to events?

[nishantmishra21]

Hi ,
I am new to Splunk, but trying to get better.

I want to hit the lookup against my events in such a way that the result should marks the result in 3 categories.

• MATCHED ( when data present in EVENT as well as LOOKUP)
• NOT MATCHED ( when data NOT present in EVENT but was there in lookup LOOKUP)
• UNIDENTIFIED ( when data present in EVENT but not present in lookup)

My lookup table looks like this:

application,sandbox,pset_name
app1,sand1,pset1
app1,sand1,pset2
app2,sand1,pset1
app2,sand2,pset2

Now suppose my events have these 2 fields:

_time,sandbox=sand1,pset_name=pset1
_time,sandbox=sand2,pset_name=pset1
_time,sandbox=sand2,pset_name=pset1

**

• RESULT

**
_time,application=app1, sandbox=sand1,pset_name=pset1,MATCH
_time,application=app1, sandbox=sand2,pset_name=pset1,UNIDENTIFIED
_time,application=app2, sandbox=sand2,pset_name=pset1,UNIDENTIFIED
NULL,application=app1,sandbox=,sand1,pset_name=pset2,NOT_MATCHED
NULL,application=app2,sandbox=sand1,pset_name=pset1,NOT_MATCHED
NULL,application=app2,sandbox=sand2,pset_name=pset2,NOT_MATCHED

I tried something like below, but getting weird result

index=index_name|lookup ia_lookup_name sandbox as sandbox,pset_name as pset output application
| eval status=if(application="", "UNIDENTIFIEDS","MATCHED")
|append [ |inputlookup ia_lookup_name | table sandbox,pset_name,application]
|dedup sandbox,pset_name
|eval status=if(status="","NOT MATCHED","status)
|table application,sandbox,pset_name,status

[woodcock · Accepted solution]

Like this:

index=index_name|lookup ia_lookup_name sandbox as sandbox,pset_name as pset output application
| eval status=if(isnull(application), "UNIDENTIFIEDS", "MATCHED")
| append [|inputlookup ia_lookup_name | table sandbox,pset_name,application | eval sourcetype="FROMLOOKUP"]
| eventstats dc(sourcetype) AS numSourcetypes BY sandbox pset
| eval status=case(isnotnull(status), status,  numSourcetypes=2, "MATCH", true(), "NOT_MATCHED")
| table application,sandbox,pset_name,status

[nishantmishra21]

Thanks. Will try this. Just to have better understanding, what eactly the distinct count did here, (sandbox and pset are unique conbination, but i see only by sandbox).

Also why it is that numsourcetype=2 in the case?

I am just trying to understand the approach

[woodcock]

Good point. You need both. I updated the answer.

[nishantmishra21]

Thanks.
And mybad for the other question in my previous comment.

It seems 'EVENTSTATS' did the jinx. I am new to this, just went through documentation. I understand it better now.
:)