ALB Log Analyzer

Analyze AWS Application Load Balancer (ALB) logs using DuckDB for fast, flexible S3-based queries.

Quick Start

Secure Method (Recommended for Named Profiles)

For AWS named profiles, use these secure scripts that keep credentials private:

bash

# One-time setup (credentials read from ~/.aws/credentials)
./scripts/setup_with_profile.sh your-profile-name

# Load logs (profile name only, credentials stay secure)
./scripts/load_with_profile.sh your-profile-name 's3://bucket/path/**/*.log.gz'

# Analyze
./scripts/analyze.sh errors
./scripts/analyze.sh performance

Security: Only profile names appear in commands and logs. Credentials are read internally from ~/.aws/credentials.

Standard Method

Basic workflow for ALB log analysis:

•Setup DuckDB with AWS extensions
•Load logs from S3 into a table
•Run analysis queries (errors, performance, traffic, etc.)
•Export results or create custom queries

Prerequisites

Ensure DuckDB is installed:

bash

# Install DuckDB (if not already installed)
brew install duckdb  # macOS
# or download from https://duckdb.org/docs/installation/

AWS credentials must be configured. See AWS Credentials Setup section below for details.

Database File Location

By default, the DuckDB database file is stored at:

code

/tmp/alb-log-analyzer-${USER}/alb_analysis.duckdb

Important: The database file is automatically deleted when you run setup or load commands. This ensures:

•Clean start with each analysis
•No accumulation of old data in /tmp
•Reduced disk space usage

This temporary location also:

•Keeps your skill directory clean
•Prevents data from being packaged with the skill
•May be cleared on system restart

To use a custom location, set the DB_FILE environment variable:

bash

export DB_FILE=/path/to/custom/database.duckdb

Setup

Method 1: Using CREDENTIAL_CHAIN (Recommended)

This method automatically detects AWS credentials from environment variables, ~/.aws/credentials, or IAM roles:

bash

./scripts/analyze.sh setup

Method 2: Using Environment Variables

If CREDENTIAL_CHAIN doesn't work, explicitly set environment variables.

For default profile:

bash

# Set AWS credentials
export AWS_ACCESS_KEY_ID=your_key_id
export AWS_SECRET_ACCESS_KEY=your_secret_key
export AWS_DEFAULT_REGION=ap-northeast-1

# Setup DuckDB
./scripts/analyze.sh setup-env

For named profile:

bash

# Export credentials from your profile
export AWS_PROFILE=your-profile-name
export AWS_ACCESS_KEY_ID=$(aws configure get aws_access_key_id --profile your-profile-name)
export AWS_SECRET_ACCESS_KEY=$(aws configure get aws_secret_access_key --profile your-profile-name)
export AWS_DEFAULT_REGION=$(aws configure get region --profile your-profile-name)

# Setup DuckDB
./scripts/analyze.sh setup-env

Method 3: Manual SQL

bash

duckdb alb_analysis.duckdb < scripts/setup.sql
# or
duckdb alb_analysis.duckdb < scripts/setup_s3_env.sql

Verify Setup

Diagnose your AWS credentials configuration:

bash

./scripts/analyze.sh diagnose

This will show:

•Extension installation status
•Environment variable configuration
•Configured secrets in DuckDB

AWS Credentials Setup

DuckDB needs AWS credentials to access S3. Choose one of these methods:

Option 1: AWS CLI Configuration (Recommended)

If you have AWS CLI configured, DuckDB can use those credentials:

bash

# Check if AWS CLI is configured
aws s3 ls

# If not configured, run:
aws configure

Option 2: Environment Variables

Export credentials in your shell:

bash

export AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE
export AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
export AWS_DEFAULT_REGION=ap-northeast-1

Option 3: AWS Credentials File

Create or edit ~/.aws/credentials:

ini

[default]
aws_access_key_id = AKIAIOSFODNN7EXAMPLE
aws_secret_access_key = wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY

And ~/.aws/config:

ini

[default]
region = ap-northeast-1

Option 4: AWS Profile (For Named Profiles)

If you're using a named AWS profile (not [default]), you need to explicitly export the credentials.

Easy way (using helper script):

bash

# List available profiles
aws configure list-profiles

# Load credentials from your profile
source scripts/load_profile.sh your-profile-name

# Setup and use
./scripts/analyze.sh setup-env
./scripts/analyze.sh load 's3://your-bucket/path/**/*.log.gz'

Manual way:

bash

# Check your profiles
aws configure list-profiles

# Export credentials from your profile
export AWS_PROFILE=your-profile-name
export AWS_ACCESS_KEY_ID=$(aws configure get aws_access_key_id --profile your-profile-name)
export AWS_SECRET_ACCESS_KEY=$(aws configure get aws_secret_access_key --profile your-profile-name)
export AWS_DEFAULT_REGION=$(aws configure get region --profile your-profile-name)

# Setup DuckDB with these credentials
./scripts/analyze.sh setup-env

# Load logs
./scripts/analyze.sh load 's3://your-bucket/path/**/*.log.gz'

Why this is needed: DuckDB doesn't automatically read from named AWS profiles. It only supports environment variables or the [default] profile. By exporting the profile's credentials as environment variables, DuckDB can access them.

Option 5: IAM Role (EC2/ECS)

If running on EC2 or ECS, DuckDB can use the instance's IAM role automatically.

Loading Logs

Load ALB logs from S3 into a DuckDB table.

Using the analyze.sh script

bash

# Load logs from S3
./scripts/analyze.sh load 's3://my-bucket/AWSLogs/123456789012/elasticloadbalancing/us-east-1/2024/11/**/*.log.gz'

Manual SQL approach

•Generate SQL from template:

bash

# Replace placeholders
sed -e "s|{{TABLE_NAME}}|alb_logs|g" \
    -e "s|{{S3_PATH}}|s3://my-bucket/path/to/logs/**/*.log.gz|g" \
    scripts/load_template.sql > load_logs.sql

•Execute:

bash

duckdb alb_analysis.duckdb < load_logs.sql

S3 Path Patterns

Use glob patterns to load multiple files:

code

# Single month
s3://bucket/AWSLogs/account-id/elasticloadbalancing/region/2024/11/**/*.log.gz

# Multiple months
s3://bucket/AWSLogs/account-id/elasticloadbalancing/region/2024/*/**/*.log.gz

# Specific day
s3://bucket/AWSLogs/account-id/elasticloadbalancing/region/2024/11/01/**/*.log.gz

Analysis Tasks

Error Analysis

Analyze HTTP errors, status code distributions, and error patterns:

bash

./scripts/analyze.sh errors

This provides:

•Status code distribution
•Error details (non-200 responses)
•5xx errors by hour
•Common error reasons

Performance Analysis

Analyze response times and latency:

bash

./scripts/analyze.sh performance

This provides:

•Response time statistics (avg, p50, p95, p99)
•Slowest requests
•Response time trends by hour
•Slow request percentage

Custom Queries

Execute custom SQL queries:

bash

# Using a custom SQL file
./scripts/analyze.sh query my_query.sql

# Or directly with DuckDB
duckdb alb_analysis.duckdb

Common Analysis Patterns

Find requests from specific IP

sql

SELECT *
FROM alb_logs
WHERE client_ip_port LIKE '192.168.1.%'
ORDER BY timestamp DESC;

Analyze specific URL path

sql

SELECT
    elb_status_code,
    COUNT(*) as count,
    ROUND(AVG(target_processing_time), 3) as avg_time
FROM alb_logs
WHERE request LIKE '%/api/users%'
GROUP BY elb_status_code;

Traffic by time of day

sql

SELECT
    EXTRACT(HOUR FROM timestamp) as hour,
    COUNT(*) as request_count
FROM alb_logs
GROUP BY hour
ORDER BY hour;

Filter by date range

sql

SELECT *
FROM alb_logs
WHERE timestamp BETWEEN '2024-11-01' AND '2024-11-30'
AND elb_status_code >= 500;

Advanced Usage

Create aggregated summaries

sql

CREATE TABLE daily_summary AS
SELECT
    DATE_TRUNC('day', timestamp) as day,
    COUNT(*) as total_requests,
    SUM(CASE WHEN elb_status_code >= 500 THEN 1 ELSE 0 END) as errors_5xx,
    ROUND(AVG(target_processing_time), 3) as avg_response_time
FROM alb_logs
GROUP BY day;

Export results

sql

-- Export to CSV
COPY (SELECT * FROM alb_logs WHERE elb_status_code >= 500)
TO 'errors.csv' (HEADER, DELIMITER ',');

-- Export to Parquet
COPY alb_logs TO 'alb_logs.parquet' (FORMAT PARQUET);

Multiple table analysis

Load logs into separate tables for comparison:

bash

# Load last week's logs
sed -e "s|{{TABLE_NAME}}|alb_logs_last_week|g" \
    -e "s|{{S3_PATH}}|s3://bucket/logs/2024/10/**/*.log.gz|g" \
    scripts/load_template.sql | duckdb alb_analysis.duckdb

# Compare with this week
SELECT
    'last_week' as period,
    COUNT(*) as requests,
    AVG(target_processing_time) as avg_time
FROM alb_logs_last_week
UNION ALL
SELECT
    'this_week' as period,
    COUNT(*) as requests,
    AVG(target_processing_time) as avg_time
FROM alb_logs;

Troubleshooting

S3 Access Issues

If you get "Access Denied" or credential errors when loading from S3:

Step 1: Diagnose the issue

bash

./scripts/analyze.sh diagnose

This shows your AWS credential configuration status.

Step 2: Verify AWS credentials work

Test with AWS CLI:

bash

aws s3 ls s3://your-bucket/path/to/logs/

If this fails, fix your AWS credentials first.

Step 3: Try different setup methods

3a. If using default AWS profile:

bash

# Set credentials
export AWS_ACCESS_KEY_ID=$(aws configure get aws_access_key_id)
export AWS_SECRET_ACCESS_KEY=$(aws configure get aws_secret_access_key)
export AWS_DEFAULT_REGION=$(aws configure get region)

# Setup DuckDB with environment variables
./scripts/analyze.sh setup-env

# Try loading again
./scripts/analyze.sh load 's3://your-bucket/path/**/*.log.gz'

3b. If using a named AWS profile:

bash

# Check which profile you're using
aws configure list-profiles

# Replace 'your-profile-name' with your actual profile
export AWS_PROFILE=your-profile-name
export AWS_ACCESS_KEY_ID=$(aws configure get aws_access_key_id --profile your-profile-name)
export AWS_SECRET_ACCESS_KEY=$(aws configure get aws_secret_access_key --profile your-profile-name)
export AWS_DEFAULT_REGION=$(aws configure get region --profile your-profile-name)

# Setup DuckDB with environment variables
./scripts/analyze.sh setup-env

# Try loading again
./scripts/analyze.sh load 's3://your-bucket/path/**/*.log.gz'

Step 4: Alternative - Load from local files

If S3 access still doesn't work, download logs locally:

bash

# Download logs from S3
aws s3 sync s3://your-bucket/path/to/logs/ ./local-logs/

# Modify load_template.sql to use local path
sed -e "s|{{TABLE_NAME}}|alb_logs|g" \
    -e "s|{{S3_PATH}}|./local-logs/**/*.log.gz|g" \
    scripts/load_template.sql | duckdb alb_analysis.duckdb

"Extension aws not found"

Run setup script first:

bash

./scripts/analyze.sh setup

Or manually:

bash

duckdb alb_analysis.duckdb < scripts/setup.sql

"Invalid Input Error" when loading

This usually means the S3 path pattern doesn't match any files:

bash

# Check if files exist
aws s3 ls --recursive s3://your-bucket/path/to/logs/ | head

# Verify the path pattern matches your bucket structure
# ALB logs are typically at:
# s3://bucket/AWSLogs/{account-id}/elasticloadbalancing/{region}/{year}/{month}/{day}/*.log.gz

Empty results after loading

bash

# Check table contents
duckdb alb_analysis.duckdb -c "SELECT COUNT(*) FROM alb_logs"

# If 0, verify S3 path and try loading again

Out of memory

Use persistent database instead of :memory::

bash

# Already using persistent storage by default
# alb_analysis.duckdb is a file-based database

Permission errors on scripts

bash

chmod +x scripts/analyze.sh

Region-specific issues

Ensure the region matches your S3 bucket:

bash

export AWS_DEFAULT_REGION=ap-northeast-1  # Change to your region
./scripts/analyze.sh setup-env

References

ALB Log Schema

See references/alb_schema.md for complete field definitions and data types.

Key fields:

•timestamp: Request timestamp
•elb_status_code: HTTP status from ALB
•target_status_code: HTTP status from target
•request: Full HTTP request line
•target_processing_time: Response time from target
•client_ip_port: Client address and port
•target_ip_port: Target address and port
•error_reason: Error details if applicable
•transformed_host, transformed_uri, request_transform_status: Request transformation fields

Query Examples

See references/query_examples.md for comprehensive query examples including:

•Error analysis queries
•Performance metrics
•Traffic analysis
•SSL/TLS analysis
•Data export patterns

Scripts Reference

analyze.sh

Main analysis script with subcommands:

bash

# Setup (uses CREDENTIAL_CHAIN)
./scripts/analyze.sh setup

# Setup with environment variables (if CREDENTIAL_CHAIN doesn't work)
./scripts/analyze.sh setup-env

# Diagnose S3 access and credentials
./scripts/analyze.sh diagnose

# Load logs
./scripts/analyze.sh load '<s3_path>'

# Analyze errors
./scripts/analyze.sh errors

# Analyze performance
./scripts/analyze.sh performance

# Custom query
./scripts/analyze.sh query <sql_file>

# Options
--db <file>      # Database file (default: alb_analysis.duckdb)
--table <name>   # Table name (default: alb_logs)

setup_with_profile.sh (Secure - Recommended)

Securely setup DuckDB with AWS profile. Credentials are read internally and never exposed in commands:

bash

# Usage
./scripts/setup_with_profile.sh your-profile-name

# Security: Only profile name is visible, credentials read from ~/.aws/credentials

Security advantage: Credentials stay private. Only profile names appear in command history and logs.

load_with_profile.sh (Secure - Recommended)

Securely load logs with AWS profile. Credentials are read internally and never exposed in commands:

bash

# Usage
./scripts/load_with_profile.sh your-profile-name 's3://bucket/path/**/*.log.gz' [table_name]

# Security: Only profile name and S3 path are visible

Security advantage: Credentials stay private. Only profile names appear in command history and logs.

load_profile.sh (Legacy - For Manual Setup)

Helper script to load AWS credentials from a named profile into environment variables:

bash

# Usage
source scripts/load_profile.sh your-profile-name

# This exports:
# - AWS_PROFILE
# - AWS_ACCESS_KEY_ID
# - AWS_SECRET_ACCESS_KEY
# - AWS_DEFAULT_REGION

Note: Must be sourced (not executed) to export variables to your current shell.

Security consideration: This exports credentials to environment variables, which may be visible in process listings. Use setup_with_profile.sh and load_with_profile.sh for better security.

SQL Files

•setup.sql: Initialize DuckDB extensions with CREDENTIAL_CHAIN
•setup_s3_env.sql: Initialize DuckDB extensions with environment variables
•diagnose_s3.sql: Diagnose AWS credentials and S3 access
•load_template.sql: Template for loading logs (requires variable substitution)
•analyze_errors.sql: Error analysis queries
•analyze_performance.sql: Performance analysis queries