Come posso utilizzare Athena per eseguire query sui log delle connessioni del mio Application Load Balancer?

Risoluzione

Per eseguire query sui log delle connessioni di un Application Load Balancer, crea un database e una tabella per Athena. Completa i seguenti passaggi:

1. Apri la console Athena.

2. Per creare un database, esegui questo comando nell'Editor di query:

   CREATE DATABASE alb_db

   Nota: è consigliabile creare il database nella stessa Regione AWS del bucket Amazon Simple Storage Service (Amazon S3).

3. Dopo aver creato il database, crea una tabella con questa query:

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

   Nota: sostituisci S3-LOCATION, ACCOUNT-NUMBER, e REGION con i valori del bucket S3.

4. Per eseguire query sui log delle connessioni dell'Application Load Balancer, utilizza istruzioni SQL.

Esempio di query

Visualizzare le ultime 100 occorrenze dei log

SELECT *FROM alb_conn_logs  
ORDER by time desc  
LIMIT 100

Contare le occorrenze in cui l'esito di tls_verify_status NON è stato "Success" e raggrupparle per 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;

Visualizzare tutte le occorrenze in cui tls_handshake_latency è superiore a due secondi in un intervallo di tempo specifico

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

Verificare le connessioni con una data di validità della certificazione esatta e raggrupparle per 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;

Informazioni correlate

Come faccio ad analizzare i log di accesso di Application Load Balancer utilizzando Amazon Athena?

Interroga i log di Application Load Balancer