[New Feature] Using CloudWatch Logs Insights Query Results as a Dynamic Lookup Table

Introduction

On July 31, 2026 (UTC), AWS CLI 2.36.14 was updated to allow query results to be used as a Lookup Table in CloudWatch Logs Insights. From the 2.36.14 changelog, extracting only the logs entry:

curl -s https://raw.githubusercontent.com/aws/aws-cli/2.36.14/.changes/2.36.14.json \
  | jq '.[] | select(.category == "``logs``")'

{
  "category": "``logs``",
  "description": "Amazon CloudWatch Logs now lets you create and update lookup tables directly from CloudWatch Logs query results by passing a queryId, and configure a lookup table as a scheduled query destination so it refreshes automatically with the latest query results on each run.",
  "type": "api-change"
}

The CloudWatch Logs Insights Lookup Table is a feature that joins CSV reference data to query results for enrichment. Using the lookup command within a query, you can use a log event field as a key to look up rows in a table and add specified columns to the output.

Previously, Lookup Tables could only be created by uploading an externally prepared CSV, but now a method of creating them directly from query results (queryId) has been added. Furthermore, by specifying a Lookup Table as the destination for a scheduled query, the table can be automatically updated.

• lookup command (CloudWatch Logs Insights query syntax)
• create-lookup-table (AWS CLI command reference)
• Automating log analysis with scheduled queries

We will sequentially try out creation from query results, automatic updates with scheduled queries, and referencing with lookup using the AWS CLI.

Comparison with the Fixed CSV Method

The difference lies in where the table contents come from. With the fixed CSV method, you create a table by uploading an externally prepared file, and when you want to change the contents, you recreate the CSV and re-upload it. With the queryId method, the results of an executed query become the table contents directly. Furthermore, if specified as the destination for a scheduled query, CloudWatch Logs will periodically overwrite the table, with a minimum interval of one minute.

With the queryId method, you can self-reference and enrich log data using only CloudWatch Logs, without involving external tools or scripts. The CSV method is suitable when bringing in data from external systems such as employee master data or IP address management ledgers, while the queryId method is appropriate when the data is self-contained within the logs.

Verification Environment

• Region: ap-northeast-1
• CloudTrail log output destination: SSM operation logs to the /aws/cloudtrail/lookup-lab log group
• Source of operation logs: EC2 (Amazon Linux 2023 / t3.micro) with an SSM role attached, executing SSM Run Command

Running a Query to Obtain a queryId

Run the query that will serve as the source data for the Lookup Table. Here, we aggregate the operator ARN, source IP, and count for each eventName from CloudTrail logs.

aws logs start-query \
  --region ap-northeast-1 \
  --log-group-name /aws/cloudtrail/lookup-lab \
  --start-time <start time (epoch seconds)> \
  --end-time <end time (epoch seconds)> \
  --query-string '
fields userIdentity.arn, sourceIPAddress, eventName, eventSource
| filter eventSource = "ssm.amazonaws.com"
| filter ispresent(userIdentity.arn)
| stats count(*) as operationCount,
        latest(userIdentity.arn) as userArn,
        latest(sourceIPAddress) as callerIp
  by eventName'

Only a queryId is returned.

{
    "queryId": "0d00be57-b126-471f-8bd2-c7d692167e93"
}

start-query starts the query asynchronously. Confirm that the status has become Complete with get-query-results before proceeding.

Creating a Lookup Table from a queryId

By passing --query-id to the create-lookup-table command, you can save the query results directly as a Lookup Table.

aws logs create-lookup-table \
  --region ap-northeast-1 \
  --lookup-table-name ssm_operations_by_user \
  --query-id 0d00be57-b126-471f-8bd2-c7d692167e93 \
  --description "SSM operations grouped by eventName with caller info" \
  --tags Project=lookup-table-lab

The response includes the table ARN and creation time.

{
    "lookupTableArn": "arn:aws:logs:ap-northeast-1:123456789012:lookup-table:ssm_operations_by_user",
    "createdAt": 1785715999458
}

Check the contents with get-lookup-table.

aws logs get-lookup-table \
  --region ap-northeast-1 \
  --lookup-table-name ssm_operations_by_user

The contents are stored in CSV format in tableBody.

eventName,operationCount,userArn,callerIp
SendCommand,1,arn:aws:sts::123456789012:assumed-role/my-role/my-session,203.0.113.10
ListInstanceAssociations,2,arn:aws:sts::123456789012:assumed-role/ec2-role/i-0abcdef1234567890,203.0.113.20
RegisterManagedInstance,1,arn:aws:sts::123456789012:assumed-role/aws:ec2-instance/i-0abcdef1234567890,203.0.113.20
UpdateInstanceInformation,3,arn:aws:sts::123456789012:assumed-role/ec2-role/i-0abcdef1234567890,203.0.113.20
DescribeInstanceInformation,3,arn:aws:sts::123456789012:assumed-role/my-role/my-session,203.0.113.10

The column names are the same as the output of stats ... by eventName, and eventName becomes the join key for the lookup command described later.

Configuring a Lookup Table as the Destination for a Scheduled Query

The contents of the created table are fixed at the query results from the time of creation. To update manually, use update-lookup-table to replace the entire table.

Scheduled query results are normally saved to S3 or similar. By specifying lookupTableConfiguration in the destination configuration (--destination-configuration), the Lookup Table can be automatically updated.

aws logs create-scheduled-query \
  --region ap-northeast-1 \
  --name ssm-operations-lookup-refresh \
  --query-language CWLI \
  --query-string '(same query as above)' \
  --log-group-identifiers "arn:aws:logs:ap-northeast-1:123456789012:log-group:/aws/cloudtrail/lookup-lab" \
  --schedule-expression "cron(0 * * * ? *)" \
  --start-time-offset 3600 \
  --end-time-offset 0 \
  --execution-role-arn "arn:aws:iam::123456789012:role/scheduled-query-role" \
  --destination-configuration '{
    "lookupTableConfiguration": {
      "tableName": "ssm_operations_by_user",
      "roleArn": "arn:aws:iam::123456789012:role/scheduled-query-role"
    }
  }'

The state was returned as ENABLED.

{
    "scheduledQueryArn": "arn:aws:logs:ap-northeast-1:123456789012:scheduled-query:4c77af92-6c6b-4894-b06f-43927d518ce8",
    "state": "ENABLED"
}

With the cron(0 * * * ? *) specification, it runs every hour. --start-time-offset and --end-time-offset are offsets from the execution time (in seconds); here, each execution targets the most recent one hour of logs. The role passed to --execution-role-arn is a role that trusts logs.amazonaws.com. It grants permissions for query execution (logs:StartQuery, logs:GetQueryResults) and Lookup Table updates (logs:UpdateLookupTable).

When retrieving the execution history with get-scheduled-query-history approximately 10 hours after setup, 10 records were recorded, all with an executionStatus of Complete. When checking with describe-lookup-tables, the table's lastUpdatedTime was also updated approximately one minute after the trigger time. An excerpt of one history record:

{
    "executionStatus": "Complete",
    "destinations": [
        {
            "destinationType": "LOOKUP_TABLE",
            "destinationIdentifier": "ssm_operations_by_user",
            "status": "COMPLETE"
        }
    ]
}

--schedule-expression supports minute-level precision, and a scheduled query specifying cron(* * * * ? *) could also be registered. The execution history recorded executions at one-minute intervals.

Referencing a Lookup Table with the lookup Command

Using the lookup command, you can join Lookup Table data within a query. The syntax is lookup <table name> <join key> OUTPUT <fields to retrieve>. The join key is specified by the column name in the table; if the field name on the log event side differs, write it as column name as field name.

Let's add the operator ARN, source IP, and operation count from the ssm_operations_by_user table to the SSM events in CloudTrail logs.

fields eventName, @timestamp
| filter eventSource = "ssm.amazonaws.com"
| lookup ssm_operations_by_user eventName OUTPUT userArn, callerIp, operationCount
| fields eventName, @timestamp, userArn, callerIp, operationCount
| filter ispresent(userArn)
| sort @timestamp desc
| limit 10

The results are as follows (excerpt; userArn omits arn:aws:sts::123456789012:).

Using eventName as the key, three columns — userArn, callerIp, and operationCount — were added. None of these fields are included in the original log events. What was enriched here is the CloudTrail SSM operation log. This can be useful during investigations when only the eventName is known and you want to get a lead on the caller.

However, this userArn and callerIp are representative values aggregated per eventName using latest() in the original query. If there are multiple callers for the same eventName, all rows will have the value from the last one. This cannot be used to identify the caller of individual events.

Summary

By combining a Lookup Table created from query results with a scheduled query, you can delegate to CloudWatch Logs the operation of keeping the enrichment reference data up to date. In situations where you want to supplement logs with information already in the logs, the queryId method becomes an alternative to the operation of recreating and re-uploading CSVs.