ALBのログのアクセス元がCloudFrontだけであることをログから検証する方法

次のコマンドからcloudfrontの全CIDRを列挙する (ちなchatgptの言うとおりにやっただけなので絞り込みが正しいかは未検証)

curl -O https://ip-ranges.amazonaws.com/ip-ranges.json
  jq '.prefixes[] | select(.service=="CLOUDFRONT") | .ip_prefix' ip-ranges.json

上記の結果から次のようなクエリを作成して実行

SELECT split(client_port, ':') [ 1 ], *
FROM "cloudfront_logs"."log_prd_alb"
where elb = 'app/xxxxxxxxxx/deadbeef1234'
	and not (



contains('120.52.22.96/27', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('205.251.249.0/24', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('180.163.57.128/26', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('204.246.168.0/22', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('111.13.171.128/26', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
... (100行以上続く)
OR contains('35.93.172.0/23', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('44.227.178.0/24', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('44.234.108.128/25', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('44.234.90.252/30', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))





	)
limit 100;

例えば、CloudFront以外からのアクセスをリクエストのホスト名をカウントして表示するSQLは以下

with t1 as 
(
SELECT
regexp_extract(request, '//([^:]*):', 1) as request_host,
split(client_port, ':') [ 1 ] as client_ip,
*
FROM "cloudfront_logs"."log_prd_alb"
where 
    year = 2025
    and month = 5
    and day = 28
	and elb = 'app/xxxxxxxxxx/deadbeef1234'
	and not (



contains('120.52.22.96/27', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('205.251.249.0/24', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('180.163.57.128/26', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('204.246.168.0/22', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('111.13.171.128/26', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
...
OR contains('44.227.178.0/24', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('44.234.108.128/25', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))
OR contains('44.234.90.252/30', CAST(split(client_port, ':') [ 1 ] AS IPADDRESS))





	)
),
t2 as (
select request_host, count(*) as c from t1 group by request_host
)
select * from t2 order by c desc;