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!

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