Splunk Search

Using a lookup table in a base search

DATT
Path Finder

I have a lookup table that we update on daily basis with two fields that are relevant here, NAME and ID. 

NAMEID
Toronto765
Toronto1157
Toronto36

 

I need to pull data from an index and filter for these three IDs. Normally I would just do 

<base search> 
| lookup lookup_table ID OUTPUT NAME
| where NAME = "Toronto"

This works, but the search takes forever since the base search is pulling records from everywhere, and filtering afterward.  I'm wondering if it's possible to do something like this (psuedo code search incoming)

index=<index> ID IN (
|[inputlookup lookup_table where NAME = "Toronto"])

Basically, I'm trying to save time by not pulling all the records at the beginning and instead filter on a dynamic value that I have to grab from a lookup table. 

Labels (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

This is Splunk.  The answer is always yes:-)  In this case, it's much simpler than you think:

index=<index>
  [inputlookup lookup_table where NAME = "Toronto"
  | fields ID]

 

View solution in original post

Tags (1)
0 Karma

jg91
Path Finder

Try this one

 

index=<index>
  [inputlookup lookup_table | search NAME = "Toronto"
  | table ID]

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

This is Splunk.  The answer is always yes:-)  In this case, it's much simpler than you think:

index=<index>
  [inputlookup lookup_table where NAME = "Toronto"
  | fields ID]

 

Tags (1)
0 Karma

DATT
Path Finder

This worked for me!  I'm kind of surprised how close my psuedo search was to the right answer! 

 

I did modify this a little to use `search` instead of `where` so that I could add a dashboard token to this query as well.  

0 Karma

yuanliu
SplunkTrust
SplunkTrust

You can still use token in that where clause.  In fact, where in an inputlookup uses the same syntax as search term, unlike the where command that requires an eval expression.

0 Karma

jg91
Path Finder

 

 

I think we should use table instead of fields.

 

 

0 Karma

yuanliu
SplunkTrust
SplunkTrust

No difference with inputlookup. fields is usually preferred if working with an index search that fetches actual events.

0 Karma
Get Updates on the Splunk Community!

Preparing your Splunk Environment for OpenSSL3

The Splunk platform will transition to OpenSSL version 3 in a future release. Actions are required to prepare ...

Unleash Unified Security and Observability with Splunk Cloud Platform

     Now Available on Microsoft AzureThursday, March 27, 2025  |  11AM PST / 2PM EST | Register NowStep boldly ...

Splunk AppDynamics with Cisco Secure Application

Web applications unfortunately present a target rich environment for security vulnerabilities and attacks. ...