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

Introduction

On July 31, 2026 (UTC), AWS CLI 2.36.14 received an update that allows query results to be used as a Lookup Table for CloudWatch Logs Insights.

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 for enriching query results by joining reference data in CSV format. Using the lookup command in a query, you can match rows in the table using log event fields as keys and add specified columns to the output.

Previously, Lookup Tables could only be created by uploading an externally prepared CSV, but a new method has been added that allows direct creation from query results (queryId). 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 content comes from. With the fixed CSV method, you upload an externally prepared file to create the table, and when you want to change the content, you recreate the CSV and re-upload it. With the queryId method, the results of an executed query become the table content directly. Furthermore, if specified as the destination for a scheduled query, CloudWatch Logs periodically overwrites it, with a minimum interval of 1 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 records, while the queryId method is appropriate when working exclusively with data 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

Executing a Query to Obtain a queryId

Execute 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'

What is returned is only the queryId.

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

start-query starts the query asynchronously. Confirm that 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 that query result 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 contains the table ARN and creation time.

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

Verify the content with get-lookup-table.

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

The content is 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 in the lookup described later.

Setting a Lookup Table as the Destination for a Scheduled Query

The content of the created table is fixed as the query results at the time of creation. To update manually, replace the entire table with update-lookup-table.

Scheduled query results are normally saved to S3 or similar. By specifying lookupTableConfiguration as the destination (--destination-configuration), you can automatically update a Lookup Table.

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"
    }
  }'

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), and here each execution targets the most recent 1 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).

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

{
    "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 1-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 table's column name, and if the field name on the log event side differs, write it as column name as field name.

Let's try adding the operator ARN, source IP, and operation count from the ssm_operations_by_user table to 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. These are all fields not included in the original log events. What we enriched here are CloudTrail SSM operation logs. This can be used to get a sense of the caller when only the eventName is known during an investigation.

However, this userArn and callerIp are representative values aggregated per eventName using latest() in the original query. When the same eventName has multiple callers, the value of the last one is attached to every row. This cannot be used to identify the caller of individual events.

Summary

By combining a Lookup Table created from query results with scheduled queries, you can entrust CloudWatch Logs with the operation of keeping reference data for enrichment up to date. In situations where you want to supplement logs with information contained within the logs themselves, the queryId method becomes an option to replace the operation of recreating and re-uploading CSVs.