ALBのアクセスログをAthena+QuickSightで分析&可視化

## はじめに

QuickSightとAthenaを使って、色々な視点からELBのログを分析してみました。

SQLをガンガンかける人は、Athenaのみで完結する場合もあるかもしれませんが、グラフで直感的に見るメリットもあると思います。

### 参考サイト
[Developers.io](https://dev.classmethod.jp/articles/20170226-quicksight-elb/)
[Carpe Diem](https://christina04.hatenablog.com/entry/alb-log-athena)

## おおまかに手順
1.  ALBアクセスログをS3に保存
2.  AthenaにALBログのテーブルの作成
3.  動作確認
4.  QuickSightにて、データセットを作成する
5.  QuickSightにて分析する

## 1.  ALBアクセスログをS3に保存

### (1)ログを保存したいALBを選択し、説明タブの一番下の「属性の編集」をクリックする
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/2bfb6703-551b-ef15-071a-dca2e32108b9.png)


### (2)「ロードバランサーの属性の編集」というウィンドウが表示されるので、「アクセスログの有効化」にチェックを入れる

#### 新規バケットを同時に作成する場合
バケットポリシーが自動で設定されるので楽なやり方です。
ELBの属性の編集でチェックボックスをONにします。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/c09e4f9b-52e6-e4f1-cbdd-ebfbf8670dfb.png)

#### 既存のS3を使いたい場合

```
{
    "Version": "2012-10-17",
    "Id": "AWSConsole-AccessLogs-Policy-1549661075379",
    "Statement": [
        {
            "Sid": "AWSConsoleStmt-1549661075379",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::582318560864:root"
            },
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::<バケット名>/AWSLogs/<アカウントID>/*"
        },
        {
            "Sid": "AWSLogDeliveryWrite",
            "Effect": "Allow",
            "Principal": {
                "Service": "delivery.logs.amazonaws.com"
            },
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::<バケット名>/AWSLogs/<アカウントID>/*",
            "Condition": {
                "StringEquals": {
                    "s3:x-amz-acl": "bucket-owner-full-control"
                }
            }
        },
        {
            "Sid": "AWSLogDeliveryAclCheck",
            "Effect": "Allow",
            "Principal": {
                "Service": "delivery.logs.amazonaws.com"
            },
            "Action": "s3:GetBucketAcl",
            "Resource": "arn:aws:s3:::<バケット名>"
        }
    ]
}
```
この582318560864というのは東京リージョンのELBのアカウントIDです。
delivery.logs.amazonaws.comにAssume Roleしてますが、こちらはフローログのための権限のようです。

### (3)「保存」をクリックして作業完了！
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/3e937f45-c1ab-abaf-2161-0ec7b4b15327.png)



## 2.  AthenaにALBログのテーブルの作成

### (1)Athenaにて以下のクエリを発行し、テーブルを作成します。

LOCATIONは

s3://<バケット名>/AWSLogs/<アカウントID>/elasticloadbalancing/<リージョン>/yyyy/
s3://<バケット名>/AWSLogs/<アカウントID>/elasticloadbalancing/<リージョン>/yyyy/MM/
まで指定可能です。ただしdd/までやるとテーブルでデータが見れませんでした。
Athenaの料金はスキャンするデータ量によって増えるので、このように分割して保存していくのも１つの手です。

また、どれも最後の/を忘れずに付けてください。

```
CREATE EXTERNAL TABLE IF NOT EXISTS `test_alb_logs` (
            type string,
            time string,
            elb string,
            client_ip string,
            client_port int,
            target_ip string,
            target_port int,
            request_processing_time double,
            target_processing_time double,
            response_processing_time double,
            elb_status_code string,
            target_status_code string,
            received_bytes bigint,
            sent_bytes bigint,
            request_verb string,
            request_url string,
            request_proto string,
            user_agent string,
            ssl_cipher string,
            ssl_protocol string,
            target_group_arn string,
            trace_id string,
            domain_name string,
            chosen_cert_arn string,
            matched_rule_priority string,
            request_creation_time string,
            actions_executed string,
            redirect_url string,
            lambda_error_reason string,
            new_field string
            )
            ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
            WITH SERDEPROPERTIES (
            'serialization.format' = '1',
            'input.regex' = 
        '([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*)[:-]([0-9]*) ([-.0-9]*) ([-.0-9]*) ([-.0-9]*) (|[-0-9]*) (-|[-0-9]*) ([-0-9]*) ([-0-9]*) \"([^ ]*) ([^ ]*) (- |[^ ]*)\" \"([^\"]*)\" ([A-Z0-9-]+) ([A-Za-z0-9.-]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\" \"([^\"]*)\" ([-.0-9]*) ([^ ]*) \"([^\"]*)\" \"([^\"]*)\"($| \"[^ ]*\")(.*)')
            LOCATION 's3://<バケット名>/AWSLogs/<アカウントID>/elasticloadbalancing/<リージョン>/';

```



## 3.  動作確認

### (1)テーブル作成ができたらちゃんとデータが見れるか確認します。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/c92ea356-be90-19f7-651f-4fa81d75ef14.png)

### (2)各ステータスコードの分布を確認できるクエリを発行してみます
```
SELECT elb_status_code, count(elb_status_code) as cnt
FROM "test_alb_logs"
GROUP BY elb_status_code
ORDER BY cnt DESC
```

### (3)こんな感じで集計できればOK
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/33dbf557-e4e7-5120-1e07-6dbc5f8b4acc.png)


### (4)時間指定して調査
```
SELECT client_ip, count(client_ip) as cnt
FROM test_alb_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
     BETWEEN parse_datetime('2019-01-12-12:00:00','yyyy-MM-dd-HH:mm:ss') 
     AND parse_datetime('2019-01-13-00:00:00','yyyy-MM-dd-HH:mm:ss') 
GROUP BY client_ip;
```

とまぁ。こんな感じでクエリを発行して様々な解析が楽にできます。

## 4.  QuickSightにて、データセットを作成する

### (1)QuickSightにて、データセットを作成する
マネージメントコンソールからQuickSightへ移動し、[New Analyze]から[New DataSet]で新しいデータセットを指定します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/1f6ac776-edca-047b-2c16-9d0317fc3caf.png)

### (2)データソース名を入力
※権限が足りない場合は、QuickSightに対してAthenaと上で作ったS3バケットに対するアクセス権限がない人です。右上の人型のアイコンから[Mange QuickSight]を選択してAthenaとS3の権限を追加します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/b1d6b036-2422-1cd8-1ef9-20084886cb4e.png)

### (3)ELBログテーブル選択
対象のELBログテーブルを選択します。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/f2827a26-5b1a-3b20-bfc5-5374e64c4016.png)

### (4)QuickSightで利用するバックエンドを指定
QuickSightで利用するバックエンドを指定します。今回は、SPICEを選択しました。

SPICEを選択した場合、データはSPICEにインポートされます。インメモリのため、高速ですがデータ容量を気にする必要があります。また、クエリを何回投げても料金を気にしなくて良いです。

Athenaを選択した場合は、S3上のデータにクエリを発行します。SPICEほど早くないですが、S3上のデータにクエリが投げられるのでニアリアルタイムに分析できます。こちらは、Athenaの利用料金を気にする必要があります。

※もし、取り込むデータ量が多い場合は必要なデータだけ抜き出してインポートしたり、SPICEの容量を見直しする必要があります ※参考：Amazon Athena のクエリ結果を SPICE にインポートして、 QuickSight で可視化する![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/af1574c7-e884-59d0-1aec-81e87122bcbc.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/c4601133-c08f-46aa-113d-9b3c72adfcb6.png)


### (5)タイムスタンプの型変換
[[TIPS] ELB のアクセスログのタイムスタンプを QuickSight で Date タイプとして利用する](https://dev.classmethod.jp/cloud/aws/tips-quicksight-date-type-format/)を参考にして、タイムスタンプをdate型で利用します。

下記でdate型の計算フィールドを作成できました。

```
parseDate(replace(left({request_timestamp}, 19), 'T', ' '), "yyyy-MM-dd HH:mm:ss")
```

## 5.  QuickSightにて分析する

### (1)時間単位での集計
時間、日、月、年単位での表示変更ができます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/254c37fb-31bf-60ed-609e-e339c3e0369f.png)



### (2)表示したい期間を絞る

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/37623195-439c-f3ad-7cbd-7ac4136018c4.png)

下部のバーからも、表示期間などを変えられます。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/2c6e8bec-858f-dc4e-9dcd-7703d5bf5127.png)



### (3)レスポンスタイムが大きいURL
レスポンスタイムが大きいURLを特定してみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/0390149c-d13d-0026-fd7c-ed9339b21cec.png)
このグラフからは、時間がかかっているリクエストはないことがわかりました。

### (4)ユーザーエジェントの種類と割合
ユーザーエージェントの割合を見てみます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/d17ec884-cf6b-011c-9509-d2d1e1973852.png)
サンプルでは、同じ割合でアクセスがあることがわかりました。

### (5)エラーコードと時間帯
ある時間帯で、障害などでどれくらい影響あったかを確認できます。 また、フィルタを使うことで特定のステータスコードだけ表示することもできます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/9f9adf1f-0a1f-8514-07d6-09de168a7221.png)


### (6)アクセスが多いIP
とある時間帯に、特定のIPから攻撃を受けていないか確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/3be3cf36-a183-ff3c-f771-710a58822656.png)

特定IPからの大量のアクセスがないことがわかりました。

### (7)アクセスが多い時間帯
アクセス数の分析です。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/626780/84e819e5-a48d-c575-3ee0-29d86195dd24.png)