Using a lookup table in a base search

[DATT]

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. 

[jg91]

Try this one

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

[yuanliu · Accepted solution]

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]

[DATT]

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.  

[yuanliu]

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.

[jg91]

I think we should use table instead of fields.

[yuanliu]

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