Consulta de los registros de consulta de Amazon Route 53 Resolver
Puede crear tablas de Athena para sus registros de consulta de Amazon Route 53 Resolver y consultarlas desde Athena.
El registro de consultas de Route 53 Resolver se utiliza para el registro de consultas de DNS realizadas por recursos dentro de una VPC, recursos en las instalaciones que utilizan un punto de conexión de Resolver saliente para resolución DNS recursiva y consultas que utilizan reglas de firewall DNS de Route 53 Resolver para bloquear, permitir o monitorear una lista de dominios. Para obtener más información acerca del registro de consultas de Resolver, consulte Registro de consultas de Resolver en la Guía para desarrolladores de Amazon Route 53. Para obtener información sobre cada uno de los campos de los registros, consulte Valores que aparecen en los registros de consulta de Resolver en la Guía para desarrolladores de Amazon Route 53.
Creación de la tabla para los registros de consultas de Resolver
Puede utilizar el Editor de consultas de la consola de Athena para crear y consultar una tabla para los registros de consulta de Route 53 Resolver.
Para crear y consultar una tabla de Athena para los registros de consulta de Route 53 Resolver
Abra la consola de Athena en https://console.aws.amazon.com/athena/
. -
En el Editor de consultas de Athena, ingrese la siguiente instrucción
CREATE TABLE
. Reemplace los valores de la cláusulaLOCATION
con los correspondientes a la ubicación de los registros de Resolver de Amazon S3.CREATE EXTERNAL TABLE r53_rlogs ( version string, account_id string, region string, vpc_id string, query_timestamp string, query_name string, query_type string, query_class string, rcode string, answers array< struct< Rdata: string, Type: string, Class: string> >, srcaddr string, srcport int, transport string, srcids struct< instance: string, resolver_endpoint: string >, firewall_rule_action string, firewall_rule_group_id string, firewall_domain_list_id string ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION 's3://DOC-EXAMPLE-BUCKET/AWSLogs/
aws_account_id
/vpcdnsquerylogs/{vpc-id}
/'Dado que los datos del registro de consulta de Resolver están en formato JSON, la instrucción CREATE TABLE utiliza una biblioteca de SerDe JSON para analizar los datos.
nota
El SerDe espera que cada documento JSON esté en una sola línea de texto sin caracteres de terminación de línea que separen los campos del registro. Si el texto JSON está en formato de impresión, puede recibir un mensaje de error como
HIVE_CURSOR_ERROR: la fila no es un objeto JSON válido
oHIVE_CURSOR_ERROR: JsonParseException: fin de entrada inesperado: marcador de cierre esperado para OBJECT
cuando intenta consultar la tabla después de crearla. Para obtener más información, consulte los Archivos de datos JSONen la documentación de OpenX SerDE en GitHub. -
Elija Ejecutar consulta. La instrucción crea una tabla de Athena llamada
r53_rlogs
cuyas columnas representan cada uno de los campos de los datos de registro de Resolver. -
En el Editor de consultas de la consola de Athena, ejecute la siguiente consulta para verificar que se ha creado la tabla.
SELECT * FROM "r53_rlogs" LIMIT 10
Ejemplo de partición
En el siguiente ejemplo, se muestra una instrucción CREATE TABLE
para los registros de consultas de Resolver que utiliza la proyección de particiones y se encuentra particionada por VPC y por fecha. Para obtener más información sobre la proyección de particiones, consulte Proyección de particiones con Amazon Athena.
CREATE EXTERNAL TABLE r53_rlogs ( version string, account_id string, region string, vpc_id string, query_timestamp string, query_name string, query_type string, query_class string, rcode string, answers array< struct< Rdata: string, Type: string, Class: string> >, srcaddr string, srcport int, transport string, srcids struct< instance: string, resolver_endpoint: string >, firewall_rule_action string, firewall_rule_group_id string, firewall_domain_list_id string ) PARTITIONED BY ( `date` string, `vpc` string ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://DOC-EXAMPLE-BUCKET/route53-query-logging/AWSLogs/
aws_account_id
/vpcdnsquerylogs/' TBLPROPERTIES( 'projection.enabled' = 'true', 'projection.vpc.type' = 'enum', 'projection.vpc.values' = 'vpc-6446ae02', 'projection.date.type' = 'date', 'projection.date.range' = '2023/06/26,NOW', 'projection.date.format' = 'yyyy/MM/dd', 'projection.date.interval' = '1', 'projection.date.interval.unit' = 'DAYS', 'storage.location.template' = 's3://DOC-EXAMPLE-BUCKET/route53-query-logging/AWSLogs/aws_account_id
/vpcdnsquerylogs/${vpc}/${date}/' )
Consultas de ejemplo
Los siguientes ejemplos muestran algunas consultas que puede efectuar desde Athena en los registros de consultas de Resolver.
Ejemplo 1: registros de consulta en orden de query_timestamp descendente
La siguiente consulta muestra los resultados del registro en orden query_timestamp
descendente.
SELECT * FROM "r53_rlogs" ORDER BY query_timestamp DESC
Ejemplo 2: registros de consulta dentro de las horas de inicio y finalización especificadas
La consulta a continuación consulta los registros entre las medianoche y las 8 h del 24 de septiembre de 2020. Sustituye las horas de inicio y finalización de acuerdo con sus propios requisitos.
SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode FROM "r53_rlogs" WHERE (parse_datetime(query_timestamp,'yyyy-MM-dd''T''HH:mm:ss''Z') BETWEEN parse_datetime('
2020-09-24-00:00:00
','yyyy-MM-dd-HH:mm:ss') AND parse_datetime('2020-09-24-00:08:00
','yyyy-MM-dd-HH:mm:ss')) ORDER BY query_timestamp DESC
Ejemplo 3: registros de consulta basados en un patrón de nombre de consulta de DNS especificado
La siguiente consulta selecciona registros cuyo nombre de consulta incluye la cadena “example.com”.
SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode, answers FROM "r53_rlogs" WHERE query_name LIKE '%example.com%' ORDER BY query_timestamp DESC
Ejemplo 4: solicitudes de registro de consultas sin respuesta
La siguiente consulta selecciona entradas de registro en las que la solicitud no recibió respuesta.
SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode, answers FROM "r53_rlogs" WHERE cardinality(answers) = 0
Ejemplo 5: registros de consultas con una respuesta específica
La siguiente consulta muestra los registros en los que el valor answer.Rdata
tiene la dirección IP especificada.
SELECT query_timestamp, srcids.instance, srcaddr, srcport, query_name, rcode, answer.Rdata FROM "r53_rlogs" CROSS JOIN UNNEST(r53_rlogs.answers) as st(answer) WHERE answer.Rdata='203.0.113.16';