lookup - Amazon CloudWatch Logs
Creating and managing lookup tablesQuery syntax for lookup
Services or capabilities described in AWS documentation might vary by Region. To see the differences applicable to the AWS European Sovereign Cloud Region, see the AWS European Sovereign Cloud User Guide.

lookup

Use lookup to enrich your query results with reference data from a lookup table. You can populate a lookup table with CSV data that you upload to Amazon CloudWatch Logs, or with the results of a completed or cancelled log query. When a query runs, the lookup command matches a field in your log events against a field in the lookup table and appends the specified output fields to the results.

Use lookup tables for data enrichment scenarios such as mapping user IDs to user details, product codes to product information, or error codes to error descriptions.

Creating and managing lookup tables

Before you can use the lookup command in a query, you must create a lookup table. You can create and manage lookup tables from the CloudWatch console or by using the Amazon CloudWatch Logs API.

To create a lookup table (console)

  1. Open the CloudWatch console at https://console.aws.amazon.com/cloudwatch/.

  2. In the navigation pane, choose Settings, and then choose the Logs tab.

  3. Scroll to Lookup tables and choose Manage.

  4. Choose Create lookup table.

  5. Enter a name for the lookup table. The name can contain only alphanumeric characters and underscores.

  6. (Optional) Enter a description.

  7. Upload a CSV file. The file must include a header row with column names, use UTF-8 encoding, and not exceed 10 MB.

  8. (Optional) Specify a AWS KMS key to encrypt the table data.

  9. Choose Create.

After you create a lookup table, you can view it in the CloudWatch Logs Insights query editor. Choose the Lookup tables tab to browse available tables and their fields.

To update a lookup table, select the table and choose Actions, Update. Upload a new CSV file to replace all existing content. To delete a lookup table, choose Actions, Delete.

Instead of uploading CSV data, you can also create or update a lookup table from the results of a completed or cancelled log query that you run in the Log Analytics console or with the Amazon CloudWatch Logs API. Specify the query ID with the queryId parameter of the CreateLookupTable or UpdateLookupTable API. A cancelled query populates the table with the partial results that were available when the query was stopped. You must specify either the CSV content or a query ID, but not both. Updating a table is a full replacement operation—all existing content is replaced regardless of the source. To refresh a lookup table with query results automatically on a schedule, use a scheduled query with a lookup table destination. For more information, see Configuring lookup table destinations for scheduled queries.

Note

You can create up to 100 lookup tables per account per AWS Region. CSV files can be up to 10 MB. You can also manage lookup tables by using the Amazon CloudWatch Logs API. For more information, see CreateLookupTable in the Amazon CloudWatch Logs API Reference.

Note

If the lookup table is encrypted with a KMS key, the caller must have the kms:Decrypt permission on the key (the KMS key used to encrypt the lookup table) to use the StartQuery API with a query that references that lookup table. For more information, see Encrypt lookup tables in CloudWatch Logs using AWS Key Management Service.

Query syntax for lookup

Command structure

The following shows the format of this command.

lookup table match-fields output-mode output-field[,...]

The command uses the following arguments:

  • table – The name of the lookup table to use.

  • match-fields – Specify one or more fields to match log events against the lookup table. You can use either of the following forms:

    • lookup-field as log-field [,...] – Use as when the lookup table column name differs from the log event field name. For example, ip_address as srcAddr matches the ip_address column in the lookup table against the srcAddr field in your log events.

    • lookup-field [,...] – When the log event field name is the same as the lookup table column name, you can omit as and specify the field name directly. For example, department, role matches both columns against log event fields with the same names.

    When multiple match fields are specified, a row in the lookup table must match all fields to produce a result (AND logic).

  • output-mode – Specifies how output fields are added to the results. Use one of the following:

    • OUTPUT – Adds the output fields to the results. If a field with the same name already exists in the log event, it is overwritten with the lookup table value. If no match is found, the field is set to null.

    • OUTPUTNEW – Adds the output fields to the results only if the field does not already exist in the log event. If the field already has a value, the original value is kept. If no match is found, the field is left unchanged.

  • output-field – One or more fields from the lookup table to add to the results.

Example: Enrich log events with user details

Suppose you have a log group with events that contain an id field, and a lookup table named user_data with columns id, name, email, and department. The following query enriches each log event with the user's name, email, and department from the lookup table.

fields action, status, name, email, department
| lookup user_data id OUTPUT name, email, department
Example: Use lookup with aggregation

You can use lookup output fields with aggregation functions. The following query enriches log events with user details and then counts events grouped by email address.

fields user_id, action, username, email, department
| lookup user_data user_id OUTPUT username, email, department
| stats count(*) by email
Example: Use lookup with filter

You can filter results based on fields returned by the lookup. The following query enriches log events and then filters to show only events from a specific department.

fields user_id, action
| lookup user_data user_id OUTPUT username, email, department
| filter department = "Engineering"
Example: Use OUTPUTNEW to enrich without overwriting

If your log events already contain a hostname field but it's sometimes empty, use OUTPUTNEW to fill in missing values without overwriting existing ones.

fields srcAddr, hostname
| lookup known_hosts ip_address as srcAddr OUTPUTNEW hostname, region
Example: Use lookup with multiple match fields

You can match on more than one field. The following query matches both srcAddr and dstPort against the lookup table to identify known network services.

fields @timestamp, srcAddr, dstAddr, dstPort
| lookup network_services ip_address as srcAddr, port as dstPort OUTPUT service_name, owner
| filter ispresent(service_name)
Example: Use lookup with matching field names

When your log event field names match the lookup table column names exactly, you can omit the as keyword. The following query matches both department and role fields directly against the lookup table.

fields @timestamp, department, role
| lookup employees department, role OUTPUT office, manager