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

Short description

Elastic Load Balancing doesn't activate access logs by default. 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.

Note: This resolution applies only to Application Load Balancers.

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, run the following command in the Query Editor:

   CREATE DATABASE alb_db

   Note: It's a best practice to create the database in the same AWS Region as the Amazon S3 bucket.
3. In the database, create an alb_logs table for the Application Load Balancer logs.
   Example query:

   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 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://<your-alb-logs-directory>/AWSLogs/<ACCOUNT-ID>/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://<your-alb-logs-directory>/AWSLogs/<ACCOUNT-ID>/elasticloadbalancing/<REGION>/${day}"
   )

   Note: In the preceding query, replace the table name and S3 locations with your table name and S3 locations. For projection.day.range, replace 2022/01/01 with a start date. For better query performance, create a table with partition projection.
4. To create a table with partitions that are stored 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://<your-alb-logs-directory>/AWSLogs/<ACCOUNT-ID>/elasticloadbalancing/<REGION>/'

   Note: In the preceding query, replace the table name and S3 locations with your table name and S3 locations.
5. (Optional) To load the partitions, run the following ALTER TABLE ADD PARTITION DDL statement:

   ALTER TABLE alb_logs_partitioned ADD PARTITION (day = '2022/05/21') LOCATION's3://<your-alb-logs-directory>/AWSLogs/<ACCOUNT-ID>/elasticloadbalancing/<REGION>/2022/05/21/'

   Note: If your table uses partition projection, then skip the load partitions step. Don't use an AWS Glue crawler on the Application Load Balancer logs.
6. In the navigation pane, under Tables, choose Preview table.
7. 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

Querying Application Load Balancer logs