lookup

• You need to enrich log events with additional metadata, such as mapping user IDs to user profiles.
• You want to correlate security logs with threat intelligence feeds.
• You need to extend OpenTelemetry traces with supplementary details, such as service dependencies.

​

For users of other query languages

index=web_logs | lookup port_lookup port AS client_port OUTPUT service_name

SELECT logs.*, ports.service_name 
FROM logs
INNER JOIN port_lookup ports ON logs.port = ports.port;

​

Usage

​

Syntax

PrimaryDataset
| lookup kind=KindOfLookup LookupTable on Conditions

​

Parameters

• PrimaryDataset: The primary dataset that you want to extend. If you expect one of the tables to contain consistently more data than the other, specify the larger table as the primary dataset.
• LookupTable: The data table containing additional data, also known as the dimension table or lookup table.
• KindOfLookup: Optionally, specifies the lookup type as leftouter or inner. The default is leftouter.
  • leftouter lookup includes all rows from the primary dataset even if they don’t match the conditions. In unmatched rows, the new fields contain nulls.
  • inner lookup only includes rows from the primary dataset if they match the conditions. Unmatched rows are excluded from the output.
• Conditions: The conditions for matching rows from PrimaryDataset to rows from LookupTable. The conditions are equality expressions that determine how Axiom matches rows from the PrimaryDataset (left side of the equality expression) with rows from the LookupTable (right side of the equality expression). The two sides of the equality expression must have the same data type.
  • To use lookup on a key column that has the same name in the primary dataset and the lookup table, simply use the field name. For example, on id.
  • To use lookup on a key column that has different names in the primary dataset and the lookup table, define the two field names in an equality expression such as on id == trace_id.
  • You can define multiple conditions. To separate conditions, use commas (,). Don’t use and. For example, on id == trace_id, span == span_id.

​

Returns

​

Use case example

let LookupTable=datatable(serviceName:string, humanreadableServiceName:string)[
	'frontend', 'Frontend',
	'frontendproxy', 'Frontend proxy',
	'flagd', 'Flagd',
	'productcatalogservice', 'Product catalog',
	'loadgenerator', 'Load generator',
	'checkoutservice', 'Checkout',
	'cartservice', 'Cart',
	'recommendationservice', 'Recommendations',
	'emailservice', 'Email',
	'adservice', 'Ads',
	'shippingservice', 'Shipping',
	'quoteservice', 'Quote',
	'currencyservice', 'Currency',
	'paymentservice', 'Payment',
	'frauddetectionservice', 'Fraud detection',
];
['otel-demo-traces']
| lookup kind=leftouter LookupTable on $left.['service.name'] == $right.serviceName
| project _time, span_id, ['service.name'], humanreadableServiceName

​

List of related operators

• join: Performs more flexible join operations, including left, right, and outer joins.
• project: Selects specific columns from a dataset, which can be used to refine the output of a lookup operation.
• union: Combines multiple datasets without requiring a key column.