How do I analyze my Application Load Balancer access logs using Athena?
Last updated: 2020-10-29
I want to analyze my Application Load Balancer access logs. How do I do that with Amazon Athena?
Short description
Elastic Load Balancing doesn't enable access logging by default. When you enable access logging, you specify an Amazon Simple Storage Service (Amazon S3) bucket. All Application Load Balancer and Classic Load Balancer access logs are stored in that S3 bucket. When you want to troubleshoot or analyze the performance of your load balancer, you can use Athena to analyze the access logs in S3.
Note: Although you can use Athena to analyze access logs for both Application Load Balancers and Classic Load Balancers, only Application Load Balancers are covered here.
Resolution
To analyze access logs in Athena, create a database and table:
1. Open the Athena console.
2. In the Query Editor, run a command similar to the following to create a database. It's a best practice to create the database in the same AWS Region as the S3 bucket.
create database alb_db
3. In the database that you created in previous step, create a table for the ALB logs.
4. Under Tables in the left pane, choose Preview table from the menu button that's next to the table name. The data from the ALB access logs is visible in the Results window.
5. Use the Query editor to run SQL statements on the table. You can save queries, view previous queries, or download query results in CSV format.
Example queries
In the examples below, alb_log is the table name. Modify the table name, column values, and other variables to fit your query.
Action | Query |
View the first 100 access log entries in chronological order. Use case: analysis and troubleshooting |
SELECT * |
List all client IP addresses that accessed the Application Load Balancer, and how many times they accessed the Application Load Balancer. Use case: analysis and troubleshooting |
SELECT distinct client_ip, count() as count from alb_log |
List the average amount of data (in kilobytes) that is passing through the Application Load Balancer in request/response pairs. Use case: analysis and troubleshooting |
SELECT (avg(sent_bytes)/1000.0 + avg(received_bytes)/1000.0) |
List all targets that the Application Load Balancer is routing traffic to and how many times the Application Load Balancer has routed requests to each target, by percentage distribution. Use case: identify potential target traffic imbalances |
SELECT target_ip, (Count(target_ip)* 100.0 / (Select Count(*) From alb_log)) |
List the times that a client sent a request to the Application Load Balancer and then terminated the connection to the Application Load Balancer before the idle timeout elapsed (HTTP 460 error). Use case: troubleshoot HTTP 460 errors |
SELECT * from alb_log where elb_status_code = '460' |
List the times that a client request wasn't routed because the listener rule forwarded the request to an empty target group (HTTP 503 error). Use case: troubleshoot HTTP 503 errors |
SELECT * from alb_log where elb_status_code = '503' |
List clients in descending order, by the number of times that each client visited a specified URL. Use case: analyze traffic patterns |
SELECT client_ip, elb, request_url, count(*) as count from alb_log |
List the 10 URLs that Firefox users accessed most frequently, in descending order. Use case: analyze traffic distribution and patterns |
SELECT request_url, user_agent, count(*) as count |
List clients in descending order, by the amount of data (in megabytes) that each client sent in their requests to the Application Load Balancer. Use case: analyze traffic distribution and patterns |
SELECT client_ip, sum(received_bytes/1000000.0) as client_datareceived_megabytes |
List each time in a specified date range when the target processing time was more than 5 seconds. Use case: troubleshoot latency in a specified time frame |
SELECT * from alb_log |
Related information
Did this article help?
Do you need billing or technical support?