Como uso o Athena para consultar meus logs de conexão do Application Load Balancer?

Resolução

Para consultar os logs de conexão para o Application Load Balancer, crie um banco de dados e uma tabela para o Athena. Conclua as etapas a seguir:

1. Abra o console do Athena.

2. Para criar um banco de dados, execute o comando a seguir no Editor de consultas:

   CREATE DATABASE alb_db

   Observação: É uma prática recomendada criar o banco de dados na mesma região da AWS do seu bucket do Amazon Simple Storage Service (Amazon S3).

3. Depois de criar o banco de dados, crie uma tabela com a seguinte consulta:

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

   Observação: Substitua S3-LOCATION, ACCOUNT-NUMBER e REGION pelos valores do seu bucket do S3.

4. Para consultar os logs de conexão do Application Load Balancer, use instruções SQL.

Exemplos de consultas

Veja as últimas 100 ocorrências de logs

SELECT *FROM alb_conn_logs  
ORDER by time desc  
LIMIT 100

Conte ocorrências em que tls_verify_status NÃO foi “Sucesso” e agrupe por 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;

Veja todas as ocorrências em que tls_handshake_latency é superior a dois segundos em um intervalo de tempo específico

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

Verifique as conexões com uma data de validade exata da certificação e agrupe por 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;

Informações relacionadas

Como usar o Amazon Athena para analisar meus logs de acesso do Application Load Balancer?

Consultar logs do Application Load Balancer