はじめに

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

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

参考サイト

Developers.io
Carpe Diem

おおまかに手順

ALBアクセスログをS3に保存
AthenaにALBログのテーブルの作成
動作確認
QuickSightにて、データセットを作成する
QuickSightにて分析する

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

(1)ログを保存したいALBを選択し、説明タブの一番下の「属性の編集」をクリックする

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

新規バケットを同時に作成する場合

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

既存の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)「保存」をクリックして作業完了！

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)テーブル作成ができたらちゃんとデータが見れるか確認します。

(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

(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]で新しいデータセットを指定します。

(2)データソース名を入力

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

(3)ELBログテーブル選択

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

(4)QuickSightで利用するバックエンドを指定

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

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

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

※もし、取り込むデータ量が多い場合は必要なデータだけ抜き出してインポートしたり、SPICEの容量を見直しする必要があります ※参考：Amazon Athena のクエリ結果を SPICE にインポートして、 QuickSight で可視化する

(5)タイムスタンプの型変換

[TIPS] ELB のアクセスログのタイムスタンプを QuickSight で Date タイプとして利用するを参考にして、タイムスタンプをdate型で利用します。

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

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

5. QuickSightにて分析する

(1)時間単位での集計

時間、日、月、年単位での表示変更ができます。

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

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

(3)レスポンスタイムが大きいURL

レスポンスタイムが大きいURLを特定してみます。

このグラフからは、時間がかかっているリクエストはないことがわかりました。

(4)ユーザーエジェントの種類と割合

ユーザーエージェントの割合を見てみます。

サンプルでは、同じ割合でアクセスがあることがわかりました。

(5)エラーコードと時間帯

ある時間帯で、障害などでどれくらい影響あったかを確認できます。また、フィルタを使うことで特定のステータスコードだけ表示することもできます。

(6)アクセスが多いIP

とある時間帯に、特定のIPから攻撃を受けていないか確認できます。

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

(7)アクセスが多い時間帯

アクセス数の分析です。