Splunk Search

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

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,psetname=pset1
time,sandbox=sand2,psetname=pset1
time,sandbox=sand2,psetname=pset1

**

  • RESULT

**
time,application=app1, sandbox=sand1,psetname=pset1,MATCH
time,application=app1, sandbox=sand2,psetname=pset1,UNIDENTIFIED
time,application=app2, sandbox=sand2,psetname=pset1,UNIDENTIFIED
NULL,application=app1,sandbox=,sand1,psetname=pset2,NOTMATCHED
NULL,application=app2,sandbox=sand1,psetname=pset1,NOTMATCHED
NULL,application=app2,sandbox=sand2,psetname=pset2,NOTMATCHED

I tried something like below, but getting weird result

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

0 Karma
1 Solution

Esteemed Legend

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

View solution in original post

Esteemed Legend

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

View solution in original post

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

0 Karma

Esteemed Legend

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

0 Karma

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.
:)

0 Karma