lookup
Description
Allows retrieving data from a predefined lookup table. The command compares specified fields in the event and the lookup table. Upon a full match, the event will be enriched with the specified fields from the lookup table.
Syntax
lookup [dedup=<boolean>] [<execution-type>] [system=<boolean>] <lookup-name> ( <lookup-field> [AS <event-field>] )... [ OUTPUT | OUTPUTNEW (<lookup-destfield> [AS <event-destfield>] )... ]
Required Arguments
| Parameter | Syntax | Description |
|---|---|---|
lookup-name | <lookup-name> | The name of the predefined lookup. |
Optional Arguments
| Parameter | Syntax | Default | Description |
|---|---|---|---|
dedup | dedup=<boolean> | false | Indicates whether duplicates should be removed from the data compared to the lookup. |
<execution-type> | type=ELK [packsize=<integer>] | FAST | DEFAULT | Specifies the execution type of the command. The ELK type indicates that the command is executed by Elasticsearch/OpenSearch, with a default packsize of 10,000. The FAST type represents an optimized, faster execution version of the command in DEFAULT mode. |
system | system=<boolean> | false | When set to true, a system lookup is performed; otherwise, a user lookup is used. |
lookup-field | <string> | Several fields can be specified, separated by spaces. | |
event-field | <string> | The name of the field in the main result for comparison with the lookup-field. This is used when lookup-field and event-field have different names. | |
lookup-destfield | <string> | The name of the resulting field with obtained data. | |
OUTPUT | OUTPUTNEW | OUTPUT | OUTPUTNEW | OUTPUT | The OUTPUT indicator means that existing data in the source query field will be overwritten by the lookup data. The OUTPUTNEW indicator means that data will be overwritten only if the source query field is empty but has corresponding data in the lookup. |
Command Configuration Recommendations
- Executing the command without specifying the execution type is recommended for small directory data volumes (up to 50 thousand events). It is also not recommended to execute commands with
smemax_initial_query_lensettings greater than 10,000 in this manner. - Executing the command with the
FASTtype is not recommended for directories larger than 500 thousand events. - For the
ELKexecution type, it is recommended not to set a largemax_initial_query_len. Thepacksizeparameter indicates how many events will be processed per request by the engine in each iteration. That is, ifmax_initial_query_len= 10,000 andpacksize= 5,000, then during one iteration of 10 thousand events, two requests (max_initial_query_len/packsize) will be sent to Elastic. Ifmax_initial_query_lenexceeds 20 thousand, it is recommended to execute the command with apacksizeparameter two or three times smaller thanmax_initial_query_len.
Query Examples
Example №1
source tables
| search database = "hr"
| lookup my_click_db name as database OUTPUT engine
Example №2
source tables
| search database = "hr"
| lookup type=SOURCE packsize=5000 my_click_db name as database OUTPUTNEW engine