Creación de una tabla para los registros de flujo de Amazon VPC y consulta de esta
En el siguiente procedimiento, se crea una tabla de Amazon VPC para los registros de flujo de Amazon VPC. Cuando crea un registro de flujo con un formato personalizado, crea una tabla con campos que coinciden con los campos especificados al crear el registro de flujo, en el mismo orden en que los especificó.
Para crear una tabla de Athena para los registros de flujo de Amazon VPC
-
Ingrese una instrucción DDL como la siguiente en el editor de consultas de la consola de Athena y siga las directrices de la sección Consideraciones y limitaciones. Con la instrucción de muestra, se crea una tabla que tiene las columnas para los registros de flujo de Amazon VPC (versiones 2 a 5), como se documenta en Registros de flujo. Si utiliza un conjunto o un orden de columnas diferente, modifique la instrucción según corresponda.
CREATE EXTERNAL TABLE IF NOT EXISTS `vpc_flow_logs` ( version int, account_id string, interface_id string, srcaddr string, dstaddr string, srcport int, dstport int, protocol bigint, packets bigint, bytes bigint, start bigint, `end` bigint, action string, log_status string, vpc_id string, subnet_id string, instance_id string, tcp_flags int, type string, pkt_srcaddr string, pkt_dstaddr string, region string, az_id string, sublocation_type string, sublocation_id string, pkt_src_aws_service string, pkt_dst_aws_service string, flow_direction string, traffic_path int ) PARTITIONED BY (`date` date) ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' LOCATION 's3://amzn-s3-demo-bucket/
prefix
/AWSLogs/{account_id}
/vpcflowlogs/{region_code}
/' TBLPROPERTIES ("skip.header.line.count"="1");Tenga en cuenta los siguientes puntos:
-
La consulta especifica
ROW FORMAT DELIMITED
y omite especificar un elemento SerDe. Esto significa que la consulta utiliza Lazy Simple SerDe para CSV, TSV y archivos con delimitación personalizada. En esta consulta los campos terminan con un espacio. -
La cláusula
PARTITIONED BY
utiliza el tipodate
. Esto permite utilizar operadores matemáticos en las consultas para seleccionar los elementos más antiguos o más recientes respecto a una fecha determinada.nota
Dado que
date
es una palabra clave reservada en las instrucciones DDL, se incluye en una secuencia de escape con caracteres de comilla simple. Para obtener más información, consulte Aplicación de caracteres de escape a las palabras clave reservadas de las consultas. -
En el caso de un registro de flujo de VPC con un formato personalizado diferente, modifique los campos para que coincidan con los campos especificados al crear el registro de flujo.
-
-
Modifique
LOCATION 's3://amzn-s3-demo-bucket/
de modo que apunte al bucket de Amazon S3 que contiene los datos de registro.prefix
/AWSLogs/{account_id}
/vpcflowlogs/{region_code}
/' -
Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla
vpc_flow_logs
, dejando los datos que contiene listos para efectuar consultas. -
Cree particiones para poder leer los datos, tal y como se indica en la siguiente consulta de ejemplo. Esta consulta de ejemplo crea una sola partición para la fecha especificada. Sustituya los marcadores de posición para la fecha y la ubicación según sea necesario.
nota
Esta consulta crea solo crea una partición única para la fecha que especifique. Para automatizar el proceso, utilice un script que ejecute esta consulta y que cree las particiones de este modo para
year/month/day
, o bien utilice una instrucciónCREATE TABLE
en la que se especifique la proyección de particiones.ALTER TABLE vpc_flow_logs ADD PARTITION (`date`='
YYYY-MM-dd
') LOCATION 's3://amzn-s3-demo-bucket/prefix
/AWSLogs/{account_id}
/vpcflowlogs/{region_code}
/YYYY
/MM
/dd
';
Consultas de ejemplo para la tabla vpc_flow_logs
Utilice el editor de consultas de la consola de Athena para ejecutar instrucciones SQL en la tabla que cree. Puede guardar las consultas, ver consultas anteriores o descargar los resultados de las consultas en formato CSV. En los siguientes ejemplos, sustituya vpc_flow_logs
por el nombre de la tabla. Modifique los valores de columna y otras variables según sus requisitos.
La siguiente consulta de ejemplo muestra un máximo de 100 registros de flujo para la fecha especificada.
SELECT * FROM vpc_flow_logs WHERE date = DATE('2020-05-04') LIMIT 100;
La siguiente consulta obtiene una lista de todas las conexiones TCP rechazadas y utiliza la columna de partición de fecha que acaba de crear, date
, para extraer de ella el día de la semana en el que se produjeron esos eventos.
SELECT day_of_week(date) AS day, date, interface_id, srcaddr, action, protocol FROM vpc_flow_logs WHERE action = 'REJECT' AND protocol = 6 LIMIT 100;
Para saber qué servidor está recibiendo el mayor número de solicitudes HTTPS, utilice la siguiente consulta. Cuenta el número de paquetes recibidos en el puerto 443 de HTTPS, los agrupa según la dirección IP de destino y devuelve los 10 principales de la última semana.
SELECT SUM(packets) AS packetcount, dstaddr FROM vpc_flow_logs WHERE dstport = 443 AND date > current_date - interval '7' day GROUP BY dstaddr ORDER BY packetcount DESC LIMIT 10;