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,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

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!