S3 バケットから削除されたオブジェクトを CloudTrail と Athena を使って調査する

# 1. はじめに
* とある S3 オブジェクトがいつ、だれによって削除されたのかを調査する。
* CloudTrail を有効にしていれば、S3オブジェクトレベルのアクセスログが記録される。
* CloudTrail のログは大量かつJSONフォーマットのため、生ログから調査するのは無理がある。
* CloudTrail のログを検索するためには、Amazon Athena を使うと便利。最近は、コンソールからポチポチで Athena に CloudTrail ログのテーブルを作成できる。
* あとは、Athena から さまざまな SQL を投げて調査をすすめる。

【参考】

* [AWS CloudTrail User Guide (Version 1.0)を読んで理解を深める](https://qiita.com/tonishy/items/bb5832c59d309fb1db4d)
* [【新機能】CloudTrailでS3オブジェクトレベルのアクセスをロギングする](https://dev.classmethod.jp/articles/cloudtrail-for-s3object-level-access/)
* [AWS CloudTrail ログ検索をするために Amazon Athena のテーブルを自動的に作成する方法を教えてください。](https://aws.amazon.com/jp/premiumsupport/knowledge-center/athena-tables-search-cloudtrail-logs/)

# 2. Amazon Athena に CloudTrail ログ のテーブルを作成する

## 2.1. デフォルトのテーブルを作成する

#### 設定方法は [公式](https://aws.amazon.com/jp/premiumsupport/knowledge-center/athena-tables-search-cloudtrail-logs/) を参照

![20200415-01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/7a76d25a-4de9-5cb7-7333-bc9748c2973a.png)<br />↓<br />テーブルの作成<br />↓<br />![20200415-03.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/7d1c529e-7a47-172f-46ab-5229f388f517.png)<br />↓<br />「default」 の Database に CloudTrail ログの テーブルが作成された<br />![20200415-04.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/d6e1787e-126d-9a0c-8dda-5156861e7a85.png)

## 2.2. デフォルトで作成したテーブルのDDLを確認する

#### 確認方法

![20200415-10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/2b5d896f-6c28-6a42-4e0b-14177dccafd6.png)<br />↓<br />結果にDDLが出力される<br />

```sql:DDL
CREATE EXTERNAL TABLE `cloudtrail_logs_cloudtrail_************`(
  `eventversion` string COMMENT 'from deserializer', 
  `useridentity` struct<type:string,principalid:string,arn:string,accountid:string,invokedby:string,accesskeyid:string,username:string,sessioncontext:struct<attributes:struct<mfaauthenticated:string,creationdate:string>,sessionissuer:struct<type:string,principalid:string,arn:string,accountid:string,username:string>>> COMMENT 'from deserializer', 
  `eventtime` string COMMENT 'from deserializer', 
  `eventsource` string COMMENT 'from deserializer', 
  `eventname` string COMMENT 'from deserializer', 
  `awsregion` string COMMENT 'from deserializer', 
  `sourceipaddress` string COMMENT 'from deserializer', 
  `useragent` string COMMENT 'from deserializer', 
  `errorcode` string COMMENT 'from deserializer', 
  `errormessage` string COMMENT 'from deserializer', 
  `requestparameters` string COMMENT 'from deserializer', 
  `responseelements` string COMMENT 'from deserializer', 
  `additionaleventdata` string COMMENT 'from deserializer', 
  `requestid` string COMMENT 'from deserializer', 
  `eventid` string COMMENT 'from deserializer', 
  `resources` array<struct<arn:string,accountid:string,type:string>> COMMENT 'from deserializer', 
  `eventtype` string COMMENT 'from deserializer', 
  `apiversion` string COMMENT 'from deserializer', 
  `readonly` string COMMENT 'from deserializer', 
  `recipientaccountid` string COMMENT 'from deserializer', 
  `serviceeventdetails` string COMMENT 'from deserializer', 
  `sharedeventid` string COMMENT 'from deserializer', 
  `vpcendpointid` string COMMENT 'from deserializer')
COMMENT 'CloudTrail table for cloudtrail-************ bucket'
ROW FORMAT SERDE 
  'com.amazon.emr.hive.serde.CloudTrailSerde' 
STORED AS INPUTFORMAT 
  'com.amazon.emr.cloudtrail.CloudTrailInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://cloudtrailログのバケット/AWSLogs/************/CloudTrail'
TBLPROPERTIES (
  'classification'='cloudtrail', 
  'transient_lastDdlTime'='1586924592')
```
（「************」は、AWSアカウントID）

#### デフォルトで作成されるテーブルのS3バケット ロケーション

* 次の通り、全リージョンとなっている。

```sql:DDL抜粋
~
LOCATION
  's3://cloudtrail-************/AWSLogs/************/CloudTrail'
~
```

* CloudTrail を全リージョンで有効にしている場合、デフォルトのロケーション設定では、全リージョンの CloudTrail ログがスキャン対象となっている。

![20200415-12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/6424819a-ef21-3ad6-4951-972512b86483.png)

* Athena では、スキャンされたバイト数に対して課金される。また、スキャン対象が多いほど結果に時間がかかる。
* 検索対象を絞れる場合は、ロケーションを指定してテーブルを作成した方がよい。

## 2.3. S3バケットのロケーションを指定してテーブルを作成する

* 検索対象を、ap-northeast-1 リージョンの 2020年 とする。
* デフォルトで作成されたテーブルの DDL の次の２箇所（テーブル名と、ロケーション）を変更する。

```sql:カスタムDDL
CREATE EXTERNAL TABLE `cloudtrail_ap-northeast-1_2020`(
~
LOCATION
  's3://cloudtrailログのバケット/AWSLogs/************/CloudTrail/ap-northeast-1/2020/'
~
```

* 編集した DDL を実行する

![20200415-1３.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/9fbb8e88-739b-f442-e2e9-73e0277640d5.png)<br />↓<br />![20200415-1４.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/911359ca-08b1-069a-569a-7a6e7b1704ee.png)


# 3. Athena テーブルに SQL を投げる際の注意点

## 配列をフラット化

CloudTrail ログの Athena テーブルに次の配列のフィールドがある。

```
resources (array<struct<arn:string,accountId:string,type:string>>)
```

この中の arn が S3オブジェクトの ARN なので、この arn を検索条件にしたいが、この配列のままで SQL を投げるとエラーになるため、CROSS JOIN 演算子を UNNEST 演算子と組み合わせて、配列をフラット化する必要がある。

```
CROSS JOIN UNNEST(resources) AS t(resource)
```

【参考】

* [ネストされた配列のフラット化](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/flattening-arrays.html)


# 4. SQL 実行例

## 4.1. SQL実行例 その１

#### SQL実行

* S3オブジェクト「foo.png」が削除（DeleteObjectイベント)された日時と、削除したユーザー名を調べる。
* CloudTrail ログバケット「cloudtrail_logs_cloudtrail_************」内のすべてのログが検索対象。
* 結果は 2020-03-01 00:00:00 (UTC) 以降を表示する。

```sql:例その１
SELECT
  eventtime, useridentity.username, eventname, resource.arn
FROM
  cloudtrail_logs_cloudtrail_************
CROSS JOIN UNNEST(resources) AS t(resource)
WHERE
  resource.arn LIKE '%foo.png%'
AND
  eventname = 'DeleteObject'
AND
  eventtime >= '2020-03-01T00:00:00Z'
ORDER BY eventtime;
```
<br />↓<br />

#### 実行結果

※クエリは例。結果は実際のものなので伏せてる
![20200415-05.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/a573839f-1d79-3579-6fac-52a191cdefd0.png)

#### ロケーションを ap-northeast-1 の 2020年に設定した テーブルの場合

* sql
 
```sql:例その１：cloudtrail_ap-northeast-1_2020
SELECT
  eventtime, useridentity.username, eventname, resource.arn
FROM
  cloudtrail_ap-northeast-1_2020
CROSS JOIN UNNEST(resources) AS t(resource)
WHERE
  resource.arn LIKE '%foo.png%'
AND
  eventname = 'DeleteObject'
AND
  eventtime >= '2020-03-01T00:00:00Z'
ORDER BY eventtime;
```

* 結果

![20200415-1５.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/b54ca552-6c2a-893f-8903-b7b00a99630e.png)

スキャン時間は 2分36秒 から 33.94秒
スキャンされたバイト数は 14.49GB から 12.04GB 

スキャン時間が激減した。

## 4.2. SQL実行例 その２
* S3バケット 「foo内のオブジェクトが削除（DeleteObjectイベント)された日時と、削除したユーザー名を調べる。
* CloudTrail ログバケット「cloudtrail_logs_cloudtrail_************」内のすべてのログが検索対象。
* 結果は 2020-03-01 00:00:00 (UTC) 以降を表示する。

```sql:例その２
SELECT
  eventtime, useridentity.username, eventname, resource.arn
FROM
  cloudtrail_logs_cloudtrail_************
CROSS JOIN UNNEST(resources) AS t(resource)
WHERE
  resource.arn LIKE '%arn:aws:s3:::foo/%'
AND
  eventname = 'DeleteObject'
AND
  eventtime >= '2020-03-01T00:00:00Z'
ORDER BY eventtime;
```

<br />↓<br />
実行結果
※クエリは例。結果は実際のものなので伏せてる
![20200415-06.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/2ff7d634-27b3-034f-bb5f-16437620220f.png)


# 5. その他
## 5.1. Athena の料金
* Amazon Athena によってスキャンされたバイト数に対して課金される。

* 東京リージョンの場合、スキャンされたデータ 1 TB あたり 5.00USD

* CloudTrail のログ容量が大きいほど、スキャンに時間がかかるし、Athenaコストもかかる。<br />~~（CloudTrailログのバケットは、年単位とかで分けたほうが良いかもしれない。年が変わったら、新しいS3バケットにCloudTrailログを出すように出力設定変するとか。）~~

* コスト削減方法 その１
 * Athena テーブル作成時に[パーティション](https://aws.amazon.com/jp/blogs/news/top-10-performance-tuning-tips-for-amazon-athena/)
分けをすることで、パフォーマンス向上、スキャンするデータ量を制限できる。

* コスト削減方法 その2
 * CloudTrailログは S3バケットでリージョンと年/月/日でプリフィックス区切りされている。Athenaでテーブル作成時に、S3バケットロケーションで ap-northeast-1 の 2020/04/15 内のログだけをスキャン対象とすることで、スキャンするバイト数を減らすことができる。<p />
CloudTrailログのS3バケット例![20200415-16.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/148868/277d6062-47ca-1735-c0a9-130f368db2e8.png)



【参考】

* [Amazon Athena の料金](https://aws.amazon.com/jp/athena/pricing/)

## 5.2. 何かしらの事故でオブジェクトが消えて泣かないために

* [クロスアカウントで s3 バケットを非同期コピーするコンテナを定時実行する](https://qiita.com/tonishy/items/6ac628686ea0cfad7dd2)