Como usar Amazon Athena para analisar logs de acesso ao servidor do Amazon S3?

Resolução

O Amazon S3 armazena logs de acesso ao servidor como objetos em um bucket do S3.

Pré-requisito:

Para os logs armazenados em seu bucket do Amazon S3, defina o particionamento baseado em data para sua estrutura do S3. No exemplo de resolução a seguir, a tabela é uma tabela particionada e a coluna particionada tem carimbo de data/hora. Para sua localização no S3, inclua uma estrutura de pastas (/AAA/MM/DD) a ser mapeada para a coluna de carimbo de data/hora.

Para usar o Athena para analisar os logs de acesso ao servidor de consultas do S3, conclua as seguintes etapas:

1. Ative o registro em log de acesso ao servidor para seu bucket do S3, caso ainda não tenha feito isso. Anote os valores do Bucket de destino e do Prefixo de destino. Você precisa de ambos valores para especificar a localização do Amazon S3 em uma consulta do Athena.
   **Observação:**para o formato da chave do objeto de log, escolha o particionamento baseado em data para acelerar aplicações de análise e consulta.

2. Abra o console do Amazon Athena.
   **Observação:**antes de executar sua primeira consulta, configure um local para o resultado da consulta no Amazon S3.

3. No editor de consultas, execute uma instrução DDL para criar um banco de dados.
   Observação: é uma prática recomendada criar o banco de dados na mesma região da AWS que o bucket do S3.

   create database s3_access_logs_db

4. Crie um esquema de tabela no banco de dados. No exemplo a seguir, os valores dos tipos de dado STRING e BIGINT são as propriedades do log de acesso. É possível consultar essas propriedades no Athena. Em LOCATION, insira o bucket do S3 e o caminho do prefixo da Etapa 1. Certifique-se de incluir uma barra (/) no final do prefixo (por exemplo, s3://doc-example-bucket/prefix/). Se não estiver usando um prefixo, inclua uma barra (/) no final do nome do bucket (por exemplo, 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. No painel esquerdo, em Tabelas, escolha as reticências ao lado do nome da tabela e escolha Visualizar tabela. Se você vir dados dos logs de acesso ao servidor na janela Resultados, significa que criou a tabela do Athena com êxito. Os dados contém valores como bucketowner, bucket\ _name e requestdatetime.

Exemplos de consultas

Para encontrar a solicitação de um objeto excluído, use a seguinte consulta:

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

Para mostrar IDs de solicitação do Amazon S3 para solicitações que resultaram em erros 403 Acesso Negado, use a seguinte consulta:

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

Para encontrar IDs de solicitação do Amazon S3 para erros HTTP 5xx em um período específico com a chave e o código do erro, execute a seguinte 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 quem excluiu um objeto e quando, com o carimbo de data e hora, endereço IP e função do AWS Identity and Access Management (IAM), use a seguinte 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 as operações que uma função do IAM executa, use a seguinte consulta:

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

Para mostrar todas as operações que foram realizadas em um objeto em um período específico, use a seguinte 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 quantos dados são transferidos para um endereço IP durante um período específico, use a seguinte 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 as operações de expiração que as regras de ciclo de vida executaram em um período específico, use a seguinte 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 o número de objetos que expiraram em um período específico, use a seguinte 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 as operações de transição que as regras de ciclo de vida realizaram em um período específico, use a seguinte 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 os solicitantes agrupados por Signature Version, use a seguinte consulta:

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

Para mostrar todos os solicitantes anônimos que fizeram solicitações em um período específico, use a seguinte 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 os solicitantes que enviaram solicitações de objetos PUT em um período específico, use a seguinte 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 os solicitantes que enviaram solicitações de objetos GET em um período específico, use a seguinte 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 os solicitantes, ordenados pelo maior tempo de resposta em um período específico, use a seguinte 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;

Observação: como o número de objetos nos buckets do S3 aumenta com o tempo, as consultas processarão mais dados ao longo do tempo. É uma prática recomendada criar uma política do prazo de validade para o bucket de logs de acesso ao servidor. Configure a política do prazo de validade para remover periodicamente os arquivos de log. Essa política reduz a quantidade de dados que o Athena analisa para cada consulta.

Informações relacionadas

Como analisar logs de acesso ao servidor do Amazon S3 usando o Amazon OpenSearch Service

Formato de log de acesso ao servidor do Amazon S3

Como consultar logs de serviços da AWS

Consultar logs de acesso para solicitações usando o Amazon Athena

Considerações e limitações