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=SOURCE [packsize=<int>] | FAST | ERGO | DEFAULT | See Execution Modes |
system | system=<boolean> | false | When set to true, requests a system lookup, otherwise requests a user lookup. |
lookup-field | <field> | Field name for comparison. Multiple fields can be specified. Fields are separated by commas. | |
event-field | <field> | Name of the field in the main result for comparison with lookup-field. Used when the names of lookup-field and event-field do not match. | |
lookup-destfield | <field> | Name of the resulting field with data obtained from the lookup table. | |
OUTPUT | OUTPUTNEW | OUTPUT | OUTPUTNEW | OUTPUT | The OUTPUT indicator specifies that existing data in the field of the source query will be overwritten with data from the lookup. The OUTPUTNEW indicator specifies that data in the field of the source query will be overwritten if the field is empty but there is data for this field in the lookup. |
Execution Modes
DEFAULT- The default mode. Recommended for use only with a small incoming event stream and small lookupsFAST- A version of the standard algorithm optimized for speed. Recommended for use in most casesERGO- A version of the standard algorithm optimized for RAM usage. Recommended for use when comparing across multiple fieldsSOURCE- Performs matching using the storage engine's capabilities. Additionally, you can specify thepacksizevalue, which determines the batch size for queries to the storage
When loading lookups into RAM in FAST and ERGO modes, the user-defined Data Request Limit is applied. If the lookup size exceeds this limit, you can increase it or use the SOURCE mode.
SOURCE ModeFor the SOURCE execution type, it is recommended not to set a high max_initial_query_len. The packsize parameter indicates how many events will be processed by the engine per query in each iteration. For example, if max_initial_query_len = 10,000 and packsize = 5,000, then during one iteration of 10,000 events, two queries (max_initial_query_len/packsize) will be sent. If max_initial_query_len is greater than 20,000, it is recommended to run the command with a packsize parameter two or three times smaller than max_initial_query_len.
Working with Multivalue
Multivalue is the ability for an attribute to have multiple values instead of a single one. A multivalue can be thought of as a list of values. Examples of multivalue include a list of user roles.
When the lookup command is executed, both fields from search results (multivalue on the left) and fields from lookup tables (multivalue on the right) can be of the multivalue type. Thus, there are three cases where the lookup command involves multivalue:
- multivalue on the left
- multivalue on the right
- multivalue on both left and right
Multivalue Value on the left
A value from the lookup table is matched with the search result if the value from the lookup table is contained within the multivalue value of the search result.
| Left Value | Right Value | Value Intersection | Match |
|---|---|---|---|
[1, 2, 3, 4, 5] | 3 | 3 | Yes |
[1, 2, 3, 4, 5] | 6 | - | No |
Multivalue Value on the Right
A value from the lookup table is matched with the search result if the search result is contained within the multivalue value from the lookup table.
| Left Value | Right Value | Value Intersection | Match |
|---|---|---|---|
3 | [1, 2, 3, 4, 5] | 3 | Yes |
6 | [1, 2, 3, 4, 5] | - | No |
Multivalue Value on Both Left and Right
A value from the lookup table is matched with the search result if the intersection of the multivalue values on the left and right is not empty.
| Left Value | Right Value | Value Intersection | Match |
|---|---|---|---|
[1, 2, 3, 4, 5] | [3] | [3] | Yes |
[1, 2, 3, 4, 5] | [1, 2, 5] | [1, 2, 5] | Yes |
[1, 2, 3, 4, 5] | [1, 2, 6] | [1, 2] | Yes |
[1, 2, 3] | [4, 5, 6] | - | No |
[1, 2, 3] | [4] | - | No |
Query Examples
Example 1
In the first example, for each incoming event, a match is performed between the database field from the event and the name field in each row of the lookup table. If these values match, the engine field will be added to the event, with its value taken from the corresponding row of the lookup table. If the event already had an engine field, it will be overwritten with the new value.
Sample input data tables-*:
| _time | database | table_name | engine |
|---|---|---|---|
| 2025-06-05 14:30:00 | hr | employees | |
| 2025-06-05 14:31:00 | hr | departments | MyISAM |
| 2025-06-05 14:32:00 | sales | orders | InnoDB |
Lookup table my_click_db:
| name | table_name | engine | description | version | rows | size.mb |
|---|---|---|---|---|---|---|
| hr | employees | InnoDB | Employee data | 8.0.30 | 100 | 1.2 |
| hr | departments | InnoDB | Department data | 8.0.30 | 10 | 0.5 |
| sales | orders | MyISAM | Orders data | 5.7.42 | 250000 | 45.7 |
| finance | transactions | InnoDB | Financial transactions | 8.0.30 | 50000 | 8.1 |
Using the OUTPUT Option
The OUTPUT option overwrites the engine field in events with values from the lookup table, even if engine is already populated.
sm-sml source tables | search database = "hr" | lookup my_click_db name as database OUTPUT engine
The query result may be the following table:
| _time | database | table_name | engine | description | version | rows | size.mb |
| - | - | - | - | - | - | - | - |
| 2025-06-05 14:30:00 | hr | employees | InnoDB | Employee data | 8.0.30 | 100 | 1.2 |
| 2025-06-05 14:31:00 | hr | departments | InnoDB | Department data | 8.0.30 | 10 | 0.5 |
##### Using the OUTPUTNEW Option and SOURCE Execution Mode
In this example, a similar match is performed in `SOURCE` mode. This means the matching will be executed by the storage engine. The `packsize=5000` parameter indicates that queries to the storage will be sent in batches of 5000. The `OUTPUTNEW` option specifies that the `engine` field will not be overwritten if it already exists in the event.
sm-sml
source tables
| search database = "hr"
| lookup type=SOURCE packsize=5000 my_click_db name as database OUTPUTNEW engine
The query result may be the following table:
| _time | database | table_name | engine | description | version | rows | size.mb |
|---|---|---|---|---|---|---|---|
| 2025-06-05 14:30:00 | hr | employees | InnoDB | Employee data | 8.0.30 | 100 | 1.2 |
| 2025-06-05 14:31:00 | hr | departments | MyISAM | Department data | 8.0.30 | 10 | 0.5 |
Example 2
In this example, a comparison is performed between the vulnerabilities field from the event and the multivalue field cve_id in each row of the lookup table. The command is executed in ERGO mode. This means that the comparison is optimized to save memory. The OUTPUT option indicates that the description and severity fields from the lookup table overwrite or are added to the events, enriching them with vulnerability information.
source security_incidents-*
| search vulnerabilities="CVE-*"
| lookup type=ERGO vulnerabilities_info cve_id AS vulnerabilities OUTPUT description, severity
Example input data security_incidents-*:
| _time | host | vulnerabilities | affected_system |
|---|---|---|---|
| 2025-06-05 14:30:00 | VLG-01 | CVE-2023-1234 CVE-2023-5678 | WebServer |
| 2025-06-05 14:32:00 | VLG-02 | CVE-2023-5678 CVE-2024-9012 | Database |
Lookup table vulnerabilities_info:
| cve_id | description | severity |
|---|---|---|
| CVE-2023-1234 | SQL Injection Vulnerability | High |
| CVE-2023-5678 | Cross-Site Scripting (XSS) | Medium |
| CVE-2024-9012 | Remote Code Execution | Critical |
The result of the query may be the following table:
| host | affected_system | description | severity |
|---|---|---|---|
| VLG-01 | WebServer | SQL Injection Vulnerability | High |
| VLG-01 | WebServer | Cross-Site Scripting (XSS) | Medium |
| VLG-02 | Database | Cross-Site Scripting (XSS) | Medium |
| VLG-02 | Database | Remote Code Execution | Critical |
Using the dedup argument
The lookup command enriches events with vulnerability information description and severity, excluding duplicates in the vulnerabilities field before matching thanks to the dedup=true option.
source security_incidents-*
| search vulnerabilities="CVE-*"
| lookup dedup=true type=ERGO vulnerabilities_info cve_id AS vulnerabilities OUTPUT description, severity
Example input data security_incidents-* with repeated values CVE-2023-5678 in the first record, where affected_system="WebServer", and in the second record, where affected_system="Database":
| _time | host | vulnerabilities | affected_system |
|---|---|---|---|
| 2025-06-05 14:30:00 | VLG-01 | CVE-2023-1234 CVE-2023-5678 CVE-2023-5678 | WebServer |
| 2025-06-05 14:32:00 | VLG-02 | CVE-2023-5678 CVE-2024-9012 CVE-2023-5678 | Database |
In this case, the result of the query will exclude CVE duplicates, as in the beginning of example 2:
| host | affected_system | description | severity |
|---|---|---|---|
| VLG-01 | WebServer | SQL Injection Vulnerability | High |
| VLG-01 | WebServer | Cross-Site Scripting (XSS) | Medium |
| VLG-02 | Database | Cross-Site Scripting (XSS) | Medium |
| VLG-02 | Database | Remote Code Execution | Critical |
Example 3
In the third example, a comparison is performed between the multivalue field malware_description from the event and the multivalue field description in each row of the lookup table. The lookup command enriches events with malware type information malware_type based on partial or complete intersection of signature lists.
Example input data security_alerts-*:
| _time | host | malware_description | file | malware_type |
|---|---|---|---|---|
| 2025-06-05 14:30:00 | VLG-01 | W32.File.Mal W32.2C2D W32.Auto.A | mssecsvc.exe | |
| 2025-06-05 14:30:00 | VLG-02 | W32.ED01 W32.Overdrive | OLD.exe | Virus |
| 2025-06-05 14:30:00 | VLG-03 | W32.ED01 W32.Generic | Fax.exe | Worm |
Lookup table malware_info:
| description | type |
|---|---|
| W32.File.Mal W32.2C2D W32.Auto.A | Ransomware |
| W32.ED01 W32.44D7 | Worm |
Using the OUTPUT option
The OUTPUT option overwrites the malware_type field in events with values from the lookup table for all events where there is a non-empty intersection between the event's malware_description and the lookup's description, regardless of whether the malware_type field was initially populated.
source security_alerts-*
| search malware_info="W32*"
| lookup malware_info description AS malware_description OUTPUT type as malware_type
The result of the query may be the following table:
| _time | host | malware_description | file | malware_type |
|---|---|---|---|---|
| 2025-06-05 14:30:00 | VLG-01 | W32.File.Mal W32.2C2D W32.Auto.A | mssecsvc.exe | Ransomware |
| 2025-06-05 14:30:00 | VLG-02 | W32.ED01 W32.Overdrive | OLD.exe | Worm |
| 2025-06-05 14:30:00 | VLG-03 | W32.ED01 W32.Generic | Fax.exe | Worm |
Using the OUTPUTNEW option
OUTPUTNEW adds type values from the lookup table only for events where the malware_type field is absent or empty. If malware_type is already populated, it is preserved.
source security_alerts-*
| search malware_info="W32*"
| lookup malware_info description AS malware_description OUTPUTNEW type AS malware_type
The result of the query may be the following table:
| _time | host | malware_description | file | malware_type |
|---|---|---|---|---|
| 2025-06-05 14:30:00 | VLG-01 | W32.File.Mal W32.2C2D W32.Auto.A | mssecsvc.exe | Ransomware |
| 2025-06-05 14:30:00 | VLG-02 | W32.ED01 W32.Overdrive | OLD.exe | Virus |
| 2025-06-05 14:30:00 | VLG-03 | W32.ED01 W32.Generic | Fax.exe | Worm |