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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...