# Consulta de registros de Servicio de AWS
<a name="querying-aws-service-logs"></a>

En esta sección se incluyen varios procedimientos para utilizar Amazon Athena a fin de consultar conjuntos de datos habituales, como registros de AWS CloudTrail, de Amazon CloudFront, del Classic Load Balancer, del Application Load Balancer, registros de flujo de Amazon VPC y registros del Network Load Balancer.

Las tareas de esta sección utilizan la consola de Athena, pero también puede utilizar otras herramientas como el [controlador JDBC de Athena](connect-with-jdbc.md), la [AWS CLI](https://docs.aws.amazon.com/cli/latest/reference/athena/) o la [Referencia de la API de Amazon Athena](https://docs.aws.amazon.com/athena/latest/APIReference/).

Para obtener información acerca del uso de AWS CloudFormation para crear automáticamente tablas de registro de Servicio de AWS, particiones y consultas de ejemplo en Athena, consulte [Automatización de la creación de tablas de registro y consulta del Servicio de AWS con Amazon Athena](https://aws.amazon.com/blogs/big-data/automating-aws-service-logs-table-creation-and-querying-them-with-amazon-athena/) en el Blog de macrodatos de AWS. Para obtener información sobre el uso de una biblioteca de Python para AWS Glue a fin de crear un marco común para el procesamiento de registros de Servicio de AWS y consultarlos en Athena, vea [Consultar registros de Servicio de AWS fácilmente con Amazon Athena](https://aws.amazon.com/blogs/big-data/easily-query-aws-service-logs-using-amazon-athena/).

Los temas de esta sección presuponen que ha configurado los permisos adecuados para acceder a Athena y al bucket de Amazon S3 en el que deben residir los datos que se van a consultar. Para obtener más información, consulte [Configuración de acceso administrativo y de programación](setting-up.md) y [Introducción](getting-started.md).

**Topics**
+ [Equilibrador de carga de aplicación](application-load-balancer-logs.md)
+ [Elastic Load Balancing](elasticloadbalancer-classic-logs.md)
+ [CloudFront](cloudfront-logs.md)
+ [CloudTrail](cloudtrail-logs.md)
+ [Amazon EMR](emr-logs.md)
+ [Global Accelerator](querying-global-accelerator-flow-logs.md)
+ [GuardDuty](querying-guardduty.md)
+ [Network Firewall](querying-network-firewall-logs.md)
+ [Network Load Balancer](networkloadbalancer-classic-logs.md)
+ [Route 53](querying-r53-resolver-logs.md)
+ [Amazon SES](querying-ses-logs.md)
+ [Amazon VPC](vpc-flow-logs.md)
+ [AWS WAF](waf-logs.md)

# Consulta de los registros del Equilibrador de carga de aplicación
<a name="application-load-balancer-logs"></a>

Un Application Load Balancer aplicaciones es una opción de equilibro de carga de Equilibrador de carga de aplicación que permite la distribución del tráfico en una implementación de microservicios con contenedores. La consulta de los registros del Equilibrador de carga de aplicación le permite ver el origen del tráfico, la latencia y los bytes transferidos a instancias de Elastic Load Balancing y desde dichas instancias y las aplicaciones de backend. Para obtener más información, consulte [Registros de acceso para el equilibrador de carga de aplicación](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-access-logs.html) y [Registros de conexión para el equilibrador de carga de aplicación](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-connection-logs.html) en la *Guía del usuario para equilibradores de carga de aplicación*.

## Requisitos previos
<a name="application-load-balancer-logs-prerequisites"></a>
+ Habilite el [registro de acceso](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-access-logs.html) o el [registro de conexión ](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-connection-logs.html) para que los registros del equilibrador de carga de aplicación se puedan guardar en el bucket de Amazon S3.
+ Una base de datos en la que contener la tabla que creará para Athena. Para crear una base de datos, puede utilizar Athena o la consola de AWS Glue. Para obtener más información, consulte [Creación de bases de datos en Athena](creating-databases.md) en esta guía o [Trabajo con bases de datos en la consola de AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/console-databases.html) en la *Guía para desarrolladores de AWS Glue*. 

**Topics**
+ [Requisitos previos](#application-load-balancer-logs-prerequisites)
+ [Creación de la tabla para los registros de acceso del ALB](create-alb-access-logs-table.md)
+ [Creación de la tabla para los registros de acceso del ALB en Athena mediante la proyección de particiones](create-alb-access-logs-table-partition-projection.md)
+ [Consultas de ejemplo para registros de acceso del ALB](query-alb-access-logs-examples.md)
+ [Creación de la tabla para los registros de conexión del ALB](create-alb-connection-logs-table.md)
+ [Creación de la tabla para los registros de conexión del ALB en Athena mediante la proyección de particiones](create-alb-connection-logs-table-partition-projection.md)
+ [Consultas de ejemplo para registros de conexión del ALB](query-alb-connection-logs-examples.md)
+ [Recursos adicionales](application-load-balancer-logs-additional-resources.md)

# Creación de la tabla para los registros de acceso del ALB
<a name="create-alb-access-logs-table"></a>

1. Copie y pegue la siguiente instrucción `CREATE TABLE` en el editor de consultas de la Consola de Athena y, a continuación, modifíquela según sea necesario para sus propios requisitos de entrada de registros. Para obtener más información acerca de cómo empezar a usar la consola de Athena, consulte [Introducción](getting-started.md). Sustituya la ruta de la cláusula `LOCATION` por la ubicación de la carpeta de registro de acceso de Amazon S3. Para obtener más información sobre la ubicación del archivo de registro de acceso, consulte [Archivos de registro de acceso](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-access-logs.html#access-log-file-format) en la *Guía del usuario para equilibradores de carga de aplicación*.

   Para obtener información sobre cada campo del archivo de registro, consulte [Entradas de registro de acceso](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-access-logs.html#access-log-entry-format) en la *Guía del usuario de los equilibradores de carga de aplicaciones*.
**nota**  
La siguiente instrucción `CREATE TABLE` de ejemplo incluye las columnas `classification`, `classification_reason` y `conn_trace_id` (“ID de trazabilidad”, o TID) agregadas recientemente. Para crear una tabla para los registros de acceso al equilibrador de carga de aplicación que no contenga estas entradas, elimine las columnas correspondientes de la instrucción `CREATE TABLE` y modifique la expresión regular en consecuencia. 

   ```
   CREATE EXTERNAL TABLE IF NOT EXISTS alb_access_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,
               conn_trace_id 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://amzn-s3-demo-bucket/access-log-folder-path/'
   ```
**nota**  
Se sugiere que el patrón *`?( .*)?`* al final del parámetro `input.regex` permanezca siempre, para manejar futuras entradas de registro en caso de que se agreguen nuevos campos de registro de ALB. 

1. Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla `alb_access_logs`, dejando los datos que contiene listos para efectuar consultas.

# Creación de la tabla para los registros de acceso del ALB en Athena mediante la proyección de particiones
<a name="create-alb-access-logs-table-partition-projection"></a>

Dado que los registros de acceso del ALB tienen una estructura conocida, cuyo esquema de partición puede especificarse de antemano, es posible reducir el tiempo de ejecución de las consultas y automatizar la administración de particiones mediante la característica de proyección de particiones de Athena. La proyección de particiones agrega de forma automática nuevas particiones a medida que se agregan nuevos datos. Esto hace que no sea necesario agregar particiones manualmente mediante `ALTER TABLE ADD PARTITION`. 

El siguiente ejemplo de instrucción `CREATE TABLE` utiliza de manera automática la proyección de particiones en registros de acceso del ALB desde una fecha especificada hasta el presente para una sola región de AWS. La instrucción se basa en el ejemplo de la sección anterior, pero agrega las cláusulas `PARTITIONED BY` y `TBLPROPERTIES` para habilitar la proyección de la partición. En las cláusulas `LOCATION` y `storage.location.template`, reemplace los marcadores de posición por valores que identifiquen la ubicación del bucket de Amazon S3 de los registros del ALB. Para obtener más información sobre la ubicación del archivo de registro de acceso, consulte [Archivos de registro de acceso](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-access-logs.html#access-log-file-format) en la *Guía del usuario para equilibradores de carga de aplicación*. Para `projection.day.range`, reemplace *2022*/*01*/*01* por la fecha de inicio que quiere utilizar. Una vez ejecutada la consulta correctamente, puede consultar la tabla. No tiene que ejecutar `ALTER TABLE ADD PARTITION` para cargar las particiones. Para obtener información sobre cada campo del archivo de registro, consulte [Acceso a las entradas del registro](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-access-logs.html#access-log-entry-format). 

```
CREATE EXTERNAL TABLE IF NOT EXISTS alb_access_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,
            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]*) ([-.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://amzn-s3-demo-bucket/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/'
            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://amzn-s3-demo-bucket/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/${day}"
            )
```

Para obtener más información sobre la proyección de particiones, consulte [Uso de proyección de particiones con Amazon Athena](partition-projection.md).

**nota**  
Se sugiere que el patrón *?( .\$1)?* al final del parámetro `input.regex` se mantenga siempre para manejar futuras entradas de registro en caso de que se agreguen nuevos campos de registro de ALB. 

# Consultas de ejemplo para registros de acceso del ALB
<a name="query-alb-access-logs-examples"></a>

La siguiente consulta cuenta el número de solicitudes HTTP GET que el equilibrador de carga ha recibido, agrupadas en función de la dirección IP del cliente:

```
SELECT COUNT(request_verb) AS
 count,
 request_verb,
 client_ip
FROM alb_access_logs
GROUP BY request_verb, client_ip
LIMIT 100;
```

Otra consulta muestra las direcciones URL visitadas por los usuarios del navegador Safari:

```
SELECT request_url
FROM alb_access_logs
WHERE user_agent LIKE '%Safari%'
LIMIT 10;
```

En la siguiente consulta, se muestran registros que tienen valores de código de estado de ELB superiores o iguales a 500.

```
SELECT * FROM alb_access_logs
WHERE elb_status_code >= 500
```

En el siguiente ejemplo, se muestra cómo analizar los registros por `datetime`:

```
SELECT client_ip, sum(received_bytes) 
FROM alb_access_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
     BETWEEN parse_datetime('2018-05-30-12:00:00','yyyy-MM-dd-HH:mm:ss') 
     AND parse_datetime('2018-05-31-00:00:00','yyyy-MM-dd-HH:mm:ss') 
GROUP BY client_ip;
```

La siguiente consulta examina la tabla que utiliza la proyección de particiones en busca de todos los registros de acceso al equilibrador de carga de aplicación del día especificado.

```
SELECT * 
FROM alb_access_logs 
WHERE day = '2022/02/12'
```

# Creación de la tabla para los registros de conexión del ALB
<a name="create-alb-connection-logs-table"></a>

1. Copie y pegue la siguiente instrucción `CREATE TABLE` de ejemplo en el editor de consultas de la Consola de Athena y, a continuación, modifíquela según sea necesario para satisfacer sus propios requisitos de entrada de registros. Para obtener más información acerca de cómo empezar a usar la consola de Athena, consulte [Introducción](getting-started.md). Sustituya la ruta de la cláusula `LOCATION` por la ubicación de la carpeta del registro de conexiones de Amazon S3. Para obtener más información sobre la ubicación del archivo de registro de conexión, consulte [Archivos de registro de conexión](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-connection-logs.html#connection-log-file-format) en la *Guía del usuario para equilibradores de carga de aplicación*. Para obtener información sobre cada campo del archivo de registro, consulte [Entradas del registro de conexión](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-connection-logs.html#connection-log-entry-format). 

   ```
   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
            ) 
            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://amzn-s3-demo-bucket/connection-log-folder-path/'
   ```

1. Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla `alb_connection_logs`, dejando los datos que contiene listos para efectuar consultas.

# Creación de la tabla para los registros de conexión del ALB en Athena mediante la proyección de particiones
<a name="create-alb-connection-logs-table-partition-projection"></a>

Dado que los registros de conexión del ALB tienen una estructura conocida, cuyo esquema de partición puede especificarse de antemano, es posible reducir el tiempo de ejecución de las consultas y automatizar la administración de particiones mediante la característica de proyección de particiones de Athena. La proyección de particiones agrega de forma automática nuevas particiones a medida que se agregan nuevos datos. Esto hace que no sea necesario agregar particiones manualmente mediante `ALTER TABLE ADD PARTITION`. 

El siguiente ejemplo de instrucción `CREATE TABLE` utiliza de manera automática la proyección de particiones en registros de conexión del ALB desde una fecha especificada hasta el presente para una sola región de AWS. La instrucción se basa en el ejemplo de la sección anterior, pero agrega las cláusulas `PARTITIONED BY` y `TBLPROPERTIES` para habilitar la proyección de la partición. En las cláusulas `LOCATION` y `storage.location.template`, reemplace los marcadores de posición por valores que identifiquen la ubicación del bucket de Amazon S3 de los registros de conexión del ALB. Para obtener más información sobre la ubicación del archivo de registro de conexión, consulte [Archivos de registro de conexión](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-connection-logs.html#connection-log-file-format) en la *Guía del usuario para equilibradores de carga de aplicación*. Para `projection.day.range`, reemplace *2023*/*01*/*01* por la fecha de inicio que quiere utilizar. Una vez ejecutada la consulta correctamente, puede consultar la tabla. No tiene que ejecutar `ALTER TABLE ADD PARTITION` para cargar las particiones. Para obtener información sobre cada campo del archivo de registro, consulte [Entradas del registro de conexión](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-connection-logs.html#connection-log-entry-format).

```
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://amzn-s3-demo-bucket/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://amzn-s3-demo-bucket/AWSLogs/<ACCOUNT-NUMBER>/elasticloadbalancing/<REGION>/${day}"
            )
```

Para obtener más información sobre la proyección de particiones, consulte [Uso de proyección de particiones con Amazon Athena](partition-projection.md).

# Consultas de ejemplo para registros de conexión del ALB
<a name="query-alb-connection-logs-examples"></a>

El siguiente recuento de consultas se produce cuando el valor de `tls_verify_status` no era `'Success'`, agrupadas por dirección IP del cliente:

```
SELECT DISTINCT client_ip, count() AS count FROM alb_connection_logs
WHERE tls_verify_status != 'Success'
GROUP BY client_ip
ORDER BY count() DESC;
```

La siguiente consulta busca los incidentes en los que el valor de `tls_handshake_latency` fuera superior a 2 segundos en el intervalo de tiempo especificado:

```
SELECT * FROM alb_connection_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);
```

# Recursos adicionales
<a name="application-load-balancer-logs-additional-resources"></a>

Para más información sobre los registros del ALB, consulte los siguientes recursos.
+ [¿Cómo analizo los registros de acceso al Equilibrador de carga de aplicación mediante Amazon Athena](https://repost.aws/knowledge-center/athena-analyze-access-logs) en el *Centro de conocimientos de AWS*.
+ Para obtener información sobre los códigos de estado HTTP de Elastic Load Balancing, consulte [Solución de problemas de equilibradores de carga de aplicación](https://docs.aws.amazon.com/elasticloadbalancing/latest/application/load-balancer-troubleshooting.html) en la *Guía del usuario para equilibradores de carga de aplicación*.
+ [Catalogue y analice los registros del Equilibrador de carga de aplicación de forma más eficiente con clasificadores AWS Glue personalizados y Amazon Athena](https://aws.amazon.com/blogs/big-data/catalog-and-analyze-application-load-balancer-logs-more-efficiently-with-aws-glue-custom-classifiers-and-amazon-athena/) en el *Blog de macrodatos de AWS*.

# Consulta de los registros del Equilibrador de carga clásico
<a name="elasticloadbalancer-classic-logs"></a>

Utilice los registros del Equilibrador de carga clásico para analizar y conocer los patrones del tráfico desde y hacia instancias de Elastic Load Balancing y aplicaciones de backend. Puede ver el origen del tráfico, su latencia y los bytes que se han transferido.

Antes de analizar los registros del Elastic Load Balancing, configúrelos para que se guarden en el bucket de Amazon S3 de destino. Para obtener más información, consulte [Habilitación de los registros de acceso del equilibrador de carga clásico](https://docs.aws.amazon.com/elasticloadbalancing/latest/classic/enable-access-logs.html).

**Cómo crear la tabla de registros de Elastic Load Balancing**

1. Copie y pegue la siguiente instrucción DDL en la consola de Athena. Verifique la [sintaxis](https://docs.aws.amazon.com/elasticloadbalancing/latest/classic/access-log-collection.html#access-log-entry-format) de los registros log de Elastic Load Balancing. Es posible que tenga que actualizar la siguiente consulta para incluir las columnas y la sintaxis Regex de la versión más reciente del registro. 

   ```
   CREATE EXTERNAL TABLE IF NOT EXISTS elb_logs (
    
    timestamp string,
    elb_name string,
    request_ip string,
    request_port int,
    backend_ip string,
    backend_port int,
    request_processing_time double,
    backend_processing_time double,
    client_response_time double,
    elb_response_code string,
    backend_response_code string,
    received_bytes bigint,
    sent_bytes bigint,
    request_verb string,
    url string,
    protocol string,
    user_agent string,
    ssl_cipher string,
    ssl_protocol 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.-]*)$'
   )
   LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/AWS_account_ID/elasticloadbalancing/';
   ```

1. Modifique el bucket de Amazon S3 `LOCATION` para especificar el destino de los registros de Elastic Load Balancing.

1. Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla `elb_logs` y los datos que contiene quedan disponibles para efectuar consultas. Para obtener más información, consulte [Consultas de ejemplo](#query-elb-classic-example).

## Consultas de ejemplo
<a name="query-elb-classic-example"></a>

Utilice una consulta similar a la del siguiente ejemplo. En ella se muestran los servidores de aplicaciones backend que han devuelto un código de respuesta de error `4XX` o `5XX`. Utilice el operador `LIMIT` para limitar el número de registros que devuelve la consulta.

```
SELECT
 timestamp,
 elb_name,
 backend_ip,
 backend_response_code
FROM elb_logs
WHERE backend_response_code LIKE '4%' OR
      backend_response_code LIKE '5%'
LIMIT 100;
```

Ejecute una consulta adicional para sumar el tiempo de respuesta de todas las transacciones agrupado por la dirección IP y el nombre de la instancia de Elastic Load Balancing del backend.

```
SELECT sum(backend_processing_time) AS
 total_ms,
 elb_name,
 backend_ip
FROM elb_logs WHERE backend_ip <> ''
GROUP BY backend_ip, elb_name
LIMIT 100;
```

Para obtener más información, consulte [Análisis de datos en S3 con Athena](https://aws.amazon.com/blogs/big-data/analyzing-data-in-s3-using-amazon-athena/).

# Consultas de registros de Amazon CloudFront
<a name="cloudfront-logs"></a>

Puede configurar Amazon CloudFront CDN para que exporte los registros de acceso a distribuciones web a Amazon Simple Storage Service. Con estos registros podrá explorar los patrones de navegación de los usuarios en función de las propiedades web ofrecidas por CloudFront.

Para poder consultar los registros, debe haber habilitado previamente el registro de acceso a distribuciones web en su distribución de CloudFront preferida. Para obtener más información, consulte [Registros de acceso](https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/AccessLogs.html) en la *Guía para desarrolladores de Amazon CloudFront*. Anote el bucket de Amazon S3 en el que guarda estos registros.

**Topics**
+ [Creación de una tabla para los registros estándar de CloudFront (heredados)](create-cloudfront-table-standard-logs.md)
+ [Creación de una tabla para los registros de CloudFront en Athena mediante la partición manual con JSON](create-cloudfront-table-manual-json.md)
+ [Creación de una tabla para los registros de CloudFront en Athena mediante la partición manual con Parquet](create-cloudfront-table-manual-parquet.md)
+ [Creación de una tabla para los registros de CloudFront en Athena mediante la proyección de particiones con JSON](create-cloudfront-table-partition-json.md)
+ [Creación de una tabla para los registros de CloudFront en Athena mediante la proyección de particiones con Parquet](create-cloudfront-table-partition-parquet.md)
+ [Creación de una tabla para registros en tiempo real de CloudFront](create-cloudfront-table-real-time-logs.md)
+ [Recursos adicionales](cloudfront-logs-additional-resources.md)

# Creación de una tabla para los registros estándar de CloudFront (heredados)
<a name="create-cloudfront-table-standard-logs"></a>

**nota**  
El procedimiento siguiente funciona para los registros de acceso a distribuciones web en CloudFront. No es aplicable a los registros de transmisión de distribuciones RTMP.

**Cómo crear una tabla para los campos de los archivos de registro estándar de CloudFront**

1. Copie y pegue la siguiente instrucción DDL de ejemplo en el Editor de consultas de la consola de Athena. La instrucción de ejemplo utiliza los campos del archivo de registro documentados en la sección [Campos de archivos de registro estándar](https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/AccessLogs.html#BasicDistributionFileFormat) de *Guía para desarrolladores de Amazon CloudFront*. Modifique el valor de `LOCATION` para indicar el bucket de Amazon S3 donde se almacenan los registros. Para obtener información sobre cómo usar el Editor de consultas, consulte [Introducción](getting-started.md).

   Esta consulta especifica `ROW FORMAT DELIMITED` y `FIELDS TERMINATED BY '\t'` para indicar que los campos están delimitados por caracteres de tabulación. Para `ROW FORMAT DELIMITED`, Athena utiliza [LazySimpleSerDe](lazy-simple-serde.md) de forma predeterminada. La columna `date` se ha incluido en una secuencia de escape con acentos graves (`) porque es una palabra reservada en Athena. Para obtener más información, consulte [Aplicación de caracteres de escape a las palabras clave reservadas de las consultas](reserved-words.md).

   ```
   CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_standard_logs (
     `date` DATE,
     time STRING,
     x_edge_location STRING,
     sc_bytes BIGINT,
     c_ip STRING,
     cs_method STRING,
     cs_host STRING,
     cs_uri_stem STRING,
     sc_status INT,
     cs_referrer STRING,
     cs_user_agent STRING,
     cs_uri_query STRING,
     cs_cookie STRING,
     x_edge_result_type STRING,
     x_edge_request_id STRING,
     x_host_header STRING,
     cs_protocol STRING,
     cs_bytes BIGINT,
     time_taken FLOAT,
     x_forwarded_for STRING,
     ssl_protocol STRING,
     ssl_cipher STRING,
     x_edge_response_result_type STRING,
     cs_protocol_version STRING,
     fle_status STRING,
     fle_encrypted_fields INT,
     c_port INT,
     time_to_first_byte FLOAT,
     x_edge_detailed_result_type STRING,
     sc_content_type STRING,
     sc_content_len BIGINT,
     sc_range_start BIGINT,
     sc_range_end BIGINT
   )
   ROW FORMAT DELIMITED 
   FIELDS TERMINATED BY '\t'
   LOCATION 's3://amzn-s3-demo-bucket/'
   TBLPROPERTIES ( 'skip.header.line.count'='2' )
   ```

1. Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla `cloudfront_standard_logs`, dejando los datos que contiene listos para efectuar consultas.

## Consultas de ejemplo
<a name="query-examples-cloudfront-logs"></a>

La siguiente consulta suma el número de bytes enviados por CloudFront entre el 9 y el 11 de junio de 2018. El nombre de la columna de fecha se indica entre comillas dobles, ya que se trata de una palabra reservada.

```
SELECT SUM(bytes) AS total_bytes
FROM cloudfront_standard_logs
WHERE "date" BETWEEN DATE '2018-06-09' AND DATE '2018-06-11'
LIMIT 100;
```

Para eliminar filas duplicadas (por ejemplo, filas vacías duplicadas) de los resultados de la consulta, puede utilizar la instrucción `SELECT DISTINCT`, como en el ejemplo siguiente. 

```
SELECT DISTINCT * 
FROM cloudfront_standard_logs 
LIMIT 10;
```

# Creación de una tabla para los registros de CloudFront en Athena mediante la partición manual con JSON
<a name="create-cloudfront-table-manual-json"></a>

**Cómo crear una tabla para los campos de los archivos de registro estándar de CloudFront con un formato JSON**

1. Copie y pegue la instrucción DDL siguiente de ejemplo en el Editor de consultas de la consola de Athena. La instrucción de ejemplo utiliza los campos del archivo de registro documentados en la sección [Campos de archivos de registro estándar](https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/AccessLogs.html#BasicDistributionFileFormat) de *Guía para desarrolladores de Amazon CloudFront*. Modifique el valor de `LOCATION` para indicar el bucket de Amazon S3 donde se almacenan los registros. 

   Esta consulta utiliza OpenX JSON SerDe con las siguientes propiedades SerDe para leer correctamente los campos JSON en Athena.

   ```
   CREATE EXTERNAL TABLE `cf_logs_manual_partition_json`(
     `date` string , 
     `time` string , 
     `x-edge-location` string , 
     `sc-bytes` string , 
     `c-ip` string , 
     `cs-method` string , 
     `cs(host)` string , 
     `cs-uri-stem` string , 
     `sc-status` string , 
     `cs(referer)` string , 
     `cs(user-agent)` string , 
     `cs-uri-query` string , 
     `cs(cookie)` string , 
     `x-edge-result-type` string , 
     `x-edge-request-id` string , 
     `x-host-header` string , 
     `cs-protocol` string , 
     `cs-bytes` string , 
     `time-taken` string , 
     `x-forwarded-for` string , 
     `ssl-protocol` string , 
     `ssl-cipher` string , 
     `x-edge-response-result-type` string , 
     `cs-protocol-version` string , 
     `fle-status` string , 
     `fle-encrypted-fields` string , 
     `c-port` string , 
     `time-to-first-byte` string , 
     `x-edge-detailed-result-type` string , 
     `sc-content-type` string , 
     `sc-content-len` string , 
     `sc-range-start` string , 
     `sc-range-end` string )
   ROW FORMAT SERDE 
     'org.openx.data.jsonserde.JsonSerDe' 
   WITH SERDEPROPERTIES ( 
     'paths'='c-ip,c-port,cs(Cookie),cs(Host),cs(Referer),cs(User-Agent),cs-bytes,cs-method,cs-protocol,cs-protocol-version,cs-uri-query,cs-uri-stem,date,fle-encrypted-fields,fle-status,sc-bytes,sc-content-len,sc-content-type,sc-range-end,sc-range-start,sc-status,ssl-cipher,ssl-protocol,time,time-taken,time-to-first-byte,x-edge-detailed-result-type,x-edge-location,x-edge-request-id,x-edge-response-result-type,x-edge-result-type,x-forwarded-for,x-host-header') 
   STORED AS INPUTFORMAT 
     'org.apache.hadoop.mapred.TextInputFormat' 
   OUTPUTFORMAT 
     'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
   LOCATION
     's3://amzn-s3-demo-bucket/'
   ```

1. Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla `cf_logs_manual_partition_json`, dejando los datos que contiene listos para efectuar consultas.

## Consultas de ejemplo
<a name="query-examples-cloudfront-logs-manual-json"></a>

La siguiente consulta suma el número de bytes enviados por CloudFront para el 15 de enero de 2025.

```
SELECT sum(cast("sc-bytes" as BIGINT)) as sc
FROM cf_logs_manual_partition_json
WHERE "date"='2025-01-15'
```

Para eliminar filas duplicadas (por ejemplo, filas vacías duplicadas) de los resultados de la consulta, puede utilizar la instrucción `SELECT DISTINCT`, como en el ejemplo siguiente. 

```
SELECT DISTINCT * FROM cf_logs_manual_partition_json
```

# Creación de una tabla para los registros de CloudFront en Athena mediante la partición manual con Parquet
<a name="create-cloudfront-table-manual-parquet"></a>

**Cómo crear una tabla para los campos de los archivos de registro estándar de CloudFront con un formato Parquet**

1. Copie y pegue la instrucción DDL siguiente de ejemplo en el Editor de consultas de la consola de Athena. La instrucción de ejemplo utiliza los campos del archivo de registro documentados en la sección [Campos de archivos de registro estándar](https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/AccessLogs.html#BasicDistributionFileFormat) de *Guía para desarrolladores de Amazon CloudFront*. 

   Esta consulta utiliza ParquetHiveSerDe con las siguientes propiedades SerDe para leer correctamente los campos Parquet en Athena.

   ```
   CREATE EXTERNAL TABLE `cf_logs_manual_partition_parquet`(
     `date` string, 
     `time` string, 
     `x_edge_location` string, 
     `sc_bytes` string, 
     `c_ip` string, 
     `cs_method` string, 
     `cs_host` string, 
     `cs_uri_stem` string, 
     `sc_status` string, 
     `cs_referer` string, 
     `cs_user_agent` string, 
     `cs_uri_query` string, 
     `cs_cookie` string, 
     `x_edge_result_type` string, 
     `x_edge_request_id` string, 
     `x_host_header` string, 
     `cs_protocol` string, 
     `cs_bytes` string, 
     `time_taken` string, 
     `x_forwarded_for` string, 
     `ssl_protocol` string, 
     `ssl_cipher` string, 
     `x_edge_response_result_type` string, 
     `cs_protocol_version` string, 
     `fle_status` string, 
     `fle_encrypted_fields` string, 
     `c_port` string, 
     `time_to_first_byte` string, 
     `x_edge_detailed_result_type` string, 
     `sc_content_type` string, 
     `sc_content_len` string, 
     `sc_range_start` string, 
     `sc_range_end` string)
   ROW FORMAT SERDE 
     'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
   STORED AS INPUTFORMAT 
     'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
   OUTPUTFORMAT 
     'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
   LOCATION
     's3://amzn-s3-demo-bucket/'
   ```

1. Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla `cf_logs_manual_partition_parquet`, dejando los datos que contiene listos para efectuar consultas.

## Consultas de ejemplo
<a name="query-examples-cloudfront-logs-manual-parquet"></a>

La siguiente consulta suma el número de bytes enviados por CloudFront para el 19 de enero de 2025.

```
SELECT sum(cast("sc_bytes" as BIGINT)) as sc
FROM cf_logs_manual_partition_parquet
WHERE "date"='2025-01-19'
```

Para eliminar filas duplicadas (por ejemplo, filas vacías duplicadas) de los resultados de la consulta, puede utilizar la instrucción `SELECT DISTINCT`, como en el ejemplo siguiente. 

```
SELECT DISTINCT * FROM cf_logs_manual_partition_parquet
```

# Creación de una tabla para los registros de CloudFront en Athena mediante la proyección de particiones con JSON
<a name="create-cloudfront-table-partition-json"></a>

Puede reducir el tiempo de ejecución de las consultas y automatizar la administración de particiones con la característica de proyección de particiones de Athena. La proyección de particiones agrega de forma automática nuevas particiones a medida que se agregan nuevos datos. Esto hace que no sea necesario agregar particiones manualmente mediante `ALTER TABLE ADD PARTITION`.

En el siguiente ejemplo, la instrucción CREATE TABLE utiliza automáticamente la proyección de particiones en los registros de CloudFront desde una distribución especificada de CloudFront hasta el presente para una sola Región de AWS. Una vez ejecutada la consulta correctamente, puede consultar la tabla.

```
CREATE EXTERNAL TABLE `cloudfront_logs_pp`(
  `date` string, 
  `time` string, 
  `x-edge-location` string, 
  `sc-bytes` string, 
  `c-ip` string, 
  `cs-method` string, 
  `cs(host)` string, 
  `cs-uri-stem` string, 
  `sc-status` string, 
  `cs(referer)` string, 
  `cs(user-agent)` string, 
  `cs-uri-query` string, 
  `cs(cookie)` string, 
  `x-edge-result-type` string, 
  `x-edge-request-id` string, 
  `x-host-header` string, 
  `cs-protocol` string, 
  `cs-bytes` string, 
  `time-taken` string, 
  `x-forwarded-for` string, 
  `ssl-protocol` string, 
  `ssl-cipher` string, 
  `x-edge-response-result-type` string, 
  `cs-protocol-version` string, 
  `fle-status` string, 
  `fle-encrypted-fields` string, 
  `c-port` string, 
  `time-to-first-byte` string, 
  `x-edge-detailed-result-type` string, 
  `sc-content-type` string, 
  `sc-content-len` string, 
  `sc-range-start` string, 
  `sc-range-end` string)
  PARTITIONED BY(
         distributionid string,
         year int,
         month int,
         day int,
         hour int )
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 
  'paths'='c-ip,c-port,cs(Cookie),cs(Host),cs(Referer),cs(User-Agent),cs-bytes,cs-method,cs-protocol,cs-protocol-version,cs-uri-query,cs-uri-stem,date,fle-encrypted-fields,fle-status,sc-bytes,sc-content-len,sc-content-type,sc-range-end,sc-range-start,sc-status,ssl-cipher,ssl-protocol,time,time-taken,time-to-first-byte,x-edge-detailed-result-type,x-edge-location,x-edge-request-id,x-edge-response-result-type,x-edge-result-type,x-forwarded-for,x-host-header') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_ID/CloudFront/'
TBLPROPERTIES (
  'projection.distributionid.type'='enum',
  'projection.distributionid.values'='E2Oxxxxxxxxxxx',
  'projection.day.range'='01,31', 
  'projection.day.type'='integer', 
  'projection.day.digits'='2', 
  'projection.enabled'='true', 
  'projection.month.range'='01,12', 
  'projection.month.type'='integer', 
  'projection.month.digits'='2', 
  'projection.year.range'='2025,2026', 
  'projection.year.type'='integer', 
  'projection.hour.range'='00,23',
  'projection.hour.type'='integer',
  'projection.hour.digits'='2',
  'storage.location.template'='s3://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_ID/CloudFront/${distributionid}/${year}/${month}/${day}/${hour}/')
```

A continuación, se presentan algunas consideraciones sobre las propiedades utilizadas en el ejemplo anterior.
+ **Nombre de la tabla:** el nombre de la tabla *`cloudfront_logs_pp`* se puede reemplazar. Puede cambiarlo por el nombre que prefiera.
+ **Ubicación:** modifique `s3://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_ID/` para que apunte a su bucket de Amazon S3.
+ **ID de distribución:** para `projection.distributionid.values`, puede especificar varios ID de distribución si los separa con comas. Por ejemplo, *<distributionID1>*, *<distributionID2>*.
+ **Rango de años:** en `projection.year.range`, puede definir el rango de años en función de sus datos. Por ejemplo, puede ajustarlo a cualquier período, como *2025*, *2026*.
**nota**  
La inclusión de particiones vacías, como las de fechas futuras (por ejemplo: 2025-2040), puede afectar al rendimiento de las consultas. Sin embargo, la proyección de particiones está diseñada para gestionar de forma eficaz las fechas futuras. Para mantener un rendimiento óptimo, asegúrese de que las particiones se gestionen de forma cuidadosa y evite el exceso de particiones vacías siempre que sea posible.
+ **Plantilla de ubicación de almacenamiento:** debe asegurarse de actualizar el `storage.location.template` correctamente según la siguiente estructura de particiones de CloudFront y la ruta de S3.  
****    
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/es_es/athena/latest/ug/create-cloudfront-table-partition-json.html)

  Después de confirmar que la estructura de particiones de CloudFront y la estructura de S3 coinciden con los patrones requeridos, actualice el `storage.location.template` de la siguiente manera:

  ```
  'storage.location.template'='s3://amzn-s3-demo-bucket/AWSLogs/account_id/CloudFront/${distributionid}/folder2/${year}/${month}/${day}/${hour}/folder3/'
  ```
**nota**  
La configuración adecuada del `storage.location.template` es crucial para garantizar el correcto almacenamiento y recuperación de los datos.

# Creación de una tabla para los registros de CloudFront en Athena mediante la proyección de particiones con Parquet
<a name="create-cloudfront-table-partition-parquet"></a>

En el siguiente ejemplo, la instrucción CREATE TABLE utiliza automáticamente la proyección de particiones en los registros de CloudFront en Parquet desde una distribución especificada de CloudFront hasta el presente para una sola Región de AWS. Una vez ejecutada la consulta correctamente, puede consultar la tabla.

```
CREATE EXTERNAL TABLE `cloudfront_logs_parquet_pp`(
`date` string, 
`time` string, 
`x_edge_location` string, 
`sc_bytes` string, 
`c_ip` string, 
`cs_method` string, 
`cs_host` string, 
`cs_uri_stem` string, 
`sc_status` string, 
`cs_referer` string, 
`cs_user_agent` string, 
`cs_uri_query` string, 
`cs_cookie` string, 
`x_edge_result_type` string, 
`x_edge_request_id` string, 
`x_host_header` string, 
`cs_protocol` string, 
`cs_bytes` string, 
`time_taken` string, 
`x_forwarded_for` string, 
`ssl_protocol` string, 
`ssl_cipher` string, 
`x_edge_response_result_type` string, 
`cs_protocol_version` string, 
`fle_status` string, 
`fle_encrypted_fields` string, 
`c_port` string, 
`time_to_first_byte` string, 
`x_edge_detailed_result_type` string, 
`sc_content_type` string, 
`sc_content_len` string, 
`sc_range_start` string, 
`sc_range_end` string)
PARTITIONED BY(
 distributionid string,
 year int,
 month int,
 day int,
 hour int )
ROW FORMAT SERDE 
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
's3://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_ID/CloudFront/'
TBLPROPERTIES (
'projection.distributionid.type'='enum',
'projection.distributionid.values'='E3OK0LPUNWWO3',
'projection.day.range'='01,31',
'projection.day.type'='integer',
'projection.day.digits'='2',
'projection.enabled'='true',
'projection.month.range'='01,12',
'projection.month.type'='integer',
'projection.month.digits'='2',
'projection.year.range'='2019,2025',
'projection.year.type'='integer',
'projection.hour.range'='01,12',
'projection.hour.type'='integer',
'projection.hour.digits'='2',
'storage.location.template'='s3://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_ID/CloudFront/${distributionid}/${year}/${month}/${day}/${hour}/')
```

A continuación, se presentan algunas consideraciones sobre las propiedades utilizadas en el ejemplo anterior.
+ **Nombre de la tabla:** el nombre de la tabla *`cloudfront_logs_pp`* se puede reemplazar. Puede cambiarlo por el nombre que prefiera.
+ **Ubicación:** modifique `s3://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_ID/` para que apunte a su bucket de Amazon S3.
+ **ID de distribución:** para `projection.distributionid.values`, puede especificar varios ID de distribución si los separa con comas. Por ejemplo, *<distributionID1>*, *<distributionID2>*.
+ **Rango de años:** en `projection.year.range`, puede definir el rango de años en función de sus datos. Por ejemplo, puede ajustarlo a cualquier período, como *2025*, *2026*.
**nota**  
La inclusión de particiones vacías, como las de fechas futuras (por ejemplo: 2025-2040), puede afectar al rendimiento de las consultas. Sin embargo, la proyección de particiones está diseñada para gestionar de forma eficaz las fechas futuras. Para mantener un rendimiento óptimo, asegúrese de que las particiones se gestionen de forma cuidadosa y evite el exceso de particiones vacías siempre que sea posible.
+ **Plantilla de ubicación de almacenamiento:** debe asegurarse de actualizar el `storage.location.template` correctamente según la siguiente estructura de particiones de CloudFront y la ruta de S3.  
****    
[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/es_es/athena/latest/ug/create-cloudfront-table-partition-parquet.html)

  Después de confirmar que la estructura de particiones de CloudFront y la estructura de S3 coinciden con los patrones requeridos, actualice el `storage.location.template` de la siguiente manera:

  ```
  'storage.location.template'='s3://amzn-s3-demo-bucket/AWSLogs/account_id/CloudFront/${distributionid}/folder2/${year}/${month}/${day}/${hour}/folder3/'
  ```
**nota**  
La configuración adecuada del `storage.location.template` es crucial para garantizar el correcto almacenamiento y recuperación de los datos.

# Creación de una tabla para registros en tiempo real de CloudFront
<a name="create-cloudfront-table-real-time-logs"></a>

**Cómo crear una tabla para los campos de los archivos de registro en tiempo real de CloudFront**

1. Copie y pegue la siguiente instrucción DDL de ejemplo en el Editor de consultas de la consola de Athena. La instrucción de ejemplo utiliza los campos del archivo de registro documentados en la sección [Registros en tiempo real](https://docs.aws.amazon.com/AmazonCloudFront/latest/DeveloperGuide/real-time-logs.html) de la *Guía para desarrolladores de Amazon CloudFront*. Modifique el valor de `LOCATION` para indicar el bucket de Amazon S3 donde se almacenan los registros. Para obtener información sobre cómo usar el Editor de consultas, consulte [Introducción](getting-started.md).

   Esta consulta especifica `ROW FORMAT DELIMITED` y `FIELDS TERMINATED BY '\t'` para indicar que los campos están delimitados por caracteres de tabulación. Para `ROW FORMAT DELIMITED`, Athena utiliza [LazySimpleSerDe](lazy-simple-serde.md) de forma predeterminada. La columna `timestamp` se ha incluido en una secuencia de escape con acentos graves (`) porque es una palabra reservada en Athena. Para obtener más información, consulte [Aplicación de caracteres de escape a las palabras clave reservadas de las consultas](reserved-words.md).

   El siguiente ejemplo contiene todos los campos disponibles. Puede añadir comentarios en los campos o eliminar los que no necesite.

   ```
   CREATE EXTERNAL TABLE IF NOT EXISTS cloudfront_real_time_logs ( 
   `timestamp` STRING,
   c_ip STRING,
   time_to_first_byte BIGINT,
   sc_status BIGINT,
   sc_bytes BIGINT,
   cs_method STRING,
   cs_protocol STRING,
   cs_host STRING,
   cs_uri_stem STRING,
   cs_bytes BIGINT,
   x_edge_location STRING,
   x_edge_request_id STRING,
   x_host_header STRING,
   time_taken BIGINT,
   cs_protocol_version STRING,
   c_ip_version STRING,
   cs_user_agent STRING,
   cs_referer STRING,
   cs_cookie STRING,
   cs_uri_query STRING,
   x_edge_response_result_type STRING,
   x_forwarded_for STRING,
   ssl_protocol STRING,
   ssl_cipher STRING,
   x_edge_result_type STRING,
   fle_encrypted_fields STRING,
   fle_status STRING,
   sc_content_type STRING,
   sc_content_len BIGINT,
   sc_range_start STRING,
   sc_range_end STRING,
   c_port BIGINT,
   x_edge_detailed_result_type STRING,
   c_country STRING,
   cs_accept_encoding STRING,
   cs_accept STRING,
   cache_behavior_path_pattern STRING,
   cs_headers STRING,
   cs_header_names STRING,
   cs_headers_count BIGINT,
   primary_distribution_id STRING,
   primary_distribution_dns_name STRING,
   origin_fbl STRING,
   origin_lbl STRING,
   asn STRING
   )
   ROW FORMAT DELIMITED 
   FIELDS TERMINATED BY '\t'
   LOCATION 's3://amzn-s3-demo-bucket/'
   TBLPROPERTIES ( 'skip.header.line.count'='2' )
   ```

1. Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla `cloudfront_real_time_logs`, dejando los datos que contiene listos para efectuar consultas.

# Recursos adicionales
<a name="cloudfront-logs-additional-resources"></a>

Para obtener más información sobre cómo utilizar Athena para realizar consultas en los registros de CloudFront, consulte las siguientes publicaciones del [Blog de macrodatos de AWS](https://aws.amazon.com/blogs/big-data/).

[Consulte los registros de Servicio de AWS fácilmente con Amazon Athena)](https://aws.amazon.com/blogs/big-data/easily-query-aws-service-logs-using-amazon-athena/) (29 de mayo de 2019).

[Analyze your Amazon CloudFront access logs at scale](https://aws.amazon.com/blogs/big-data/analyze-your-amazon-cloudfront-access-logs-at-scale/) (21 de diciembre de 2018).

[Cree una arquitectura sin servidor para analizar los registros de acceso de Amazon CloudFront con AWS Lambda, Amazon Athena, y Amazon Managed Service para Apache Flink](https://aws.amazon.com/blogs/big-data/build-a-serverless-architecture-to-analyze-amazon-cloudfront-access-logs-using-aws-lambda-amazon-athena-and-amazon-kinesis-analytics/) (26 de mayo de 2017).

# Consulta de registros de AWS CloudTrail
<a name="cloudtrail-logs"></a>

AWS CloudTrail es un servicio que registra las llamadas a la API de AWS y los eventos para las cuentas de Amazon Web Services. 

Los registros de CloudTrail contienen detalles sobre las llamadas a la API a los Servicios de AWS, incluida la consola. CloudTrail genera archivos de registro cifrados y los almacena en Amazon S3. Para obtener más información, consulte la [Guía del usuario de AWS CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-user-guide.html). 

**nota**  
Si se desea realizar consultas SQL sobre la información de eventos de CloudTrail en diversas cuentas, regiones y fechas, se puede utilizar CloudTrail Lake. CloudTrail Lake es una alternativa de AWS a la creación de trazas que agrega información de una empresa en un único almacén de datos de eventos con capacidad de búsqueda. En lugar de utilizar el almacenamiento de bucket de Amazon S3, almacena los eventos en un lago de datos, lo que permite realizar consultas más ricas y rápidas. Se puede utilizar para crear consultas SQL que buscan eventos en organizaciones, regiones y dentro de intervalos de tiempo personalizados. Dado que realiza consultas de CloudTrail Lake dentro de la consola de CloudTrail, usarlo no requiere Athena. Para obtener más información, consulte la documentación de [CloudTrail Lake](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-lake.html).

El uso de Athena con los registros de CloudTrail supone un modo eficaz de mejorar el análisis de la actividad de un Servicio de AWS. Por ejemplo, puede ejecutar consultas que identifiquen tendencias y aislar la actividad por atributos, como el usuario o la dirección IP de origen.

Una aplicación habitual es usar los registros de CloudTrail para analizar la actividad operativa a fin de garantizar la seguridad y la conformidad. Para obtener información sobre un ejemplo detallado, consulte la publicación del Blog de macrodatos de AWS, [Analyze security, compliance, and operational activity using AWS CloudTrail and Amazon Athena](https://aws.amazon.com/blogs/big-data/aws-cloudtrail-and-amazon-athena-dive-deep-to-analyze-security-compliance-and-operational-activity/).

Athena le permite consultar estos archivos de registro directamente desde Amazon S3 especificando su ubicación con `LOCATION`. Puede hacerlo de una de las dos formas siguientes:
+ Creando tablas para los archivos de registro de CloudTrail directamente desde la consola de CloudTrail.
+ Creando tablas manualmente para los archivos de registro de CloudTrail en la consola de Athena.

**Topics**
+ [Descripción de los registros de CloudTrail y las tablas de Athena](create-cloudtrail-table-understanding.md)
+ [Uso de la consola de CloudTrail para crear una tabla de Athena para registros de CloudTrail](create-cloudtrail-table-ct.md)
+ [Creación de una tabla para los registros de CloudTrail en Athena mediante la partición manual](create-cloudtrail-table.md)
+ [Creación de una tabla para la ruta de toda una organización mediante particiones manuales](create-cloudtrail-table-org-wide-trail.md)
+ [Creación de la tabla para los registros de CloudTrail en Athena mediante la proyección de particiones](create-cloudtrail-table-partition-projection.md)
+ [Consultas de registros de CloudTrail de ejemplo](query-examples-cloudtrail-logs.md)

# Descripción de los registros de CloudTrail y las tablas de Athena
<a name="create-cloudtrail-table-understanding"></a>

Antes de comenzar a crear tablas, debe comprender mejor el funcionamiento de CloudTrail y la forma en la que almacena los datos. Esto puede ayudarlo a crear las tablas que necesita, tanto si lo hace con la consola de CloudTrail como si usa Athena.

CloudTrail guarda los registros como archivos de texto JSON en formato comprimido gzip (`*.json.gz`). La ubicación de los archivos de registro depende del modo en que se configuran los registros de seguimiento, la Región de AWS (o regiones) donde se lleva a cabo el registro y otros factores. 

Para obtener más información sobre el lugar donde se almacenan los logs, la estructura de JSON y el contenido de los archivos de registro, consulte los siguientes temas en la [Guía del usuario de AWS CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-user-guide.html):
+  [Búsqueda de archivos de registros de CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-find-log-files.html) 
+  [Ejemplos de archivos de registros de CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-log-file-examples.html) 
+  [Contenido de los registros de CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-event-reference-record-contents.html)
+  [Referencia de eventos de CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-event-reference.html) 

Para recopilar los registros y guardarlos en Amazon S3, habilite CloudTrail desde la Consola de administración de AWS. Para obtener más información, consulte [Crear un registro de seguimiento](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-create-a-trail-using-the-console-first-time.html) en la *Guía del usuario de AWS CloudTrail*.

# Uso de la consola de CloudTrail para crear una tabla de Athena para registros de CloudTrail
<a name="create-cloudtrail-table-ct"></a>

Puede crear una tabla de Athena no particionada para consultar registros de CloudTrail directamente desde la consola de CloudTrail. La creación de una tabla de Athena desde la consola de CloudTrail requiere que inicie sesión con un rol que tenga permisos suficientes para crear tablas en Athena.

**nota**  
No puede utilizar la consola de CloudTrail para crear una tabla de Athena para los registros de seguimiento de la organización. En su lugar, cree la tabla manualmente con la consola de Athena para que pueda especificar la ubicación de almacenamiento correcta. Para obtener información sobre los registros de seguimiento de una organización, consulte [Creación de registros de seguimiento de una organización](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/creating-trail-organization.html) en la *Guía del usuario de AWS CloudTrail*.
+ Para obtener información sobre cómo configurar permisos de Athena, consulte [Configuración de acceso administrativo y de programación](setting-up.md).
+ Para obtener información sobre la creación de una tabla con particiones, consulte [Creación de una tabla para los registros de CloudTrail en Athena mediante la partición manual](create-cloudtrail-table.md).

**Para usar la consola de CloudTrail a fin de crear una tabla de Athena para registros de CloudTrail**

1. Abra la consola de CloudTrail en [https://console.aws.amazon.com/cloudtrail/](https://console.aws.amazon.com/cloudtrail/).

1. En el panel de navegación, elija **Historial de eventos**. 

1. Elija **Creación de una tabla de Athena**.  
![\[Elija Creación de una tabla de Athena\]](http://docs.aws.amazon.com/es_es/athena/latest/ug/images/cloudtrail-logs-create-athena-table.png)

1. En **Ubicación de almacenamiento**, utilice la flecha hacia abajo para seleccionar el bucket de Amazon S3 en el que se almacenan los archivos de registro de seguimiento que desea consultar.
**nota**  
Para buscar el nombre del bucket asociado a un registro de seguimiento, elija **Registros de seguimiento** en el panel de navegación de CloudTrail y vea la columna del **bucket de S3** del registro de seguimiento. Para ver la ubicación del bucket en Amazon S3, elija el enlace del bucket en la columna del **bucket de S3**. Esto abre la consola de Amazon S3 en la ubicación del bucket de CloudTrail. 

1. Seleccione **Create table (Creación de tabla)**. La tabla se crea con un nombre predeterminado que incluye el nombre del bucket de Amazon S3.

# Creación de una tabla para los registros de CloudTrail en Athena mediante la partición manual
<a name="create-cloudtrail-table"></a>

Puede crear manualmente tablas para los archivos de registro de CloudTrail en la consola de Athena y, a continuación, ejecutar consultas en Athena.

**Para crear una tabla de Athena para un registro de seguimiento de CloudTrail con la consola de Athena**

1. Copie y pegue la siguiente instrucción de DDL en el editor de consultas de la consola de Athena y, a continuación, modifíquela según sea necesario para sus propios requisitos de entrada de registros. Tenga en cuenta que, dado que los archivos de registro de CloudTrail no son un seguimiento ordenado de las llamadas a la API públicas, los campos de los archivos de registro no aparecen en ningún orden específico.

   ```
   CREATE EXTERNAL TABLE cloudtrail_logs (
   eventversion STRING,
   useridentity STRUCT<
                  type:STRING,
                  principalid:STRING,
                  arn:STRING,
                  accountid:STRING,
                  invokedby:STRING,
                  accesskeyid:STRING,
                  username:STRING,
                  onbehalfof: STRUCT<
                       userid: STRING,
                       identitystorearn: STRING>,
     sessioncontext:STRUCT<
       attributes:STRUCT<
                  mfaauthenticated:STRING,
                  creationdate:STRING>,
       sessionissuer:STRUCT<  
                  type:STRING,
                  principalid:STRING,
                  arn:STRING, 
                  accountid:STRING,
                  username:STRING>,
       ec2roledelivery:string,
       webidfederationdata: STRUCT<
                  federatedprovider: STRING,
                  attributes: map<string,string>>
     >
   >,
   eventtime STRING,
   eventsource STRING,
   eventname STRING,
   awsregion STRING,
   sourceipaddress STRING,
   useragent STRING,
   errorcode STRING,
   errormessage STRING,
   requestparameters STRING,
   responseelements STRING,
   additionaleventdata STRING,
   requestid STRING,
   eventid STRING,
   resources ARRAY<STRUCT<
                  arn:STRING,
                  accountid:STRING,
                  type:STRING>>,
   eventtype STRING,
   apiversion STRING,
   readonly STRING,
   recipientaccountid STRING,
   serviceeventdetails STRING,
   sharedeventid STRING,
   vpcendpointid STRING,
   vpcendpointaccountid STRING,
   eventcategory STRING,
   addendum STRUCT<
     reason:STRING,
     updatedfields:STRING,
     originalrequestid:STRING,
     originaleventid:STRING>,
   sessioncredentialfromconsole STRING,
   edgedevicedetails STRING,
   tlsdetails STRUCT<
     tlsversion:STRING,
     ciphersuite:STRING,
     clientprovidedhostheader:STRING>
   )
   PARTITIONED BY (region string, year string, month string, day string)
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
   OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
   LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/Account_ID/';
   ```
**nota**  
Sugerimos usar `org.apache.hive.hcatalog.data.JsonSerDe` como se muestra en el ejemplo. Aunque existe `com.amazon.emr.hive.serde.CloudTrailSerde`, actualmente no gestiona algunos de los campos más recientes de CloudTrail. 

1. (Opcional) Elimine los campos que no sean obligatorios para la tabla. Si solo necesita leer un determinado conjunto de columnas, la definición de la tabla puede excluir las demás columnas.

1. Modifique `s3://amzn-s3-demo-bucket/AWSLogs/Account_ID/` de modo que apunte al bucket de Amazon S3 que contiene los datos de registro que desea consultar. En el ejemplo se usa como valor de `LOCATION` los registros de una cuenta determinada, pero puede especificar tanto como requiera su aplicación. Por ejemplo:
   + Para analizar datos de varias cuentas, puede modificar el especificador `LOCATION` para que indique todos los `AWSLogs` con el valor `LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/'`.
   + Para analizar los datos de una fecha, cuenta y región específicas, utilice `LOCATION 's3://amzn-s3-demo-bucket/123456789012/CloudTrail/us-east-1/2016/03/14/'.` 
   + Para analizar los datos de actividad de la red en lugar de los eventos de administración, sustituya `/CloudTrail/` en la cláusula `LOCATION` por `/CloudTrail-NetworkActivity/`. 

   El uso del máximo nivel en la jerarquía de objetos le ofrece la mayor flexibilidad al ejecutar consultas con Athena.

1. Compruebe que los campos se muestran correctamente. Para obtener más información sobre la lista completa de campos de un registro de CloudTrail, consulte [Contenido de los registros de CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-event-reference-record-contents.html).

   La instrucción `CREATE TABLE` de ejemplo del paso 1 utiliza [El SerDe JSON de Hive](hive-json-serde.md). En el ejemplo, los campos `requestparameters`, `responseelements` y `additionaleventdata` aparecen como tipo `STRING` en la consulta, pero son del tipo de datos `STRUCT`utilizado en JSON. Por lo tanto, para obtener datos de estos campos puede usar funciones `JSON_EXTRACT`. Para obtener más información, consulte [Extracción de datos JSON de cadenas](extracting-data-from-JSON.md). Para mejorar el rendimiento, este ejemplo particiona los datos por Región de AWS, año, mes y día.

1. Ejecutar la instrucción `CREATE TABLE` en la consola de Athena.

1. Utilice el comando [ALTER TABLE ADD PARTITION](alter-table-add-partition.md) para cargar las particiones de modo que pueda consultarlas, como en el ejemplo siguiente.

   ```
   ALTER TABLE table_name ADD 
      PARTITION (region='us-east-1',
                 year='2019',
                 month='02',
                 day='01')
      LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/Account_ID/CloudTrail/us-east-1/2019/02/01/'
   ```

# Creación de una tabla para la ruta de toda una organización mediante particiones manuales
<a name="create-cloudtrail-table-org-wide-trail"></a>

Para crear una tabla para los archivos de registro de CloudTrail de toda la organización en Athena, siga los pasos de [Creación de una tabla para los registros de CloudTrail en Athena mediante la partición manual](create-cloudtrail-table.md), pero realice las modificaciones que se indican en el siguiente procedimiento.

**Para crear una tabla de Athena para los registros de CloudTrail de toda una organización**

1. En la instrucción `CREATE TABLE`, modifique la cláusula `LOCATION` para incluir el ID de organización, como en el siguiente ejemplo:

   ```
   LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/organization_id/'
   ```

1. En la cláusula `PARTITIONED BY`, agregue una entrada para el ID de cuenta en forma de cadena, como en el siguiente ejemplo:

   ```
   PARTITIONED BY (account string, region string, year string, month string, day string)
   ```

   En el siguiente ejemplo, se muestra el resultado combinado:

   ```
   ...
   
   PARTITIONED BY (account string, region string, year string, month string, day string) 
   ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
   STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
   OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
   LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/organization_id/Account_ID/CloudTrail/'
   ```

1. En la cláusula `ADD PARTITION` de la instrucción `ALTER TABLE`, incluya el ID de cuenta, como en el siguiente ejemplo:

   ```
   ALTER TABLE table_name ADD
   PARTITION (account='111122223333',
   region='us-east-1',
   year='2022',
   month='08',
   day='08')
   ```

1. En la cláusula `LOCATION` de la instrucción `ALTER TABLE`, incluya el ID de la organización, el ID de cuenta y la partición que desea agregar, como en el siguiente ejemplo:

   ```
   LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/organization_id/Account_ID/CloudTrail/us-east-1/2022/08/08/'
   ```

   En el siguiente ejemplo, la instrucción `ALTER TABLE` muestra el resultado combinado:

   ```
   ALTER TABLE table_name ADD
   PARTITION (account='111122223333',
   region='us-east-1',
   year='2022',
   month='08',
   day='08')
   LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/organization_id/111122223333/CloudTrail/us-east-1/2022/08/08/'
   ```

Tenga en cuenta que, en una organización grande, el uso de este método para agregar y mantener manualmente una partición para cada ID de cuenta de la organización puede resultar engorroso. En un caso así, puede utilizar CloudTrail Lake en lugar de Athena. CloudTrail Lake ofrece las siguientes ventajas en un caso como el expuesto:
+ Agrega automáticamente registros de toda la organización
+ No requiere configurar ni mantener particiones ni una tabla Athena
+ Las consultas se ejecutan directamente en la consola de CloudTrail
+ Usa un lenguaje de consulta compatible con SQL

Para obtener más información, consulte [Trabajar con AWS CloudTrail Lake](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-lake.html) en la *Guía del usuario de AWS CloudTrail*. 

# Creación de la tabla para los registros de CloudTrail en Athena mediante la proyección de particiones
<a name="create-cloudtrail-table-partition-projection"></a>

Dado que los registros de CloudTrail tienen una estructura conocida cuyo esquema de partición puede especificar de antemano, puede reducir el tiempo de ejecución de las consultas y automatizar la administración de particiones mediante la característica de proyección de particiones de Athena. La proyección de particiones agrega de forma automática nuevas particiones a medida que se agregan nuevos datos. Esto hace que no sea necesario agregar particiones manualmente mediante `ALTER TABLE ADD PARTITION`. 

En el siguiente ejemplo, la instrucción `CREATE TABLE` utiliza automáticamente la proyección de particiones en los registros de CloudTrail desde una fecha especificada hasta el presente para una sola Región de AWS. En las cláusulas `LOCATION` y `storage.location.template`, reemplace los marcadores de posición *bucket*, *account-id* y *aws-region* por valores idénticos en consecuencia. Para `projection.timestamp.range`, reemplace *2020*/*01*/*01* por la fecha de inicio que quiere utilizar. Una vez ejecutada la consulta correctamente, puede consultar la tabla. No tiene que ejecutar `ALTER TABLE ADD PARTITION` para cargar las particiones.

```
CREATE EXTERNAL TABLE cloudtrail_logs_pp(
    eventversion STRING,
    useridentity STRUCT<
        type: STRING,
        principalid: STRING,
        arn: STRING,
        accountid: STRING,
        invokedby: STRING,
        accesskeyid: STRING,
        username: STRING,
        onbehalfof: STRUCT<
             userid: STRING,
             identitystorearn: STRING>,
        sessioncontext: STRUCT<
            attributes: STRUCT<
                mfaauthenticated: STRING,
                creationdate: STRING>,
            sessionissuer: STRUCT<
                type: STRING,
                principalid: STRING,
                arn: STRING,
                accountid: STRING,
                username: STRING>,
            ec2roledelivery:string,
            webidfederationdata: STRUCT<
                federatedprovider: STRING,
                attributes: map<string,string>>
        >
    >,
    eventtime STRING,
    eventsource STRING,
    eventname STRING,
    awsregion STRING,
    sourceipaddress STRING,
    useragent STRING,
    errorcode STRING,
    errormessage STRING,
    requestparameters STRING,
    responseelements STRING,
    additionaleventdata STRING,
    requestid STRING,
    eventid STRING,
    readonly STRING,
    resources ARRAY<STRUCT<
        arn: STRING,
        accountid: STRING,
        type: STRING>>,
    eventtype STRING,
    apiversion STRING,
    recipientaccountid STRING,
    serviceeventdetails STRING,
    sharedeventid STRING,
    vpcendpointid STRING,
    vpcendpointaccountid STRING,
    eventcategory STRING,
    addendum STRUCT<
      reason:STRING,
      updatedfields:STRING,
      originalrequestid:STRING,
      originaleventid:STRING>,
    sessioncredentialfromconsole STRING,
    edgedevicedetails STRING,
    tlsdetails STRUCT<
      tlsversion:STRING,
      ciphersuite:STRING,
      clientprovidedhostheader:STRING>
  )
PARTITIONED BY (
   `timestamp` string)
ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS INPUTFORMAT 'com.amazon.emr.cloudtrail.CloudTrailInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://amzn-s3-demo-bucket/AWSLogs/account-id/CloudTrail/aws-region'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='2020/01/01,NOW', 
  'projection.timestamp.type'='date', 
  'storage.location.template'='s3://amzn-s3-demo-bucket/AWSLogs/account-id/CloudTrail/aws-region/${timestamp}')
```

Para obtener más información sobre la proyección de particiones, consulte [Uso de proyección de particiones con Amazon Athena](partition-projection.md).

# Consultas de registros de CloudTrail de ejemplo
<a name="query-examples-cloudtrail-logs"></a>

En el siguiente ejemplo, se muestra una parte de una consulta que devuelve todas las solicitudes anónimas (sin firmar) de la tabla creada para registros de eventos de CloudTrail. Esta consulta selecciona esas solicitudes donde `useridentity.accountid` es anónimo y no se ha especificado `useridentity.arn`:

```
SELECT *
FROM cloudtrail_logs
WHERE 
    eventsource = 's3.amazonaws.com' AND 
    eventname in ('GetObject') AND 
    useridentity.accountid = 'anonymous' AND 
    useridentity.arn IS NULL AND
    requestparameters LIKE '%[your bucket name ]%';
```

Para obtener más información, consulte la publicación del Blog de macrodatos de AWS [Análisis de la seguridad, la conformidad y la actividad operativa por medio de AWS CloudTrail y Amazon Athena](https://aws.amazon.com/blogs/big-data/aws-cloudtrail-and-amazon-athena-dive-deep-to-analyze-security-compliance-and-operational-activity/).

## Consulta de campos anidados en los registros de CloudTrail
<a name="cloudtrail-logs-nested-fields"></a>

Ya que los campos `userIdentity` y `resources` son tipos de datos anidados, su consulta requiere un tratamiento especial.

El objeto `userIdentity` se compone de tipos `STRUCT` anidados. Estos se pueden consultar utilizando un punto para separar los campos, como en el ejemplo a continuación:

```
SELECT 
    eventsource, 
    eventname,
    useridentity.sessioncontext.attributes.creationdate,
    useridentity.sessioncontext.sessionissuer.arn
FROM cloudtrail_logs
WHERE useridentity.sessioncontext.sessionissuer.arn IS NOT NULL
ORDER BY eventsource, eventname
LIMIT 10
```

El campo `resources` es una matriz de objetos `STRUCT`. Para estas matrices, utilice `CROSS JOIN UNNEST` para desanidar la matriz, de modo que pueda consultar sus objetos.

En el ejemplo siguiente se devuelven todas las filas donde el ARN de recurso termina en `example/datafile.txt`. Por motivos de legibilidad, la función [replace](https://prestodb.io/docs/current/functions/string.html#replace) elimina la subcadena `arn:aws:s3:::` inicial del ARN.

```
SELECT 
    awsregion,
    replace(unnested.resources_entry.ARN,'arn:aws:s3:::') as s3_resource,
    eventname,
    eventtime,
    useragent
FROM cloudtrail_logs t
CROSS JOIN UNNEST(t.resources) unnested (resources_entry)
WHERE unnested.resources_entry.ARN LIKE '%example/datafile.txt'
ORDER BY eventtime
```

Las siguientes son consultas de ejemplo para eventos `DeleteBucket`. La consulta extrae el nombre del bucket y el ID de cuenta al que pertenece el bucket del objeto `resources`.

```
SELECT 
    awsregion,
    replace(unnested.resources_entry.ARN,'arn:aws:s3:::') as deleted_bucket,
    eventtime AS time_deleted,
    useridentity.username, 
    unnested.resources_entry.accountid as bucket_acct_id 
FROM cloudtrail_logs t
CROSS JOIN UNNEST(t.resources) unnested (resources_entry)
WHERE eventname = 'DeleteBucket'
ORDER BY eventtime
```

Para obtener más información acerca de la desanidación, consulte [Filtrado de matrices](filtering-arrays.md).

## Sugerencias para consultar registros de CloudTrail
<a name="tips-for-querying-cloudtrail-logs"></a>

Tenga en cuenta lo siguiente cuando explore los datos de los registro de CloudTrail:
+ Antes de consultar los registros, compruebe que la tabla de registros sea similar a la de [Creación de una tabla para los registros de CloudTrail en Athena mediante la partición manual](create-cloudtrail-table.md). Si no es la primera tabla, elimine la tabla existente con el siguiente comando: `DROP TABLE cloudtrail_logs`.
+ Después de eliminar la tabla existente, vuelva a crearla. Para obtener más información, consulte [Creación de una tabla para los registros de CloudTrail en Athena mediante la partición manual](create-cloudtrail-table.md).

  Verifique que los campos de la consulta de Athena se muestren correctamente. Para obtener información sobre la lista completa de campos de un registro de CloudTrail, consulte [Contenido de los registros de CloudTrail](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-event-reference-record-contents.html). 

  Si la consulta incluye campos en formato JSON, como `STRUCT`, extraiga los datos de JSON. Para obtener más información, consulte [Extracción de datos JSON de cadenas](extracting-data-from-JSON.md). 

  Algunas sugerencias para realizar consultas en la tabla de CloudTrail:
+ Para comenzar, observe qué usuarios de han invocado qué operaciones de la API y desde qué direcciones IP lo han hecho.
+ Utilice la siguiente consulta SQL básica como plantilla. Pegue la consulta en la consola de Athena y ejecútela.

  ```
  SELECT
   useridentity.arn,
   eventname,
   sourceipaddress,
   eventtime
  FROM cloudtrail_logs
  LIMIT 100;
  ```
+ Modifique la consulta para explorar en profundidad los datos.
+ Para mejorar el rendimiento, incluya la cláusula `LIMIT` para obtener un subconjunto de filas determinado.

# Consultas de los registros de Amazon EMR
<a name="emr-logs"></a>

Amazon EMR y las aplicaciones de macrodatos que se ejecutan en Amazon EMR producen archivos de registro. Los archivos de registro se escriben en el [nodo principal](https://docs.aws.amazon.com/emr/latest/ManagementGuide/emr-master-core-task-nodes.html). Además, puede configurar Amazon EMR de manera que archive los archivos de registro en Amazon S3 automáticamente. Puede utilizar Amazon Athena para consultar estos registros con el fin de identificar eventos y tendencias de las aplicaciones y los clústeres. Para obtener más información sobre los tipos de archivos de registro en Amazon EMR y guardarlos en Amazon S3, consulte [Ver archivos de registro](https://docs.aws.amazon.com/emr/latest/ManagementGuide/emr-manage-view-web-log-files.html) en la *Guía de administración de Amazon EMR*.

**Topics**
+ [Creación y consulta de una tabla básica basada en archivos de registro de Amazon EMR](emr-create-table.md)
+ [Creación y consulta de una tabla particionada basada en los registros de Amazon EMR](emr-create-table-partitioned.md)

# Creación y consulta de una tabla básica basada en archivos de registro de Amazon EMR
<a name="emr-create-table"></a>

El siguiente ejemplo crea una tabla básica, `myemrlogs`, basada en archivos de registro guardados en `s3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/elasticmapreduce/`. La ubicación de Amazon S3 utilizada en los siguientes ejemplos refleja el patrón de la ubicación de registro predeterminada para un clúster de EMR creado por la cuenta *123456789012* de Amazon Web Services en la región *us-west-2*. Si utiliza una ubicación personalizada, el patrón es s3://amzn-s3-demo-bucket/*ClusterID*.

Para obtener información sobre cómo crear una tabla con particiones para mejorar potencialmente el rendimiento de las consultas y reducir la transferencia de datos, consulte [Creación y consulta de una tabla particionada basada en los registros de Amazon EMR](emr-create-table-partitioned.md).

```
CREATE EXTERNAL TABLE `myemrlogs`(
  `data` string COMMENT 'from deserializer')
ROW FORMAT DELIMITED  
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6'
```

## Consultas de ejemplo
<a name="emr-example-queries-basic"></a>

Las siguientes consultas de ejemplo se pueden ejecutar en la tabla `myemrlogs` creada por el ejemplo anterior.

**Example – Consultar registros de pasos para incidentes de ERROR, WARN, INFO, EXCEPTION, FATAL o DEBUG**  

```
SELECT data,
        "$PATH"
FROM "default"."myemrlogs"
WHERE regexp_like("$PATH",'s-86URH188Z6B1')
        AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;
```

**Example – Consultar un registro de instancia específico, i-00b3c0a839ece0a9c, para ERROR, WARN, INFO, EXCEPTION, FATAL o DEBUG**  

```
SELECT "data",
        "$PATH" AS filepath
FROM "default"."myemrlogs"
WHERE regexp_like("$PATH",'i-00b3c0a839ece0a9c')
        AND regexp_like("$PATH",'state')
        AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;
```

**Example – Consultar los registros de aplicación Presto para ERROR, WARN, INFO, EXCEPTION, FATAL o DEBUG**  

```
SELECT "data",
        "$PATH" AS filepath
FROM "default"."myemrlogs"
WHERE regexp_like("$PATH",'presto')
        AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;
```

**Example – Consultar los registros de aplicación Namenode para ERROR, WARN, INFO, EXCEPTION, FATAL o DEBUG**  

```
SELECT "data",
        "$PATH" AS filepath
FROM "default"."myemrlogs"
WHERE regexp_like("$PATH",'namenode')
        AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;
```

**Example – Consultar todos los registros por fecha y hora para ERROR, WARN, INFO, EXCEPTION, FATAL o DEBUG**  

```
SELECT distinct("$PATH") AS filepath
FROM "default"."myemrlogs"
WHERE regexp_like("$PATH",'2019-07-23-10')
        AND regexp_like(data, 'ERROR|WARN|INFO|EXCEPTION|FATAL|DEBUG') limit 100;
```

# Creación y consulta de una tabla particionada basada en los registros de Amazon EMR
<a name="emr-create-table-partitioned"></a>

Estos ejemplos utilizan la misma ubicación de registro para crear una tabla de Athena, pero la tabla está particionada y, a continuación, se crea una partición para cada ubicación de registro. Para obtener más información, consulte [Partición de datos](partitions.md).

La siguiente consulta crea la tabla particionada con el nombre `mypartitionedemrlogs`:

```
CREATE EXTERNAL TABLE `mypartitionedemrlogs`(
  `data` string COMMENT 'from deserializer')
 partitioned by (logtype string)
ROW FORMAT DELIMITED  
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6'
```

Las siguientes instrucciones de consulta crean particiones de tabla basadas en subdirectorios para diferentes tipos de registro que Amazon EMR crea en Amazon S3:

```
ALTER TABLE mypartitionedemrlogs ADD
     PARTITION (logtype='containers')
     LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/containers/'
```

```
ALTER TABLE mypartitionedemrlogs ADD
     PARTITION (logtype='hadoop-mapreduce')
     LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/hadoop-mapreduce/'
```

```
ALTER TABLE mypartitionedemrlogs ADD
     PARTITION (logtype='hadoop-state-pusher')
     LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/hadoop-state-pusher/'
```

```
ALTER TABLE mypartitionedemrlogs ADD
     PARTITION (logtype='node')
     LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/node/'
```

```
ALTER TABLE mypartitionedemrlogs ADD
     PARTITION (logtype='steps')
     LOCATION 's3://aws-logs-123456789012-us-west-2/elasticmapreduce/j-2ABCDE34F5GH6/steps/'
```

Después de crear las particiones, puede ejecutar una consulta `SHOW PARTITIONS` en la tabla para confirmar:

```
SHOW PARTITIONS mypartitionedemrlogs;
```

## Consultas de ejemplo
<a name="emr-example-queries-partitioned"></a>

Los siguientes ejemplos demuestran que las consultas de entradas de registro específicas utilizan la tabla y las particiones creadas por los ejemplos anteriores.

**Example – Consulta de los registros de aplicación de application\$11561661818238\$10002 en la partición de contenedores para ERROR o WARN**  

```
SELECT data,
        "$PATH"
FROM "default"."mypartitionedemrlogs"
WHERE logtype='containers'
        AND regexp_like("$PATH",'application_1561661818238_0002')
        AND regexp_like(data, 'ERROR|WARN') limit 100;
```

**Example – Consulta de la partición Hadoop-Mapreduce para el trabajo job\$11561661818238\$10004 y las reducciones con error**  

```
SELECT data,
        "$PATH"
FROM "default"."mypartitionedemrlogs"
WHERE logtype='hadoop-mapreduce'
        AND regexp_like(data,'job_1561661818238_0004|Failed Reduces') limit 100;
```

**Example – Consulta de los registros de Hive en la partición de nodo para el ID de consulta 056e0609-33e1-4611-956c-7a31b42d2663**  

```
SELECT data,
        "$PATH"
FROM "default"."mypartitionedemrlogs"
WHERE logtype='node'
        AND regexp_like("$PATH",'hive')
        AND regexp_like(data,'056e0609-33e1-4611-956c-7a31b42d2663') limit 100;
```

**Example – Consulta de los registros Resourcemanager en la partición de nodo para la aplicación 1567660019320\$10001\$101\$1000001**  

```
SELECT data,
        "$PATH"
FROM "default"."mypartitionedemrlogs"
WHERE logtype='node'
        AND regexp_like(data,'resourcemanager')
        AND regexp_like(data,'1567660019320_0001_01_000001') limit 100
```

# Consulta de registros de flujo de AWS Global Accelerator
<a name="querying-global-accelerator-flow-logs"></a>

Puede utilizar AWS Global Accelerator para crear aceleradores que dirijan el tráfico de red a los puntos de conexión óptimos a través de la red global de AWS. Para obtener más información sobre Global Accelerator, consulte [Qué es AWS Global Accelerator](https://docs.aws.amazon.com/global-accelerator/latest/dg/what-is-global-accelerator.html).

Los registros de flujo de Global Accelerator le permiten capturar información acerca del tráfico de direcciones IP entrante y saliente de las interfaces de red de los aceleradores. Los datos de registro de flujo se publican en Amazon S3, donde puede recuperar y ver los datos. Para obtener más información, consulte [Registros de flujo de AWS Global Accelerator](https://docs.aws.amazon.com/global-accelerator/latest/dg/monitoring-global-accelerator.flow-logs.html).

Puede utilizar Athena para consultar los registros de flujo de Global Accelerator mediante la creación de una tabla que especifique su ubicación en Amazon S3.

**Para crear la tabla de los registros de flujo de Global Accelerator**

1. Copie y pegue la siguiente instrucción DDL en la consola de Athena. Esta consulta especifica *ROW FORMAT DELIMITED (formato de columna delimitado)* y omite la especificación de [SerDe](serde-reference.md), lo que significa que la consulta utiliza [`LazySimpleSerDe`](lazy-simple-serde.md). En esta consulta los campos terminan con un espacio.

   ```
   CREATE EXTERNAL TABLE IF NOT EXISTS aga_flow_logs (
     version string,
     account string,
     acceleratorid string,
     clientip string,
     clientport int,
     gip string,
     gipport int,
     endpointip string,
     endpointport int,
     protocol string,
     ipaddresstype string,
     numpackets bigint,
     numbytes int,
     starttime int,
     endtime int,
     action string,
     logstatus string,
     agasourceip string,
     agasourceport int,
     endpointregion string,
     agaregion string,
     direction string,
     vpc_id string,
     reject_reason string
   )
   PARTITIONED BY (dt string)
   ROW FORMAT DELIMITED
   FIELDS TERMINATED BY ' '
   LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/account_id/globalaccelerator/region/'
   TBLPROPERTIES ("skip.header.line.count"="1");
   ```

1. Modifique el valor `LOCATION` para que apunte al bucket de Amazon S3 que contiene los datos de registro.

   ```
   's3://amzn-s3-demo-bucket/prefix/AWSLogs/account_id/globalaccelerator/region_code/'
   ```

1. Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla `aga_flow_logs` y los datos que contiene quedan disponibles para efectuar consultas.

1. Cree particiones para leer los datos, tal y como se indica en la siguiente consulta de ejemplo. La consulta crea una sola partición para la fecha especificada. Sustituya los marcadores de posición de fecha y ubicación.

   ```
   ALTER TABLE aga_flow_logs
   ADD PARTITION (dt='YYYY-MM-dd')
   LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/account_id/globalaccelerator/region_code/YYYY/MM/dd';
   ```

## Ejemplos de consulta de registros de flujo de AWS Global Accelerator
<a name="querying-global-accelerator-flow-logs-examples"></a>

**Example – Enumera las solicitudes que pasan a través de una ubicación de borde específica**  
En la siguiente consulta de ejemplo se enumeran las solicitudes que pasaron a través de la ubicación periférica LHR. Utilice el operador `LIMIT` para limitar el número de registros que devuelve la consulta a la vez.  

```
SELECT 
  clientip, 
  agaregion, 
  protocol, 
  action 
FROM 
  aga_flow_logs 
WHERE 
  agaregion LIKE 'LHR%' 
LIMIT 
  100;
```

**Example – Enumera las direcciones IP del punto de conexión que reciben la mayor cantidad de solicitudes HTTPS**  
Para ver qué direcciones IP de punto de conexión reciben el mayor número de solicitudes HTTPS, utilice la siguiente consulta. Esta consulta cuenta el número de paquetes recibidos en el puerto HTTPS 443, los agrupa según la dirección IP de destino, y devuelve las 10 direcciones IP principales.  

```
SELECT 
  SUM(numpackets) AS packetcount, 
  endpointip 
FROM 
  aga_flow_logs 
WHERE 
  endpointport = 443 
GROUP BY 
  endpointip 
ORDER BY 
  packetcount DESC 
LIMIT 
  10;
```

# Consulta de los resultados de Amazon GuardDuty
<a name="querying-guardduty"></a>

[Amazon GuardDuty](https://aws.amazon.com/guardduty/) es un servicio de monitoreo de seguridad que ayuda a identificar actividades inesperadas y potencialmente no autorizadas o malintencionadas en su entorno de AWS. Cuando detecta alguna actividad inesperada y potencialmente malintencionada, GuardDuty genera [resultados](https://docs.aws.amazon.com/guardduty/latest/ug/guardduty_findings.html) de seguridad que puede exportar a Amazon S3 para su almacenamiento y análisis. Después de exportar los resultados a Amazon S3, puede utilizar Athena para consultarlos. En este artículo se muestra cómo crear una tabla en Athena para obtener resultados en GuardDuty y consultarlos.

Para obtener más información sobre Amazon GuardDuty, consulte la [Guía del usuario de Amazon GuardDuty](https://docs.aws.amazon.com/guardduty/latest/ug/).

## Requisitos previos
<a name="querying-guardduty-prerequisites"></a>
+ Habilite la característica GuardDuty para exportar los resultados a Amazon S3. Para ver los pasos, consulte [Exportación de resultados](https://docs.aws.amazon.com/guardduty/latest/ug/guardduty_exportfindings.html) en la Guía del usuario de Amazon GuardDuty.

## Creación de una tabla en Athena para los resultados de GuardDuty
<a name="querying-guardduty-creating-a-table-in-athena-for-guardduty-findings"></a>

Para consultar los resultados de GuardDuty desde Athena, debe crear una tabla para ellos.

**Para crear una tabla en Athena para los resultados de GuardDuty**

1. Abra la consola de Athena en [https://console.aws.amazon.com/athena/](https://console.aws.amazon.com/athena/home).

1. Pegue la siguiente instrucción DDL en la consola de Athena. Modifique los valores de `LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/account-id/GuardDuty/'` para que apunten a los resultados de GuardDuty en Amazon S3.

   ```
   CREATE EXTERNAL TABLE `gd_logs` (
     `schemaversion` string,
     `accountid` string,
     `region` string,
     `partition` string,
     `id` string,
     `arn` string,
     `type` string,
     `resource` string,
     `service` string,
     `severity` string,
     `createdat` string,
     `updatedat` string,
     `title` string,
     `description` string)
   ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
    LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/account-id/GuardDuty/'
    TBLPROPERTIES ('has_encrypted_data'='true')
   ```
**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\$1CURSOR\$1ERROR: la fila no es un objeto JSON válido o HIVE\$1CURSOR\$1ERROR: 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](https://github.com/rcongiu/Hive-JSON-Serde#json-data-files) en la documentación de OpenX SerDE en GitHub. 

1. Ejecute la consulta en la consola de Athena para registrar la tabla `gd_logs`. Cuando se complete la consulta, los resultados estarán listos para su consulta desde Athena.

## Consultas de ejemplo
<a name="querying-guardduty-examples"></a>

En los siguientes ejemplos se muestra cómo consultar resultados de GuardDuty desde Athena.

**Example – Exfiltración de datos DNS**  
La siguiente consulta devuelve información acerca de las instancias de Amazon EC2 que podrían estar filtrando datos a través de consultas de DNS.  

```
SELECT
    title,
    severity,
    type,
    id AS FindingID,
    accountid,
    region,
    createdat,
    updatedat,
    json_extract_scalar(service, '$.count') AS Count,
    json_extract_scalar(resource, '$.instancedetails.instanceid') AS InstanceID,
    json_extract_scalar(service, '$.action.actiontype') AS DNS_ActionType,
    json_extract_scalar(service, '$.action.dnsrequestaction.domain') AS DomainName,
    json_extract_scalar(service, '$.action.dnsrequestaction.protocol') AS protocol,
    json_extract_scalar(service, '$.action.dnsrequestaction.blocked') AS blocked
FROM gd_logs
WHERE type = 'Trojan:EC2/DNSDataExfiltration'
ORDER BY severity DESC
```

**Example – Acceso no autorizado de un usuario de IAM**  
La siguiente consulta devuelve todos los tipos de resultados `UnauthorizedAccess:IAMUser` de una entidad principal de IAM en todas las regiones.   

```
SELECT title,
         severity,
         type,
         id,
         accountid,
         region,
         createdat,
         updatedat,
         json_extract_scalar(service, '$.count') AS Count, 
         json_extract_scalar(resource, '$.accesskeydetails.username') AS IAMPrincipal, 
         json_extract_scalar(service,'$.action.awsapicallaction.api') AS APIActionCalled
FROM gd_logs
WHERE type LIKE '%UnauthorizedAccess:IAMUser%' 
ORDER BY severity desc;
```

## Consejos para consultar los resultados de GuardDuty
<a name="querying-guardduty-tips"></a>

Cuando cree una consulta, tenga en cuenta lo siguiente.
+ Para extraer datos de campos JSON anidados, utilice las funciones `json_extract` o `json_extract_scalar` de Presto. Para obtener más información, consulte [Extracción de datos JSON de cadenas](extracting-data-from-JSON.md).
+ Asegúrese de que todos los caracteres de los campos JSON estén en minúsculas.
+  Para obtener información sobre la descarga de resultados de consulta, consulte [Descarga de archivos de resultados de consultas mediante la consola de Athena](saving-query-results.md).

# Consulta de registros de AWS Network Firewall
<a name="querying-network-firewall-logs"></a>

AWS Network Firewall es un servicio administrado que puede utilizar para implementar protecciones de red esenciales para las instancias de Amazon Virtual Private Cloud. AWS Network Firewall funciona junto con AWS Firewall Manager para que pueda crear políticas basadas en reglas de AWS Network Firewall y, a continuación, aplicar esas directivas de forma centralizada en las VPC y las cuentas. Para obtener más información acerca de AWS Network Firewall, consulte [AWS Network Firewall](https://aws.amazon.com/network-firewall/).

Puede configurar el registro de AWS Network Firewall para el tráfico que reenvía al motor de reglas con estado de su firewall. El registro proporciona información detallada sobre el tráfico de red, incluida la hora en la que el motor con estado recibió un paquete, información detallada sobre el paquete y cualquier acción de regla de estado realizada con el paquete. Los registros se publican en el destino de registro que ha configurado, donde puede recuperarlos y verlos. Para obtener más información, consulte [Registro del tráfico de red desde AWS Network Firewall](https://docs.aws.amazon.com/network-firewall/latest/developerguide/firewall-logging.html) en la *Guía para desarrolladores de AWS Network Firewall*.

**Topics**
+ [Creación y consulta de una tabla para los registros de alertas](querying-network-firewall-logs-sample-alert-logs-table.md)
+ [Creación y consulta de una tabla para los registros de netflow](querying-network-firewall-logs-sample-netflow-logs-table.md)

# Creación y consulta de una tabla para los registros de alertas
<a name="querying-network-firewall-logs-sample-alert-logs-table"></a>

1. Modifique el siguiente ejemplo de instrucción DDL para adaptarlo a la estructura de su registro de alertas. Es posible que tenga que actualizar la instrucción para incluir las columnas de la versión más reciente de los registros. Para obtener más información, consulte [Contenido de un registro de firewall](https://docs.aws.amazon.com/network-firewall/latest/developerguide/firewall-logging.html#firewall-logging-contents) en la *Guía para desarrolladores de AWS Network Firewall*.

   ```
   CREATE EXTERNAL TABLE network_firewall_alert_logs (
     firewall_name string,
     availability_zone string,
     event_timestamp string,
     event struct<
       timestamp:string,
       flow_id:bigint,
       event_type:string,
       src_ip:string,
       src_port:int,
       dest_ip:string,
       dest_port:int,
       proto:string,
       app_proto:string,
       sni:string,
       tls_inspected:boolean,
       tls_error:struct<
         error_message:string>,
       revocation_check:struct<
         leaf_cert_fpr:string,
         status:string,
         action:string>,
       alert:struct<
         alert_id:string,
         alert_type:string,
         action:string,
         signature_id:int,
         rev:int,
         signature:string,
         category:string,
         severity:int,
         rule_name:string,
         alert_name:string,
         alert_severity:string,
         alert_description:string,
         file_name:string,
         file_hash:string,
         packet_capture:string,
         reference_links:array<string>
       >, 
       src_country:string, 
       dest_country:string, 
       src_hostname:string, 
       dest_hostname:string, 
       user_agent:string, 
       url:string
      >
   )
    ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
    LOCATION 's3://amzn-s3-demo-bucket/path_to_alert_logs_folder/';
   ```

1. Modifique la cláusula `LOCATION` para especificar la carpeta de sus registros en Amazon S3.

1. Ejecute la consulta `CREATE TABLE` en el editor de consultas de Athena. Cuando la consulta finaliza, Athena registra la tabla `network_firewall_alert_logs`, lo que hace que los datos a los que apunta estén listos para las consultas.

## Consulta de ejemplo
<a name="querying-network-firewall-logs-alert-log-sample-query"></a>

El ejemplo de consulta del registro de alertas de esta sección filtra los eventos en los que se realizó una inspección de TLS que tienen alertas con un nivel de gravedad igual o superior a 2.

La consulta utiliza alias para crear encabezados de columna de salida que muestran a qué `struct` pertenece la columna. Por ejemplo, el encabezado de la columna del campo `event.alert.category` es `event_alert_category` en lugar de simplemente `category`. Para personalizar aún más los nombres de las columnas, puede modificar los alias según sus preferencias. Por ejemplo, puede usar guiones bajos u otros separadores para delimitar los nombres de `struct` y los nombres de los campos. 

Recuerde modificar los nombres y las referencias a `struct` de las columnas en función de la definición de la tabla y de los campos que desee incluir en el resultado de la consulta.

```
SELECT 
  firewall_name,
  availability_zone,
  event_timestamp,
  event.timestamp AS event_timestamp,
  event.flow_id AS event_flow_id,
  event.event_type AS event_type,
  event.src_ip AS event_src_ip,
  event.src_port AS event_src_port,
  event.dest_ip AS event_dest_ip,
  event.dest_port AS event_dest_port,
  event.proto AS event_protol,
  event.app_proto AS event_app_proto,
  event.sni AS event_sni,
  event.tls_inspected AS event_tls_inspected,
  event.tls_error.error_message AS event_tls_error_message,
  event.revocation_check.leaf_cert_fpr AS event_revocation_leaf_cert,
  event.revocation_check.status AS event_revocation_check_status,
  event.revocation_check.action AS event_revocation_check_action,
  event.alert.alert_id AS event_alert_alert_id,
  event.alert.alert_type AS event_alert_alert_type,
  event.alert.action AS event_alert_action,
  event.alert.signature_id AS event_alert_signature_id,
  event.alert.rev AS event_alert_rev,
  event.alert.signature AS event_alert_signature,
  event.alert.category AS event_alert_category,
  event.alert.severity AS event_alert_severity,
  event.alert.rule_name AS event_alert_rule_name,
  event.alert.alert_name AS event_alert_alert_name,
  event.alert.alert_severity AS event_alert_alert_severity,
  event.alert.alert_description AS event_alert_alert_description,
  event.alert.file_name AS event_alert_file_name,
  event.alert.file_hash AS event_alert_file_hash,
  event.alert.packet_capture AS event_alert_packet_capture,
  event.alert.reference_links AS event_alert_reference_links,
  event.src_country AS event_src_country,
  event.dest_country AS event_dest_country,
  event.src_hostname AS event_src_hostname,
  event.dest_hostname AS event_dest_hostname,
  event.user_agent AS event_user_agent,
  event.url AS event_url
FROM 
  network_firewall_alert_logs 
WHERE 
  event.alert.severity >= 2
  AND event.tls_inspected = true 
LIMIT 10;
```

# Creación y consulta de una tabla para los registros de netflow
<a name="querying-network-firewall-logs-sample-netflow-logs-table"></a>

1. Modifique el siguiente ejemplo de instrucción DDL para adaptarlo a la estructura de su registro de alertas. Es posible que tenga que actualizar la instrucción para incluir las columnas de la versión más reciente de los registros. Para obtener más información, consulte [Contenido de un registro de firewall](https://docs.aws.amazon.com/network-firewall/latest/developerguide/firewall-logging.html#firewall-logging-contents) en la *Guía para desarrolladores de AWS Network Firewall*.

   ```
   CREATE EXTERNAL TABLE network_firewall_netflow_logs (
     firewall_name string,
     availability_zone string,
     event_timestamp string,
     event struct<
       timestamp:string,
       flow_id:bigint,
       event_type:string,
       src_ip:string,
       src_port:int,
       dest_ip:string,
       dest_port:int,
       proto:string,
       app_proto:string,
       tls_inspected:boolean,
       netflow:struct<
         pkts:int,
         bytes:bigint,
         start:string,
         `end`:string,
         age:int,
         min_ttl:int,
         max_ttl:int,
         tcp_flags:struct<
           syn:boolean,
           fin:boolean,
           rst:boolean,
           psh:boolean,
           ack:boolean,
           urg:boolean
           >
         >
       >
   )
   ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
   LOCATION 's3://amzn-s3-demo-bucket/path_to_netflow_logs_folder/';
   ```

1. Modifique la cláusula `LOCATION` para especificar la carpeta de sus registros en Amazon S3.

1. Ejecute la consulta `CREATE TABLE` en el editor de consultas de Athena. Cuando la consulta finaliza, Athena registra la tabla `network_firewall_netflow_logs`, lo que hace que los datos a los que apunta estén listos para las consultas.

## Consulta de ejemplo
<a name="querying-network-firewall-logs-netflow-log-sample-query"></a>

El ejemplo de consulta de registro de netflow de esta sección filtra los eventos en los que se realizó una inspección de TLS.

La consulta utiliza alias para crear encabezados de columna de salida que muestran a qué `struct` pertenece la columna. Por ejemplo, el encabezado de la columna del campo `event.netflow.bytes` es `event_netflow_bytes` en lugar de simplemente `bytes`. Para personalizar aún más los nombres de las columnas, puede modificar los alias según sus preferencias. Por ejemplo, puede usar guiones bajos u otros separadores para delimitar los nombres de `struct` y los nombres de los campos. 

Recuerde modificar los nombres y las referencias a `struct` de las columnas en función de la definición de la tabla y de los campos que desee incluir en el resultado de la consulta.

```
SELECT
  event.src_ip AS event_src_ip,
  event.dest_ip AS event_dest_ip,
  event.proto AS event_proto,
  event.app_proto AS event_app_proto,
  event.tls_inspected AS event_tls_inspected,
  event.netflow.pkts AS event_netflow_pkts,
  event.netflow.bytes AS event_netflow_bytes,
  event.netflow.tcp_flags.syn AS event_netflow_tcp_flags_syn 
FROM network_firewall_netflow_logs 
WHERE event.tls_inspected = true
```

# Consulta de los registros del Equilibrador de carga de red
<a name="networkloadbalancer-classic-logs"></a>

Utilice Athena para analizar y procesar los registros del Network Load Balancer. Estos registros reciben información detallada de las solicitudes de seguridad de la capa de transporte (TLS) enviadas al Network Load Balancer. Puede utilizar estos registros de acceso para analizar los patrones de tráfico y solucionar problemas. 

Antes de analizar los registros de acceso del Network Load Balancer, habilítelos y configúrelos para que se guarden en el bucket de Amazon S3 de destino. Para obtener más información sobre cada entrada del registro de acceso al Equilibrador de carga de red, consulte [Registros de acceso al Equilibrador de carga de red](https://docs.aws.amazon.com/elasticloadbalancing/latest/network/load-balancer-access-logs.html).

**Para crear la tabla para los registros del Network Load Balancer**

1. Copie y pegue la instrucción DDL siguiente en la consola de Athena. Verifique la [sintaxis](https://docs.aws.amazon.com/elasticloadbalancing/latest/network/load-balancer-access-logs.html#access-log-file-format) de los registros log del Network Load Balancer. Actualice la instrucción según sea necesario para incluir las columnas y la expresión regular correspondientes a los registros de su registro.

   ```
   CREATE EXTERNAL TABLE IF NOT EXISTS nlb_tls_logs (
               type string,
               version string,
               time string,
               elb string,
               listener_id string,
               client_ip string,
               client_port int,
               target_ip string,
               target_port int,
               tcp_connection_time_ms double,
               tls_handshake_time_ms double,
               received_bytes bigint,
               sent_bytes bigint,
               incoming_tls_alert int,
               cert_arn string,
               certificate_serial string,
               tls_cipher_suite string,
               tls_protocol_version string,
               tls_named_group string,
               domain_name string,
               alpn_fe_protocol string,
               alpn_be_protocol string,
               alpn_client_preference_list string,
               tls_connection_creation_time 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]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ?([^ ]*)?( .*)?'
               )
               LOCATION 's3://amzn-s3-demo-bucket/AWSLogs/AWS_account_ID/elasticloadbalancing/region';
   ```

1. Modifique el bucket de Amazon S3 `LOCATION` para especificar el destino de los registros del Network Load Balancer.

1. Ejecute la consulta en la consola de Athena. Cuando la consulta finaliza, Athena registra la tabla `nlb_tls_logs` y los datos que contiene quedan disponibles para efectuar consultas.

## Consultas de ejemplo
<a name="query-nlb-example"></a>

Para saber cuántas veces se utiliza un certificado, utilice una consulta similar a la de este ejemplo:

```
SELECT count(*) AS 
         ct,
         cert_arn
FROM "nlb_tls_logs"
GROUP BY  cert_arn;
```

En la siguiente consulta, se muestra la cantidad de usuarios que utilizan una versión de TLS anterior a la versión 1.3:

```
SELECT tls_protocol_version,
         COUNT(tls_protocol_version) AS 
         num_connections,
         client_ip
FROM "nlb_tls_logs"
WHERE tls_protocol_version < 'tlsv13'
GROUP BY tls_protocol_version, client_ip;
```

Utilice la siguiente consulta para identificar las conexiones que tardan mucho tiempo en completar el protocolo TLS:

```
SELECT *
FROM "nlb_tls_logs"
ORDER BY  tls_handshake_time_ms DESC 
LIMIT 10;
```

Utilice la siguiente consulta para identificar y contar qué versiones del protocolo TLS y suites de cifrado se negociaron en los últimos 30 días.

```
SELECT tls_cipher_suite,
         tls_protocol_version,
         COUNT(*) AS ct
FROM "nlb_tls_logs"
WHERE from_iso8601_timestamp(time) > current_timestamp - interval '30' day
        AND NOT tls_protocol_version = '-'
GROUP BY tls_cipher_suite, tls_protocol_version
ORDER BY ct DESC;
```

# Consulta de los registros de consulta de Amazon Route 53 Resolver
<a name="querying-r53-resolver-logs"></a>

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](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/resolver-query-logs.html) 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](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/resolver-query-logs-format.html) en la *Guía para desarrolladores de Amazon Route 53*.

**Topics**
+ [Creación de la tabla para los registros de consultas de Resolver](querying-r53-resolver-logs-creating-the-table.md)
+ [Uso de la proyección de particiones](querying-r53-resolver-logs-partitioning-example.md)
+ [Consultas de ejemplo](querying-r53-resolver-logs-example-queries.md)

# Creación de la tabla para los registros de consultas de Resolver
<a name="querying-r53-resolver-logs-creating-the-table"></a>

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/](https://console.aws.amazon.com/athena/home).

1. 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://amzn-s3-demo-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](json-serde.md) 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\$1CURSOR\$1ERROR: la fila no es un objeto JSON válido o HIVE\$1CURSOR\$1ERROR: 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](https://github.com/rcongiu/Hive-JSON-Serde#json-data-files) en la documentación de OpenX SerDE en GitHub. 

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

1. 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
   ```

# Uso de la proyección de particiones
<a name="querying-r53-resolver-logs-partitioning-example"></a>

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 [Uso de proyección de particiones con Amazon Athena](partition-projection.md).

```
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://amzn-s3-demo-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://amzn-s3-demo-bucket/route53-query-logging/AWSLogs/aws_account_id/vpcdnsquerylogs/${vpc}/${date}/'
)
```

# Consultas de ejemplo
<a name="querying-r53-resolver-logs-example-queries"></a>

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\$1timestamp descendente
<a name="querying-r53-resolver-logs-example-1-query-logs-in-descending-query_timestamp-order"></a>

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
<a name="querying-r53-resolver-logs-example-2-query-logs-within-specified-start-and-end-times"></a>

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
<a name="querying-r53-resolver-logs-example-3-query-logs-based-on-a-specified-dns-query-name-pattern"></a>

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
<a name="querying-r53-resolver-logs-example-4-query-log-requests-with-no-answer"></a>

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
<a name="querying-r53-resolver-logs-example-5-query-logs-with-a-specific-answer"></a>

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';
```

# Consulta de registros de eventos de Amazon SES
<a name="querying-ses-logs"></a>

Puede utilizar Amazon Athena para consultar registros de eventos de [Amazon Simple Email Service](https://aws.amazon.com/ses/) (Amazon SES).

Amazon SES es una plataforma de correo electrónico que ofrece un método sencillo y rentable de envío y recepción de correo electrónico a través de sus propios dominios y direcciones de correo electrónico. Puede supervisar la actividad de envío de Amazon SES a un nivel detallado mediante eventos, métricas y estadísticas.

Según las características que defina, puede publicar eventos de Amazon SES en [Amazon CloudWatch](https://aws.amazon.com/cloudwatch/), [Amazon Data Firehose](https://aws.amazon.com/kinesis/data-firehose/) o [Amazon Simple Notification Service](https://aws.amazon.com/sns/). Después de almacenar la información en Amazon S3, puede consultarla desde Amazon Athena. 

Para ver un ejemplo de instrucción `CREATE TABLE` de Athena para los registros de Amazon SES, incluidos los pasos sobre cómo crear vistas y aplanar matrices anidadas en los datos de registro de eventos de Amazon SES, consulte el “Paso 3: Utilizar Amazon Athena para consultar los registros de eventos de SES” en la AWSpublicación en el blog [Analizar los datos de eventos de Amazon SES con servicios de análisis](https://aws.amazon.com/blogs/messaging-and-targeting/analyzing-amazon-ses-event-data-with-aws-analytics-services/).

# Consulta de los registros de flujo de Amazon VPC
<a name="vpc-flow-logs"></a>

Los registros de flujo de Amazon Virtual Private Cloud capturan información acerca del tráfico IP entrante y saliente por las interfaces de red de una VPC. Utilice los registros para investigar los patrones de tráfico de red e identificar las amenazas y los riesgos en la red de VPC.

Tiene dos opciones para consultar los registros de flujo de Amazon VPC:

****
+ **Consola de Amazon VPC**: utilice la característica de integración de Athena en la consola de Amazon VPC para generar una plantilla de CloudFormation que crea una base de datos, un grupo de trabajo y una tabla de registros de flujo de Athena con particiones por usted. La plantilla también crea un conjunto de [consultas de registro de flujo predefinidas](https://docs.aws.amazon.com/vpc/latest/userguide/flow-logs-athena.html#predefined-queries) que puede utilizar para obtener información sobre el tráfico que fluye a través de la VPC.

  Para obtener más información sobre este enfoque, consulte [Realizar consultas en los registros de flujo mediante Amazon Athena](https://docs.aws.amazon.com/vpc/latest/userguide/flow-logs-athena.html) en la *Guía del usuario de Amazon VPC*.
+ **Consola Amazon Athena**: cree tablas y consultas directamente en la consola de Athena. Para obtener más información, continúe leyendo esta página.

Antes de comenzar la consulta de los registros en Athena, [habilite los registros de flujo de VPC](https://docs.aws.amazon.com/AmazonVPC/latest/UserGuide/flow-logs.html) y configúrelos para que se guarden en el bucket de Amazon S3. Después de crear los registros, deje que se ejecuten durante unos minutos para recopilar algunos datos. Los registros se crean con un formato de compresión GZIP que Athena le permite consultar directamente. 

Al crear un registro de flujo de VPC, puede utilizar un formato personalizado cuando quiera especificar los campos que se devolverán en el registro de flujo y el orden en que aparecerán. Para obtener más información sobre los registros de flujo, consulte [Registros de flujo](https://docs.aws.amazon.com/vpc/latest/userguide/flow-logs.html#flow-log-records) en la *Guía del usuario de Amazon VPC*.

## Consideraciones y limitaciones
<a name="vpc-flow-logs-common-considerations"></a>

Cuando cree tablas en los registros de flujo de Athena para Amazon VPC, recuerde los puntos a continuación:
+ De forma predeterminada, en Athena, Parquet accede a las columnas por su nombre. Para obtener más información, consulte [Gestión de las actualizaciones de los esquemas](handling-schema-updates-chapter.md).
+ Utilice los nombres de los registros del registro de flujo en los nombres de las columnas de Athena. Los nombres de las columnas del esquema de Athena deben coincidir exactamente con los nombres de campo de los registros de flujo de Amazon VPC, con las siguientes diferencias: 
  + Sustituya los guiones de los nombres de los campos de registro de Amazon VPC por guiones bajos en los nombres de columna de Athena. Para obtener información sobre los caracteres aceptables para los nombres de base de datos, los nombres de tablas y los nombres de columnas en Athena, consulte [Nombrar tablas, bases de datos y columnas](tables-databases-columns-names.md).
  + Escape los nombres de registros del registro de flujo que sean [palabras clave reservadas](reserved-words.md) en Athena encerrándolos con acentos graves. 
+ Los registros de flujo de VPC son específicos de la Cuenta de AWS. Cuando publica sus archivos de registro en Amazon S3, la ruta que Amazon VPC crea en Amazon S3 incluye el ID de la Cuenta de AWS que se usó para crear el registro de flujo. Para obtener más información, consulte [Publicar registros de flujo en Amazon S3](https://docs.aws.amazon.com/vpc/latest/userguide/flow-logs-s3.html) en la *Guía del usuario de Amazon VPC*.

**Topics**
+ [Consideraciones y limitaciones](#vpc-flow-logs-common-considerations)
+ [Creación de una tabla para los registros de flujo de Amazon VPC y consulta de esta](vpc-flow-logs-create-table-statement.md)
+ [Creación de tablas para los registros de flujo en formato Apache Parquet](vpc-flow-logs-parquet.md)
+ [Creación y consulta de una tabla para los registros de flujo de Amazon VPC mediante la proyección de particiones](vpc-flow-logs-partition-projection.md)
+ [Creación de tablas para los registros de flujo en formato Apache Parquet mediante la proyección de particiones](vpc-flow-logs-partition-projection-parquet-example.md)
+ [Recursos adicionales](query-examples-vpc-logs-additional-resources.md)

# Creación de una tabla para los registros de flujo de Amazon VPC y consulta de esta
<a name="vpc-flow-logs-create-table-statement"></a>

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**

1. 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](vpc-flow-logs.md#vpc-flow-logs-common-considerations). 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](https://docs.aws.amazon.com/vpc/latest/userguide/flow-logs.html#flow-log-records). 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](lazy-simple-serde.md). En esta consulta los campos terminan con un espacio.
   + La cláusula `PARTITIONED BY` utiliza el tipo `date`. 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](reserved-words.md).
   + 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.

1. Modifique `LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/'` de modo que apunte al bucket de Amazon S3 que contiene los datos de registro.

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

1. 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ón `CREATE TABLE` en la que se especifique la [proyección de particiones](vpc-flow-logs-partition-projection.md).

   ```
   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\$1flow\$1logs
<a name="query-examples-vpc-logs"></a>

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

# Creación de tablas para los registros de flujo en formato Apache Parquet
<a name="vpc-flow-logs-parquet"></a>

En el siguiente procedimiento, se crea una tabla de Amazon VPC para los registros de flujo de Amazon VPC en formato Apache Parquet.

**Para crear una tabla de Athena para los registros de flujo de Amazon VPC en formato Parquet**

1. 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](vpc-flow-logs.md#vpc-flow-logs-common-considerations). 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](https://docs.aws.amazon.com/vpc/latest/userguide/flow-logs.html#flow-log-records) en formato Parquet, particionada en Hive por hora. Si no tiene particiones por hora, elimine `hour` de la cláusula `PARTITIONED BY`.

   ```
   CREATE EXTERNAL TABLE IF NOT EXISTS vpc_flow_logs_parquet (
     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 (
     `aws-account-id` string,
     `aws-service` string,
     `aws-region` string,
     `year` string, 
     `month` string, 
     `day` string,
     `hour` string
   )
   ROW FORMAT SERDE 
     'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
   STORED AS INPUTFORMAT 
     'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
   OUTPUTFORMAT 
     'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
   LOCATION
     's3://amzn-s3-demo-bucket/prefix/AWSLogs/'
   TBLPROPERTIES (
     'EXTERNAL'='true', 
     'skip.header.line.count'='1'
     )
   ```

1. Modifique la muestra `LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/'` para que apunte a la ruta de Amazon S3 que contiene los datos de registro.

1. Ejecute la consulta en la consola de Athena.

1. Si sus datos están en formato compatible con HIVE, ejecute el siguiente comando en la consola de Athena para actualizar y cargar las particiones de Hive en el almacén de metadatos. Una vez completada la consulta, puede consultar los datos en la tabla `vpc_flow_logs_parquet`.

   ```
   MSCK REPAIR TABLE vpc_flow_logs_parquet
   ```

   Si no utiliza datos compatibles con Hive, ejecute [ALTER TABLE ADD PARTITION](alter-table-add-partition.md) para cargar las particiones.

Para obtener más información sobre el uso de Athena para consultar los registros de flujo de Amazon VPC en formato Parquet, consulte la publicación [Optimice el rendimiento y reduzca los costos de los análisis de red con los registros de flujo de VPC en formato Apache Parquet](https://aws.amazon.com/blogs/big-data/optimize-performance-and-reduce-costs-for-network-analytics-with-vpc-flow-logs-in-apache-parquet-format/) en el *Blog de macrodatos de AWS*.

# Creación y consulta de una tabla para los registros de flujo de Amazon VPC mediante la proyección de particiones
<a name="vpc-flow-logs-partition-projection"></a>

Use la instrucción `CREATE TABLE` como la siguiente para crear una tabla, particionar la tabla y rellenar las particiones automáticamente mediante la [proyección de particiones](partition-projection.md). Sustituya el nombre de la tabla `test_table_vpclogs` en el ejemplo con el nombre de su tabla. Edite la cláusula `LOCATION` para especificar el bucket de Amazon S3 que contiene los datos de registro de Amazon VPC.

La siguiente instrucción `CREATE TABLE` es para los registros de flujo de VPC entregados en formato de partición que no es de estilo Hive. El ejemplo permite agregar varias cuentas. Si va a centralizar los registros de flujo de VPC de varias cuentas en un bucket de Amazon S3, el ID de la cuenta debe introducirse en la ruta de Amazon S3.

```
CREATE EXTERNAL TABLE IF NOT EXISTS test_table_vpclogs (
  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,
  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 (accid string, region string, day string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
LOCATION '$LOCATION_OF_LOGS'
TBLPROPERTIES
(
"skip.header.line.count"="1",
"projection.enabled" = "true",
"projection.accid.type" = "enum",
"projection.accid.values" = "$ACCID_1,$ACCID_2",
"projection.region.type" = "enum",
"projection.region.values" = "$REGION_1,$REGION_2,$REGION_3",
"projection.day.type" = "date",
"projection.day.range" = "$START_RANGE,NOW",
"projection.day.format" = "yyyy/MM/dd",
"storage.location.template" = "s3://amzn-s3-demo-bucket/AWSLogs/${accid}/vpcflowlogs/${region}/${day}"
)
```

## Consultas de ejemplo para test\$1table\$1vpclogs
<a name="query-examples-vpc-logs-pp"></a>

Las consultas de ejemplo a continuación consultan los `test_table_vpclogs` creados por la instrucción `CREATE TABLE` anterior. Reemplace `test_table_vpclogs` en las consultas por el nombre de su propia tabla. Modifique los valores de columna y otras variables según sus requisitos.

Para devolver las 100 primeras entradas de registro de acceso en orden cronológico durante un período especificado, ejecute una consulta como la siguiente.

```
SELECT *
FROM test_table_vpclogs
WHERE day >= '2021/02/01' AND day < '2021/02/28'
ORDER BY day ASC
LIMIT 100
```

Para ver qué servidor recibe el mayor número de paquetes HTTP durante un período especificado, ejecute una consulta como la siguiente. La consulta cuenta el número de paquetes recibidos en el puerto 443 HTTPS, los agrupa según la dirección IP de destino y devuelve las 10 principales entradas de la semana anterior.

```
SELECT SUM(packets) AS packetcount, 
       dstaddr
FROM test_table_vpclogs
WHERE dstport = 443
  AND day >= '2021/03/01'
  AND day < '2021/03/31'
GROUP BY dstaddr
ORDER BY packetcount DESC
LIMIT 10
```

Para devolver los registros creados durante un período especificado, ejecute una consulta como la siguiente.

```
SELECT interface_id,
       srcaddr,
       action,
       protocol,
       to_iso8601(from_unixtime(start)) AS start_time,
       to_iso8601(from_unixtime("end")) AS end_time
FROM test_table_vpclogs
WHERE DAY >= '2021/04/01'
  AND DAY < '2021/04/30'
```

Para devolver los registros de acceso de una dirección IP de origen entre periodos especificados, ejecute una consulta como la siguiente.

```
SELECT *
FROM test_table_vpclogs
WHERE srcaddr = '10.117.1.22'
  AND day >= '2021/02/01'
  AND day < '2021/02/28'
```

Para enumerar las conexiones TCP rechazadas, ejecute una consulta como la siguiente.

```
SELECT day,
       interface_id,
       srcaddr,
       action,
       protocol
FROM test_table_vpclogs
WHERE action = 'REJECT' AND protocol = 6 AND day >= '2021/02/01' AND day < '2021/02/28'
LIMIT 10
```

Para devolver los registros de acceso del intervalo de direcciones IP que comienza por `10.117`, ejecute una consulta como la siguiente.

```
SELECT *
FROM test_table_vpclogs
WHERE split_part(srcaddr,'.', 1)='10'
  AND split_part(srcaddr,'.', 2) ='117'
```

Para devolver los registros de acceso de una dirección IP de destino entre un intervalo de tiempo determinado, ejecute una consulta como la siguiente.

```
SELECT *
FROM test_table_vpclogs
WHERE dstaddr = '10.0.1.14'
  AND day >= '2021/01/01'
  AND day < '2021/01/31'
```

# Creación de tablas para los registros de flujo en formato Apache Parquet mediante la proyección de particiones
<a name="vpc-flow-logs-partition-projection-parquet-example"></a>

La siguiente instrucción `CREATE TABLE` de proyección de particiones para los registros de flujo de VPC se encuentra en formato Apache Parquet, no es compatible con Hive y se divide por hora y fecha en lugar de por día. Sustituya el nombre de la tabla `test_table_vpclogs_parquet` en el ejemplo con el nombre de su tabla. Edite la cláusula `LOCATION` para especificar el bucket de Amazon S3 que contiene los datos de registro de Amazon VPC.

```
CREATE EXTERNAL TABLE IF NOT EXISTS test_table_vpclogs_parquet (
  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,
  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 (region string, date string, hour string)
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/'
TBLPROPERTIES (
"EXTERNAL"="true",
"skip.header.line.count" = "1",
"projection.enabled" = "true",
"projection.region.type" = "enum",
"projection.region.values" = "us-east-1,us-west-2,ap-south-1,eu-west-1",
"projection.date.type" = "date",
"projection.date.range" = "2021/01/01,NOW",
"projection.date.format" = "yyyy/MM/dd",
"projection.hour.type" = "integer",
"projection.hour.range" = "00,23",
"projection.hour.digits" = "2",
"storage.location.template" = "s3://amzn-s3-demo-bucket/prefix/AWSLogs/${account_id}/vpcflowlogs/${region}/${date}/${hour}"
)
```

# Recursos adicionales
<a name="query-examples-vpc-logs-additional-resources"></a>

Para obtener más información acerca de cómo utilizar Athena para consultar registros, consulte las siguientes entradas del Blog de macrodatos de AWS:
+ [Analyze VPC Flow Logs with point-and-click Amazon Athena integration](https://aws.amazon.com/blogs/networking-and-content-delivery/analyze-vpc-flow-logs-with-point-and-click-amazon-athena-integration/) (Análisis de los registros de flujo de VPC con una integración de Amazon Athena de apuntar y hacer clic) 
+ [Analyzing VPC flow logs using Amazon Athena and Quick](https://aws.amazon.com/blogs/big-data/analyzing-vpc-flow-logs-using-amazon-athena-and-amazon-quicksight/)
+ [Optimize performance and reduce costs for network analytics with VPC Flow Logs in Apache Parquet format](https://aws.amazon.com/blogs/big-data/optimize-performance-and-reduce-costs-for-network-analytics-with-vpc-flow-logs-in-apache-parquet-format/) (Optimización del rendimiento y reducción de los costos de análisis de red con registros de flujo de VPC en formato Apache Parquet)

# Consulta de registros de AWS WAF
<a name="waf-logs"></a>

AWS WAF es un firewall de aplicaciones web que permite supervisar y controlar las solicitudes HTTP y HTTPS que reciben sus aplicaciones web protegidas de los clientes. Usted define cómo gestionar las solicitudes web configurando las reglas dentro de una lista de control de acceso (ACL) web AWS WAF. A continuación, se protege una aplicación web asociándole una ACL web. Entre los ejemplos de recursos de aplicaciones web que puede proteger con AWS WAF incluyen las distribuciones de Amazon CloudFront, las API de REST de Amazon API Gateway y los equilibradores de carga de aplicación. Para obtener más información acerca de las AWS WAF, consulte [AWS WAF](https://docs.aws.amazon.com/waf/latest/developerguide/waf-chapter.html) en la *Guía para desarrolladores de AWS WAF*.

Los registros de AWS WAF incluyen información sobre el tráfico que analiza su ACL web, como la hora en que AWS WAF recibió la solicitud de su recurso de AWS, información detallada sobre la solicitud y la acción de la regla con la que coincide cada solicitud.

Puede configurar una web ACL AWS WAF para publicar los registros en uno de varios destinos, donde podrá consultarlos y verlos. Para obtener más información sobre cómo configurar el registro de ACL web y el contenido de los registros AWS WAF, consulte [Registrar el tráfico de ACL AWS WAF web](https://docs.aws.amazon.com/waf/latest/developerguide/logging.html) en la *Guía para desarrolladores de AWS WAF*.

Para encontrar información sobre cómo utilizar Athena para analizar los registros de AWS WAF a fin de obtener información sobre la detección de amenazas y los posibles ataques a la seguridad, consulte la entrada del blog de AWS sobre redes y entrega de contenido [How to use Amazon Athena queries to analyze AWS WAF logs and provide the visibility needed for threat detection](https://aws.amazon.com/blogs/networking-and-content-delivery/how-to-use-amazon-athena-queries-to-analyze-aws-waf-logs-and-provide-the-visibility-needed-for-threat-detection/).

Para ver un ejemplo sobre cómo agregar registros AWS WAF en un repositorio de lago de datos central y consultarlos con Athena, consulte la publicación del Blog de macrodatos de AWS [“Analyzing AWS WAF logs with OpenSearch Service, Amazon Athena, and Quick”](https://aws.amazon.com/blogs/big-data/analyzing-aws-waf-logs-with-amazon-es-amazon-athena-and-amazon-quicksight/).

En este tema se proporcionan `CREATE TABLE` instrucciones de ejemplo para la proyección de particiones, la partición manual y una que no utiliza ninguna partición.

**nota**  
Las instrucciones `CREATE TABLE` de este tema se pueden usar tanto para registros AWS WAF v1 como v2. En v1, el campo `webaclid` contiene un ID. En v2, el campo `webaclid` contiene un ARN completo. Las instrucciones `CREATE TABLE` aquí tratan este contenido de forma agnóstica mediante el uso de tipos de datos `string`.

**Topics**
+ [Creación de una tabla para los registros de S3 de AWS WAF en Athena mediante la proyección de particiones](create-waf-table-partition-projection.md)
+ [Creación de una tabla para los registros de S3 de AWS WAF en Athena mediante la partición manual](create-waf-table-manual-partition.md)
+ [Creación de una tabla para registros de AWS WAF sin particionar](create-waf-table.md)
+ [Ejemplos de consultas de registros de AWS WAF](query-examples-waf-logs.md)

# Creación de una tabla para los registros de S3 de AWS WAF en Athena mediante la proyección de particiones
<a name="create-waf-table-partition-projection"></a>

Dado que los registros de AWS WAF tienen una estructura conocida cuyo esquema de partición puede especificar de antemano, puede reducir el tiempo de ejecución de las consultas y automatizar la administración de particiones mediante la característica de [proyección de particiones](partition-projection.md) de Athena. La proyección de particiones agrega de forma automática nuevas particiones a medida que se agregan nuevos datos. Esto hace que no sea necesario agregar particiones manualmente mediante `ALTER TABLE ADD PARTITION`. 

La siguiente instrucción de ejemplo `CREATE TABLE` utiliza automáticamente la proyección de particiones en registros AWS WAF desde una fecha especificada hasta el presente para cuatro regiones diferentes de AWS. La cláusula `PARTITION BY` de este ejemplo particiona por región y por fecha, pero puede modificarla según sus requisitos. Modifique los campos según sea necesario para que coincidan con el resultado del registro. En las cláusulas `LOCATION` y `storage.location.template`, reemplace los marcadores de posición de *amzn-s3-demo-bucket* y *AWS\$1ACCOUNT\$1NUMBER* por valores que identifiquen la ubicación del bucket de Amazon S3 de los registros de AWS WAF. Para `projection.day.range`, reemplace *2021*/*01*/*01* por la fecha de inicio que quiera utilizar. Una vez ejecutada la consulta correctamente, puede consultar la tabla. No tiene que ejecutar `ALTER TABLE ADD PARTITION` para cargar las particiones. 

```
CREATE EXTERNAL TABLE `waf_logs_partition_projection`(
  `timestamp` bigint, 
  `formatversion` int, 
  `webaclid` string, 
  `terminatingruleid` string, 
  `terminatingruletype` string, 
  `action` string, 
  `terminatingrulematchdetails` array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>, 
  `httpsourcename` string, 
  `httpsourceid` string, 
  `rulegrouplist` array<struct<rulegroupid:string,terminatingrule:struct<ruleid:string,action:string,rulematchdetails:array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>>,nonterminatingmatchingrules:array<struct<ruleid:string,action:string,overriddenaction:string,rulematchdetails:array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>,challengeresponse:struct<responsecode:string,solvetimestamp:string>,captcharesponse:struct<responsecode:string,solvetimestamp:string>>>,excludedrules:string>>, 
  `ratebasedrulelist` array<struct<ratebasedruleid:string,limitkey:string,maxrateallowed:int>>, 
  `nonterminatingmatchingrules` array<struct<ruleid:string,action:string,rulematchdetails:array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>,challengeresponse:struct<responsecode:string,solvetimestamp:string>,captcharesponse:struct<responsecode:string,solvetimestamp:string>>>, 
  `requestheadersinserted` array<struct<name:string,value:string>>, 
  `responsecodesent` string, 
  `httprequest` struct<clientip:string,country:string,headers:array<struct<name:string,value:string>>,uri:string,args:string,httpversion:string,httpmethod:string,requestid:string,fragment:string,scheme:string,host:string>,
  `labels` array<struct<name:string>>, 
  `captcharesponse` struct<responsecode:string,solvetimestamp:string,failurereason:string>, 
  `challengeresponse` struct<responsecode:string,solvetimestamp:string,failurereason:string>, 
  `ja3fingerprint` string, 
  `ja4fingerprint` string, 
  `oversizefields` string, 
  `requestbodysize` int, 
  `requestbodysizeinspectedbywaf` int)
  PARTITIONED BY ( 
   `log_time` 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://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_NUMBER/WAFLogs/cloudfront/testui/'
TBLPROPERTIES (
 'projection.enabled'='true',
  'projection.log_time.format'='yyyy/MM/dd/HH/mm',
  'projection.log_time.interval'='1',
  'projection.log_time.interval.unit'='minutes',
  'projection.log_time.range'='2025/01/01/00/00,NOW',
  'projection.log_time.type'='date',
  'storage.location.template'='s3://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_NUMBER/WAFLogs/cloudfront/testui/${log_time}')
```

**nota**  
El formato de la ruta de la cláusula `LOCATION` del ejemplo es estándar, pero puede variar en función de la configuración de AWS WAF que haya implementado. Por ejemplo, el siguiente ejemplo de ruta de registros de AWS WAF es para una distribución de CloudFront:   

```
s3://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_NUMBER/WAFLogs/cloudfront/cloudfronyt/2025/01/01/00/00/
```
Si tiene problemas al crear o consultar la tabla de registros de AWS WAF, confirme la ubicación de los datos de registro o [póngase en contacto con Soporte](https://console.aws.amazon.com/support/home/).

Para obtener más información sobre la proyección de particiones, consulte [Uso de proyección de particiones con Amazon Athena](partition-projection.md).

# Creación de una tabla para los registros de S3 de AWS WAF en Athena mediante la partición manual
<a name="create-waf-table-manual-partition"></a>

En esta sección se describe cómo crear una tabla para AWS WAF registros mediante partición manual.

En las cláusulas `LOCATION` y `storage.location.template`, reemplace los marcadores de posición de *amzn-s3-demo-bucket* y *AWS\$1ACCOUNT\$1NUMBER* por valores que identifiquen la ubicación del bucket de Amazon S3 de los registros de AWS WAF.

```
CREATE EXTERNAL TABLE `waf_logs_manual_partition`(
  `timestamp` bigint, 
  `formatversion` int, 
  `webaclid` string, 
  `terminatingruleid` string, 
  `terminatingruletype` string, 
  `action` string, 
  `terminatingrulematchdetails` array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>, 
  `httpsourcename` string, 
  `httpsourceid` string, 
  `rulegrouplist` array<struct<rulegroupid:string,terminatingrule:struct<ruleid:string,action:string,rulematchdetails:array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>>,nonterminatingmatchingrules:array<struct<ruleid:string,action:string,overriddenaction:string,rulematchdetails:array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>,challengeresponse:struct<responsecode:string,solvetimestamp:string>,captcharesponse:struct<responsecode:string,solvetimestamp:string>>>,excludedrules:string>>, 
  `ratebasedrulelist` array<struct<ratebasedruleid:string,limitkey:string,maxrateallowed:int>>, 
  `nonterminatingmatchingrules` array<struct<ruleid:string,action:string,rulematchdetails:array<struct<conditiontype:string,sensitivitylevel:string,location:string,matcheddata:array<string>>>,challengeresponse:struct<responsecode:string,solvetimestamp:string>,captcharesponse:struct<responsecode:string,solvetimestamp:string>>>, 
  `requestheadersinserted` array<struct<name:string,value:string>>, 
  `responsecodesent` string, 
  `httprequest` struct<clientip:string,country:string,headers:array<struct<name:string,value:string>>,uri:string,args:string,httpversion:string,httpmethod:string,requestid:string,fragment:string,scheme:string,host:string>, 
  `labels` array<struct<name:string>>, 
  `captcharesponse` struct<responsecode:string,solvetimestamp:string,failurereason:string>, 
  `challengeresponse` struct<responsecode:string,solvetimestamp:string,failurereason:string>, 
  `ja3fingerprint` string, 
  `ja4fingerprint` string, 
  `oversizefields` string, 
  `requestbodysize` int, 
  `requestbodysizeinspectedbywaf` int)
  PARTITIONED BY ( `year` string, `month` string, `day` string, `hour` string, `min` 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://amzn-s3-demo-bucket/AWSLogs/AWS_ACCOUNT_NUMBER/WAFLogs/cloudfront/webacl/'
```

# Creación de una tabla para registros de AWS WAF sin particionar
<a name="create-waf-table"></a>

En esta sección se describe cómo crear una tabla para registros AWS WAF sin particiones ni proyección de particiones.

**nota**  
Por motivos de rendimiento y costo, no le recomendamos utilizar un esquema no particionado para las consultas. Para obtener información, consulte [Los 10 principales consejos de ajuste de rendimiento de Amazon Athena](https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/) en el Blog de macrodatos de AWS.

**Para crear la tabla de AWS WAF**

1. Copie y pegue la siguiente instrucción DDL en la consola de Athena. Modifique los campos según sea necesario para que coincidan con el resultado del registro. Modifique el valor de `LOCATION` para indicar el bucket de Amazon S3 que se corresponda con el bucket donde se almacenan los registros.

   Esta consulta utiliza [El SerDe JSON de OpenX](openx-json-serde.md).
**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\$1CURSOR\$1ERROR: la fila no es un objeto JSON válido o HIVE\$1CURSOR\$1ERROR: 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](https://github.com/rcongiu/Hive-JSON-Serde#json-data-files) en la documentación de OpenX SerDE en GitHub. 

   ```
   CREATE EXTERNAL TABLE `waf_logs`(
     `timestamp` bigint,
     `formatversion` int,
     `webaclid` string,
     `terminatingruleid` string,
     `terminatingruletype` string,
     `action` string,
     `terminatingrulematchdetails` array <
                                       struct <
                                           conditiontype: string,
                                           sensitivitylevel: string,
                                           location: string,
                                           matcheddata: array < string >
                                             >
                                        >,
     `httpsourcename` string,
     `httpsourceid` string,
     `rulegrouplist` array <
                         struct <
                             rulegroupid: string,
                             terminatingrule: struct <
                                                 ruleid: string,
                                                 action: string,
                                                 rulematchdetails: array <
                                                                      struct <
                                                                          conditiontype: string,
                                                                          sensitivitylevel: string,
                                                                          location: string,
                                                                          matcheddata: array < string >
                                                                             >
                                                                       >
                                                   >,
                             nonterminatingmatchingrules: array <
                                                                 struct <
                                                                     ruleid: string,
                                                                     action: string,
                                                                     overriddenaction: string,
                                                                     rulematchdetails: array <
                                                                                          struct <
                                                                                              conditiontype: string,
                                                                                              sensitivitylevel: string,
                                                                                              location: string,
                                                                                              matcheddata: array < string >
                                                                                                 >
                                                                      >,
                                                                     challengeresponse: struct <
                                                                               responsecode: string,
                                                                               solvetimestamp: string
                                                                                 >,
                                                                     captcharesponse: struct <
                                                                               responsecode: string,
                                                                               solvetimestamp: string
                                                                                 >
                                                                       >
                                                                >,
                             excludedrules: string
                               >
                          >,
   `ratebasedrulelist` array <
                            struct <
                                ratebasedruleid: string,
                                limitkey: string,
                                maxrateallowed: int
                                  >
                             >,
     `nonterminatingmatchingrules` array <
                                       struct <
                                           ruleid: string,
                                           action: string,
                                           rulematchdetails: array <
                                                                struct <
                                                                    conditiontype: string,
                                                                    sensitivitylevel: string,
                                                                    location: string,
                                                                    matcheddata: array < string >
                                                                       >
                                                                >,
                                           challengeresponse: struct <
                                                               responsecode: string,
                                                               solvetimestamp: string
                                                                >,
                                           captcharesponse: struct <
                                                               responsecode: string,
                                                               solvetimestamp: string
                                                                >
                                             >
                                        >,
     `requestheadersinserted` array <
                                   struct <
                                       name: string,
                                       value: string
                                         >
                                    >,
     `responsecodesent` string,
     `httprequest` struct <
                       clientip: string,
                       country: string,
                       headers: array <
                                   struct <
                                       name: string,
                                       value: string
                                         >
                                    >,
                       uri: string,
                       args: string,
                       httpversion: string,
                       httpmethod: string,
                       requestid: string
                         >,
     `labels` array <
                  struct <
                      name: string
                        >
                   >,
     `captcharesponse` struct <
                           responsecode: string,
                           solvetimestamp: string,
                           failureReason: string
                             >,
     `challengeresponse` struct <
                           responsecode: string,
                           solvetimestamp: string,
                           failureReason: string
                           >,
     `ja3Fingerprint` string,
     `oversizefields` string,
     `requestbodysize` int,
     `requestbodysizeinspectedbywaf` int
   )
   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://amzn-s3-demo-bucket/prefix/'
   ```

1. Ejecute la instrucción `CREATE EXTERNAL TABLE` en el editor de consultas de la consola de Athena. Esto registra la tabla `waf_logs` y hace que los datos que contiene estén disponibles para las consultas de Athena.

# Ejemplos de consultas de registros de AWS WAF
<a name="query-examples-waf-logs"></a>

En muchas de las consultas de ejemplo de esta sección se utiliza la tabla de proyección de particiones creada anteriormente. Modifique el nombre de la tabla, los valores de columna y otras variables de los ejemplos según sus requisitos. Para mejorar el rendimiento de las consultas y reducir el costo, agregue la columna de partición en la condición de filtro.

**Topics**
+ [Recuento de los orígenes de referencia, las direcciones IP o las reglas coincidentes](query-examples-waf-logs-count.md)
+ [Consulta con la fecha y hora](query-examples-waf-logs-date-time.md)
+ [Consulta de las solicitudes o direcciones bloqueadas](query-examples-waf-logs-blocked-requests.md)

# Recuento de los orígenes de referencia, las direcciones IP o las reglas coincidentes
<a name="query-examples-waf-logs-count"></a>

En los ejemplos de esta sección se consultan los recuentos de elementos de registro de interés.
+ [Count the number of referrers that contain a specified term](#waf-example-count-referrers-with-specified-term)
+ [Count all matched IP addresses in the last 10 days that have matched excluded rules](#waf-example-count-matched-ip-addresses)
+ [Group all counted managed rules by the number of times matched](#waf-example-group-managed-rules-by-times-matched)
+ [Group all counted custom rules by number of times matched](#waf-example-group-custom-rules-by-times-matched)

**Example : contar el número de referencias que contienen un término especificado**  
La siguiente consulta cuenta el número de referencias que contienen el término “amazon” para el intervalo de fechas especificado.  

```
WITH test_dataset AS 
  (SELECT header FROM waf_logs
    CROSS JOIN UNNEST(httprequest.headers) AS t(header) WHERE "date" >= '2021/03/01'
    AND "date" < '2021/03/31')
SELECT COUNT(*) referer_count 
FROM test_dataset 
WHERE LOWER(header.name)='referer' AND header.value LIKE '%amazon%'
```

**Example : contar todas las direcciones IP coincidentes en los últimos 10 días que coincidieron con las reglas excluidas**  
En la siguiente consulta se cuenta el número de veces que, en los últimos 10 días, la dirección IP coincidió con la regla excluida del grupo de reglas.   

```
WITH test_dataset AS 
  (SELECT * FROM waf_logs 
    CROSS JOIN UNNEST(rulegrouplist) AS t(allrulegroups))
SELECT 
  COUNT(*) AS count, 
  "httprequest"."clientip", 
  "allrulegroups"."excludedrules",
  "allrulegroups"."ruleGroupId"
FROM test_dataset 
WHERE allrulegroups.excludedrules IS NOT NULL AND from_unixtime(timestamp/1000) > now() - interval '10' day
GROUP BY "httprequest"."clientip", "allrulegroups"."ruleGroupId", "allrulegroups"."excludedrules"
ORDER BY count DESC
```

**Example : agrupar todas las reglas administradas contadas por el número de veces que coinciden**  
Si estableció las acciones de reglas del grupo de reglas en Contar en su configuración de ACL web antes del 27 de octubre de 2022, AWS WAF guardó las anulaciones en el archivo JSON de ACL web como `excludedRules`. Ahora, la configuración JSON para anular una regla en Contar se encuentra en la configuración `ruleActionOverrides`. Para obtener más información, consulte [Anulación de acciones en grupos de reglas](https://docs.aws.amazon.com/waf/latest/developerguide/web-acl-rule-group-override-options.html) en la *Guía para desarrolladores de AWS WAF*. Para extraer las reglas administradas en el modo Contar de la estructura de registro nueva, consulte `nonTerminatingMatchingRules` en la sección `ruleGroupList` en lugar del campo `excludedRules`, como en el siguiente ejemplo.  

```
SELECT
 count(*) AS count,
 httpsourceid,
 httprequest.clientip,
 t.rulegroupid, 
 t.nonTerminatingMatchingRules
FROM "waf_logs" 
CROSS JOIN UNNEST(rulegrouplist) AS t(t) 
WHERE action <> 'BLOCK' AND cardinality(t.nonTerminatingMatchingRules) > 0 
GROUP BY t.nonTerminatingMatchingRules, action, httpsourceid, httprequest.clientip, t.rulegroupid 
ORDER BY "count" DESC 
Limit 50
```

**Example : agrupar todas las reglas personalizadas contadas por el número de veces que coinciden**  
En la siguiente consulta se agrupan todas las reglas personalizadas contadas según el número de veces que coinciden.  

```
SELECT
  count(*) AS count,
         httpsourceid,
         httprequest.clientip,
         t.ruleid,
         t.action
FROM "waf_logs" 
CROSS JOIN UNNEST(nonterminatingmatchingrules) AS t(t) 
WHERE action <> 'BLOCK' AND cardinality(nonTerminatingMatchingRules) > 0 
GROUP BY t.ruleid, t.action, httpsourceid, httprequest.clientip 
ORDER BY "count" DESC
Limit 50
```

Para obtener información sobre las ubicaciones de registro de las reglas personalizadas y los grupos de reglas administrados, consulte [Supervisión y ajuste](https://docs.aws.amazon.com/waf/latest/developerguide/web-acl-testing-activities.html) en la *Guía para desarrolladores de AWS WAF*.

# Consulta con la fecha y hora
<a name="query-examples-waf-logs-date-time"></a>

En los ejemplos de esta sección se incluyen consultas que utilizan valores de fecha y hora.
+ [Return the timestamp field in human-readable ISO 8601 format](#waf-example-return-human-readable-timestamp)
+ [Return records from the last 24 hours](#waf-example-return-records-last-24-hours)
+ [Return records for a specified date range and IP address](#waf-example-return-records-date-range-and-ip)
+ [For a specified date range, count the number of IP addresses in five minute intervals](#waf-example-count-ip-addresses-in-date-range)
+ [Count the number of X-Forwarded-For IP in the last 10 days](#waf-example-count-x-forwarded-for-ip)

**Example : devolver el campo de marca de tiempo en formato ISO 8601 legible por humanos**  
La siguiente consulta utiliza las funciones `from_unixtime` y `to_iso8601` para devolver el campo `timestamp` en formato ISO 8601 legible por humanos (por ejemplo, `2019-12-13T23:40:12.000Z` en lugar de `1576280412771`). La consulta devuelve también el nombre de origen HTTP, el ID de origen y la solicitud.   

```
SELECT to_iso8601(from_unixtime(timestamp / 1000)) as time_ISO_8601,
       httpsourcename,
       httpsourceid,
       httprequest
FROM waf_logs
LIMIT 10;
```

**Example : devolver registros de las últimas 24 horas**  
La siguiente consulta utiliza un filtro en el cláusula `WHERE` para devolver el nombre de origen HTTP, el ID de origen HTTP y los campos de solicitud HTTP para los registros de las últimas 24 horas.  

```
SELECT to_iso8601(from_unixtime(timestamp/1000)) AS time_ISO_8601, 
       httpsourcename, 
       httpsourceid, 
       httprequest 
FROM waf_logs
WHERE from_unixtime(timestamp/1000) > now() - interval '1' day
LIMIT 10;
```

**Example : devolver registros para un intervalo de fechas y una dirección IP especificados**  
En la siguiente consulta, se enumeran los registros de un intervalo de fechas especificado para una dirección IP de cliente especificada.  

```
SELECT * 
FROM waf_logs 
WHERE httprequest.clientip='53.21.198.66' AND "date" >= '2021/03/01' AND "date" < '2021/03/31'
```

**Example : contar el número de direcciones IP en intervalos de cinco minutos para un intervalo de fechas especificado**  
La siguiente consulta cuenta el número de direcciones IP en intervalos de cinco minutos para un intervalo de fechas determinado.  

```
WITH test_dataset AS 
  (SELECT 
     format_datetime(from_unixtime((timestamp/1000) - ((minute(from_unixtime(timestamp / 1000))%5) * 60)),'yyyy-MM-dd HH:mm') AS five_minutes_ts,
     "httprequest"."clientip" 
     FROM waf_logs 
     WHERE "date" >= '2021/03/01' AND "date" < '2021/03/31')
SELECT five_minutes_ts,"clientip",count(*) ip_count 
FROM test_dataset 
GROUP BY five_minutes_ts,"clientip"
```

**Example : contar el número de IP X-Forwarded-For en los últimos 10 días**  
En la siguiente consulta se filtran los encabezados de la solicitud y se cuenta el número de IP de X-Forwarded-For de los últimos 10 días.  

```
WITH test_dataset AS
  (SELECT header
   FROM waf_logs
   CROSS JOIN UNNEST (httprequest.headers) AS t(header)
   WHERE from_unixtime("timestamp"/1000) > now() - interval '10' DAY) 
SELECT header.value AS ip,
       count(*) AS COUNT 
FROM test_dataset 
WHERE header.name='X-Forwarded-For' 
GROUP BY header.value 
ORDER BY COUNT DESC
```

Para obtener más información sobre las funciones de fecha y hora, consulte [Funciones y operadores de fecha y hora](https://trino.io/docs/current/functions/datetime.html) en la documentación de Trino.

# Consulta de las solicitudes o direcciones bloqueadas
<a name="query-examples-waf-logs-blocked-requests"></a>

En los ejemplos de esta sección se consultan solicitudes o direcciones bloqueadas.
+ [Extract the top 100 IP addresses blocked by a specified rule type](#waf-example-extract-top-100-blocked-ip-by-rule)
+ [Count the number of times a request from a specified country has been blocked](#waf-example-count-request-blocks-from-country)
+ [Count the number of times a request has been blocked, grouping by specific attributes](#waf-example-count-request-blocks-by-attribute)
+ [Count the number of times a specific terminating rule ID has been matched](#waf-example-count-terminating-rule-id-matches)
+ [Retrieve the top 100 IP addresses blocked during a specified date range](#waf-example-top-100-ip-addresses-blocked-for-date-range)

**Example : extraer las 100 direcciones IP principales bloqueadas por un tipo de regla especificado**  
La siguiente consulta extrae y cuenta las 100 direcciones IP principales que han sido bloqueadas por la regla de finalización `RATE_BASED` durante el intervalo de tiempo especificado.  

```
SELECT COUNT(httpRequest.clientIp) as count,
httpRequest.clientIp
FROM waf_logs
WHERE terminatingruletype='RATE_BASED' AND action='BLOCK' and "date" >= '2021/03/01'
AND "date" < '2021/03/31'
GROUP BY httpRequest.clientIp
ORDER BY count DESC
LIMIT 100
```

**Example : contar el número de veces que se bloqueó una solicitud de un país especificado**  
La siguiente consulta cuenta el número de veces que la solicitud ha llegado de una dirección IP que pertenece a Irlanda (IE) y ha sido bloqueada por la regla de terminación `RATE_BASED`.  

```
SELECT 
  COUNT(httpRequest.country) as count, 
  httpRequest.country 
FROM waf_logs
WHERE 
  terminatingruletype='RATE_BASED' AND 
  httpRequest.country='IE'
GROUP BY httpRequest.country
ORDER BY count
LIMIT 100;
```

**Example : contar el número de veces que se bloqueó una solicitud, agrupando por atributos específicos**  
La siguiente consulta cuenta el número de veces que la solicitud se ha bloqueado, con los resultados agrupados por WebACL, RuleId, ClientIP y URI de la solicitud HTTP.  

```
SELECT 
  COUNT(*) AS count,
  webaclid,
  terminatingruleid,
  httprequest.clientip,
  httprequest.uri
FROM waf_logs
WHERE action='BLOCK'
GROUP BY webaclid, terminatingruleid, httprequest.clientip, httprequest.uri
ORDER BY count DESC
LIMIT 100;
```

**Example : contar el número de veces que se encontraron coincidencias con un ID de regla de terminación específico**  
La siguiente consulta cuenta el número de veces que se han encontrado coincidencias con un ID de regla de terminación específico (`WHERE terminatingruleid='e9dd190d-7a43-4c06-bcea-409613d9506e'`). La consulta agrupa después los resultados por WebACL, Action, ClientIP y URI de solicitud HTTP.  

```
SELECT 
  COUNT(*) AS count,
  webaclid,
  action,
  httprequest.clientip,
  httprequest.uri
FROM waf_logs
WHERE terminatingruleid='e9dd190d-7a43-4c06-bcea-409613d9506e'
GROUP BY webaclid, action, httprequest.clientip, httprequest.uri
ORDER BY count DESC
LIMIT 100;
```

**Example : recuperar las 100 direcciones IP principales bloqueadas durante un intervalo de fechas especificado**  
La siguiente consulta extrae las 100 direcciones IP principales que se han bloqueado durante un intervalo de tiempo especificado. La consulta muestra también el número de veces que se han bloqueado las direcciones IP.  

```
SELECT "httprequest"."clientip", "count"(*) "ipcount", "httprequest"."country"
FROM waf_logs
WHERE "action" = 'BLOCK' and "date" >= '2021/03/01'
AND "date" < '2021/03/31'
GROUP BY "httprequest"."clientip", "httprequest"."country"
ORDER BY "ipcount" DESC limit 100
```

Para obtener más información sobre la consulta de registros de Amazon S3, consulte los siguientes temas:
+ [¿Cómo analizo los registros de acceso al servidor de Amazon S3 mediante Athena?](https://aws.amazon.com/premiumsupport/knowledge-center/analyze-logs-athena/) en el Centro de conocimientos de AWS
+ [Consulta de los registros de acceso de Amazon S3 para solicitudes mediante Amazon Athena](https://docs.aws.amazon.com/AmazonS3/latest/dev/using-s3-access-logs-to-identify-requests.html#querying-s3-access-logs-for-requests) en la Guía del usuario de Amazon Simple Storage Service
+ [Uso de AWS CloudTrail para identificar solicitudes de Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/dev/cloudtrail-request-identification.html) en la Guía del usuario de Amazon Simple Storage Service