topic Re: Using a lookup table in a base search in Splunk Search

Using a lookup table in a base search

DATT — Fri, 04 Oct 2024 00:37:50 GMT

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

NAME	ID
Toronto	765
Toronto	1157
Toronto	36

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.

Re: Using a lookup table in a base search

yuanliu — Fri, 04 Oct 2024 00:52:48 GMT

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]

Re: Using a lookup table in a base search

jg91 — Fri, 04 Oct 2024 01:27:18 GMT

I think we should use table instead of fields.

Re: Using a lookup table in a base search

jg91 — Fri, 04 Oct 2024 01:25:03 GMT

Try this one

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

Re: Using a lookup table in a base search

yuanliu — Fri, 04 Oct 2024 01:43:15 GMT

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

Re: Using a lookup table in a base search

DATT — Fri, 04 Oct 2024 16:31:15 GMT

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.

Re: Using a lookup table in a base search

yuanliu — Fri, 04 Oct 2024 16:50:25 GMT

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.