¿Cómo puedo utilizar Amazon Athena para analizar mis registros de acceso al servidor de Amazon S3?

Solución

Amazon S3 almacena los registros de acceso al servidor como objetos en un bucket de S3. Siga estos pasos para usar Athena con el fin de analizar y consultar los registros de acceso al servidor de S3.

1. Active el registro de acceso al servidor para su bucket de S3 si aún no lo ha hecho. Anote los valores de Bucket de destino y Prefijo de destino. Necesita ambos para especificar la ubicación de Amazon S3 en una consulta de Athena.
   Nota: Para el formato de clave de objeto de registro, elija la partición por fecha para acelerar las aplicaciones de consulta y análisis.

2. Abra la consola de Amazon Athena.
   Nota: Antes de ejecutar la primera consulta, es posible que necesite configurar una ubicación para los resultados de la consulta en Amazon S3.

3. En el Editor de consultas, ejecute una instrucción de DDL para crear una base de datos.
   Nota: Se recomienda crear la base de datos en la misma región de AWS que su bucket de S3.

   create database s3_access_logs_db

4. Cree un esquema de tabla en la base de datos. En el siguiente ejemplo, los valores de los tipos de datos STRING y BIGINT son las propiedades del registro de acceso. Puede consultar estas propiedades en Athena. En LOCATION, introduzca el bucket de S3 y la ruta del prefijo del paso 1. Asegúrese de incluir una barra (/) al final del prefijo (por ejemplo, s3://doc-example-bucket/prefix/). Si no usa un prefijo, incluya una barra (/) al final del nombre del bucket, por ejemplo, s3://doc-example-bucket/.

   CREATE EXTERNAL TABLE s3_access_logs_db.mybucket_logs(
    `bucketowner` STRING,
    `bucket_name` STRING,
    `requestdatetime` STRING,
    `remoteip` STRING,
    `requester` STRING,
    `requestid` STRING,
    `operation` STRING,
    `key` STRING,
    `request_uri` STRING,
    `httpstatus` STRING,
    `errorcode` STRING,
    `bytessent` BIGINT,
    `objectsize` BIGINT,
    `totaltime` STRING,
    `turnaroundtime` STRING,
    `referrer` STRING,
    `useragent` STRING,
    `versionid` STRING,
    `hostid` STRING,
    `sigv` STRING,
    `ciphersuite` STRING,
    `authtype` STRING,
    `endpoint` STRING,
    `tlsversion` STRING,
    `accesspointarn` STRING,
    `aclrequired` STRING)
    PARTITIONED BY (
      `timestamp` string)
   ROW FORMAT SERDE
    'org.apache.hadoop.hive.serde2.RegexSerDe'
   WITH SERDEPROPERTIES (
    'input.regex'='([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$')
   STORED AS INPUTFORMAT
    'org.apache.hadoop.mapred.TextInputFormat'
   OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
   LOCATION
    's3://bucket-name/prefix-name/account-id/region/source-bucket-name/'
    TBLPROPERTIES (
     'projection.enabled'='true',
     'projection.timestamp.format'='yyyy/MM/dd',
     'projection.timestamp.interval'='1',
     'projection.timestamp.interval.unit'='DAYS',
     'projection.timestamp.range'='2024/01/01,NOW',
     'projection.timestamp.type'='date',
     'storage.location.template'='s3://bucket-name/prefix-name/account-id/region/source-bucket-name/${timestamp}')

5. En el panel izquierdo, en ** Tablas**, elija los puntos suspensivos situados junto al nombre de la tabla y, a continuación, seleccione Vista previa de la tabla. Si ve los datos de los registros de acceso al servidor en la ventana Resultados, ha creado correctamente la tabla de Athena. Los datos se parecen a valores como bucketowner, bucket_name y requestdatetime.

Ahora puede consultar los registros de acceso al servidor de Amazon S3.

Consultas de ejemplo

Para buscar la solicitud de un objeto eliminado, utilice la siguiente consulta:

SELECT * FROM s3_access_logs_db.mybucket_logs WHERE key = 'images/picture.jpg' AND operation like '%DELETE%';

Para mostrar los ID de solicitud de Amazon S3 de las solicitudes que han generado errores 403 Acceso denegado, utilice la siguiente consulta:

SELECT requestdatetime, requester, operation, requestid, hostid FROM s3_access_logs_db.mybucket_logs
WHERE httpstatus = '403';

Para buscar los ID de solicitud de Amazon S3 para errores HTTP 5xx en un período de tiempo específico con la clave y el código de error, ejecute la siguiente consulta:

SELECT requestdatetime, key, httpstatus, errorcode, requestid, hostid FROM s3_access_logs_db.mybucket_logs WHERE httpstatus like '5%'
AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'

Para mostrar quién ha eliminado un objeto y cuándo, incluida la marca temporal, la dirección IP y el rol de AWS Identity and Access Management (IAM), utilice la siguiente consulta:

SELECT requestdatetime, remoteip, requester, key FROM s3_access_logs_db.mybucket_logs
WHERE key = 'images/picture.jpg'
AND operation like '%DELETE%';

Para mostrar todas las operaciones realizadas por un rol de IAM, utilice la siguiente consulta:

SELECT * FROM s3_access_logs_db.mybucket_logs
WHERE requester='arn:aws:iam::123456789123:user/user_name';

Para mostrar todas las operaciones que se realizaron en relación con un objeto en un período de tiempo específico, utilice la siguiente consulta:

SELECT *FROM s3_access_logs_db.mybucket_logs WHERE Key='prefix/images/picture.jpg'  AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'

Para mostrar la cantidad de datos que se transfirieron a una dirección IP durante un período de tiempo específico, utilice la siguiente consulta:

SELECT coalesce(SUM(bytessent), 0) AS bytessentTOTAL
FROM s3_access_logs_db.mybucket_logs
WHERE RemoteIP='1.2.3.4'AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'

Para mostrar todas las operaciones de vencimiento realizadas por las reglas del ciclo de vida en un período de tiempo específico, utilice la siguiente consulta:

SELECT * FROM s3_access_logs_db.mybucket_logs WHERE operation = 'S3.EXPIRE.OBJECT'
AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'

Para contar el número de objetos que han vencido en un período de tiempo específico, utilice la siguiente consulta:

SELECT count(*) as ExpireCount FROM s3_access_logs_db.mybucket_logs
WHERE operation = 'S3.EXPIRE.OBJECT'
AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'

Para mostrar todas las operaciones de transición realizadas por las reglas del ciclo de vida en un período de tiempo específico, utilice la siguiente consulta:

SELECT * FROM s3_access_logs_db.mybucket_logs
WHERE operation like 'S3.TRANSITION%'
AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'

Para mostrar todos los solicitantes agrupados por versión de firma, utilice la siguiente consulta:

SELECT requester, Sigv, Count(Sigv) as SigCount
FROM s3_access_logs_db.mybucket_logs
GROUP BY requester, Sigv;

Para mostrar todos los solicitantes anónimos que hayan realizado solicitudes en un período de tiempo específico, utilice la siguiente consulta:

SELECT bucket_name, requester, remoteip, key, httpstatus, errorcode, requestdatetime
FROM s3_access_logs_db.mybucket_logs
WHERE requester IS NULL AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'

Para mostrar todos los solicitantes que hayan enviado solicitudes de objetos PUT en un período de tiempo específico, utilice la siguiente consulta:

SELECT bucket_name, requester, remoteip, key, httpstatus, errorcode, requestdatetime
FROM s3_access_logs_db.mybucket_logs
WHERE operation='REST.PUT.OBJECT'
AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'

Para mostrar todos los solicitantes que hayan enviado solicitudes de objetos GET en un período de tiempo específico, utilice la siguiente consulta:

SELECT bucket_name, requester, remoteip, key, httpstatus, errorcode, requestdatetime
FROM s3_access_logs_db.mybucket_logs
WHERE operation='REST.GET.OBJECT'
AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'

Para mostrar todos los solicitantes ordenados por el tiempo de respuesta más alto dentro de un período de tiempo específico, utilice la siguiente consulta:

SELECT * FROM s3_access_logs_db.mybucket_logs
WHERE NOT turnaroundtime='-'
AND timestamp
BETWEEN '2024/01/29'
AND '2024/01/30'
ORDER BY CAST(turnaroundtime AS INT) DESC;

Nota: Como la cantidad de objetos dentro de los buckets de S3 aumenta con el tiempo, las consultas procesarán más datos. Se recomienda crear una política de ciclo de vida para el bucket de registros de acceso al servidor. Configure la política de ciclo de vida de modo que los archivos de registro se eliminen periódicamente. De este modo se reduce la cantidad de datos que Athena analiza para cada consulta.

Información relacionada

Analyzing Amazon S3 server access logs using Amazon OpenSearch Service

Formato de registro de acceso al servidor Amazon S3

Querying AWS service logs