Amazon ELB Logging: notes and tips

This note was created on 2024-05-02
This note was last edited on 2024-05-09

=== Enable logs on ELB ===
1. Create S3 bucket for storing logs.
2. Create bucket policy to allow write access from ELB using the following template:
~~~
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::$ELBACCOUNTID:root"
            },
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::$BUCKETNAME/AWSLogs/$AWSACCOUNTID/*"
        }
    ]
}
~~~
2.1. Replace $ELBACCOUNTID with the following:
     - US East (N. Virginia): 127311923021
     - US East (Ohio): 033677994240
     - US West (N. California): 027434742980
     - US West (Oregon): 797873946194
     - Africa (Cape Town): 098369216593
     - Asia Pacific (Hong Kong): 754344448648
     - Asia Pacific (Jakarta): 589379963580
     - Asia Pacific (Mumbai): 718504428378
     - Asia Pacific (Osaka): 383597477331
     - Asia Pacific (Seoul): 600734575887
     - Asia Pacific (Singapore): 114774131450
     - Asia Pacific (Sydney): 783225319266
     - Asia Pacific (Tokyo): 582318560864
     - Canada (Central): 985666609251
     - Europe (Frankfurt): 054676820928
     - Europe (Ireland): 156460612806
     - Europe (London): 652711504416
     - Europe (Milan): 635631232127
     - Europe (Paris): 009996457667
     - Europe (Stockholm): 897822967062
     - Middle East (Bahrain): 076674570225
     - South America (São Paulo): 507241528517
2.2. Replace $BUCKETNAME with your bucket name.
2.3. Replace $AWSACCOUNTID with ID of your AWS account.
4. Enable Access Logs on ELB in Attributes tab and choose your S3 bucket.

=== ELB logs analysis ===
ELB logs could be analyzed using Amazon Athena.
1. Go to Athena and create a table for ELB logs using the following query:
~~~
CREATE EXTERNAL TABLE IF NOT EXISTS alb_access_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
   )
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://$BUCKETNAME/';
~~~
Note: Don't forget to replace $BUCKETNAME with your bucket name.
2. Use SQL to query what you are looking for. Note that you don't need to update the data in the table.
3. For example, here's how to get the top 100 endpoints by number of requests between two timestamps:
~~~
SELECT COUNT(request_url) AS
   count,
   request_url
FROM alb_access_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
   BETWEEN parse_datetime('2024-05-01-00:00:00','yyyy-MM-dd-HH:mm:ss') 
   AND parse_datetime('2024-05-02-07:00:00','yyyy-MM-dd-HH:mm:ss')
   -- If more then one ELB have logging enabled, then you may want to filter statistic by specific ELB
   AND elb = 'app/ExampleELBName/xxxxxxxxxxxxxxxx'
GROUP BY request_url
ORDER BY 1 DESC
LIMIT 100;
~~~
4. Good luck with your analysis ;-)