Splunk Search

How to compare to a lookup table and pull fields?

mgrosholz
Path Finder

I have an index=foo and a lookup table defined as foo2. How can I compare my index to the table to show only results matching fieldA in foo2 and pulling fieldB from foo2?

I have tried below and many variations of it but I get no results.

index=foo
[| inputlookup foo2 | fields fieldA]

index=foo
| lookup foo2 fieldA OUTPUT fieldB
Tags (1)
0 Karma
1 Solution

chrishartsock
Path Finder

Ultimately, you should be able to do:

index=foo
| lookup foo2 fieldA AS fieldA_name_in_foo OUTPUT fieldB
| where NOT isnull(fieldB)

View solution in original post

0 Karma

chrishartsock
Path Finder

Ultimately, you should be able to do:

index=foo
| lookup foo2 fieldA AS fieldA_name_in_foo OUTPUT fieldB
| where NOT isnull(fieldB)

0 Karma

mgrosholz
Path Finder

Why did you add the not null for fieldB?

0 Karma

chrishartsock
Path Finder

The lookup simply adds fieldB to events in index=foo where fieldA is matched. If fieldA is not matched, the event still shows up, but fieldB is null. Therefore the not isnull fulfills the "show only results matching fieldA in foo2" requirement.

0 Karma

mgrosholz
Path Finder

Oh, and thanks btw.

0 Karma

chrishartsock
Path Finder

Is fieldA the same name in foo and foo2?

0 Karma

chrishartsock
Path Finder

Not necessarily. But since it is different you will need to rename fieldA to what it is in foo:

index=foo
| lookup foo2 fieldA AS fieldA_name_in_foo OUTPUT fieldB

0 Karma

mgrosholz
Path Finder

I had a typo on my end. It works.

0 Karma

mgrosholz
Path Finder

Sweet we are getting somewhere! I got the output of fieldB but results are still showing all results of fieldA not just what populates compared to the lookup table. <--does that make sense?

0 Karma

mgrosholz
Path Finder

No. It is not. Should it be?

0 Karma
Get Updates on the Splunk Community!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...