LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@conte0745

CloudfrontのログをAthenaを使い集計しよう。

Last updated at Posted at 2024-09-08

はじめに

AWS CloudFrontを利用している場合、ユーザのリクエストやアクセス状況を把握するために、アクセスログを集計・分析することが重要です。しかし、CloudFrontのログは大量に生成されるため、手動で分析するのは非常に手間がかかります。そこで、AWSのサービスを活用してログの自動集計を行う方法についてハンズオン形式で紹介します。
今回は以下のAWSサービスを使い、Cloudfrontのログを月次で集計します。

  • Glue：テーブル定義
  • S3: CloudFrontのログを保存
  • Athena: ログのクエリを実行して集計

前提として、S3の「cloudfront-log-bucket」にCloudfrontで出力されたログがあるものとします。

image.png

ログのサンプルは以下の通りです。
AWS公式のログフォーマットのサンプルは 本リンク 参照してください。

#Version: 1.0
#Fields: date time x-edge-location sc-bytes c-ip cs-method cs(Host) cs-uri-stem sc-status cs(Referer) cs(User-Agent) cs-uri-query cs(Cookie) x-edge-result-type x-edge-request-id x-host-header cs-protocol cs-bytes time-taken x-forwarded-for ssl-protocol ssl-cipher x-edge-response-result-type cs-protocol-version fle-status fle-encrypted-fields c-port time-to-first-byte x-edge-detailed-result-type sc-content-type sc-content-len sc-range-start sc-range-end
2024-08-31	14:00:00	SFO53-P5	511	27.143.20.200	GET	dewhexwchqqkk.cloudfront.net	/	403	-	Mozilla/5.0%20(Macintosh;%20Intel%20Mac%20OS%20X%2010_15_7)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/128.0.0.0%20Safari/537.36	-	-	Error	sfOWjMbfDIwKv67Fg9mjuf2AZA0zIE8nl-KvJw-jeDyN56LfpovUkw==	dewhexwchqqkk.cloudfront.net	https	496	0.438	-	TLSv1.3	TLS_AES_128_GCM_SHA256	Error	HTTP/2.0	-	-	49626	0.438	Error	application/xml	-	-	-
2024-08-31	14:59:59	SFO53-P5	483	27.143.20.200	GET	dewhexwchqqkk.cloudfront.net	/favicon.ico	403	https://dewhexwchqqkk.cloudfront.net/	Mozilla/5.0%20(Macintosh;%20Intel%20Mac%20OS%20X%2010_15_7)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/128.0.0.0%20Safari/537.36	-	-	Error	U7aDuNRgCfhfOwiowHI5CMHdrN-X39tS6NbgvftIdmbUsJa5Fj2Wdg==	dewhexwchqqkk.cloudfront.net	https	145	0.457	-	TLSv1.3	TLS_AES_128_GCM_SHA256	Error	HTTP/2.0	-	-	49626	0.457	Error	application/xml	-	-	-
2024-08-31	14:59:59	SFO53-P5	509	27.143.20.200	GET	dewhexwchqqkk.cloudfront.net	/	403	-	Mozilla/5.0%20(Macintosh;%20Intel%20Mac%20OS%20X%2010_15_7)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/128.0.0.0%20Safari/537.36	-	-	Error	ElQ0G2q2b3c3N_Mr5LLjZCk9fjlj4hHMmnuNMc6o37rzulHr3sPhEg==	dewhexwchqqkk.cloudfront.net	https	28	0.437	-	TLSv1.3	TLS_AES_128_GCM_SHA256	Error	HTTP/2.0	-	-	49626	0.437	Error	application/xml	-	-	-
2024-08-31	14:59:59	SFO53-P5	485	27.143.20.200	GET	dewhexwchqqkk.cloudfront.net	/favicon.ico	403	https://dewhexwchqqkk.cloudfront.net/	Mozilla/5.0%20(Macintosh;%20Intel%20Mac%20OS%20X%2010_15_7)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/128.0.0.0%20Safari/537.36	-	-	Error	kWRWTjO7krWYxUMdB4XSjRNiRSY3CL4AT19qdjozV0v-j4AIjazILw==	dewhexwchqqkk.cloudfront.net	https	36	0.438	-	TLSv1.3	TLS_AES_128_GCM_SHA256	Error	HTTP/2.0	-	-	49626	0.438	Error	application/xml	-	-	-
2024-08-31	15:00:00	SFO53-P5	511	27.143.20.200	GET	dewhexwchqqkk.cloudfront.net	/	403	-	Mozilla/5.0%20(Macintosh;%20Intel%20Mac%20OS%20X%2010_15_7)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/128.0.0.0%20Safari/537.36	-	-	Error	RAYVkLgbCeJ7MqDIO1PTR7EsrONdH9mKGmrQUufNbd7XLWj7isL-Pw==	dewhexwchqqkk.cloudfront.net	https	28	0.438	-	TLSv1.3	TLS_AES_128_GCM_SHA256	Error	HTTP/2.0	-	-	49626	0.438	Error	application/xml	-	-	-
2024-08-31	15:00:01	SFO53-P5	483	27.143.20.216	GET	dewhexwchqqkk.cloudfront.net	/favicon.ico	403	https://dewhexwchqqkk.cloudfront.net/	Mozilla/5.0%20(Macintosh;%20Intel%20Mac%20OS%20X%2010_15_7)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/128.0.0.0%20Safari/537.36	-	-	Error	3c9rA_LI_g3m3GByYB_3o3ofpvhWpWtJA2H2NNnkdmRIfjv0RjtZHQ==	dewhexwchqqkk.cloudfront.net	https	36	0.447	-	TLSv1.3	TLS_AES_128_GCM_SHA256	Error	HTTP/2.0	-	-	49626	0.447	Error	application/xml	-	-	-
2024-09-16	22:36:27	SEA19-C1	900	192.0.2.200	GET	d111111abcdef8.cloudfront.net	/favicon.ico	502	http://www.example.com/	Mozilla/5.0%20(Windows%20NT%2010.0;%20Win64;%20x64)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/78.0.3904.108%20Safari/537.36	-	-	Error	1pkpNfBQ39sYMnjjUQjmH2w1wdJnbHYTbag21o_3OfcQgPzdL2RSSQ==	www.example.com	http	675	0.102	-	-	-	Error	HTTP/1.1	-	-	25260	0.102	OriginDnsError	text/html	507	-	-
2024-09-16	22:36:26	SEA19-C1	900	192.0.2.200	GET	d111111abcdef8.cloudfront.net	/	502	-	Mozilla/5.0%20(Windows%20NT%2010.0;%20Win64;%20x64)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/78.0.3904.108%20Safari/537.36	-	-	Error	3AqrZGCnF_g0-5KOvfA7c9XLcf4YGvMFSeFdIetR1N_2y8jSis8Zxg==	www.example.com	http	735	0.107	-	-	-	Error	HTTP/1.1	-	-	3802	0.107	OriginDnsError	text/html	507	-	-
2024-09-30	14:59:59	LAX1	392	192.0.2.100	GET	d111111abcdef8.cloudfront.net	/index.html	200	-	Mozilla/5.0%20(Windows%20NT%2010.0;%20Win64;%20x64)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/78.0.3904.108%20Safari/537.36	-	-	Hit	SOX4xwn4XV6Q4rgb7XiVGOHms_BGlTAC4KyHmureZmBNrjGdRLiNIQ==	d111111abcdef8.cloudfront.net	https	23	0.001	-	TLSv1.2	ECDHE-RSA-AES128-GCM-SHA256	Hit	HTTP/2.0	-	-	11040	0.001	Hit	text/html	78	-	-
2024-10-01	15:00:00	LAX1	392	192.0.2.100	GET	d111111abcdef8.cloudfront.net	/index.html	200	-	Mozilla/5.0%20(Windows%20NT%2010.0;%20Win64;%20x64)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/78.0.3904.108%20Safari/537.36	-	-	Hit	k6WGMNkEzR5BEM_SaF47gjtX9zBDO2m349OY2an0QPEaUum1ZOLrow==	d111111abcdef8.cloudfront.net	https	23	0.000	-	TLSv1.2	ECDHE-RSA-AES128-GCM-SHA256	Hit	HTTP/2.0	-	-	11040	0.000	Hit	text/html	78	-	-
2024-10-01	16:00:00	LAX1	392	192.0.2.100	GET	d111111abcdef8.cloudfront.net	/index.html	200	-	Mozilla/5.0%20(Windows%20NT%2010.0;%20Win64;%20x64)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/78.0.3904.108%20Safari/537.36	-	-	Hit	f37nTMVvnKvV2ZSvEsivup_c2kZ7VXzYdjC-GUQZ5qNs-89BlWazbw==	d111111abcdef8.cloudfront.net	https	23	0.001	-	TLSv1.2	ECDHE-RSA-AES128-GCM-SHA256	Hit	HTTP/2.0	-	-	11040	0.001	Hit	text/html	78	-	-

注意ポイント

  • 上から2行はヘッダー、上から3行目からデータが登場
  • ヘッダーはスペース区切り、データはタブ区切りで構成
  • date と time はUTC基準(従って日本のタイムゾーンに読み替えて集計する)

実践

1. Athena の画面からデータベースを作成する

AWSのコンソールにログイン後、Athenaを選択してください。

image.png
image.png

# DB作成
CREATE DATABASE cloudfrontlogs;

※ ちなみに上記クエリ実行後、AWS Glueに移動するとDBが作成されている。
image.png

2. 1で作成したデータベースの中にテーブルを作成する

こちらもGUI上から以下のクエリを実行します。
image.png

-- 基本的にカラム名はログファイルのヘッダと同じように記載していますが、
-- 予約後を回避するため、一部カラム名を変更しています。： "date" → "request-date", "time" → "request-time" 
-- また、一律で「前カッコ"("」は「ハイフン"-"」に置換し、「後カッコ")"」は削除しました。
CREATE EXTERNAL TABLE IF NOT EXISTS cloudfrontlogs.cloudfront_logs (
    `request-date` STRING,
    `request-time` STRING,
    `x-edge-location` STRING,
    `sc-bytes` BIGINT,
    `c-ip` STRING,
    `cs-method` STRING,
    `cs-Host` STRING,
    `cs-uri-stem` STRING,
    `sc-status` INT,
    `cs-Referer` STRING,
    `cs-User-Agent` STRING,
    `cs-uri-query` STRING,
    `cs-Cookie` STRING,
    `x-edge-result-type` STRING,
    `x-edge-request-id` STRING,
    `x-host-header` STRING,
    `cs-protocol` STRING,
    `cs-bytes` BIGINT,
    `time-taken` FLOAT,
    `x-forwarded-for` STRING,
    `ssl-protocol` STRING,
    `ssl-cipher` STRING,
    `x-edge-response-result-type` STRING,
    `cs-protocol-version` STRING,
    `fle-status` STRING,
    `fle-encrypted-fields` BIGINT,
    `c-port` INT,
    `time-to-first-byte` FLOAT,
    `x-edge-detailed-result-type` STRING,
    `sc-content-type` STRING,
    `sc-content-len` BIGINT,
    `sc-range-start` BIGINT,
    `sc-range-end` BIGINT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
-- s3のバケット名は適宜修正してください。
LOCATION 's3://cloudfront-log-bucket/logger/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

※ データベース作成時と同様に、AWS Glue上にテーブルが作成されている。
image.png

3. 動作確認

簡単なクエリを実行してエラーが出なければ成功です。
image.png

SELECT * FROM "cloudfrontlogs"."cloudfront_logs" limit 10;

4. 月別に集計するSQL

2024年9月分の集計を行うようなSQLを作成しました。Cloudfront のログのタイムゾーンは UTC なので日本時間(UTC+9)に修正して集計します。

SELECT
    *,
    date_format(
        parse_datetime(
            concat("request-date", ' ', "request-time"),
            'yyyy-MM-dd HH:mm:ss'
        ) AT TIME ZONE 'Asia/Tokyo',
        '%Y-%m-%d'
    ) as "日付",
    date_format(
        parse_datetime(
            concat("request-date", ' ', "request-time"),
            'yyyy-MM-dd HH:mm:ss'
        ) AT TIME ZONE 'Asia/Tokyo',
        '%T'
    ) as "時間",
    date_format(
        parse_datetime(
            concat("request-date", ' ', "request-time"),
            'yyyy-MM-dd HH:mm:ss'
        ) AT TIME ZONE 'Asia/Tokyo',
        '%Y-%m-%d %T'
    ) as "日時"
FROM cloudfront_logs
where
    -- 実行した日時
    date(
        parse_datetime(
            concat("request-date", ' ', "request-time"),
            'yyyy-MM-dd HH:mm:ss'
        ) AT TIME ZONE 'Asia/Tokyo'
    -- 最初の日付
    ) between date(
        date_trunc(
            'month',
            DATE '2024-09-01'
        )
    )
    -- 最後の日付
    AND last_day_of_month(
        date_trunc(
            'month',
            DATE '2024-09-01'
        )
    )
ORDER BY
    "日付",
    "時間";

エディタに貼り付けて実行し、9月中のログが全て取得できていれば成功です。

image.png

↓取得したログ

request-date	request-time	x-edge-location	sc-bytes	c-ip	cs-method	cs-host	cs-uri-stem	sc-status	cs-referer	cs-user-agent	cs-uri-query	cs-cookie	x-edge-result-type	x-edge-request-id	x-host-header	cs-protocol	cs-bytes	time-taken	x-forwarded-for	ssl-protocol	ssl-cipher	x-edge-response-result-type	cs-protocol-version	fle-status	fle-encrypted-fields	c-port	time-to-first-byte	x-edge-detailed-result-type	sc-content-type	sc-content-len	sc-range-start	sc-range-end	日付	時間	日時
2024-08-31	15:00:00	SFO53-P5	511	27.143.20.200	GET	dewhexwchqqkk.cloudfront.net	/	403	-	Mozilla/5.0%20(Macintosh;%20Intel%20Mac%20OS%20X%2010_15_7)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/128.0.0.0%20Safari/537.36	-	-	Error	RAYVkLgbCeJ7MqDIO1PTR7EsrONdH9mKGmrQUufNbd7XLWj7isL-Pw==	dewhexwchqqkk.cloudfront.net	https	28	0.438	-	TLSv1.3	TLS_AES_128_GCM_SHA256	Error	HTTP/2.0	-		49626	0.438	Error	application/xml				2024-09-01	00:00:00	2024-09-01 00:00:00
2024-08-31	15:00:01	SFO53-P5	483	27.143.20.216	GET	dewhexwchqqkk.cloudfront.net	/favicon.ico	403	https://dewhexwchqqkk.cloudfront.net/	Mozilla/5.0%20(Macintosh;%20Intel%20Mac%20OS%20X%2010_15_7)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/128.0.0.0%20Safari/537.36	-	-	Error	3c9rA_LI_g3m3GByYB_3o3ofpvhWpWtJA2H2NNnkdmRIfjv0RjtZHQ==	dewhexwchqqkk.cloudfront.net	https	36	0.447	-	TLSv1.3	TLS_AES_128_GCM_SHA256	Error	HTTP/2.0	-		49626	0.447	Error	application/xml				2024-09-01	00:00:01	2024-09-01 00:00:01
2024-09-16	22:36:26	SEA19-C1	900	192.0.2.200	GET	d111111abcdef8.cloudfront.net	/	502	-	Mozilla/5.0%20(Windows%20NT%2010.0;%20Win64;%20x64)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/78.0.3904.108%20Safari/537.36	-	-	Error	3AqrZGCnF_g0-5KOvfA7c9XLcf4YGvMFSeFdIetR1N_2y8jSis8Zxg==	www.example.com	http	735	0.107	-	-	-	Error	HTTP/1.1	-		3802	0.107	OriginDnsError	text/html	507			2024-09-17	07:36:26	2024-09-17 07:36:26
2024-09-16	22:36:27	SEA19-C1	900	192.0.2.200	GET	d111111abcdef8.cloudfront.net	/favicon.ico	502	http://www.example.com/	Mozilla/5.0%20(Windows%20NT%2010.0;%20Win64;%20x64)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/78.0.3904.108%20Safari/537.36	-	-	Error	1pkpNfBQ39sYMnjjUQjmH2w1wdJnbHYTbag21o_3OfcQgPzdL2RSSQ==	www.example.com	http	675	0.102	-	-	-	Error	HTTP/1.1	-		25260	0.102	OriginDnsError	text/html	507			2024-09-17	07:36:27	2024-09-17 07:36:27
2024-09-30	14:59:59	LAX1	392	192.0.2.100	GET	d111111abcdef8.cloudfront.net	/index.html	200	-	Mozilla/5.0%20(Windows%20NT%2010.0;%20Win64;%20x64)%20AppleWebKit/537.36%20(KHTML,%20like%20Gecko)%20Chrome/78.0.3904.108%20Safari/537.36	-	-	Hit	SOX4xwn4XV6Q4rgb7XiVGOHms_BGlTAC4KyHmureZmBNrjGdRLiNIQ==	d111111abcdef8.cloudfront.net	https	23	0.001	-	TLSv1.2	ECDHE-RSA-AES128-GCM-SHA256	Hit	HTTP/2.0	-		11040	0.001	Hit	text/html	78			2024-09-30	23:59:59	2024-09-30 23:59:59

終わりに

以上で Athena から Cloudfront ログを集計することができました。次回は以下のことをできるようにしましょう。

  • Lamnda関数から上記を実行する
  • Lamnda関数を EventBridge から毎月自動で実行する
  • 実行後の完了や、エラーを Slack に通知する
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?