Amazon Athena
User Guide  | API Reference

Querying Application Load Balancer Logs

An Application Load Balancer is a load balancing option for Elastic Load Balancing that enables traffic distribution in a microservices deployment using containers. Querying Application Load Balancer logs allows you to see the source of traffic, latency, and bytes transferred to and from Elastic Load Balancing instances and backend applications.

Before you begin, enable access logging for Application Load Balancer logs to be saved to your Amazon S3 bucket.

Creating the Table for ALB Logs#

  1. Copy and paste the following DDL statement into the AWS Management Console for Athena, and modify values in LOCATION 's3://your_log_bucket/prefix/AWSLogs/your_ID/elasticloadbalancing/'
CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs (
 type string,
 time string,
 elb string,
 client_ip string,
 client_port string,
 target string,
 request_processing_time int,
 target_processing_time int,
 response_processing_time int,
 elb_status_code int,
 target_status_code string,
 received_bytes int,
 sent_bytes int,
 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
)
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]*) \"([^ ]*) ([^ ]*) ([^ ]*)\" \"([^\"]*)\" ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*)' )
LOCATION 's3://your_log_bucket/prefix/AWSLogs/your_ID/elasticloadbalancing/';
  1. Run the query in the AWS Management Console for Athena. After the query completes, Athena registers the alb_logs table, making the data in it ready for you to issue queries.

Example Queries for ALB logs#

The following query counts the number of HTTP GET requests received by the load balancer grouped by the client IP address.

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

Another query shows the URLs visited by Safari browser users.

SELECT request_url
FROM alb_logs
WHERE user_agent LIKE '%Safari%'
LIMIT 10;