Feedback:

lookup

Use the lookup operator to query a source of content other than event logs.

Syntax

Note: For the insubnet operator to function correctly, recreate your lookup table if you created it before January 2025. This is because in lookup tables created after Jan 2025, the lookup table automatically creates <field_name>_network_start and <field_name>_network_end, which are necessary for insubnet to return the correct output.

|lookup <lookup_table> on <condition1> [and <condition2> ...] output <lookup_field> [, <lookup_field>, ...]

<lookup_table>

Name of the lookup table that was set when the lookup table was uploaded with the API.

<condition1> [and <condition2> ...]

Note: For the insubnet operator to function correctly, recreate your lookup table if you created it before January 2025. This is because in lookup tables created after Jan 2025, the lookup table automatically creates <field_name>_network_start and <field_name>_network_end, which are necessary for insubnet to return the correct output.

Specify a lookup condition using the following condition types:

  • <event_field>=<lookup_field>: Fields that join the event log and the lookup table.

    For example, if the event log field src_ip and the lookup table field is geoip, specify the following:

    src_ip=geo_ip

  • <event_field> insubnet <lookup_field>: Returns TRUE if the <event_field> falls within the subnet in the <lookup_field>; otherwise, returns FALSE.

    For example, to check if src_ip IP address falls within the src_ip_cidr subnet, specify the following:

    src_ip insubnet src_ip_cidr

  • <event_field> like <lookup_field>: Evaluate whether the <event_field> matches the pattern specified in the <lookup_field>. Specify the pattern using the calc operator.

    For example:

    |calc url = if(url='', "https://www.salesmanagopush.com/index.html", url)

    |lookup example_lookup_table on url like url

You can specify multiple conditions using the AND operator. Conditions can be of any type specified above. For example,

url like url and dest like dest and src_ip insubnet src_ip_cidr

output <lookup_field1>, <lookup_field2>

Specify the fieldnames from the lookup table to add to Event Search results. For example, if the lookup table has fields country, city, and zipcode, specify the following:

output country, city, zipcode

Usage

A lookup table is a CSV file that has enrichment data—for example, the commonly used MaxMind IP geolocation data. A lookup table can be any set of fields that you want to correlate with an event log field, enabling the lookup table data to enrich Event Search results.

See Managing Lookup Tables for information about uploading a lookup table.

The lookup operator is used to join Event Search results with the specified lookup table to correlate event log fields with enrichment fields that give you more insight into IT operations and security events.

Tip: In the Event Search text search box, enter the lookup table name with no other syntax to display the contents of the lookup table. For example, to display the contents of a lookup table named geoinfo, type geoinfo in the Event Search text box and click Search.

Examples

Example 1: Join event results with geolocation lookup data using a field match.

Copy 
geoinfo
|where sourcetype contains "pan_cef" |lookup geoinfo on src=ip output ip, country, hostname as asset_host |where country = "US"

Example 2: Join event results using subnet and pattern matching conditions.

Note: For the insubnet operator to function correctly, recreate your lookup table if you created it before January 2025. This is because in lookup tables created after Jan 2025, the lookup table automatically creates <field_name>_network_start and <field_name>_network_end, which are necessary for insubnet to return the correct output.

Copy 
example_lookup_table
|where sourcetype="ms_defender" and src_ip != "" and not src_ip contains ":"
|calc src_ip = if(src_ip startswith '54.240.' , '0.0.0.0', src_ip)
|calc dest = if(isnull(dest), "www.salesmanagopush.com", dest)
|calc url = if(url='', "https://www.salesmanagopush.com/index.html", url)
|lookup example_lookup_table on src_ip insubnet src_ip_cidr and dest like dest output src_ip_cidr, url as up_url

In the example above, the insubnet will return the correct output if the example_lookup_table lookup table was created after January 2025.

Example 3: Filters OCSF events to those with both source and destination IPs, then enriches each event by looking up the source IP against the domain_ip lookup table to append country, IP, and hostname details.

Copy 
ocsf | where src_endpoint.ip!='' and dst_endpoint.ip!='' | lookup domain_ip on src_endpoint.ip=ip output country, ip, hostname