AWS Athena를 활용한 ALB Connection Log 확인

 

 

1. ALB Connection Log

-. 언제부터 생긴지는 모르겠으나, ALB에 Connection Log 가 새로 생겨있음

-. 새로 생긴 기능이라 그런지 Table 생성 쿼리가 공식문서에는 아직 안나와있음

-. 로그에는 클라이언트의 IP 주소 및 포트, 리스너 포트, 사용된 TLS 암호 및 프로토콜, TLS 핸드셰이크 지연 시간, 연결 상태, 클라이언트 인증서 세부 정보 등의 정보가 포함되므로, 연결 로그를 사용하여 요청 패턴을 분석하고 문제를 해결 가능

 

 

2. Connection Log 활성화 후 수집 내용

2024-03-11T23:59:59.187088Z [CLIENT-IP-숨김] 4704 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.012 "-" - - Success
2024-03-11T23:59:59.203439Z [CLIENT-IP-숨김] 51214 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.013 "-" - - Success
2024-03-11T23:59:59.210184Z [CLIENT-IP-숨김] 61194 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.004 "-" - - Success
2024-03-11T23:59:59.215868Z [CLIENT-IP-숨김] 54444 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.003 "-" - - Success
2024-03-11T23:59:59.241786Z [CLIENT-IP-숨김] 56294 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.007 "-" - - Success
2024-03-11T23:59:59.242564Z [CLIENT-IP-숨김] 60949 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.009 "-" - - Success
2024-03-11T23:59:59.258342Z [CLIENT-IP-숨김] 62395 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.042 "-" - - Success
2024-03-11T23:59:59.263639Z [CLIENT-IP-숨김] 41166 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.004 "-" - - Success
2024-03-11T23:59:59.287119Z [CLIENT-IP-숨김] 44380 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.010 "-" - - Success
2024-03-11T23:59:59.290264Z [CLIENT-IP-숨김] 54454 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.003 "-" - - Success
2024-03-11T23:59:59.292470Z [CLIENT-IP-숨김] 56314 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.008 "-" - - Success
2024-03-11T23:59:59.318069Z [CLIENT-IP-숨김] 51230 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.013 "-" - - Success
2024-03-11T23:59:59.333857Z [CLIENT-IP-숨김] 38647 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.000 "-" - - Success
2024-03-11T23:59:59.340725Z [CLIENT-IP-숨김] 56328 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.005 "-" - - Success
2024-03-11T23:59:59.345163Z [CLIENT-IP-숨김] 54462 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.005 "-" - - Success
2024-03-11T23:59:59.369386Z [CLIENT-IP-숨김] 50565 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.009 "-" - - Success
2024-03-11T23:59:59.372092Z [CLIENT-IP-숨김] 51232 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.012 "-" - - Success
2024-03-11T23:59:59.378269Z [CLIENT-IP-숨김] 48360 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 1.445 "-" - - Success
2024-03-11T23:59:59.385046Z [CLIENT-IP-숨김] 56348 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.006 "-" - - Success
2024-03-11T23:59:59.407253Z [CLIENT-IP-숨김] 4705 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.010 "-" - - Success
2024-03-11T23:59:59.408600Z [CLIENT-IP-숨김] 60956 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.020 "-" - - Success
2024-03-11T23:59:59.415877Z [CLIENT-IP-숨김] 56642 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.032 "-" - - Success
2024-03-11T23:59:59.417354Z [CLIENT-IP-숨김] 44388 443 TLSv1.2 ECDHE-RSA-AES128-GCM-SHA256 0.004 "-" - - Succes

각 필드별 값은 AWS 공식 document 확인(https://docs.aws.amazon.com/ko_kr/elasticloadbalancing/latest/application/load-balancer-connection-logs.html)

3. Athena Table 생성 쿼리

CREATE EXTERNAL TABLE IF NOT EXISTS alb_conn_logs (
    time string,
    client_ip string,
    client_port int,
    listener_port int,
    tls_protocol string,
    tls_cipher string,
    tls_handshake_latency double,
    leaf_client_cert_subject string,
    leaf_client_cert_validity string,
    leaf_client_cert_serial_number string,
    tls_verify_status string
)
PARTITIONED BY
(
    day STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = '1',
'input.regex' =
    '([^ ]*) ([^ ]*) ([0-9]*) ([0-9]*) ([A-Za-z0-9.-]*) ([^ ]*) ([-.0-9]*) \"([^\"]*)\" ([^ ]*) ([^ ]*) ([^ ]*)')
LOCATION 's3://<S3-LOCATION>/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/'
TBLPROPERTIES
(
    "projection.enabled" = "true",
    "projection.day.type" = "date",
    "projection.day.range" = "2023/11/27,NOW",
    "projection.day.format" = "yyyy/MM/dd",
    "projection.day.interval" = "1",
    "projection.day.interval.unit" = "DAYS",
    "storage.location.template" = "s3://<S3-LOCATION>/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/${day}"
)