Subchapter 37.8

SQL Logs Traces

references/cloudwatch-omni/query/sql-logs-traces.mdMarkdown37 KBView on GitHub

Omni Query Syntax Reference

The SQL dialect for querying logs and traces in CloudWatch Omni. Both Omni experiences — Application Observability (APM, distributed tracing, log analysis) and Agent Observability (LLM/AI agent monitoring) — use this same SQL query surface. All queries are read-only statements.

SQL Logs Traces · AWS Observability · aws/agent-toolkit-for-aws · Skills Docs

SELECT

Table Reference	Contents
`default`	Logs + traces combined
`logs.default`	Logs only — syntactic sugar for `WHERE \`@telemetry_type` = ‘logs’`
`traces.default`	Traces/spans only — syntactic sugar for `WHERE \`@telemetry_type` = ‘traces’`

Field	Description
`@timestamp`	Mandatory filter field. Every query must constrain this.
`@record`	All user-visible data as JSON. Contains the full record structure.
`@message`	Original ingestion payload. Permission-gated — may not be available to all users.
`@ingest_time`	When the record was ingested.
`@telemetry_type`	The record’s telemetry type, e.g. `'logs'` or `'traces'`.

Field	Description
`@aws.account`	The AWS account where the data was originally ingested.
`@aws.region`	The AWS region where the data was originally ingested.
`@data_source_name`	Identifies the source of the data (e.g., `"amazon_vpc"`). See AWS data sources (opens in a new tab) and third-party data sources (opens in a new tab).
`@data_source_type`	The type of data source (e.g., `"flow"`). See AWS data sources (opens in a new tab) and third-party data sources (opens in a new tab).
`@data_format`	The format of the ingested data (e.g., `"VPC_FLOW_LOGS"`).
`@logGroupName`	The CloudWatch log group name (e.g., `"/aws/vpcflowlogs/vpc-xxx"`).
`@logStream`	The CloudWatch log stream name.

-- Given ingested JSON: { "request": { "method": "GET", "url": "/api" } }
SELECT request['method'], request['url']
FROM logs.default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()

-- Given ingested JSON: { "attrs": { "http.method": "GET", "service-name": "api" } }
SELECT attrs['http.method'], attrs['service-name']
FROM logs.default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()

SELECT *
FROM "logs.default" a TABLESAMPLE (10 PERCENT)
JOIN "traces.default" b TABLESAMPLE (50 PERCENT) ON a.id = b.id
WHERE a.`@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
  AND b.`@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()

-- 'your.field.key' is a placeholder — confirm real keys with ANALYZE_FIELDS first
SELECT `@timestamp`, `@record`
FROM default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
  AND attributes['your.field.key'] = 'some-value'
ORDER BY `@timestamp` DESC

-- 'your.group.key' is a placeholder for a real field in your data
SELECT date_trunc('minute', `@timestamp`) AS time_bucket,
       attributes['your.group.key'] AS group_key,
       count(*) AS record_count
FROM default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '6 HOURS' AND NOW()
GROUP BY time_bucket, group_key
ORDER BY time_bucket DESC

SELECT `@timestamp`,
       json_get_str(body, 'error', 'message') AS error_message,
       json_get_int(body, 'error', 'code') AS error_code
FROM default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '30 MINUTE' AND NOW()
  AND json_contains(body, 'error')
ORDER BY `@timestamp` DESC

SELECT `@timestamp`, name, resource['attributes']['service.name'] AS service,
       CAST(durationNano AS DOUBLE) / 1e6 AS duration_ms_derived
FROM traces.default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
  AND resource['attributes']['service.name'] = '<service-name>'
  AND durationNano IS NOT NULL
ORDER BY CAST(durationNano AS DOUBLE) DESC
LIMIT 20

SELECT `@timestamp`, `@record`
FROM traces.default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
  AND upper(TRY_CAST(status['code'] AS VARCHAR)) IN ('2', 'ERROR', 'STATUS_CODE_ERROR')
-- narrow to the service via a discovered resource attribute, e.g.
--   AND resource['attributes']['service.name'] = '<your-service-name>'
LIMIT 100

SELECT `@timestamp`, `@record`
FROM default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
  AND TRY_CAST(attributes['your.numeric.key'] AS BIGINT) > 1000
ORDER BY TRY_CAST(attributes['your.numeric.key'] AS BIGINT) DESC
LIMIT 20

-- 'your.partition.key' is a placeholder for a real field
SELECT `@timestamp`,
       attributes['your.partition.key'] AS group_key,
       count(*) OVER (
         PARTITION BY attributes['your.partition.key']
         ORDER BY `@timestamp`
       ) AS running_count
FROM default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
ORDER BY `@timestamp` DESC
LIMIT 200

-- 'your.value.key' is a placeholder for a real numeric field
SELECT attributes['your.group.key'] AS group_key,
       approx_percentile_cont(TRY_CAST(attributes['your.value.key'] AS BIGINT), 0.50) AS p50,
       approx_percentile_cont(TRY_CAST(attributes['your.value.key'] AS BIGINT), 0.95) AS p95,
       approx_percentile_cont(TRY_CAST(attributes['your.value.key'] AS BIGINT), 0.99) AS p99
FROM default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
GROUP BY group_key
ORDER BY p99 DESC

WITH candidates AS (
  SELECT DISTINCT attributes['your.correlation.key'] AS corr_key
  FROM default
  WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
    AND TRY_CAST(attributes['your.value.key'] AS BIGINT) > 5000
)
SELECT t.`@timestamp`, t.`@record`
FROM default AS t
INNER JOIN candidates AS c
  ON t.attributes['your.correlation.key'] = c.corr_key
WHERE t.`@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
ORDER BY t.`@timestamp` ASC

One unified query over default, which holds both telemetry types, filtered by the shared field and tagged with `@telemetry_type`:

SELECT `@telemetry_type`, `@timestamp`, `@record`
FROM default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
  AND <shared_field> = '<trace-id>'
ORDER BY `@timestamp` ASC

A JOIN of logs.default with traces.default on the shared field, with the `@timestamp` filter on both sides:

SELECT l.`@timestamp` AS log_time, l.`@message`, t.name AS span_name
FROM logs.default AS l
INNER JOIN traces.default AS t
  ON l.<shared_field> = t.<shared_field>
WHERE l.`@timestamp` BETWEEN NOW() - INTERVAL

SELECT `@telemetry_type`, `@timestamp`, `@record`
FROM default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()
  AND `@telemetry_type` IN ('logs', 'traces')
ORDER BY `@timestamp` ASC

SELECT 'logs' AS source, `@message` AS detail, `@timestamp`
FROM logs.default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()

UNION ALL

SELECT 'traces' AS source, `@message` AS detail, `@timestamp`
FROM traces.default
WHERE `@timestamp` BETWEEN NOW() - INTERVAL '1 HOUR' AND NOW()

ORDER BY `@timestamp` ASC