How to use AWS ALB Logs
CREATE DATABASE alb_logs;{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "ALBAccessLoggingPermissions",
"Effect": "Allow",
"Principal": {
"Service": "elasticloadbalancing.amazonaws.com"
},
"Action": "s3:PutObject",
"Resource": "arn:aws:s3:::your-alb-log-bucket/alb-logs/*"
}
]
}
CREATE EXTERNAL TABLE alb_logs.alb_log (
`type` string,
`time` string,
`elb` string,
`client_ip_port` string,
`target_ip_port` string,
`request_processing_time` double,
`target_processing_time` double,
`response_processing_time` double,
`elb_status_code` string,
`target_status_code` string,
`received_bytes` bigint,
`sent_bytes` bigint,
`request_verb` string,
`request_url` string,
`request_http_version` 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,
`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 (
"input.regex" = "([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \\\"([A-Z]+) ([^ ]+) ([^\\\"]+)\\\" \\\"([^\\\"]*)\\\" ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*)"
)
LOCATION 's3://your-alb-log-bucket/alb-logs/'
TBLPROPERTIES ('has_encrypted_data'='false');
SELECT
time,
request_url,
elb_status_code,
client_ip_port,
user_agent
FROM alb_logs.alb_log
WHERE elb_status_code LIKE '4%'
ORDER BY time DESC
LIMIT 100;
SELECT request_url, COUNT(*) AS occurrences
FROM alb_logs.alb_log
WHERE elb_status_code LIKE '4%'
GROUP BY request_url
ORDER BY occurrences DESC
LIMIT 10;