¿Cómo analizo mis registros de acceso al equilibrador de carga de aplicación con Amazon Athena?

** Quiero analizar mis registros de acceso al equilibrador de carga de aplicación con Amazon Athena.**

Descripción breve

Elastic Load Balancing no activa el registro de acceso de forma predeterminada. Al activar el registro de acceso, especifique un bucket de Amazon Simple Storage Service (Amazon S3). Todos los registros de acceso al equilibrador de carga de aplicación y al equilibrador de carga clásico se almacenan en el bucket de Amazon S3. Para solucionar problemas o analizar el rendimiento de su equilibrador de carga, utilice Athena para analizar los registros de acceso en Amazon S3.

Nota: Si bien puede usar Athena para analizar los registros de acceso de los equilibradores de carga de aplicación y los equilibradores de carga clásicos, esta resolución solo se aplica a los equilibradores de carga de aplicación.

Resolución

Cree una base de datos y una tabla para los registros del equilibrador de carga de aplicación

Para analizar los registros de acceso en Athena, cree una base de datos y una tabla con los siguientes pasos:

1.    Abra la consola de Athena.

2.    Para crear una base de datos, ejecute el siguiente comando en el Editor de consultas. Se recomienda crear la base de datos en la misma región de AWS que el bucket de S3.

create database alb_db

3.    En la base de datos que creó en el paso 2, cree una tabla alb_logs para los registros del equilibrador de carga de aplicación. Para obtener más información, consulte Crear la tabla para los registros del equilibrador de carga de aplicación.

Nota: Para obtener un mejor rendimiento de las consultas, puede crear una tabla con proyección de particiones. En la proyección de particiones, Athena calcula los valores y las ubicaciones de las particiones a partir de la configuración en lugar de leerlos desde un repositorio, como el Catálogo de datos de AWS Glue. Para obtener más información, consulte Proyección de particiones con Amazon Athena.

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs (
            type string,
            time string,
            elb string,
            client_ip string,
            client_port int,
            target_ip string,
            target_port int,
            request_processing_time double,
            target_processing_time double,
            response_processing_time double,
            elb_status_code int,
            target_status_code string,
            received_bytes bigint,
            sent_bytes bigint,
            request_verb string,
            request_url string,
            request_proto string,
            user_agent string,
            ssl_cipher string,
            ssl_protocol string,
            target_group_arn string,
            trace_id string,
            domain_name string,
            chosen_cert_arn string,
            matched_rule_priority string,
            request_creation_time string,
            actions_executed string,
            redirect_url string,
            lambda_error_reason string,
            target_port_list string,
            target_status_code_list string,
            classification string,
            classification_reason 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]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) (.*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-_]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^ ]*)\" \"([^\s]+?)\" \"([^\s]+)\" \"([^ ]*)\" \"([^ ]*)\"')
            LOCATION 's3://your-alb-logs-directory/AWSLogs/1111222233334444/elasticloadbalancing//'
            TBLPROPERTIES
            (
             "projection.enabled" = "true",
             "projection.day.type" = "date",
             "projection.day.range" = "2022/01/01,NOW",
             "projection.day.format" = "yyyy/MM/dd",
             "projection.day.interval" = "1",
             "projection.day.interval.unit" = "DAYS",
             "storage.location.template" = "s3://your-alb-logs-directory/AWSLogs/1111222233334444/elasticloadbalancing//${day}"
            )

Nota: Sustituya el nombre de la tabla y las ubicaciones de S3 según su caso de uso.

O bien, utilice la siguiente consulta para crear una tabla con particiones:

CREATE EXTERNAL TABLE IF NOT EXISTS alb_logs_partitioned (
            type string,
            time string,
            elb string,
            client_ip string,
            client_port int,
            target_ip string,
            target_port int,
            request_processing_time double,
            target_processing_time double,
            response_processing_time double,
            elb_status_code string,
            target_status_code string,
            received_bytes bigint,
            sent_bytes bigint,
            request_verb string,
            request_url string,
            request_proto string,
            user_agent string,
            ssl_cipher string,
            ssl_protocol string,
            target_group_arn string,
            trace_id string,
            domain_name string,
            chosen_cert_arn string,
            matched_rule_priority string,
            request_creation_time string,
            actions_executed string,
            redirect_url string,
            lambda_error_reason string,
            target_port_list string,
            target_status_code_list string,
            classification string,
            classification_reason 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]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) ([^ ]*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^ ]*)\" \"([^\s]+?)\" \"([^\s]+)\" \"([^ ]*)\" \"([^ ]*)\"')
            LOCATION 's3://my_log_bucket/AWSLogs/1111222233334444/elasticloadbalancing/us-east-1/'

A continuación, utilice el comando ALTER TABLE ADD PARTITION para cargar las particiones:

ALTER TABLE alb_logs_partitioned ADD
  PARTITION (day = '2022/05/21')
  LOCATION's3://my_log_bucket/AWSLogs/1111222233334444/elasticloadbalancing/us-east-1/2022/05/21/'

Nota: No se recomienda utilizar un rastreador de AWS Glue en los registros del equilibrador de carga de aplicación.

4.    En Tablas del panel de navegación, seleccione Vista previa de la tabla en el menú. Puede ver los datos de los registros de acceso del equilibrador de carga de aplicación en la ventana de resultados.

5.    Utilice el editor de consultas para ejecutar instrucciones SQL en la tabla. Puede guardar consultas, ver consultas anteriores o descargar los resultados de las consultas en formato de archivo.csv.

Consultas de ejemplo

En los siguientes ejemplos, asegúrese de modificar el nombre de la tabla, los valores de las columnas y otras variables para que se ajusten a la consulta:

Ver las 100 primeras entradas del registro de acceso en orden cronológico

Utilice esta consulta para analizar y solucionar problemas:

SELECT *  
FROM alb_logs  
ORDER by time ASC  
LIMIT 100

Enumere todas las direcciones IP de los clientes que accedieron al equilibrador de carga de aplicación y cuántas veces accedieron a él 

Utilice esta consulta para analizar y solucionar problemas:

SELECT distinct client_ip, count() as count from alb_logs  
GROUP by client_ip  
ORDER by count() DESC;

Enumere la cantidad promedio de datos (en kilobytes) que pasan por el equilibrador de carga de aplicación en pares de solicitudes o respuestas

Utilice esta consulta para analizar y solucionar problemas:

SELECT (avg(sent_bytes)/1000.0 + avg(received_bytes)/1000.0)  
as prewarm_kilobytes from alb_logs;

Enumere todos los destinos a los que el equilibrador de carga de aplicación envía el tráfico y el número de solicitudes enrutadas por destino, por distribución porcentual   

Utilice esta consulta para identificar posibles desequilibrios en el tráfico objetivo:

SELECT target_ip, (Count(target_ip)* 100.0 / (Select Count(*) From alb_logs))  
as backend_traffic_percentage  
FROM alb_logs  
GROUP by target_ip<  
ORDER By count() DESC;

Enumere las veces que un cliente envió una solicitud al equilibrador de carga de aplicación y, a continuación, cerró la conexión antes de que transcurriera el tiempo de espera de inactividad (error HTTP 460)

Utilice esta consulta para solucionar errores HTTP 460:

SELECT * from alb_logs where elb_status_code = '460';

Enumere las veces en las que no se envió una solicitud de cliente porque la regla de escucha reenvió la solicitud a un grupo de destino vacío (error HTTP 503)

Utilice esta consulta para solucionar errores HTTP 503:

SELECT * from alb_logs where elb_status_code = '503';

Enumere los clientes, en orden descendente, según el número de veces que cada cliente visitó una URL específica

Utilice esta consulta para analizar los patrones de tráfico:

SELECT client_ip, elb, request_url, count(*) as count from alb_logs  
GROUP by client_ip, elb, request_url  
ORDER by count DESC;

Enumere las 10 URL a las que los usuarios de Firefox accedieron con más frecuencia, en orden descendente

Utilice esta consulta para analizar la distribución y los patrones del tráfico:

SELECT request_url, user_agent, count(*) as count  
FROM alb_logs  
WHERE user_agent LIKE '%Firefox%'  
GROUP by request_url, user_agent  
ORDER by count(*) DESC  
LIMIT 10;

Enumere los clientes, en orden descendente, según la cantidad de datos (en megabytes) que cada cliente envió en sus solicitudes al equilibrador de carga de aplicación

Utilice esta consulta para analizar la distribución y los patrones del tráfico:

SELECT client_ip, sum(received_bytes/1000000.0) as client_datareceived_megabytes  
FROM alb_logs  
GROUP by client_ip  
ORDER by client_datareceived_megabytes DESC;

Enumere cada tiempo en un intervalo de fechas especificado, cuando el tiempo de procesamiento objetivo haya sido superior a 5 segundos

Utilice esta consulta para solucionar problemas de latencia en un período de tiempo específico:

SELECT * from alb_logs  
WHERE (parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')  
    BETWEEN parse_datetime('2018-08-08-00:00:00','yyyy-MM-dd-HH:mm:ss')  
    AND parse_datetime('2018-08-08-02:00:00','yyyy-MM-dd-HH:mm:ss'))  
AND (target_processing_time >= 5.0);

Cuente el número de solicitudes HTTP GET recibidas por el equilibrador de carga agrupadas por la dirección IP del cliente

Utilice esta consulta para analizar la distribución del tráfico entrante:

SELECT COUNT(request_verb)   
AS count, request_verb, client_ip   
FROM alb_logs_partitioned   
WHERE day = '2022/05/21'   
GROUP by request_verb, client_ip;