# CloudTrail ログクエリの例
<a name="query-examples-cloudtrail-logs"></a>

以下の例は、CloudTrail イベントログ向けに作成されたテーブルから、匿名化された (署名されていない) すべてのリクエストを返すクエリの一部を示しています。このクエリは、`useridentity.accountid` が匿名で `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 ]%';
```

詳細については、「AWS Big·Data·Blog」(ビッグデータブログ) の記事「[AWS CloudTrail と Amazon Athena を使用して、セキュリティ、コンプライアンス、運用上のアクティビティを分析する](https://aws.amazon.com/blogs/big-data/aws-cloudtrail-and-amazon-athena-dive-deep-to-analyze-security-compliance-and-operational-activity/)」を参照してください。

## CloudTrail ログのネストされたフィールドをクエリする
<a name="cloudtrail-logs-nested-fields"></a>

`userIdentity` および `resources` フィールドはネストされたデータ型であるため、それらのクエリには特別な処理が必要です。

`userIdentity` オブジェクトは、ネストされた `STRUCT` 型で構成されています。以下の例にあるように、これらは、フィールドを区切るためにドットを使用してクエリすることができます。

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

`resources` フィールドは `STRUCT` オブジェクトの配列です。これらの配列では、`CROSS JOIN UNNEST` を使用して配列のネストを解除し、そのオブジェクトをクエリできるようにします。

以下の例は、リソース ARN が `example/datafile.txt` で終わるすべての行を返します。読みやすさのために、[replace](https://prestodb.io/docs/current/functions/string.html#replace) 関数が ARN から初期の `arn:aws:s3:::` サブストリングを削除します。

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

以下の例は、`DeleteBucket` のイベントをクエリします。このクエリは、バケットの名前とバケットが属するアカウント ID を `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
```

ネスト解除の詳細については、「[配列をフィルタリングする](filtering-arrays.md)」を参照してください。

## CloudTrail ログのクエリに関するヒント
<a name="tips-for-querying-cloudtrail-logs"></a>

CloudTrail ログデータを調査する際には、次を考慮してください。
+ ログをクエリする前に、ログテーブルが「[Athena で手動パーティショニングを使用して CloudTrail ログ用のテーブルを作成する](create-cloudtrail-table.md)」に示している内容と同じになっていることを確認します。これが最初のテーブルではない場合は、コマンド `DROP TABLE cloudtrail_logs` を使用して既存のテーブルを削除します。
+ 既存のテーブルを削除した後、再作成します。詳細については、「[Athena で手動パーティショニングを使用して CloudTrail ログ用のテーブルを作成する](create-cloudtrail-table.md)」を参照してください。

  Athena クエリのフィールドが正しく表示されていることを確認します。CloudTrail レコード内のフィールドの完全なリストについては、「[CloudTrail レコードの内容](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-event-reference-record-contents.html)」を参照してください。

  クエリ内のフィールドが JSON 形式 (`STRUCT` など) である場合は、JSON からデータを抽出します。詳細については、「[文字列から JSON データを抽出する](extracting-data-from-JSON.md)」を参照してください。

  CloudTrail テーブルに対してクエリを発行するためのいくつかの提案事項:
+ 最初に、どの ユーザーが、どの送信元 IP アドレスから API オペレーションを呼び出したかを確認します。
+ 次の基本的な SQL クエリをテンプレートとして使用します。このクエリを Athena コンソールに貼り付けて実行します。

  ```
  SELECT
   useridentity.arn,
   eventname,
   sourceipaddress,
   eventtime
  FROM cloudtrail_logs
  LIMIT 100;
  ```
+ クエリを修正して、データをさらに詳しく調べます。
+ パフォーマンスを強化するには、`LIMIT` 句を追加して、指定したサブセットの行のみが返るようにします。