AWS ALB (Classic) - CLB Logs in Athena

clb_logs.md

Create Athena DB and table and run queries on AWS Classic Load Balancer logs.

clb_logs_all.sql

SELECT * FROM "clb_logs"."clb_logs" limit 10;

clb_logs_cols.sql

SELECT
    timestamp,
    elb_name,
    url,
    protocol,
    user_agent,
    ssl_cipher,
    ssl_protocol
FROM "clb_logs"."clb_logs"
WHERE elb_name like '<CLB_NAME_PREFIX>%' 
    AND timestamp like '2024-%'
LIMIT 1000000
;

clb_logs_count_protocol.sql

SELECT
    distinct(protocol),
    count(protocol) as count
FROM "clb_logs"."clb_logs"
WHERE timestamp LIKE '2024-0%'
GROUP BY protocol

clb_logs_count_ssl_cipher.sql

SELECT
    distinct(ssl_cipher),
    count(protocol) as count
FROM "clb_logs"."clb_logs"
WHERE timestamp LIKE '2024-0%'
GROUP BY ssl_cipher

clb_logs_count_ssl_protocol.sql

SELECT
    distinct(ssl_protocol),
    count(protocol) as count
FROM "clb_logs"."clb_logs"
WHERE timestamp LIKE '2024-0%'
GROUP BY ssl_protocol

clb_logs_CREATE_DB_and_TABLE.sql

CREATE EXTERNAL TABLE IF NOT EXISTS `clb_logs`.`clb_logs` (
  `timestamp` string,
  `elb_name` string,
  `request_ip` string,
  `request_port` int,
  `backend_ip` string,
  `backend_port` int,
  `request_processing_time` double,
  `backend_processing_time` double,
  `client_response_time` double,
  `elb_response_code` string,
  `backend_response_code` string,
  `received_bytes` bigint,
  `sent_bytes` bigint,
  `request_verb` string,
  `url` string,
  `protocol` string,
  `user_agent` string,
  `ssl_cipher` string,
  `ssl_protocol` string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  '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.-]*)$',
  'serialization.format' = '1'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://<ELB_LOG_BUCKET_NAME>/';