lookup
Description
Use the lookup
command to invoke field value lookups.
For information about the types of lookups you can define, see About lookups in the Knowledge Manager Manual.
Syntax
lookup [local=<bool>] [update=<bool>] <lookup-table-name> ( <lookup-field> [AS <event-field>] )... [ OUTPUT | OUTPUTNEW (<lookup-destfield> [AS <event-destfield>] )... ]
Note: The lookup command can accept multiple lookup and event fields
and destfields
. For example:
...| lookup <lookup-table-name> <lookup-field1> AS <event-field1>, <lookup-field2> AS <event-field2> OUTPUTNEW <lookup-destfield1> AS <event-destfield1>, <lookup-destfield2> AS <event-destfield2>
Required arguments
- <lookup-table-name>
- Syntax: <string>
- Description: Refers to a stanza name in the
transforms.conf
file. This stanza specifies the location of the lookup table file.
Optional arguments
- local
- Syntax: local=<bool>
- Description: If
local=true
, forces the lookup to run on the search head and not on any remote peers. - Default: false
- update
- Syntax: update=<bool>
- Description: If the lookup table is modified on disk while the search is running, real-time searches do not automatically reflect the update. To do this, specify
update=true
. This does not apply to searches that are not real-time searches. This implies that local=true. - Default: false
- <lookup-field>
- Syntax: <string>
- Description: Refers to a field in the lookup table to match against the events. You can specify multiple <lookup-field> values.
- <event-field>
- Syntax: <string>
- Description: Refers to a field the events from which to acquire the value to match in the lookup table. You can specify multiple <event-field> values.
- Default: The value of the <lookup-field>.
- <lookup-destfield>
- Syntax: <string>
- Description: Refers to a field in the lookup table to be copied into the events. You can specify multiple <lookup-destfield> values.
- <event-destfield>
- Syntax: <string>
- Description: A field in the events. You can specify multiple <event-destfield> values.
- Default: The value of the <lookup-destfield> argument.
Usage
When using the lookup
command, 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. If the OUTPUTNEW clause is specified, the lookup is not performed for events in which the output fields already exist.
Optimizing your lookup search
If you are using the lookup
command in the same pipeline as a transforming command, and it is possible to retain the field you will lookup on after the transforming command, do the lookup after the transforming command. For example, run:
sourcetype=access_* | stats count by status | lookup status_desc status OUTPUT description
and not:
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.
Basic example
1. Lookup users and return the corresponding group the user belongs to
There is a lookup table specified in a stanza named usertogroup
in the transforms.conf
file. This lookup table contains (at least) two fields, user
and group
. For each event, the following search looks up the value of the field local_user
in the table. For any entries that match, the value of the group
field in the lookup table is written to the field user_group
in the event.
... | lookup usertogroup user as local_user OUTPUT group as user_group
Extended example
2. Lookup price and vendor information and return the count for each product sold by a vendor
Note: This example uses the Buttercup Games data (tutorialdata.zip) and lookup file (prices.csv) from the Search Tutorial. In addition, this example uses the vendors.csv file. To follow along with this example with your Splunk deployment, download these files and complete the steps in the Use field lookups section of the tutorial for both the prices.csv
and the vendors.csv
files. When you create the lookup definition for the vendors.csv
file, name the lookup vendors_lookup. You can skip the step in the tutorial that makes the lookups automatic.
This example calculates the count of each product sold by each vendor.
The prices.csv
files contains the product names, price, and code. For example:
productId | product_name | price | sale_price | Code |
---|---|---|---|---|
DB-SG-G01 | Mediocre Kingdoms | 24.99 | 19.99 | A |
DC-SG-G02 | Dream Crusher | 39.99 | 24.99 | B |
FS-SG-G03 | Final Sequel | 24.99 | 16.99 | C |
WC-SH-G04 | World of Cheese | 24.99 | 19.99 | D |
The vendors.csv
file contains vendor information, such as vendor name, city, and ID. For example:
Vendor | VendorCity | VendorID | VendorLatitude | VendorLongitude | Vendor StateProvince | Vendor Country | Weight |
---|---|---|---|---|---|---|---|
Anchorage Gaming | Anchorage | 1001 | 61.17440033 | -149.9960022 | Alaska | United States | 3 |
Games of Salt Lake | Salt Lake City | 1002 | 40.78839874 | -111.9779968 | Utah | United States | 3 |
New Jack Games | New York | 1003 | 40.63980103 | -73.77890015 | New York | United States | 4 |
Seals Gaming | San Francisco | 1004 | 37.61899948 | -122.375 | California | United States | 5 |
Use the time range All time
.
The following search queries the vendor_sales.log
file, which is part of the tutorialdata.zip file. The vendor_sales.log
file contains the VendorID, Code, and AcctID fields. For example:
[05/Apr/2017:18:24:02] VendorID=5036 Code=B AcctID=6024298300471575
sourcetype=vendor_*
| stats count by Code VendorID
| lookup prices_lookup Code OUTPUTNEW product_name
The stats
command is used to calculate the count
by Code and VendorID. The prices_lookup
is used to match the Code field in each event and return the product names.
Extend the search. Use the table
command to return only the fields that you need. In this case you want the product_name
, VendorID
, and count
fields. Use the vendors_lookup
file to output all the fields in the vendors.csv
file that match the VendorID in each event.
sourcetype=vendor_* | stats count by Code VendorID | lookup prices_lookup Code OUTPUTNEW product_name | table product_name VendorID count | lookup vendors_lookup VendorID
This search produces a table displayed on the Statistics tab.
To expand the search to display the results on a map, see the geostats command.
See also
Commands:
Related topics:
About lookups in the Knowledge Manager Manual
Answers
Have questions? Visit Splunk Answers and see what questions and answers the Splunk community has using the lookup command.
localop | makecontinuous |
This documentation applies to the following versions of Splunk® Enterprise: 7.0.0, 7.0.1, 7.0.2, 7.0.3, 7.0.4, 7.0.5, 7.0.6, 7.0.7, 7.0.8, 7.0.9, 7.0.10, 7.0.11, 7.0.13
Feedback submitted, thanks!