Analyzing ELB Access Logs with Athena: Configuration and Query Examples

I recently enabled access logging for the Application Load Balancer (ELB) to swiftly analyze application behavior in case of issues like database overloads. These logs are stored in S3, and I used Athena to create a dedicated database and table for efficient log handling. This setup allows for organized and quick analyses whenever necessary.

Enabling Access Logs for AWS ELB (ALB) with Terraform

Athena Configuration

Creating the Database

Open the Athena query editor and execute the following SQL command to create a database dedicated to logging:

CREATE DATABASE IF NOT EXISTS production_logs;

Creating the Table

Referencing the document on “Create the table for ALB access logs in Athena using partition projection,” set up the table. Creating daily partitions enhances query performance, and new logs added to S3 are automatically incorporated into the table.

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' = '[Your Regex Here]'
)
LOCATION 's3://DOC-EXAMPLE-BUCKET/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/'
TBLPROPERTIES ( "projection.enabled" = "true", "projection.day.type" = "date", "projection.day.range" = "2022/01/01,NOW", "projection.day.format" = "yyyy/MM/dd", "projection.day.interval" = "1", "projection.day.interval.unit" = "DAYS", "storage.location.template" = "s3://DOC-EXAMPLE-BUCKET/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/${day}"
);

Troubleshooting Queries

Here are some queries that can be frequently used when issues occur:

Most Frequently Accessed Endpoints

SELECT request_url, COUNT(*) AS request_count
FROM alb_access_logs
GROUP BY request_url
ORDER BY request_count DESC
LIMIT 100;

Endpoints with the Longest Average Response Time

SELECT request_url, AVG(response_processing_time) AS avg_response_time
FROM alb_access_logs
GROUP BY request_url
ORDER BY avg_response_time DESC
LIMIT 100;

Request Count by Hour

SELECT date_trunc('hour', parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSSZ')) AS hour, COUNT(*) AS request_count
FROM alb_access_logs
WHERE parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSSZ') BETWEEN timestamp '2024-08-13 00:00:00.000' AND timestamp '2024-08-14 00:00:00.000'
GROUP BY date_trunc('hour', parse_datetime(time, 'yyyy-MM-dd''T''HH:mm:ss.SSSSSSZ'))
ORDER BY hour;

Analysis of Abnormal Status Codes

SELECT elb_status_code, COUNT(*) AS status_count
FROM alb_access_logs
GROUP BY elb_status_code
ORDER BY status_count DESC;

Request Analysis by Client IP

SELECT COUNT(request_verb) AS count, request_verb, client_ip
FROM alb_access_logs
GROUP BY request_verb, client_ip
LIMIT 100;

References

Discover more from Coursity

Subscribe to get the latest posts sent to your email.