Study guide

Technical reference and lesson notes

Querying Application Load Balancer Access Logs with Amazon Athena

Purpose of This Lesson

This hands-on workflow demonstrates how to:

Deploy an Application Load Balancer (ALB) with AWS CloudFormation.
Configure an Amazon S3 bucket to receive ALB access logs.
Enable access logging on the load balancer.
Configure Amazon Athena to query the log files in S3 using SQL.
Interpret request data such as client IP addresses, requested URLs, and browser information.
Clean up the CloudFormation stack, S3 resources, and Athena table after testing.

The central operational pattern is load balancer logs in S3 plus serverless SQL analysis in Athena.

Key Concepts

Application Load Balancer access logs

ALB access logs record information about requests reaching the load balancer. The log data can include details such as:

Source and destination IP addresses
Requested URLs and HTTP request information
Load balancer-related details
User-agent or browser information

Access logging is configured in the load balancer’s attributes. Logs are delivered to an S3 bucket under an AWS Logs path and may take several minutes to appear.

S3 bucket policy for log delivery

The destination bucket must allow the load balancing service to write objects. The required policy is supplied with the lab materials. When applying it:

Preserve the AWS account number used by the service principal in the supplied policy.
Change the bucket ARN to match the bucket created for the exercise.
Preserve the trailing /AWSLogs/* portion required by the policy.

A successful elb-access-log-test object is a useful indication that the load balancer can write to the bucket.

Athena external tables

Amazon Athena queries data in S3 without requiring a database server. The lab creates an external table named ALB_logs that describes the fields contained in ALB access log files.

The table definition does not move the log data. It provides Athena with the schema and S3 location needed to interpret the existing files.

Athena query-result location

Before running queries, Athena requires a location for query results. In this workflow, a separate S3 bucket is created for Athena query results and selected in the Athena query editor settings.

This is distinct from the bucket that stores the ALB access logs. If the query-result location is not configured, Athena can generate errors or prevent queries from running.

AWS Implementation Workflow

1. Deploy the load balancer

Use the supplied ALB CloudFormation template from the course materials. Provide:

A stack name, such as alb-access-logs-lab
The AMI ID for the Amazon Linux instance used by the lab
The default VPC
Two subnets in that VPC

Wait for the stack and load balancer to become active before changing its attributes.

2. Create the access-log bucket

Create an S3 bucket dedicated to ALB access logs. Bucket names must be globally unique, so add a unique suffix if necessary.

Apply the supplied bucket policy, updating only the bucket ARN-specific portion. The policy must allow the load balancing service to write to the expected AWS Logs path.

3. Enable ALB access logging

In the EC2 console:

Open the load balancer configuration.
Choose Actions and then Edit load balancer attributes.
Expand Access logs.
Browse to and select the access-log S3 bucket.
Save the changes.

Generate requests against the load balancer DNS name. Requests to valid and invalid paths can help produce varied log entries, but delivery is not instantaneous.

4. Confirm delivery to S3

Refresh the bucket after waiting several minutes. The expected hierarchy includes an AWSLogs path and folders associated with Elastic Load Balancing. The test object, such as elb-access-log-test, confirms that the service has write access.

5. Configure Athena

Open the Athena query editor and configure the query-result location:

Create a separate S3 bucket for Athena results.
In Athena, choose the option to manage or browse query-result settings.
Select the results bucket.

Do this before attempting to execute SQL statements.

6. Create the external table

Use the table-creation SQL supplied with the lab. Update its LOCATION value so it points to the actual ALB log path in S3. The location needs the correct:

Access-log bucket name
/AWSLogs/ path
AWS account number
Remaining log folder structure expected by the ALB logs

The resulting table is named ALB_logs in the lab. After the statement succeeds, it appears in the Athena query editor and can be queried.

7. Query the log data

The lab demonstrates queries that:

Return the first 100 access-log entries in chronological order
Count or list clients in descending order based on visits to a specified URL
List URLs visited by users with a specified browser, such as Chrome or Safari

The browser query depends on the user-agent values actually present in the logs. A Chrome filter returns no rows when the generated requests came from Safari; changing the filter to Safari produces matching results.

Exam- or Assessment-Relevant Takeaways

S3 is the log destination; Athena is the query engine. Athena analyzes the files in place rather than requiring the logs to be loaded into a traditional database.
The bucket policy is a prerequisite. The load balancer needs permission to put objects into the destination bucket.
Do not replace the service account number in the supplied policy without a reason supported by the lab. The lecture specifically warns that this value should remain unchanged.
The bucket ARN and log path must match. A policy aimed at the wrong bucket will not authorize delivery to the intended destination.
Configure Athena’s query-result location before running queries. This is separate from configuring the ALB log destination.
The external table’s S3 LOCATION must point to the actual log prefix. An incorrect account number, bucket name, or path can result in no data being returned.
Expect delivery delay. Newly generated requests may not be visible in S3 immediately.
Use the observed user agent when filtering. A browser-specific query can legitimately return zero rows if no matching browser generated requests.
CloudFormation accelerates repeatable lab deployment. It creates the load balancer environment, while logging and Athena configuration are completed afterward.

Tool / Feature Decision Guide

Requirement	Appropriate choice	Reason
Deploy the lab’s repeatable ALB environment	CloudFormation	Uses the supplied template to create the stack consistently.
Store ALB access-log files	Amazon S3	The ALB delivers log objects to an S3 bucket.
Authorize log delivery	S3 bucket policy	Grants the load balancing service permission to put objects.
Run SQL against the log files	Amazon Athena	Provides serverless querying over data stored in S3.
Store Athena execution results	A separate S3 query-results bucket	Athena requires a configured output location for query results.
Analyze requests by browser or client	Athena SQL filters and aggregations	The log schema exposes request and user-agent information that can be filtered or counted.

Common Traps / Misconceptions

Assuming logs appear instantly: ALB log delivery can take several minutes.
Using only one S3 bucket setting: The ALB destination bucket and Athena query-result location are separate configuration concerns.
Changing every account number in the bucket policy: The supplied service account number is intentional and should be left as provided; update the bucket ARN instead.
Removing the log prefix from the ARN: The /AWSLogs/* suffix is part of the required write target and should be preserved.
Pointing Athena at the bucket root without checking the actual prefix: The table location must correspond to the ALB log hierarchy.
Treating an empty query result as a system failure: A browser-specific filter returns no rows when the request user agent does not match.
Forgetting Athena output configuration: Queries may fail or be unavailable to run until a query-result bucket is selected.
Leaving lab resources running: The CloudFormation stack, S3 buckets, and Athena table should be removed when the exercise is complete.

Real-World Engineer / Analyst Notes

Verify delivery in S3 before troubleshooting Athena. A test object can separate an access-policy or delivery problem from a table-definition problem.
When Athena returns no rows, check the S3 LOCATION, account number, bucket name, prefix, and whether enough time has passed for log delivery.
Inspect an actual log file when building or troubleshooting a table schema. The file reveals the fields and values available for analysis, including user-agent strings.
Generate representative traffic before querying. A single client and one browser will naturally produce limited results.
Keep log storage and query-result storage conceptually separate. This makes the workflow easier to understand and avoids confusing input data with Athena outputs.
Clean up both infrastructure and data resources after a temporary lab. Deleting the Athena table alone does not remove the CloudFormation stack or S3 buckets.

Quick Reference Summary

Deploy the ALB with the supplied CloudFormation template.
Create an S3 bucket for ALB access logs.
Apply the supplied bucket policy, changing the bucket ARN while preserving the service account number and /AWSLogs/* path.
Enable access logs in the ALB attributes and select the log bucket.
Generate traffic and wait for log delivery.
Confirm the AWSLogs hierarchy and test write object in S3.
Create and select a separate Athena query-result bucket.
Create the ALB_logs external table with the correct S3 LOCATION.
Query request data using Athena SQL.
Delete the table and clean up the CloudFormation and S3 resources when finished.

Flashcards

Q: A load balancer is active, but its access logs are not appearing in S3 immediately after requests are generated. What should you check first?

A: Allow several minutes for log delivery and refresh the bucket. The lecture warns that ALB log files are not necessarily available instantly.

Q: Which AWS service should you use to run SQL queries directly against ALB log files stored in S3?

A: Use Amazon Athena. It queries the S3 data without requiring a database server.

Q: What permission must the ALB log destination bucket provide?

A: Its bucket policy must allow the load balancing service to put objects into the expected AWS Logs path.

Q: When editing the supplied bucket policy, which values must be changed and which account-related value should remain unchanged?

A: Change the bucket ARN to the ARN of the new log bucket and preserve the /AWSLogs/* suffix. Leave the AWS account number in the supplied service principal policy unchanged.

Q: Why does this workflow use two S3 buckets?

A: One bucket stores ALB access logs, while the other is configured as Athena’s query-result location. Athena requires an output location in addition to the data it reads.

Q: What does the Athena external table represent in this lab?

A: ALB_logs describes the structure of the ALB log files and points Athena to their S3 location. It does not copy the files into a separate database.

Q: An Athena query succeeds but returns no rows. What path-related issue should you investigate?

A: Verify that the table’s LOCATION contains the correct log bucket, /AWSLogs/ prefix, account number, and remaining ALB folder structure.

Q: Why might a query for URLs visited by Chrome users return no results even though the ALB has logs?

A: The generated requests may have come from another browser, such as Safari. The filter must match the user-agent values actually present in the logs.

Q: What does an elb-access-log-test object indicate during setup?

A: It indicates that the load balancer successfully tested its ability to write to the S3 bucket, supporting that the bucket policy and destination are configured correctly.

Q: When would you use CloudFormation in this workflow rather than manually creating the ALB?

A: Use the supplied CloudFormation template when you want the lab’s load balancer environment deployed consistently and repeatably.

Q: What kinds of request attributes can be analyzed in ALB access logs?

A: The logs can include client and destination IP addresses, requested URLs and HTTP request details, and browser or user-agent information.

Q: What must be configured in Athena before executing the log queries?

A: A query-result location must be selected, typically an S3 bucket created for Athena results.

Q: What is the operational difference between enabling ALB access logging and creating an Athena table?

A: Enabling logging produces and delivers request files to S3. Creating the Athena table defines how those existing files are interpreted and queried.

Practice Questions

Question 1

An engineer enables ALB access logging and generates requests, but Athena returns no rows. The S3 bucket contains no ALB log files yet. What is the most appropriate next step?

A. Recreate the Athena table immediately
B. Wait for log delivery and verify the ALB can write to the bucket
C. Delete the query-result bucket
D. Change the browser filter to Chrome

Correct answer: B

The decisive clue is that the source log files are not present in S3. Delivery can take several minutes, and the bucket should be checked for the AWS Logs hierarchy or test object before troubleshooting Athena queries.

Question 2

A supplied bucket policy contains an AWS account number and a bucket ARN ending in /AWSLogs/*. The engineer is configuring a newly created log bucket. What change is required?

A. Replace the supplied account number with the account that owns the S3 bucket
B. Remove the /AWSLogs/* suffix
C. Update the bucket ARN while preserving the supplied account number and log suffix
D. Replace the policy with an IAM user policy

Correct answer: C

The lab specifically instructs the learner to preserve the account number, update the bucket ARN, and keep the /AWSLogs/* path.

Question 3

Athena will not execute a query because no query-result location has been configured. Which action resolves the problem?

A. Enable ALB access logging again
B. Select an S3 bucket as Athena’s query-result location
C. Add a second subnet to the default VPC
D. Change the ALB listener port

Correct answer: B

Athena requires an S3 output location for query results. This is separate from the S3 bucket used to receive ALB access logs.

Question 4

An Athena table exists, but queries return no data even though S3 contains ALB log files. Which troubleshooting check is most relevant?

A. Confirm that the table LOCATION matches the actual bucket and AWS Logs path
B. Replace Athena with CloudFormation
C. Remove the ALB bucket policy
D. Query only for Chrome users

Correct answer: A

The external table must point to the correct S3 location, including the bucket, AWS Logs prefix, account number, and expected folder structure.

Question 5

An analyst wants to identify which URLs were requested by Safari users. Which approach matches the demonstrated workflow?

A. Query the ALB logs table with a browser/user-agent filter for Safari
B. Change the S3 bucket policy to allow Safari
C. Create a new load balancer for each browser
D. Query only the Athena result bucket without the ALB table