如何使用 Amazon Athena 分析我的 Amazon S3 服务器访问日志？

解决方法

Amazon S3 将服务器访问日志作为对象存储在一个 S3 存储桶中。

先决条件：

对于存储在 Amazon S3 存储桶中的日志，为 S3 结构定义基于日期的分区。在以下解决示例中，表是分区表，分区列是时间戳。对于 S3 位置，添加要映射到时间戳列的文件夹结构 (/YYY/MM/DD)。

要使用 Athena 分析 S3 查询服务器访问日志，完成以下步骤：

1. 为您的 S3 存储桶启用服务器访问日志记录（如果还未启用）。记下目标存储桶和目标前缀的值。要在 Athena 查询中指定 Amazon S3 位置，您同时需要这两个值。
   **注意：**对于日志对象键格式，选择基于日期的分区以加快分析和查询应用程序的速度。

2. 打开 Amazon Athena 控制台。
   **注意：**在运行第一个查询之前，在 Amazon S3 中设置查询结果位置。

3. 在查询编辑器中，运行 DDL 语句创建数据库。
   **注意：**最好是在与 S3 存储桶相同的 AWS 区域中创建数据库。

   create database s3_access_logs_db

4. 在数据库中创建表架构。以下示例中，**STRING ** 和 BIGINT 数据类型值是访问日志属性。您可在 Athena 中查询这些属性。对于 LOCATION，输入步骤 1 中的 S3 存储桶和前缀路径。确保在前缀末尾加上一个正斜杠（/）（例如 s3://doc-example-bucket/prefix/）。如没有使用前缀，则在桶名称末尾加上一个正斜杠（/），例如 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. 在左侧窗格的表下，选择表名称旁边的省略号，然后选择预览表。如您在结果窗口中看到服务器访问日志中的数据，说明您已成功创建 Athena 表。这些数据包含 bucketowner、bucket_name、requestdatetime 等值。

查询示例

要查找对已删除对象的请求，请使用以下查询：

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

要显示导致 403 访问被拒绝错误的请求的 Amazon S3 请求 ID，请使用以下查询：

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

要查找在特定时间段内出现 HTTP 5xx 错误的 Amazon S3 请求 ID（包括密钥和错误代码），请使用以下查询：

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'

要显示谁删除了对象以及何时删除的对象，包括时间戳、IP 地址和 AWS Identity and Access Management（IAM）角色，请使用以下查询：

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

要显示 IAM 角色执行的所有操作，使用以下查询：

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

要显示在特定时间段内对某对象执行的所有操作，使用以下查询：

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

要显示在特定时间段内向 IP 地址传输了多少数据，使用以下查询：

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'

要显示生命周期规则在特定时间段内执行的所有过期操作，使用以下查询：

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

要计算特定时间段内过期的对象数量，使用以下查询：

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'

要显示生命周期规则在特定时间段内执行的所有转换操作，使用以下查询：

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

要显示按签名版本分组的所有请求者，使用以下查询：

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

要显示在特定时间段内发出请求的所有匿名请求者，请使用以下查询：

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'

要显示在特定时间段内发送 PUT 对象请求的所有请求者，请使用以下查询：

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'

要显示在特定时间段内发送 GET 对象请求的所有请求者，请使用以下查询：

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'

要显示所有请求者（按特定时间段内的最高周转时间排序），请使用以下查询：

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;

**注意：**由于 S3 存储桶中的对象数量会随着时间的推移而增加，因此查询会随着时间处理更多数据。最佳做法是为您的服务器访问日志存储桶创建生命周期策略。配置生命周期策略来定期删除日志文件。此策略将减少 Athena 为每个查询分析的数据量。

相关信息

使用 Amazon OpenSearch Service 分析 Amazon S3 服务器访问日志

Amazon S3 服务器访问日志格式

查询 AWS 服务日志

使用 Amazon Athena 查询请求访问日志

注意事项和限制