SQL for Your Logs
CloudWatch Logs Insights is an interactive query engine built directly into CloudWatch. Think of it like running a SQL query against your log files — except you never have to download the logs, set up a database, or write a parser. You write a query, select a time range, and AWS scans the relevant log data and returns results in seconds.
The query language is purpose-built for log analysis. It is not SQL, but it borrows the same mental model: filter rows, extract fields, aggregate, sort, and limit. Every query runs against one or more Log Groups, and results can be visualized as tables or time-series charts directly in the console.
Core Commands:
| Command | Purpose | Example |
|---|---|---|
filter |
Restrict results to matching lines | filter @message like /ERROR/ |
parse |
Extract a named field from unstructured text using a glob or regex | parse @message "duration=* ms" as latencyMs |
stats |
Aggregate extracted or built-in fields | stats avg(latencyMs) by bin(5m) |
sort |
Order results | sort exceptionCount desc |
limit |
Cap the number of returned rows | limit 20 |
Built-in Fields:
Every log event automatically gets several @-prefixed fields you can use without parsing:
@timestamp— when the event was ingested@message— the raw log line@logStream— which stream the event came from@duration— for Lambda REPORT lines, the function execution time in milliseconds@requestId— for Lambda, the invocation request ID
Example: Lambda p99 Latency
Lambda automatically writes a REPORT line for every invocation containing duration, billed duration, and memory usage. You can query these directly:
filter @type = "REPORT"
| stats
avg(@duration) as avgDuration,
pct(@duration, 50) as p50,
pct(@duration, 95) as p95,
pct(@duration, 99) as p99,
max(@duration) as maxDuration
by bin(5m)
| sort @timestamp desc
This query computes a full latency distribution in 5-minute buckets. The pct() function is the key — it calculates true percentiles from the raw data, not approximations.
Example: Extracting Fields from Unstructured Logs
If your application logs lines like INFO: processed order order_id=abc123 in 342ms, you can extract the duration without changing your application code:
filter @message like /processed order/
| parse @message "in *ms" as processingMs
| stats avg(processingMs), pct(processingMs, 99) by bin(10m)
The parse command uses * as a wildcard to capture the value between "in " and "ms". The captured value is stored as a temporary field (processingMs) that you can use in subsequent stats commands.
Querying Multiple Log Groups:
Logs Insights supports querying up to 50 log groups simultaneously. This is useful for cross-service analysis — for example, correlating errors in your API log group with errors in your downstream Lambda log group during the same time window.
Cost Awareness:
Logs Insights charges per GB of log data scanned. A query over a 24-hour window on a high-volume log group can scan hundreds of gigabytes. Always start with a narrow time range and expand only if needed. Use filter as early as possible in your query to reduce the data scanned — Logs Insights applies filters before scanning the full dataset when the filter targets indexed fields like @timestamp and @logStream.
A common interview question is: "How do you calculate p99 latency from raw application logs without using a third-party tool like Datadog?" The answer is CloudWatch Logs Insights using stats pct(@duration, 99). Follow up by mentioning cost awareness — scanning large time windows is expensive, so you would pair this with a Metric Filter to track p99 continuously without running ad-hoc queries.