Home / AWS

AWSCheat sheetAdvanced

CloudWatch Metrics Insights and metric math cheat sheet: fleet-wide top-N queries, SEARCH, gap handling and alarms on expressions

Copy-ready Metrics Insights SQL and metric math for fleet-wide questions such as the noisiest instances, error ratios and per-AZ skew, plus get-metric-data and alarm patterns that handle missing data correctly.

Deepak Bhardwaj6 min read

Applies toAmazon CloudWatch Metrics Insights, metric math, GetMetricData, CloudWatch alarms, AWS CLI v2

When to use which

Need Use
Top N resources across a fleet, grouped or filtered by dimension Metrics Insights (SELECT ... FROM SCHEMA(...))
Ratios, sums and conditional logic over a few named metrics Metric math (m1/m2*100, IF, FILL)
Every series matching a pattern, including ones created later SEARCH() inside metric math
One alarm covering a whole fleet Alarm on a Metrics Insights query or a metric math expression

Metrics Insights only queries recent data (check the current query window and limits in the CloudWatch quotas page). For long lookbacks, use SEARCH or plain metric queries.

Metrics Insights query shape

SELECT FUNCTION(metricName)
FROM namespace | SCHEMA(namespace[, dimension1[, dimension2 ...]])
[ WHERE dimension = 'value' [AND|OR ...] ]
[ GROUP BY dimension[, ...] ]
[ ORDER BY FUNCTION() [ASC|DESC] ]
[ LIMIT n ]

The functions are AVG, COUNT, MAX, MIN and SUM. SCHEMA(...) with dimensions matches only metrics that have exactly those dimensions. A plain FROM "AWS/EC2" matches every dimension combination, which can double-count.

Fleet queries you'll reuse

-- Ten busiest EC2 instances by CPU
SELECT MAX(CPUUtilization) FROM SCHEMA("AWS/EC2", InstanceId)
GROUP BY InstanceId ORDER BY MAX() DESC LIMIT 10

-- CPU per Auto Scaling group
SELECT AVG(CPUUtilization) FROM SCHEMA("AWS/EC2", AutoScalingGroupName)
GROUP BY AutoScalingGroupName ORDER BY AVG() DESC

-- Lambda functions with the most errors
SELECT SUM(Errors) FROM SCHEMA("AWS/Lambda", FunctionName)
GROUP BY FunctionName ORDER BY SUM() DESC LIMIT 20

-- Lambda functions closest to throttling
SELECT SUM(Throttles) FROM SCHEMA("AWS/Lambda", FunctionName)
GROUP BY FunctionName ORDER BY SUM() DESC LIMIT 10

-- ALB 5xx by load balancer
SELECT SUM(HTTPCode_Target_5XX_Count) FROM SCHEMA("AWS/ApplicationELB", LoadBalancer)
GROUP BY LoadBalancer ORDER BY SUM() DESC

-- Per-AZ skew behind one ALB
SELECT SUM(RequestCount) FROM SCHEMA("AWS/ApplicationELB", AvailabilityZone, LoadBalancer)
WHERE LoadBalancer = 'app/web-prod/0123456789abcdef'
GROUP BY AvailabilityZone

-- RDS instances with the lowest free storage
SELECT MIN(FreeStorageSpace) FROM SCHEMA("AWS/RDS", DBInstanceIdentifier)
GROUP BY DBInstanceIdentifier ORDER BY MIN() ASC LIMIT 10

-- SQS queues with the oldest messages
SELECT MAX(ApproximateAgeOfOldestMessage) FROM SCHEMA("AWS/SQS", QueueName)
GROUP BY QueueName ORDER BY MAX() DESC LIMIT 10

-- DynamoDB tables with throttled requests
SELECT SUM(ThrottledRequests) FROM SCHEMA("AWS/DynamoDB", TableName, Operation)
GROUP BY TableName ORDER BY SUM() DESC LIMIT 10

Tip

You can't GROUP BY a tag directly in the query unless resource tags are enabled for Metrics Insights in your account and Region. Where that feature isn't available, put the team or environment into a custom dimension when you publish, or filter by a naming convention.

Running queries from the CLI

cat > q.json <<'EOF'
[{"Id":"top","Expression":"SELECT MAX(CPUUtilization) FROM SCHEMA(\"AWS/EC2\", InstanceId) GROUP BY InstanceId ORDER BY MAX() DESC LIMIT 10","Period":300}]
EOF

aws cloudwatch get-metric-data --metric-data-queries file://q.json \
  --start-time $(date -u -d '-3 hours' +%FT%TZ) --end-time $(date -u +%FT%TZ) \
  --query "MetricDataResults[].{Label:Label,Latest:Values[0]}" --output table

Values[0] is the newest data point, because results come back newest first by default (--scan-by TimestampDescending). A Metrics Insights query needs a Period in the query object.

Metric math expressions

Goal Expression
Error rate % 100 * m_err / m_req
Avoid divide-by-zero IF(m_req > 0, 100 * m_err / m_req, 0)
Fill gaps with zero (sparse counters) FILL(m1, 0)
Carry the last value forward FILL(m1, REPEAT)
Per-second rate from a counter RATE(m1)
Change between points DIFF(m1)
Sum of all queried metrics SUM(METRICS())
Only metrics whose Id contains a string SUM(METRICS("err"))
Period in seconds (convert sums to rates) m1 / PERIOD(m1)
Anomaly band ANOMALY_DETECTION_BAND(m1, 2)

Lambda error ratio with gap handling:

[
  {"Id":"err","MetricStat":{"Metric":{"Namespace":"AWS/Lambda","MetricName":"Errors","Dimensions":[{"Name":"FunctionName","Value":"orders-worker"}]},"Period":60,"Stat":"Sum"},"ReturnData":false},
  {"Id":"inv","MetricStat":{"Metric":{"Namespace":"AWS/Lambda","MetricName":"Invocations","Dimensions":[{"Name":"FunctionName","Value":"orders-worker"}]},"Period":60,"Stat":"Sum"},"ReturnData":false},
  {"Id":"rate","Expression":"IF(FILL(inv,0) > 0, 100 * FILL(err,0) / inv, 0)","Label":"ErrorRatePct"}
]

SEARCH: match series by pattern

SEARCH('{AWS/EC2,InstanceId} MetricName="CPUUtilization"', 'Average', 300)
SEARCH('{AWS/Lambda,FunctionName} MetricName="Errors" FunctionName="orders-"', 'Sum', 60)
SEARCH('{AWS/ApplicationELB,LoadBalancer,TargetGroup} MetricName="UnHealthyHostCount"', 'Maximum', 60)

Search terms match tokens, so FunctionName="orders-" matches names that contain orders- as a token. Use SEARCH on dashboards so new resources appear automatically. Keep in mind that one alarm can't watch every series a SEARCH returns: reduce them first with MAX(SEARCH(...)) or SUM(SEARCH(...)).

Alarms on expressions

Fleet-wide alarm on a single aggregated Metrics Insights series:

aws cloudwatch put-metric-alarm --alarm-name sqs-any-queue-stale \
  --evaluation-periods 3 --datapoints-to-alarm 3 --threshold 900 \
  --comparison-operator GreaterThanThreshold --treat-missing-data notBreaching \
  --metrics '[{"Id":"q1","Expression":"SELECT MAX(ApproximateAgeOfOldestMessage) FROM SCHEMA(\"AWS/SQS\", QueueName)","Period":300,"ReturnData":true}]' \
  --alarm-actions arn:aws:sns:eu-west-1:111122223333:oncall

Error-rate alarm on metric math:

aws cloudwatch put-metric-alarm --alarm-name orders-worker-error-rate \
  --evaluation-periods 5 --datapoints-to-alarm 3 --threshold 5 \
  --comparison-operator GreaterThanThreshold --treat-missing-data notBreaching \
  --metrics file://lambda-error-rate.json \
  --alarm-actions arn:aws:sns:eu-west-1:111122223333:oncall

In --metrics, exactly one entry must have ReturnData: true, and that's the value the alarm evaluates. Set ReturnData: false on the input metrics, as in the JSON above.

Warning

An alarm built on FILL(m, 0) never sees missing data. If the metric stops arriving because the agent died or the function was deleted, the alarm stays OK. Add a separate alarm on the raw metric with --treat-missing-data breaching for the heartbeat case.

Gotchas

Symptom Cause
Query returns nothing Dimension set doesn't match SCHEMA exactly, or the metric is older than the query window
Counts look doubled FROM namespace without SCHEMA matched multiple dimension combinations
Ratio spikes to huge values Denominator near zero; guard with IF
Alarm stuck in INSUFFICIENT_DATA Expression returns no points for periods with no traffic; use FILL or --treat-missing-data
SEARCH alarm rejected Alarm expression returns multiple series; reduce with MAX/SUM

Prevent it next time

  • Keep the queries you use most as saved Metrics Insights queries or dashboard widgets, so on-call can open them instead of rebuilding them during an incident.
  • Publish custom metrics with a small, fixed set of dimensions. That keeps SCHEMA matching predictable and query costs down.
  • Build every ratio alarm with a divide-by-zero guard and a matching heartbeat alarm.
  • Check metric names against your services' CloudWatch documentation. Some names differ between resource types, and between versions for agent-published metrics.

Related fixes