Splunk Search

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

nishantmishra21
Engager

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

woodcock
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

woodcock
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

nishantmishra21
Engager

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

woodcock
Esteemed Legend

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

0 Karma

nishantmishra21
Engager

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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...