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. |
- Calling the command without specifying the execution type is recommended for small volumes of data in the lookup (up to 50,000 events). It is also not recommended to run the command this way when
smesettings havemax_initial_query_lengreater than 10,000 - Calling the command with the
FASTtype is not recommended for lookups with more than 500,000 events - For the
ELKexecution type, it's recommended not to set a highmax_initial_query_len. Thepacksizeparameter indicates how many events are processed in each iteration. For example, ifmax_initial_query_len= 10,000, andpacksize= 5,000, then two requests are sent for a query of 10,000 events (max_initial_query_len/packsize). Ifmax_initial_query_lenis more than 20,000, it's recommended to setpacksizeto half or a third ofmax_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.
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.
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
source security_incidents-*
| search vulnerabilities="CVE-*"
| lookup type=ERGO vulnerabilities_info cve_id AS vulnerabilities OUTPUT description, severity
In the second example, a match 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, meaning the matching is optimized for RAM efficiency. The OUTPUT option indicates that the description and severity fields from the lookup table will overwrite or be added to the events, enriching them with vulnerability information.
Sample 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 query result 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 type=ERGO vulnerabilities_info cve_id AS vulnerabilities OUTPUT description, severity
Sample input data security_incidents-* with duplicate CVE-2023-5678 values 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 query result will exclude duplicate CVEs, as shown at 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 match 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 information about the malware type malware_type based on partial or complete intersection of signature lists.
Sample 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 table'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 query result 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 missing or empty. If malware_type is already populated, it is preserved.
source security_alerts-*
| search malware_info="W32*"
| lookup malware_info malware AS malware_description AS malware_description OUTPUTNEW type AS malware_type
The query result 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 |