Amazon Athenaを用いたALBログ解析

Posted at 2025-08-12

はじめに

運用中の障害調査やアクセス分析において、ALBのログ解析行いました。ALBのアクセスログはgz形式で圧縮されており、当初は、このファイルをS3からダウンロード・解凍して、Excelで集計を行っていましたが、大変手間がかかりましたので、Athenaでの分析方法を整理しました。
　その際に得た、導入に向けて参考にしたサイト、抜粋した情報、工夫している点について、いくつか紹介させて頂きます。

想定する構成

LBを経由して、EC2上のWebシステムと、S3上の静的ファイル（PDFや動画ファイルの配信）へのアクセスがあります。なお、閉域網のため、Amazon S3の静的Webホスティング機能は使用しておりません。実装の詳細は以下をご覧ください。
(Qitta)ALBとAmazon S3によるプライベートネットワーク向けのHTTPS静的ウェブサイトの構築について

前提

VPCやEndPoint、IAM、S3バケット等は全て設定されている
構築に必要な権限周りの設定が可能である

ALBの設定

『公式ドキュメント『Application Load Balancer のアクセスログを有効にする』の通りに設定しています。
　出力先のS3バケットについてですが、ライフサイクルポリシー等で、S3 Glacierに格納されたログについてクエリすることができません。Athenaでクエリする場合、S3 Standardに格納してください。

ALBのアクセスログについて

詳細は公式ドキュメントを確認していただくものとして、本ブログでは障害調査でどのフィールドを確認したのかを紹介します。

レスポンス性能に関するフィールド

「画面応答が遅い」と問われた際に確認したのが以下3つのフィールドで、ミリ秒単位の数値が記録されます。エラー生じた場合（例えば、DBサーバの応答が返る前に、クライアント側でブラウザを閉じる等）には、「-1」が記録されます。システム（サーバ内で）何らかの性能問題があった場合は図内②target_processing_timeが変わってきます。「①②③に変化が見られないが、それでも応答が遅い」といった場合は、ALBまでの通信経路を調査してみよう等が考えられます。

request_processing_time
target_processing_time
response_processing_time

TLSに関するフィールド

ALBは外部からのHTTPS通信をHTTP通信に変換して、Webサーバ側に通信させることができます。（SSLアクセラレータ）
構築時の適当な暗号化スイートになったままでいないか、セキュリティにも関わるため確認していました。

ssl_cipher
例）ECDHE-RSA-AES128-GCM-SHA256
ssl_protocol
例）TLSv1.2

処理の分散に関するフィールド

複数のWebサーバにラウンドロビンで分散処理する、分散された後は同一セッションを維持し、同じWebサーバにアクセス（一意性保証）させるなどの設定をされると思います。どのWebAPサーバへの通信か、また通信が正常であったか次のフィールドで確認しています。

target:port_list
例）10.0.47.111:80
target_status_code
例）200

アクセス先に関するフィールド

どのサイトにアクセスが多いか等の分析において、リクエストされたURLを確認します。

request_url

例）https://sample.co.jp:443/document/test.html
例）https://sample.co.jp:443/document/manual/user_manual.pdf

Athenaの設定

公式ドキュメント『Query Application Load Balancer logs』を中心に設定しています。
ただし、情報量も多いため、抜粋した手順を以下に箇条書きにします。

公式ドキュメント『データベースを作成する』
Athena上にデータベースを作成します。
公式ドキュメント『パーティション射影を使用して Athena で ALB 接続ログ用テーブルを作成する』
※私の力不足で説明がうまくできませんので、パーティション射影に関するリンクを貼らせていただきます。（参考ドキュメント『Amazon Athena でパーティション射影を使用する』）また理解が深まったら、この点についても記事にしてみたいと思います。

Athenaでのクエリ

公式ドキュメント『ALB アクセスログのクエリ例』を見ながら、作業の中で工夫を重ねています。なお運用作業としては、ログの検索、集計について単純なクエリが中心であり、使い回しや、出力方法の検討に力を入れています。

（例１）時刻と日付のJST変換

ALBのアクセスログはUTCで記録され、またtimeフィールドはTXT形式となります。
以下は公式ドキュメント『ALB アクセスログのクエリ例』の「datetime でログを解析する方法」からの引用になります。「parse_datetime」内ではUTCの時刻をそのままに指定する必要があります。

datetimeでログをSelect

SELECT client_ip, sum(received_bytes) 
FROM alb_access_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
     BETWEEN parse_datetime('2018-05-30-12:00:00','yyyy-MM-dd-HH:mm:ss') 
     AND parse_datetime('2018-05-31-00:00:00','yyyy-MM-dd-HH:mm:ss') 
GROUP BY client_ip;

いちいちUTC時間で指定するのは大変なので、JSTで指定できるようにしたいと思います。
これについては、以下Qitta記事より勉強させていただきました。
『[小ネタ]AthenaでALBアクセスログのtimestampのタイムゾーンの変換をする』

JSTで指定したdatetimeでログをSelect

SELECT client_ip, sum(received_bytes) 
FROM alb_access_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z') 
     BETWEEN timestamp '2018-05-30-21:00:00 Asia/Tokyo' 
     AND timestamp'2018-05-31-09:00:00 Asia/Tokyo'
GROUP BY client_ip;

（例２）調査対象の絞り込み

ALBの各フィールドについて、クエリを行います。WHERE句にANDしただけですが、運用やトラブル調査では頻繁に使っています。
「/*　*/」でコメントアウトし、Athenaの「保存したクエリ」から引き出すようにしています。

ログをSelect

select
 parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z' ) AT TIME ZONE 'UTC' AT TIME ZONE 'Asia/Tokyo' AS JST_TIME,
 client_ip, 
 client_port,
 target_ip, 
 target_port,
 request_processing_time,
 target_processing_time,
 response_processing_time,
 elb_status_code,
 target_status_code,
 request_verb,
 request_url,
 request_creation_time
FROM alb_access_logs
WHERE parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z')
    BETWEEN timestamp '2025-04-11 16:10:00 Asia/Tokyo'
        AND timestamp '2025-04-11 16:15:00 Asia/Tokyo'
/*    AND request_verb = 'POST' */
/*     AND elb_status_code = 200 */
/*     AND client_port = xxxxx */
/*     AND target_ip = 'xxx.xxx.xxx.xxx' */
/*    AND request_url like '%manual%' */
ORDER BY JST_TIME DESC

（例３）サイト毎のアクセス量の集計

運用の定期作業の一つとして、URL単位で、１時間ごとのアクセス数を集計しています。
出力結果は以下のイメージとなります。

url	0時	1時	・・・	22時	23時
https://sample.co.jp:443/document/test.html	(カウント)	(カウント)	・・・	(カウント)	(カウント)
https://sample.co.jp:443/document/manual/user_manual.pdf	(カウント)	(カウント)	・・・	(カウント)	(カウント)

処理の事前準備として、0時、1時、、、23時をパラメータ化した、「numbers」というテーブルを用意しました。（AWS Glue等を用いれば、また別の手順となると思います。）以下は、環境準備に使用したSQLです。

numbersテーブルの作成

CREATE EXTERNAL TABLE IF NOT EXISTS numbers(
  hour INT)
LOCATION 's3://(バケットを指定する)'
 
INSERT INTO numbers (hour) VALUES
(0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12),
(13), (14), (15), (16), (17), (18), (19), (20), (21), (22), (23);

実行するクエリは以下となります。３つの機能で構成されます。

hourly_counts CTE (Common Table Expression)
ALBアクセスログから、特定のURLへのリクエスト数を時間帯ごとに集計します。
all_hours CTE:
特定のURLに対して、0時から23時までのすべての時間帯の組み合わせを生成します。
メインクエリ
hourly_counts CTEとall_hours CTEを結合し、URLごとに時間帯別のリクエスト数を横並びで表示します。

リクエスト数を時間帯ごとに集計

WITH hourly_counts AS (
    SELECT
        request_url,
        hour( parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z' ) AT TIME ZONE 'Asia/Tokyo') AS JST_HH,
        count(*) as count
    FROM
        alb_access_logs
    WHERE
        request_url like 'https://sample.co.jp:443/document/%'
        AND
        day like '%2024/12/03%'
    GROUP BY
        request_url,
        hour( parse_datetime(time,'yyyy-MM-dd''T''HH:mm:ss.SSSSSS''Z' ) AT TIME ZONE 'Asia/Tokyo')
),
all_hours AS (
    SELECT
        DISTINCT request_url,
        hour
    FROM
        (SELECT DISTINCT request_url FROM alb_access_logs WHERE day like '%2024/12/03%') AS subquery
    CROSS JOIN
        numbers
)
SELECT
    a.request_url AS url,
    COALESCE(SUM(CASE WHEN ah.hour = 0 THEN hc.count END), 0) AS "0",
    COALESCE(SUM(CASE WHEN ah.hour = 1 THEN hc.count END), 0) AS "1",
    COALESCE(SUM(CASE WHEN ah.hour = 2 THEN hc.count END), 0) AS "2",
    COALESCE(SUM(CASE WHEN ah.hour = 3 THEN hc.count END), 0) AS "3",
    COALESCE(SUM(CASE WHEN ah.hour = 4 THEN hc.count END), 0) AS "4",
    COALESCE(SUM(CASE WHEN ah.hour = 5 THEN hc.count END), 0) AS "5",
    COALESCE(SUM(CASE WHEN ah.hour = 6 THEN hc.count END), 0) AS "6",
    COALESCE(SUM(CASE WHEN ah.hour = 7 THEN hc.count END), 0) AS "7",
    COALESCE(SUM(CASE WHEN ah.hour = 8 THEN hc.count END), 0) AS "8",
    COALESCE(SUM(CASE WHEN ah.hour = 9 THEN hc.count END), 0) AS "9",
    COALESCE(SUM(CASE WHEN ah.hour = 10 THEN hc.count END), 0) AS "10",
    COALESCE(SUM(CASE WHEN ah.hour = 11 THEN hc.count END), 0) AS "11",
    COALESCE(SUM(CASE WHEN ah.hour = 12 THEN hc.count END), 0) AS "12",
    COALESCE(SUM(CASE WHEN ah.hour = 13 THEN hc.count END), 0) AS "13",
    COALESCE(SUM(CASE WHEN ah.hour = 14 THEN hc.count END), 0) AS "14",
    COALESCE(SUM(CASE WHEN ah.hour = 15 THEN hc.count END), 0) AS "15",
    COALESCE(SUM(CASE WHEN ah.hour = 16 THEN hc.count END), 0) AS "16",
    COALESCE(SUM(CASE WHEN ah.hour = 17 THEN hc.count END), 0) AS "17",
    COALESCE(SUM(CASE WHEN ah.hour = 18 THEN hc.count END), 0) AS "18",
    COALESCE(SUM(CASE WHEN ah.hour = 19 THEN hc.count END), 0) AS "19",
    COALESCE(SUM(CASE WHEN ah.hour = 20 THEN hc.count END), 0) AS "20",
    COALESCE(SUM(CASE WHEN ah.hour = 21 THEN hc.count END), 0) AS "21",
    COALESCE(SUM(CASE WHEN ah.hour = 22 THEN hc.count END), 0) AS "22",
    COALESCE(SUM(CASE WHEN ah.hour = 23 THEN hc.count END), 0) AS "23"
FROM
    (SELECT DISTINCT request_url FROM alb_access_logs WHERE day like '%2024/12/03%') AS a
LEFT JOIN
    all_hours ah ON a.request_url = ah.request_url
LEFT JOIN
    hourly_counts hc ON ah.request_url = hc.request_url AND ah.hour = hc.JST_HH
GROUP BY
    a.request_url
ORDER BY
    a.request_url;

出力結果の参考画像です。

まとめ

この度は、ALBアクセスログの調査・集計におけるAthenaの活用で学習した内容をご紹介させていただきました。ただ調べている中で、AWS Glueを使用すればより簡単に、後々のメンテナンスも楽な実装ができそうだと気付きました。これはこれとして、苦手なSQL関連、Athenaへの学習が進んだものとして良かったかなと思います。
ログが示すより本質的な意味を理解できるよう、今後も挑戦したいと思います。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up