Comment puis-je utiliser Athena pour interroger les journaux de connexion de mon Application Load Balancer ?

Résolution

Pour interroger les journaux de connexion de l'Application Load Balancer, créez une base de données et une table pour Athena. Procédez comme suit :

1. Ouvrez la console Athena.

2. Pour créer une base de données, exécutez la commande suivante dans l’Éditeur de requête :

   CREATE DATABASE alb_db

   Remarque : Il est recommandé de créer la base de données dans la même région AWS que celle du compartiment Amazon Simple Storage Service (Amazon S3).

3. Après avoir créé la base de données, créez une table avec la requête suivante :

   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}"  
               )

   Remarque : Remplacez S3-LOCATION, ACCOUNT-NUMBER et REGION par les valeurs de votre compartiment S3.

4. Pour interroger les journaux de connexion de l'Application Load Balancer, utilisez des instructions SQL.

Exemples de requêtes

Afficher les 100 dernières occurrences de journaux

SELECT *FROM alb_conn_logs  
ORDER by time desc  
LIMIT 100

Compter les occurrences dont le statut tls_verify_status n'était PAS « Succès » et les regrouper par 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;

Afficher toutes les occurrences où tls_handshake_latency dure plus de deux secondes dans une plage de temps spécifique

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);

Vérifier les connexions avec une date de validité de certification exacte et les regrouper par 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;

Informations connexes

Comment analyser mes journaux d'accès à l'Application Load Balancer à l'aide d'Athena ?

Interroger les journaux de l’Application Load Balancer