Amazon Athena で Amazon S3 サーバーアクセスログを分析する方法を教えてください。

解決策

Amazon S3 では、サーバーアクセスログがオブジェクトとして S3 バケットに保存されます。

前提条件:

Amazon S3 バケットに保存されるログには、日付ベースのパーティショニングを S3 構造で定義します。後述の解決例では、テーブルはパーティションテーブル、パーティション列はタイムスタンプです。S3 の場所には、タイムスタンプ列へマッピングするフォルダ構造 (/YYY/MM/DD) を設定しておきます。

Athena を使用して S3 クエリのサーバーアクセスログを分析するには、次の手順を実行します。

1. S3 バケットのサーバーアクセスロギングがオンになっていなければ、オンにします。[ターゲットバケット] と [ターゲットプレフィックス] の値を書き留めておきます。Amazon S3 のロケーションを Athena クエリで指定するには、いずれの値も必要です。
   注: ログオブジェクトキー形式では、日付ベースのパーティション化を選択して、分析アプリケーションとクエリアプリケーションを高速化します。

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 Access Denied エラーが発生したリクエストの 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 を使用することによるリクエストのアクセスログのクエリ

考慮事項と制限事項