FuseQL Lookup Operators

lookup: Retrieves specified fields from a lookup table based on matching values between your log data and the table’s primary key(s).

lookup

Retrieves specified fields from a lookup table based on matching values between your log data and the table’s primary key(s).

Enrich log data by merging it with information from pre-defined lookup tables. Lookup adds context, detail, and external data to the logs, significantly improving analysis and troubleshooting.

Syntax

lookup field1 as alias1, field2 as alias2, ... \ (1)
  from "tableName" \ (2)
  on factTableKey1 = lookupTableKey1, factTableKey2 = lookupTableKey2, ... \ (3)

text

1	`field1 as alias1, field2 as alias2, …`: Defines the fields to retrieve from the lookup table, and assigns aliases to them. Mandatory when retrieving multiple fields.
2	`from "tableName"`: Specifies the name of the lookup table.
3	`on factTableKey1 = lookupTableKey1, factTableKey2 = lookupTableKey2, …`: Establishes the join condition(s) using primary keys from both the log data (fact table) and the lookup table.

Limitations

Create lookup tables and populate them in the Kloudfuse UI before using the lookup operator.
Maximum size of a lookup table is 50 MB.
Must use compatible data types for join conditions.
Must use the lookup table’s primary key(s).

Examples

Enrich Web Server Logs with User Location Data

Provide valuable geographic context for your web traffic analysis.

When using a lookup table UserLocations with columns userID, country, and city, add the user’s country and city to the web server access logs matching on the userId field.

| lookup country as userCountry, city as userCity from "UserLocations" on userId = userID

code

The query retrieves the country and city fields from UserLocations lookup table by matching the userId field, and adds them to your logs as userCountry and userCity respectively.

Correlate application errors with error codes and descriptions.

Turn a simple error code within the original logs into a full description.

When you have a lookup table ErrorCodes with columns errorCode and errorDescription, you can add detailed error descriptions to application error logs by matching on the errorId field.

| lookup errorDescription as errorMessage from "ErrorCodes" on errorId = errorCode

code

This query adds the errorDescription from the ErrorCodes table as errorMessage to the log entries.

Add product category and region based on productID

Quickly analyse logs related to sales or product.

Use a lookup table ProductInformation with columns ProductID, Category, and Region.

| lookup Category as ProductCategory, Region as SalesRegion from "ProductInformation" on product_id = ProductID

code

Add the Category and Region to each log line as ProductCategory and SalesRegion when matching the product_id value from the log line with ProductID in the ProductInformation lookup table.