Athena を使用して Application Load Balancer の接続ログをクエリする方法を教えてください。

解決策

Application Load Balancer の接続ログをクエリするには、Athena のデータベースとテーブルを作成します。次の手順を実行します。

1. Athena コンソールを開きます。

2. データベースを作成するには、クエリエディタで次のコマンドを実行します。

   CREATE DATABASE alb_db

   注: Amazon Simple Storage Service (Amazon S3) バケットと同じ AWS リージョンにデータベースを作成することをおすすめします。

3. データベースを作成した後、次のクエリを使用してテーブルを作成します。

   CREATE EXTERNAL TABLE IF NOT EXISTS alb_connection_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,  
            conn_trace_id 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/01/01,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}"  
               )

   注: S3-LOCATION、ACCOUNT-NUMBER、REGION を実際の S3 バケットの値に置き換えてください。

4. Application Load Balancer の接続ログをクエリするには、SQL ステートメントを使用します。

クエリの例

最新の 100 件のログを表示する

SELECT *FROM alb_conn_logs  
ORDER by time desc  
LIMIT 100

tls_verify_status が 'Success' ではなかった回数をカウントし、client_IP でグループ化する

SELECT distinct client_ip, count() as count from alb_conn_logs WHERE tls_verify_status != 'Success'  
GROUP by client_ip  
ORDER by count() DESC;

特定の時間範囲内で、tls_handshake_latency が 2 秒を超えたケースをすべて表示する

SELECT * FROM alb_conn_logs WHERE (parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')  
BETWEEN parse_datetime('2024-01-01-00:00:00','yyyy-MM-dd-HH:mm:ss')  
AND parse_datetime('2024-03-20-00:00:00','yyyy-MM-dd-HH:mm:ss'))  
AND (tls_handshake_latency >= 2.0);

証明書の有効期限が正確である接続を確認し、leaf_client_cert_serial_number でグループ化する

SELECT DISTINCT leaf_client_cert_serial_number, count() AS count FROM alb_conn_logs  
WHERE tls_verify_status = 'Success' AND leaf_client_cert_validity like '%NotAfter=2025-01-01%'  
GROUP BY leaf_client_cert_serial_number  
ORDER BY count() DESC;

関連情報

Athena を使用して Application Load Balancer のアクセスログを分析する方法を教えてください

Application Load Balancer ログをクエリする