Feedback:

join

Use the join operator to join tables, based on either the eventlog or OCSF schemas. See OCSF Schema Overview for more information.

The join operator also offers the match keyword to compare string fields in joins for wildcard-based matching when joining data with dynamic lookup tables. This enables flexible matching of string fields wherein "*" in the lookup table value matches any value in the join field, allowing you to filter different data combinations from search results.

Syntax

|join [join_type] ([your_subsearch] | <right_table>) on <left_field>=<right_field> output <right_field> [,<right_field>, ...]

With match keyword:

Single comparison: join left <lookup_table> on <field1> match <field2>

Multiple comparisons: join left <lookup_table> on <field1> match <field2> and <field3> match <field4>

join [join_type] <table>

join_type: inner (default), left, right, and full (or outer)

<table> specifies the table to be joined with the event log table.
<left_field>=<right_field>

Specify the field used to join the event log and the other data source table. For example, if the event log field src_ip and the data source table field is geoip, specify the following:

src_ip=geo_ip

output right_field1, right_field2, rightfield3

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

output country, city, zipcode

[your_subsearch] The subsearch embedded within the primary search, inside square brackets ( [] ). See Subsearch documentation for more information.
<field1> match <field2>

<field1> is required. The event data field (left side of the join).

<field2> is required. The lookup table field (right side of the join). This field can contain "*" as a wildcard for matching
<field1> match <field2> and <field3> match <field4>

Multiple comparisons, respectively, between

  • <field1> and <field2>

  • <field3> and <field4>

Usage

The join operator is used to join Event Search results with other data sources to correlate event log fields with enrichment fields that give you more insight into IT operations and security events.

You can use the match operator only for string fields on both sides. "*" in the lookup table acts as a wildcard and matches any value in the event field. The match operator also supports multiple comparisons, wherein each "*" acts as a wildcard for its respective field. Hence, you can have rows with one, several, or all fields set to "*", enabling highly flexible matching.

Note: The match operator does not support wildcards for numeric fields. Hence, your lookup table must contain the exact numeric values.

Example

Example 1: Filters events to Palo Alto CEF logs, then enriches each event by joining against the assetdb lookup on source IP to append the asset country and hostname.

Copy 
|where sourcetype contains "pan_cef" |join assetdb on src=ip output ip, country, hostname as asset_host

Example 2: Filters to GCP logs, selects network flow fields, then left joins against example_lookup_table matching on destination IP and port to append the matched source IP as final_output.

Copy 
|where sourcetype contains "gcp" 
|fields sourcetype, src_ip, src_port, dest_ip, dest_port
|join left example_lookup_table on dest_ip match src_ip and dest_port=dest_port output src_ip as final_output

Example 3: 

Note: Only the first 10000 result rows are considered as the output from a subsearch, which are then passed on to the primary search query. See Subsearch to understand how subsearches work.

Copy 
example_table | calc Corr_id = json_extract (message, 'svr_corr_id')
 | aggr latest(Corr_id) as correlation_id by svr_corr_id
 | where Hostname = "ASGREC05" 
 | join left [Hostname_indexes | calc username = "johndoe" | fields logs_types, Corr_id | where Hostname = "ASGREC05"] on Hostname match Hostname output indexcount_as_host as indexcount_as_host, Corr_id as correlation_id

Example 4: Counts events per source IP and retains only those enriched via lookup (matched IPs).

Note: For OCSF schema fields, array indexes start at array[1] and not array[0].

Copy 
ocsf
| where src_endpoint.ip != ''
| aggr count as cnt by src_endpoint.ip
| lookup domain_ip on src_endpoint.ip=ip output ip
| where ip is not null