How do I use Athena to analyze my Application Load Balancer access logs?

Short description

By default, Elastic Load Balancing (ELB) doesn't activate access logs. When you activate access logs, you must specify an Amazon Simple Storage Service (Amazon S3) bucket. Athena analyzes Application Load Balancer and Classic Load Balancer access logs and stores the logs in the Amazon S3 bucket.

Resolution

Create a database and table for Application Load Balancer logs

Complete the following steps:

1. Open the Athena console.

2. To create a database in the same AWS Region as the S3 bucket, run the following command in the query editor:

   CREATE DATABASE alb_db

3. To create a table in the database, use one of the following options:
   Use partition projection to create an alb_logs table for the Application Load Balancer access logs.
   -or-
   To create a table with partitions that Athena stores in the AWS Glue Data Catalog, run the following query:

   CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs_partitioned(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://amzn-s3-demo-bucket/AWSLogs/ACCOUNT-ID/elasticloadbalancing/REGION/'

   Note: Replace alb_logs_partitioned with your table name. Also, replace amzn-s3-demo-bucket with your bucket name, ACCOUNT-ID with your AWS account ID, and REGION with your Region.

4. (Optional) If your table uses partition projection, then proceed to step 5. Don't use an AWS Glue crawler on the Application Load Balancer logs. To load the partitions, run one of the following data definition language (DDL) statements:
   For Hive style partitions, run the following MSCK REPAIR TABLE DDL statement:

   MSCK REPAIR TABLE table_name

   Note: Replace table_name with your table name.
   If you don't have Hive style partitions, then run the following ALTER TABLE ADD PARTITION DDL statement:

   ALTER TABLE alb_logs_partitioned ADD PARTITION (day = '2022/05/21') LOCATION's3://amzn-s3-demo-bucket/AWSLogs/ACCOUNT-ID/elasticloadbalancing/REGION/2022/05/21/'

   Note: Replace alb_logs_partitioned with your table name. Also, replace amzn-s3-demo-bucket with your bucket name, ACCOUNT-ID with your account ID, and REGION with your Region.

5. In the navigation pane, expand Tables, and then choose Preview table.

6. In the Results window, view the data from the Application Load Balancer access logs.
   Note: To run SQL statements on the table, use the Query editor.

Related information

Access log entries

Query Application Load Balancer logs