AWS AthenaでS3にあるCloudTrailのlogsを検索する

# はじめに

CloudTrailのログをS3に配置する設定にしています。
CloudTrailでログが確認できるのは過去 7 日間までなので、7日を過ぎるとS3を確認する必要があります。
ただ、必要なファイルを見つけることが困難ですし、見つけてもJSONファイルなので、扱いづらかった感が否めません。
そこで、S3の検索をSQLでできるAthenaを利用して、検索してみましょう！

# 前提条件

- S3バケットが用意済みであること
- S3にて検索するファイルが用意済みであること
- CloudTrailログがS3バケットに出力されるよう設定されていること
- Ahtenaのクエリ性能や課金に関しては考慮しない。なので、Athenaパーティションについては考えない

# 準備

## CloudTrailログのフォーマットを確認して出力したい項目を決める

```json:CloudTrailのログフォーマット
{
  "Records": [{
    "eventVersion": "1.01",
    "userIdentity": {
      "type": "IAMUser",
      "principalId": "ABCDEFGHIJKLMNOPQRSTU",
      "arn": "arn:aws:iam::123456789012:user/Alice",
      "accountId": "123456789012",
      "accessKeyId": "ABCDEFGHIJKLMNOPQRST",
      "userName": "Alice",
      "sessionContext": {
        "attributes": {
          "mfaAuthenticated": "false",
          "creationDate": "2014-03-18T14:29:23Z"
        }
      }
    },
    "eventTime": "2014-03-18T14:30:07Z",
    "eventSource": "cloudtrail.amazonaws.com",
    "eventName": "StartLogging",
    "awsRegion": "us-west-2",
    "sourceIPAddress": "72.21.198.64",
    "userAgent": "signin.amazonaws.com",
    "requestParameters": {
      "name": "Default"
    },
    "responseElements": null,
    "requestID": "12345678-9abc-def0-1234-56789abcdef0",
    "eventID": "12345678-9abc-def0-1234-56789abcdef0"
  },
    ... additional entries ...
  ]
```

例では、以下の項目を抽出する。

- eventTime
- eventSource
- eventName
- awsRegion
- sourceIPAddress
- userIdentity
  - type
  - arn


## AhtenaのDB作成

GUIから作成する場合、
　Athena > Catalog Manager > Add table
からテーブル情報を入力していく。

CUIから作成する場合、
　Athena > Query Editor
にて、
　DATABASE: （上記で作成したDB）
を選択し、以下のクエリをRun Queryする。


```sql:DB作成クエリ
CREATE DATABASE [DB名];
```

```sql:例）DB作成クエリ
CREATE DATABASE s3_cloudtrail_logs_db;
```

## Ahtenaのテーブル作成

GUIから作成する場合、
　Athena > Catalog Manager > Add table
からテーブル情報を入力していく。

CUIから作成する場合、
　Athena > Query Editor
にて、
　DATABASE: （上記で作成したDB）
を選択し、以下のクエリをRun Queryする。

```sql:テーブル作成クエリ
CREATE EXTERNAL TABLE IF NOT EXISTS [テーブル名] (
  [カラム] [カラムの型]
)
ROW FORMAT SERDE '[形式]'
LOCATION 's3://[バケット]/[プレフィックス]/' --region [S3のリージョン]
TBLPROPERTIES ('has_encrypted_data' = 'false');
```

- ```'has_encrypted_data' = 'false'``` ：指定されたテーブルのクエリに使用する S3 のデータは暗号化されていないデータであることを Athena に指定する。
- 暗号化されていないデータに対してCreate TableをRun Queryすると、```The authorization header is malformed;``` エラーになる可能性がある。

```sql:例）テーブル作成クエリ
CREATE EXTERNAL TABLE IF NOT EXISTS cloud_trail_logs_tokyo (
  records ARRAY< STRUCT< eventTime:STRING,
      eventSource:STRING,
      eventName:STRING,
      awsRegion:STRING,
      sourceIPAddress:STRING,
      userIdentity:STRUCT< type:STRING,
        arn:STRING
      >
    >
  >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://cloudtrail-log/AWSLogs/123456789012/CloudTrail/ap-northeast-1/' --region ap-northeast-1
TBLPROPERTIES ('has_encrypted_data' = 'false');
```

## 検索

　Athena > Query Editor
にて、
　DATABASE: （上記で作成したDB）
を選択し、以下のクエリをRun Queryする。

```sql:limit例
SELECT
  record.eventTime,
  record.eventSource,
  record.eventName,
  record.awsRegion,
  record.sourceIPAddress,
  record.userIdentity.type,
  record.userIdentity.arn
FROM cloud_trail_logs_tokyo
CROSS JOIN UNNEST(records) AS t (record)
limit 10;
```

```sql:where例
SELECT
  record.eventTime,
  record.eventSource,
  record.eventName,
  record.awsRegion,
  record.sourceIPAddress,
  record.userIdentity.type,
  record.userIdentity.arn
FROM cloud_trail_logs_tokyo
CROSS JOIN UNNEST(records) AS t (record)
where record.eventTime like '2017-06-19T03:56:%'
and record.eventSource = 'ec2.amazonaws.com';
```

# おまけ

## パーティションの作成

AthenaはQueryでスキャンしたデータの容量による課金が発生する。
また、Athenaをデフォルトのまま利用し、かつQueryでSelect文をかけると、LOCATIONに配置されているファイル全てをスキャンしてしまう。
LOCATIONのS3の容量がかなり大きい場合、毎回検索していると、多額の費用になってしまう。
そのため、1テーブルにパーティションを区切り、QueryのSelect文でWhere句にパーティションを指定すると、パーティション範囲のみを検索することになるので、スキャンするデータの容量を抑えることができる。

### テーブルの作成（作り直し）

```sql:テーブル作成クエリ（パーティション有）
CREATE EXTERNAL TABLE IF NOT EXISTS [テーブル名] (
  [カラム] [カラムの型]
)
PARTITIONED BY([パーティションのカラム] [パーティションの型])
ROW FORMAT SERDE '[形式]'
LOCATION 's3://[バケット]/[プレフィックス]/' --region [S3のリージョン]
TBLPROPERTIES ('has_encrypted_data' = 'false');
```

```sql:例）テーブル作成クエリ（パーティション有）
CREATE EXTERNAL TABLE IF NOT EXISTS cloud_trail_logs_tokyo (
  records ARRAY< STRUCT< eventTime:STRING,
      eventSource:STRING,
      eventName:STRING,
      awsRegion:STRING,
      sourceIPAddress:STRING,
      userIdentity:STRUCT< type:STRING,
        arn:STRING
      >
    >
  >
)
PARTITIONED BY(year string, month string, day string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://cloudtrail-log/AWSLogs/123456789012/CloudTrail/ap-northeast-1/' --region ap-northeast-1
TBLPROPERTIES ('has_encrypted_data' = 'false');
```

### パーティションの追加

　Athena > Query Editor
にて、
　DATABASE: （上記で作成したDB）
を選択し、以下のクエリをRun Queryする。

```sql:パーティションの追加
ALTER TABLE [テーブル名] ADD PARTITION (year='[設定値]',month='[設定値]',day='[設定値]') location 's3://[バケット]/[プレフィックス]/';
```

```sql:例）パーティションの追加
ALTER TABLE cloud_trail_logs_tokyo ADD PARTITION (year='2017',month='06',day='19') location 's3://cloudtrail-log/AWSLogs/123456789012/CloudTrail/ap-northeast-1/2017/06/19/';
```

### 登録済みパーティションを確認する

```sql:パーティション確認クエリ
SHOW PARTITIONS [テーブル名];
```

```sql:例）パーティション確認クエリ
SHOW PARTITIONS cloud_trail_logs_tokyo;
```

### 検索（パーティション含め）

where句の最初の方にパーティションの指定をする。

```sql:where例
SELECT
  record.eventTime,
  record.eventSource,
  record.eventName,
  record.awsRegion,
  record.sourceIPAddress,
  record.userIdentity.type,
  record.userIdentity.arn
FROM cloud_trail_logs_tokyo
CROSS JOIN UNNEST(records) AS t (record)
where year='2017' and month='06' and day in ('18','19')
and record.eventTime like '2017-06-19T03:56:%'
and record.eventSource = 'ec2.amazonaws.com';
```

　Athena > History > Data scanned
にて、スキャンした容量が減っていることが確認できます。

# 参考資料
- [CloudTrailログからAmazon Athenaを使ってログイン監査レポートを作成する](https://dev.classmethod.jp/cloud/aws/cloudtrail-audit-report-by-amazon-athena/)
- [Amazon Athenaを使ってJSONファイルを検索してみる](https://dev.classmethod.jp/cloud/aws/athena-json/)
- [S3のデータをAmazon Athenaを使って分析する](https://aws.amazon.com/jp/blogs/news/analyzing-data-in-s3-using-amazon-athena/)
- [発表: Amazon Athena が暗号化されたデータのクエリのサポートを追加](https://aws.amazon.com/jp/blogs/news/launch-amazon-athena-adds-support-for-querying-encrypted-data/)