Amazon Athena
User Guide

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 Athena console, and modify values in LOCATION 's3://your_log_bucket/prefix/AWSLogs/your_ID/elasticloadbalancing/'. For a full list of fields present in the ALB logs, see Access Log Entries.

    Create the alb_logs table as follows.

    CREATE EXTERNAL TABLE IF NOT EXISTS alb_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 string, 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 ) 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.-]*) ([^ ]*) (.*) (.*) (.*)' ) LOCATION 's3://your-alb-logs-directory/AWSLogs/elasticloadbalancing/';
  2. Run the query in the Athena console. 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;