Splunk® Cloud Services

SPL2 Search Reference

lookup command: Overview, syntax, and usage

The SPL2 lookup command enriches your source data with related information that is in a lookup dataset. Field-value pairs in your source data are matched with field-value pairs in a lookup dataset. You can either append to or replace the values in the source data with the values in the lookup dataset.

Use these links to quickly navigate to the main sections in this topic:

How the SPL2 lookup command works

The following lookup dataset, named products, contains product information and prices for a set of board and card games. For example:

product_id product_name price
DB-SG-G01 Mediocre Kingdoms 24.99
DC-SG-G02 Dream Crusher 39.99
FS-SG-G03 Final Sequel 24.99
WC-SH-G04 World of Cheese 24.99

The events contain the field productID. A search was run to summarize the total number of purchase transactions, the total number of products purchased, and the product IDs. The results are organized by ipaddress.

This is a sample of the search results. The products are identified by the productID.

ipaddress total_purchases total_products productID
107.3.146.207 72 3 DB-SG-G01

FS-SG-G03
WC-SH-G04

128.241.220.82 95 2 DB-SG-G01

DC-SG-G02

194.215.205.19 60 4 DB-SG-G01

DC-SG-G02
FS-SG-G03
WC-SH-G04

211.166.11.101 91 2 DB-SG-G01

WC-SH-G04

87.194.216.51 134 3 DC-SG-G02

FS-SG-G03
WC-SH-G04

You can use the lookup command to lookup the product_id in the products dataset, match that with the productID in the events, and return the product_name.

...| lookup products product_id AS productID OUTPUT product_name

The results would look like this:

ipaddress total_purchases total_products productID product_name
107.3.146.207 72 3 DB-SG-G01

FS-SG-G03
WC-SH-G04

Dream Crusher

Final Sequel
World of Cheese

128.241.220.82 95 2 DB-SG-G01

DC-SG-G02

Mediocre Kingdoms

Dream Crusher

194.215.205.19 60 4 DB-SG-G01

DC-SG-G02
FS-SG-G03
WC-SH-G04

Mediocre Kingdoms

Dream Crusher
Final Sequel
World of Cheese

211.166.11.101 91 2 DB-SG-G01

WC-SH-G04

Mediocre Kingdoms

World of Cheese

87.194.216.51 134 3 DC-SG-G02

FS-SG-G03
WC-SH-G04

Dream Crusher

Final Sequel
World of Cheese

Syntax

The required syntax is in bold.

lookup <lookup-dataset> (<lookup-field> [as <event-field>] )...
[ (OUTPUT | OUTPUTNEW) ( <lookup-destfield> [as <event-destfield>] )...]


Required arguments

lookup-dataset
Syntax: <string>
Description: The name of the lookup table that is defined as a dataset in the Metadata Catalog.
lookup-field
Syntax: <string>
Description: A field in the lookup dataset to match against the search results. You can specify multiple <lookup-field> values.

Optional arguments

event-field
Syntax: as <string>
Description: A field in the incoming search results to match with a field in the <lookup-dataset>. You don't need to specify the <event-field> if the name of the <event-field> is the same as the name of the <lookup-field>. You can specify multiple <event-field> values.
Default: The name specified in the <lookup-field> argument.
OUTPUT | OUTPUTNEW
Syntax: OUTPUT | OUTPUTNEW
Description: Specifies whether to replace or append values from the lookup dataset to the search results. OUTPUT replaces values in existing search results fields with values from the lookup dataset. Where there is no value in a field, OUTPUT adds values from the lookup dataset to the search results fields. OUTPUTNEW appends fields and values from the lookup dataset to the search results. If the search results already have the fields specified in <lookup-field>, the OUTPUTNEW argument only fills in missing values in those fields. OUTPUT and OUTPUTNEW must be specified in uppercase.
Default: OUTPUT
lookup-destfield
Syntax: <string>
Description: A field in the lookup table to be applied to the search results. You can specify multiple <lookup-destfield> values. Used with OUTPUT | OUTPUTNEW to replace or append field values.
Default: All fields are applied to the search results if no fields are specified.
event-destfield
Syntax: AS <string>
Description: A field in the search results. You can specify multiple <event-destfield> values. If the name of the <event-destfield> is the same as the <lookup-destfield>, you don't need to specify the <event-destfield>. The name of the <lookup-destfield> is used. Used with OUTPUT | OUTPUTNEW to replace or append field values.
Default: The value of <lookup-destfield>.

Usage

If an OUTPUT or OUTPUTNEW clause is not specified, all of the fields in the lookup table that are not the match field are used as output fields.

If the OUTPUT clause is specified, the output lookup fields overwrite existing fields with the same name.

If the OUTPUTNEW clause is specified, the lookup is not performed for events in which the output fields already exist.

Optimization

Whenever possible, perform lookups after transforming commands like stats and timechart.

A transforming command acts like a filter. Running the transforming command before the lookup can minimize the work that the lookup command must do, if the field needed for the lookup is retained by the transforming command.

Here's an example of an optimized search. The transforming command stats is before the lookup command. The stats command retains the status field, which is the field needed for the lookup.

from <dataset> where sourcetype=access_* | stats count() by status | lookup status_desc status OUTPUT description

Here's the same search, but it is not optimized. The lookup is before the transforming command stats. In this example the stats command does not retain the status field needed for the lookup. The stats command includes the description field. There is no optimization advantage to running the stats command before the lookup.

from <dataset> where sourcetype=access_* | lookup status_desc status OUTPUT description | stats count() by description

The lookup in the first search is faster because it only needs to match the results of the stats command and not all the Web access events.

Differences between SPL and SPL2

The differences between the SPL and SPL2 lookup command are described in these sections.

The command options have been removed

The command options local and update are not supported in SPL2.

Version Example
SPL ... lookup [local=<bool>] update=<bool> <lookup-dataset>...
SPL2 Not supported

The list of lookup fields must be comma-delimited

Version Example
SPL ... lookup lookupDataset key1 AS field1 key2 AS field2
SPL2 ... lookup lookupDataset key1 AS field1, key2 AS field2

The list of output fields must be comma-delimited

Version Example
SPL ... lookup lookupDataset key1 AS field1 OUTPUT out1 AS event1 out2 AS event2
SPL2 ... lookup lookupDataset key1 AS field1 OUTPUT out1 AS event1, out2 AS event2


See also

lookup command
lookup command: Examples
Last modified on 10 April, 2025
join command: Examples   lookup command: Examples

This documentation applies to the following versions of Splunk® Cloud Services: current


Please expect delayed responses to documentation feedback while the team migrates content to a new system. We value your input and thank you for your patience as we work to provide you with an improved content experience!

Was this topic useful?







You must be logged into splunk.com in order to post comments. Log in now.

Please try to keep this discussion focused on the content covered in this documentation topic. If you have a more general question about Splunk functionality or are experiencing a difficulty with Splunk, consider posting a question to Splunkbase Answers.

0 out of 1000 Characters