Amazon Athena
User Guide  | API Reference

Querying Classic Load Balancer Logs

Use Classic Load Balancer logs to analyze and understand traffic patterns to and from Elastic Load Balancing instances and backend applications. You can see the source of traffic, latency, and bytes transferred.

Before you begin to analyze the ELB logs, configure them for saving in the destination Amazon S3 bucket. For more information, see Enable Access Logs for Your Classic Load Balancer.

Creating the Table for ELB Logs#

Create the table that you can later query. This table must include the exact location of your ELB logs in Amazon S3.

To create the ELB table#

  1. Copy and paste the following DDL statement into the AWS Management Console for Athena.

  2. Modify the LOCATION S3 bucket to specify the destination of your ELB logs.

    CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs (
     request_timestamp string,
     elb_name string,
     request_ip string,
     request_port int,
     backend_ip string,
     backend_port int,
     request_processing_time double,
     backend_processing_time double,
     client_response_time double,
     elb_response_code string,
     backend_response_code string,
     received_bytes bigint,
     sent_bytes bigint,
     request_verb string,
     url string,
     protocol string,
     user_agent string,
     ssl_cipher string,
     ssl_protocol 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_log_bucket/prefix/AWSLogs/your_bucket_ID/elasticloadbalancing/';
    
  3. Run the query in the AWS Management Console for Athena. After the query completes, Athena registers the elb_logs table, making the data in it ready for queries.

Example Queries for ELB Logs#

Use a query similar to this example. It lists the backend application servers that returned a 4XX or 5XX error response code. Use the LIMIT operator to limit the number of logs to query at a time.

SELECT
 request_timestamp,
 elb_name,
 backend_ip,
 backend_response_code
FROM elb_logs
WHERE backend_response_code LIKE '4%' OR
      backend_response_code LIKE '5%'
LIMIT 100;

Use a subsequent query to sum up the response time of all the transactions grouped by the backend IP address and ELB instance name.

SELECT sum(backend_processing_time) AS
 total_ms,
 elb_name,
 backend_ip
FROM elb_logs WHERE backend_ip <> ''
GROUP BY backend_ip, elb_name
LIMIT 100;

For more information, see Analyzing Data in S3 using Athena.