[New Feature] I Tried Turning CloudWatch Logs Insights Query Results into a Dynamic Lookup Table

[New Feature] I Tried Turning CloudWatch Logs Insights Query Results into a Dynamic Lookup Table

CloudWatch Logs Insights lookup tables can now be created directly from query results (queryId), with no CSV needed. Using CloudTrail SSM logs, I tried scheduled-query updates and `lookup` references via the AWS CLI.
2026.08.03

This page has been translated by machine translation. View original

Introduction

On July 31, 2026 (UTC), AWS CLI 2.36.14 shipped an update that lets you use query results as a Lookup Table in 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 joining CSV-format reference data to query results to enrich them. When you use the lookup command in a query, it looks up rows in the table using a field of the log event as the key, and adds the specified columns to the output.

Previously, Lookup Tables could only be created by uploading a CSV prepared externally. This update adds a way to create one directly from query results (queryId). Furthermore, if you specify a Lookup Table as the output destination of a scheduled query, the table can be updated automatically.

Using the AWS CLI, I will try in order: creating a table from query results, automatically updating it with a scheduled query, and referencing it with lookup.

Comparison with the Fixed CSV Method

The difference is where the contents of the table come from. With the fixed CSV method, you upload an externally prepared file to create the table, and when you want to change the contents, you have to recreate the CSV and upload it again. With the queryId method, the results of the query you ran become the contents of the table as they are. Furthermore, if you specify it as the output destination of a scheduled query, it is overwritten periodically on the CloudWatch Logs side, and the interval can be set as short as 1 minute.

With the queryId method, you can enrich log data by self-referencing it using only CloudWatch Logs, without external tools or scripts. The CSV method is suitable when bringing in data from external systems, such as an employee master or an IP address management ledger, while the queryId method is suitable when everything can be completed with data in the logs.

Test Environment

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

Run a Query and Get the queryId

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

aws logs start-query \
  --region ap-northeast-1 \
  --log-group-name /aws/cloudtrail/lookup-lab \
  --start-time <開始時刻(epoch秒)> \
  --end-time <終了時刻(epoch秒)> \
  --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 the queryId is returned.

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

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

Create a Lookup Table from the queryId

If you pass --query-id to the create-lookup-table command, you can save that query result as is 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's 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 in tableBody in CSV format.

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 lookup, described later.

Set the Lookup Table as the Output Destination of a Scheduled Query

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

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

aws logs create-scheduled-query \
  --region ap-northeast-1 \
  --name ssm-operations-lookup-refresh \
  --query-language CWLI \
  --query-string '(前述のクエリと同じ)' \
  --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 run targets the most recent hour of logs. The role passed to --execution-role-arn is a role that trusts logs.amazonaws.com. It allows query execution (logs:StartQuery and logs:GetQueryResults) and Lookup Table updates (logs:UpdateLookupTable).

About 10 hours after the setup, I retrieved the execution history with get-scheduled-query-history, and 10 entries were recorded, all with executionStatus of Complete. Checking with describe-lookup-tables, the table's lastUpdatedTime had also been updated about 1 minute after the trigger time. Here is 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 I was also able to register a scheduled query with cron(* * * * ? *). Executions at 1-minute intervals were recorded in the execution history.

Reference the Lookup Table with the lookup Command

With 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 is different, write column name as field name.

Let's attach the operator ARN, source IP, and operation count from the ssm_operations_by_user table to the SSM events in the 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:).

eventName @timestamp userArn callerIp operationCount
UpdateInstanceInformation 2026-08-03 00:16:26 assumed-role/ec2-role/i-0abcdef... 203.0.113.20 3
DescribeInstanceInformation 2026-08-03 00:13:11 assumed-role/my-role/my-session 203.0.113.10 3
ListInstanceAssociations 2026-08-03 00:12:06 assumed-role/ec2-role/i-0abcdef... 203.0.113.20 2
SendCommand 2026-08-03 00:12:06 assumed-role/my-role/my-session 203.0.113.10 1
RegisterManagedInstance 2026-08-03 00:12:06 assumed-role/aws:ec2-instance/i-0abcdef... 203.0.113.20 1

Using eventName as the key, the three columns userArn, callerIp, and operationCount were added. None of these fields are included in the original log events. What we enriched this time were the CloudTrail SSM operation logs. It can be used to get a lead on the caller when, during an investigation, only the eventName is known.

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

Summary

By combining a Lookup Table created from query results with a scheduled query, you can leave the very task of keeping the enrichment reference data up to date to CloudWatch Logs. In situations where you want to complement logs with information found in the logs themselves, the queryId method is an option that can replace the workflow of recreating and re-uploading a CSV.

Share this article

AWSのお困り事はクラスメソッドへ