ALB access logs in Athena - cheat sheet of queries for 5xx errors, slow targets and top clients

CloudWatch tells you the ALB returned 5xx errors. It doesn't tell you which URL, which target or which client. Access logs do. This sheet gets them into Athena and gives you queries to run during an incident.

#Enable the logs

Logs are off by default and don't cover the time before you enable them. The bucket must be in the same Region as the ALB and use SSE-S3 encryption, not KMS.

aws elbv2 describe-load-balancer-attributes --load-balancer-arn "$ALB_ARN" \
  --query "Attributes[?starts_with(Key,'access_logs')]"

aws elbv2 modify-load-balancer-attributes --load-balancer-arn "$ALB_ARN" \
  --attributes Key=access_logs.s3.enabled,Value=true \
    Key=access_logs.s3.bucket,Value=my-alb-logs \
    Key=access_logs.s3.prefix,Value=prod

The bucket policy must allow log delivery:

{
  "Effect": "Allow",
  "Principal": { "Service": "logdelivery.elasticloadbalancing.amazonaws.com" },
  "Action": "s3:PutObject",
  "Resource": "arn:aws:s3:::my-alb-logs/prod/AWSLogs/111122223333/*"
}

Note

Some older Regions use a regional Elastic Load Balancing account as the principal instead. If modify-load-balancer-attributes fails with an access error, check the "Enable access logs" page for your Region.

Each ALB node publishes a file every 5 minutes, and delivery is eventually consistent. During an incident, expect a delay before recent requests appear.

#Create the table

This is the AWS-documented table with partition projection, so you never run ALTER TABLE ADD PARTITION. Replace the bucket, prefix, account and Region in both places. Set the start date in projection.day.range to when your logs begin.

CREATE EXTERNAL TABLE IF NOT EXISTS alb_access_logs (
  type string, time string, elb string,
  client_ip string, client_port int, target_ip string, target_port int,
  request_processing_time double, target_processing_time double, response_processing_time double,
  elb_status_code int, target_status_code string,
  received_bytes bigint, sent_bytes bigint,
  request_verb string, request_url string, request_proto string,
  user_agent string, ssl_cipher string, ssl_protocol string,
  target_group_arn string, trace_id string, domain_name string, chosen_cert_arn string,
  matched_rule_priority string, request_creation_time string, actions_executed string,
  redirect_url string, lambda_error_reason string,
  target_port_list string, target_status_code_list string,
  classification string, classification_reason string, conn_trace_id string
)
PARTITIONED BY (day STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = '1',
  'input.regex' = '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) (.*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-_]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^ ]*)\" \"([^\\s]+?)\" \"([^\\s]+)\" \"([^ ]*)\" \"([^ ]*)\" ?([^ ]*)? ?( .*)?'
)
LOCATION 's3://my-alb-logs/prod/AWSLogs/111122223333/elasticloadbalancing/eu-west-1/'
TBLPROPERTIES (
  "projection.enabled" = "true",
  "projection.day.type" = "date",
  "projection.day.range" = "2026/01/01,NOW",
  "projection.day.format" = "yyyy/MM/dd",
  "projection.day.interval" = "1",
  "projection.day.interval.unit" = "DAYS",
  "storage.location.template" = "s3://my-alb-logs/prod/AWSLogs/111122223333/elasticloadbalancing/eu-west-1/${day}"
)

ALB adds log fields over time, so compare this with the current AWS page if columns look shifted. The lambda_error_reason column holds the error_reason field for all features, including WAF and authentication, not only Lambda.

Tip

Athena bills on data scanned. Always filter on day, and on elb if several load balancers log to the same path. Log times and day values are in UTC.

#Incident queries

#5xx per minute: load balancer or target?

SELECT date_trunc('minute', from_iso8601_timestamp(time)) AS minute,
       count(*) AS total,
       count_if(elb_status_code >= 500) AS elb_5xx,
       count_if(target_status_code LIKE '5%') AS target_5xx
FROM alb_access_logs
WHERE day = '2026/11/06'
  AND elb = 'app/my-alb/50dc6c495c0c9188'
GROUP BY 1
ORDER BY 1;

#Which code from which target?

SELECT elb_status_code, target_status_code, target_ip, count(*) AS hits
FROM alb_access_logs
WHERE day = '2026/11/06' AND elb_status_code >= 400
GROUP BY 1, 2, 3
ORDER BY hits DESC
LIMIT 50;

Read the result with this table:

#Errors for one URL path

SELECT url_extract_path(request_url) AS path, elb_status_code, count(*) AS hits
FROM alb_access_logs
WHERE day = '2026/11/06' AND elb_status_code >= 500
GROUP BY 1, 2
ORDER BY hits DESC
LIMIT 25;

#A time window across midnight

ISO 8601 strings sort in time order, so a string comparison works:

SELECT time, client_ip, target_ip, elb_status_code, request_url
FROM alb_access_logs
WHERE day IN ('2026/11/05', '2026/11/06')
  AND time BETWEEN '2026-11-05T23:50:00' AND '2026-11-06T00:20:00'
  AND elb_status_code >= 500
ORDER BY time;

#Trace one request

Get the X-Amzn-Trace-Id from your app log and match it here:

SELECT * FROM alb_access_logs
WHERE day = '2026/11/06' AND trace_id = 'Root=1-67891233-abcdef012345678912345678';

#Performance queries

Processing times are -1 when the request wasn't dispatched or the target didn't respond, so exclude them from averages and percentiles.

#Slowest paths

SELECT url_extract_path(request_url) AS path, count(*) AS hits,
       round(approx_percentile(target_processing_time, 0.95), 3) AS p95_s,
       round(max(target_processing_time), 3) AS max_s
FROM alb_access_logs
WHERE day = '2026/11/06' AND target_processing_time >= 0
GROUP BY 1
HAVING count(*) >= 50
ORDER BY p95_s DESC
LIMIT 25;

#One bad target

SELECT target_ip, count(*) AS hits,
       round(approx_percentile(target_processing_time, 0.95), 3) AS p95_s,
       count_if(target_status_code LIKE '5%') AS target_5xx,
       count_if(target_processing_time = -1) AS no_response
FROM alb_access_logs
WHERE day = '2026/11/06' AND target_ip <> '-'
GROUP BY 1
ORDER BY p95_s DESC;

#Traffic and security queries

#Top clients

client_ip is the immediate client, so behind a proxy you see the proxy's address.

SELECT client_ip, count(*) AS requests, sum(sent_bytes) AS bytes_out,
       count_if(elb_status_code >= 400) AS errors
FROM alb_access_logs
WHERE day = '2026/11/06'
GROUP BY 1
ORDER BY requests DESC
LIMIT 25;

#What WAF blocked

SELECT url_extract_path(request_url) AS path, client_ip, count(*) AS blocked
FROM alb_access_logs
WHERE day = '2026/11/06'
  AND actions_executed LIKE '%waf%'
  AND elb_status_code = 403
GROUP BY 1, 2
ORDER BY blocked DESC
LIMIT 25;

#Clients on old TLS before you change a security policy

SELECT ssl_protocol, ssl_cipher, count(*) AS hits, approx_distinct(client_ip) AS clients
FROM alb_access_logs
WHERE day = '2026/11/06' AND ssl_protocol <> '-'
GROUP BY 1, 2
ORDER BY hits DESC;

#Requests that break HTTP rules

SELECT classification, classification_reason, elb_status_code, count(*) AS hits
FROM alb_access_logs
WHERE day = '2026/11/06' AND classification <> '-'
GROUP BY 1, 2, 3
ORDER BY hits DESC;

#Run a query from the CLI

The workgroup needs a query result location configured.

QID=$(aws athena start-query-execution --work-group primary \
  --query-execution-context Database=default \
  --query-string "SELECT count(*) FROM alb_access_logs WHERE day = '2026/11/06'" \
  --query QueryExecutionId --output text)

aws athena get-query-execution --query-execution-id "$QID" \
  --query 'QueryExecution.Status.State' --output text

aws athena get-query-results --query-execution-id "$QID" --output table

#When the table misbehaves

#Prevent it next time

• Enable access logs on every production ALB. The AWS Config managed rule elb-logging-enabled finds the ones that were missed.
• Create the table before an incident, and save these queries with aws athena create-named-query.
• Add an S3 lifecycle rule to expire old logs, so storage doesn't grow without limit.