CloudFront の標準アクセスログを Athena で調査した時に使用したクエリメモ✍

タダです.

CloudFront の標準アクセスログを Athena を使って調査をした時があり,初めてやったためその時に使用したクエリをまとめます.なお,Athena のテーブルはドキュメントに記載のテーブル定義で作成している前提になります.

調査時に使用したクエリを用途ごとにまとめる

今回は特定の時間帯に502エラーが発生してその調査した時のクエリです.

502エラーの検索

10/4のAM9:00~AM11:30の間に発生した502エラーのログを検索するクエリです.

SELECT *
FROM cloudfront_standard_logs
WHERE "date" = DATE '2024-10-04'
    and CAST(SUBSTR(time, 1, 8) AS time) >= TIME '00:00:00'
    and CAST(SUBSTR(time, 1, 8) AS time) <= TIME '02:30:00'
    and sc_status = 502

エッジロケーション毎のエラー件数

次は全体のログを眺めた後にエッジロケーション毎のエラー発生件数を調べる時のクエリです.x_edge_location のカラムを使えばエッジロケーション毎に集計ができます.

SELECT DISTINCT(x_edge_location),count(*)
FROM cloudfront_standard_logs
WHERE "date" = DATE '2024-10-04'
    and CAST(SUBSTR(time, 1, 8) AS time) >= TIME '00:00:00'
    and CAST(SUBSTR(time, 1, 8) AS time) <= TIME '02:30:00'
    and sc_status = 502
GROUP BY x_edge_location

特定のパスをフィルターしたエッジロケーション毎のエラー件数

次に特定のパスでのエッジロケーション毎のエラー件数を調べた時のクエリです.この例ではトップページをフィルターしています.

SELECT DISTINCT(x_edge_location),count(*)
FROM cloudfront_standard_logs
WHERE "date" = DATE '2024-10-04'
    and CAST(SUBSTR(time, 1, 8) AS time) >= TIME '00:00:00'
    and CAST(SUBSTR(time, 1, 8) AS time) <= TIME '02:30:00'
    and sc_status = 502
　　 and cs_uri_stem = '/'
GROUP BY x_edge_location

特定の UserAgent を更に追加でフィルターたてエッジロケーション毎のエラー件数

最後に特定の UserAgent を条件で追加してエッジロケーション毎のエラー件数を調べた時のクエリです.この例では curl でリクエストした時のログをフィルターしています

SELECT DISTINCT(x_edge_location),count(*)
FROM cloudfront_standard_logs
WHERE "date" = DATE '2024-10-04'
    and CAST(SUBSTR(time, 1, 8) AS time) >= TIME '00:00:00'
    and CAST(SUBSTR(time, 1, 8) AS time) <= TIME '02:30:00'
    and sc_status = 502
　　 and cs_uri_stem = '/'
　　 and cs_user_agent LIKE "%curl%"
GROUP BY x_edge_location