Consulta de los registros de consulta de Amazon Route 53 Resolver - Amazon Athena
Creación de la tabla para los registros de consultas de ResolverEjemplo de particiónConsultas de ejemplo

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

  1. Abra la consola de Athena en https://console.aws.amazon.com/athena/.

  2. En el Editor de consultas de Athena, ingrese la siguiente instrucción CREATE TABLE. Reemplace los valores de la cláusula LOCATION 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 o HIVE_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 JSON en la documentación de OpenX SerDE en GitHub.

  3. 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.

  4. 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';