業務でS3にLogpushしているCloudflre ZeroTrustのGatewayログをAthenaで分析する機会があったので、備忘のため、手順を記録します。
背景
Cloudflare上のLogは保持期間が決まっており、GatewayのHTTPログはEnterpriseプランで30日間です。それ以上の期間保持したい場合はLogpushという機能を利用してAmazonのS3などのサードパーティ製品にログをエクスポートすることが推奨されています。
また、Cloudflareのダッシュボード上ではフィルター機能に限界があり、ダウンロード/アップロードされたファイルの詳細など、ログの詳細を確認するには1件1件開かなければならない、などログの集計に課題がありました。
そこで、S3にLogpushしたログからAthenaを利用してCloudflareのダッシュボード上で行えないような高度な分析が行えないか検証してみました。
構成
今回の構成は簡単に書くと以下のようになっています。Athena単体ではS3のログを直接クエリできないため、ログを読むためのテーブル定義(スキーマ)をGlue Data Catalogから参照します。
今回はS3へのLogpushはすでに設定されていたため、設定方法は割愛します。
データベースの作成
まず、Athenaでテーブル情報を格納するデータベースを作成します。
CREATE DATABASE cloudflare_test_db #任意のデータベース名
実行すると、画像のように左のデータベース一覧に作成したデータベースが表示されるので、選択します。
テーブル定義
次に、作成したデータベース内にテーブルを定義します。
2行目以降にフィールド名とデータ型を定義しています。今回はダウンロード/アップロードされたファイルの詳細を表示することを目的としていたため、それに合わせて必要なフィールドを定義しましたが目的に合わせて後から変更したり、logpushで得られるすべてのフィールドをここで定義しておくこともできます。
CREATE EXTERNAL TABLE IF NOT EXISTS cloudflare_logs_test ( #任意のテーブル名
Datetime string,
Email string,
HTTPHost string,
FileInfo struct<
files: array<
struct<
direction:string,
file_name:string,
file_size:bigint,
action:string
>
>
>
)
PARTITIONED BY (
dt string #パーティションキーの定義
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' #JSONを理解するためのコマンド
WITH SERDEPROPERTIES (
'ignore.malformed.json' = 'true'
)
LOCATION 's3://cloudflare-xxxxxx/' #参照するS3バケット名
TBLPROPERTIES ('has_encrypted_data'='false');
フィールド名とデータ型はCloudflareのダッシュボードの分析とログ>Logpush>取得したいLogpushジョブ>編集の「次のフィールドを送信」欄から確認することができます。
実行すると左側のテーブル一覧に作成したテーブルが表示されます。
画像の「テーブルとビュー」の横にある「作成」>「S3バケット」からテーブルをクエリではなくGUIで作成する事もできます。設定項目はクエリと同じなので、好きな方で作成可能です。
ここで作成したデータベースとテーブルは自動でAWS Glue Data Catalogに保存され、Glue側でも作成・編集できます。
パーティション追加
作成したテーブルにパーティションを追加します。
CloudflareからlogpushされたS3の現在のフォルダ構成は以下のようになっています。
s3://cloudflare-xxxxxx/
├── YYYYMMDD/
│ ├── 0000000000000000.json.gz
│ └── 0000000000000001.json.gz
└── YYYYMMDD/
├── 0000000000000000.json.gz
└── 0000000000000001.json.gz
パーティションがないとAthenaはLOCATION(s3://cloudflare-xxxxxx/)配下を全部読んでしまうため、読み取り量が多くなることで速度が遅くなり課金も増えます。(Athenaは読み取り量に応じて課金)
そこで、dt=20260401/のようにパーティションを追加することでクエリ WHERE dt = '20260401'のように期間指定した分析を行うことができるようになります。
S3のフォルダ名がkey=value型(例：dt=20260401/)となっていたらAthenaにフォルダ名をパーティションとして扱わせるという仕様もありますが、Cloudflareのlogpushではフォルダ名を指定できなさそうだったため、必要な日数分行を足して設定します。。。今回は検証なのでの4日分を追加しました。
ALTER TABLE cloudflare_test_table
ADD
PARTITION (dt='YYYYMMDD')
LOCATION 's3://cloudflare-xxxxxx/YYYYMMDD/'
PARTITION (dt='YYYYMMDD')
LOCATION 's3://cloudflare-xxxxxx/YYYYMMDD/'
PARTITION (dt='YYYYMMDD')
LOCATION 's3://cloudflare-xxxxxx/YYYYMMDD/'
PARTITION (dt='YYYYMMDD')
LOCATION 's3://cloudflare-xxxxxx/YYYYMMDD/';
クエリ分析
ようやくS3のログをクエリする準備が整いました。
試しに、パーティションに追加4日間に発生したファイルアップロードのあるログのうち、ファイルサイズの大きい上位20件の日時、メールアドレス、宛先ホスト、アクション、ファイル名(ファイル名がunknown file nameとなっているものを除く)、ファイルサイズを表示するクエリを投げてみました。
SELECT
Datetime,
f.action,
Email,
f.file_name,
f.file_size
FROM cloudflare_test_table
CROSS JOIN UNNEST(FileInfo.files) AS t(f)
WHERE dt BETWEEN 'YYYYMMDD’ AND 'YYYYMMDD’
AND f.direction = 'upload’
AND f.file_name <> '<unknown file name>’
ORDER BY f.file_size DESCLIMIT 20;
上手く結果が表示されています！
一度テーブルさえ作成できれば繰り返し利用できますし、柔軟な分析ができそうでしたので今後も活用していければと思います。