WAFにアタッチされたルール別の Count, Block, Allow を表にする(Cloudfront)
CloudFrontのWAFルール変更時に1週間分の統計データを遡って確認するため、Bash・jq・DuckDBを組み合わせた日別レポート生成スクリプトを作成しました。このツールの実装と使い方をご紹介します。
2026.08.13
1. はじめに
こんにちは。クラスメソッドオペレーションズのあのふじたです。
最近、CloudFrontにアタッチされているWAFルールを大きく変更する必要があり、変更後のルールの状況を1週間程度遡って確認していく機会がありました。
DevOps Agentに調査やレポート作成を任せられる場面も増えてきましたが、そういった場合でも人間側で最低限の裏付けを取れるようにしておきたいと考え、簡易的なScriptを作成しました。
備忘録として記事を書こうと思います。
2. 必要なパッケージのインストール（動作確認した環境のみ)
jq Install
MacOS: brew install jq
Amzn2023: yum install jq
DuckDB Install
MacOS/Amzn2023: curl https://install.duckdb.org | sh
3. ファイル構成
以下のような構成で実装しました。
AIの支援を活用しながら作成しています。
cloudfront_waf_daily_metrics.sh
#!/bin/bash
#
# CloudFront + AWS WAFv2 日別ルールレポート
#
# 対象期間:
# --end-date で指定したJST日付を含む直近7日間
#
# 標準出力:
# 注意事項 + Markdown表
# ※ Markdown表では横幅を抑えるため MetricName / RuleType を省略
#
# CSV:
# cloudfront_waf_daily_report_<DistributionID>_<終了日>.csv
# ※ CSVには MetricName / RuleType を含む
#
# 必須:
# - Bash 3.2
# - AWS CLI v2
# - jq
# - DuckDB CLI
#
set -euo pipefail
export LC_ALL=C
export AWS_PAGER=""
PROFILE=""
DISTRIBUTION=""
END_DATE=""
usage() {
cat <<'EOF'
Usage:
cloudfront_waf_daily_metrics.sh \
--profile PROFILE \
--distribution DISTRIBUTION \
--end-date YYYY-MM-DD
Options:
-p, --profile AWS CLI profile name
-d, --distribution Distribution ID / CloudFront domain / Alias / Comment
-e, --end-date Last date of the 7-day period in JST (YYYY-MM-DD)
-h, --help Show this help
EOF
}
die() {
echo "ERROR: $*" >&2
exit 1
}
require_value() {
[ "$#" -ge 2 ] && [ -n "${2:-}" ] || die "Option $1 requires a value."
}
while [ "$#" -gt 0 ]; do
case "$1" in
-p|--profile) require_value "$@"; PROFILE="$2"; shift 2 ;;
-d|--distribution|--distribution-name) require_value "$@"; DISTRIBUTION="$2"; shift 2 ;;
-e|--end-date) require_value "$@"; END_DATE="$2"; shift 2 ;;
-h|--help) usage; exit 0 ;;
*) die "Unknown option: $1" ;;
esac
done
[ -n "$PROFILE" ] || die "--profile is required."
[ -n "$DISTRIBUTION" ] || die "--distribution is required."
[ -n "$END_DATE" ] || die "--end-date is required."
for cmd in aws jq duckdb; do
command -v "$cmd" >/dev/null 2>&1 || die "$cmd is not installed."
done
# 日付の検証と、期間の各境界値の算出をまとめて行う。
# JST 00:00 = 前日UTC 15:00
DATE_INFO="$(
jq -nr --arg d "$END_DATE" '
(try ($d + "T00:00:00Z" | fromdateiso8601) catch empty)
| select(strftime("%Y-%m-%d") == $d)
| [
tostring,
(. - 6 * 86400 | strftime("%Y-%m-%d")),
(. - 6 * 86400 - 9 * 3600 | strftime("%Y-%m-%dT%H:%M:%SZ")),
(. + 86400 - 9 * 3600 | strftime("%Y-%m-%dT%H:%M:%SZ"))
]
| join(" ")
'
)"
[ -n "$DATE_INFO" ] ||
die "--end-date must be a valid calendar date in YYYY-MM-DD: $END_DATE"
read -r END_DATE_EPOCH START_DATE START_UTC END_UTC <<EOF
$DATE_INFO
EOF
TMP_DIR="$(mktemp -d "${TMPDIR:-/tmp}/cloudfront-waf-metrics.XXXXXX")"
trap 'rm -rf "$TMP_DIR"' EXIT HUP INT TERM
DISTRIBUTION_JSON="$TMP_DIR/distribution.json"
WEB_ACL_JSON="$TMP_DIR/web-acl.json"
RULES_TSV="$TMP_DIR/rules.tsv"
DAYS_TSV="$TMP_DIR/days.tsv"
RULE_METRICS_TSV="$TMP_DIR/rule-metrics.tsv"
DAILY_METRICS_TSV="$TMP_DIR/daily-metrics.tsv"
REPORT_DB="$TMP_DIR/report.duckdb"
# CloudWatchからSum統計(1時間粒度)をJSONで標準出力へ返す。
# 使い方: cw_get_sum NAMESPACE METRIC_NAME DIMENSION...
cw_get_sum() {
local namespace="$1" metric="$2"
shift 2
aws cloudwatch get-metric-statistics \
--profile "$PROFILE" \
--region us-east-1 \
--namespace "$namespace" \
--metric-name "$metric" \
--dimensions "$@" \
--start-time "$START_UTC" \
--end-time "$END_UTC" \
--period 3600 \
--statistics Sum \
--output json
}
# 使い方: waf_get_sum METRIC_NAME WEBACL_DIMENSION RULE_DIMENSION
waf_get_sum() {
cw_get_sum AWS/WAFV2 "$1" \
"Name=WebACL,Value=$2" \
"Name=Rule,Value=$3"
}
datapoint_count() {
jq '.Datapoints | length'
}
# 標準入力のCloudWatch JSONをJST日別のTSV行へ変換して追記する。
# RULE_NAMEを渡した場合はrule_name列を含む4列、省略時は3列。
# 使い方: append_datapoints OUTPUT_TSV METRIC_NAME [RULE_NAME]
append_datapoints() {
jq -r --arg metric "$2" --arg rule "${3:-}" '
def tz_offset:
if . == "Z" then 0
else
(if startswith("-") then -1 else 1 end)
* ((.[1:3] | tonumber) * 3600 + (.[4:6] | tonumber) * 60)
end;
def ts_to_epoch:
capture(
"^(?<datetime>[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2})(?:\\.[0-9]+)?(?<timezone>Z|[+-][0-9]{2}:[0-9]{2})$"
) as $ts
| ($ts.datetime + "Z" | fromdateiso8601) - ($ts.timezone | tz_offset);
.Datapoints[]?
| [(.Timestamp | ts_to_epoch + 9 * 3600 | strftime("%Y-%m-%d"))]
+ (if $rule == "" then [] else [$rule] end)
+ [$metric, (.Sum // 0)]
| @tsv
' >> "$1"
}
# ===== 認証処理 =====
cache_credentials() {
local profile="$1"
local creds
# export-credentials 1回だけで認証チェックとキャッシュを同時に行う
if creds="$(
aws configure export-credentials \
--profile "$profile" \
--format env 2>/dev/null
)" && [ -n "$creds" ]; then
eval "$creds"
echo "認証情報をキャッシュしました"
return 0
fi
# 失敗した場合のみ SSO login を試行
echo "認証が無効です。aws sso login を試みます..."
if ! aws sso login --profile "$profile"; then
echo "ログインに失敗しました。" >&2
return 1
fi
echo "ログインに成功しました。再取得中..."
# 再取得
if creds="$(
aws configure export-credentials \
--profile "$profile" \
--format env 2>/dev/null
)" && [ -n "$creds" ]; then
eval "$creds"
echo "認証情報をキャッシュしました"
return 0
fi
echo "認証情報の取得に失敗しました。" >&2
return 1
}
# 認証情報のキャッシュ
echo "AWS profile: ${PROFILE}"
cache_credentials "$PROFILE" ||
die "AWS authentication failed for profile: $PROFILE"
echo "Resolving CloudFront distribution..." >&2
aws cloudfront list-distributions \
--profile "$PROFILE" \
--output json |
jq --arg target "$DISTRIBUTION" '
[
.DistributionList.Items[]?
| select(
.Id == $target
or .DomainName == $target
or .Comment == $target
or ((.Aliases.Items // []) | index($target) != null)
)
]
' > "$DISTRIBUTION_JSON"
DISTRIBUTION_COUNT="$(jq 'length' "$DISTRIBUTION_JSON")"
[ "$DISTRIBUTION_COUNT" -gt 0 ] ||
die "CloudFront distribution was not found: $DISTRIBUTION"
if [ "$DISTRIBUTION_COUNT" -gt 1 ]; then
echo "The value matched multiple distributions:" >&2
jq -r '.[] | " \(.Id) \(.DomainName) Comment=\(.Comment)"' \
"$DISTRIBUTION_JSON" >&2
die "Specify the Distribution ID instead."
fi
DISTRIBUTION_ID="$(jq -r '.[0].Id' "$DISTRIBUTION_JSON")"
DISTRIBUTION_DOMAIN="$(jq -r '.[0].DomainName' "$DISTRIBUTION_JSON")"
REPORT_CSV="cloudfront_waf_daily_report_${DISTRIBUTION_ID}_${END_DATE}.csv"
WEB_ACL_ARN="$(
aws cloudfront get-distribution-config \
--profile "$PROFILE" \
--id "$DISTRIBUTION_ID" \
--output json |
jq -r '.DistributionConfig.WebACLId // empty'
)"
[ -n "$WEB_ACL_ARN" ] ||
die "No Web ACL is associated with distribution $DISTRIBUTION_ID."
case "$WEB_ACL_ARN" in
arn:*:wafv2:*:global/webacl/*/*) ;;
*) die "The associated Web ACL is not a CloudFront-scoped WAFv2 Web ACL: $WEB_ACL_ARN" ;;
esac
# ARN末尾は ... /webacl / <Name> / <Id>
WEB_ACL_ID="${WEB_ACL_ARN##*/}"
WEB_ACL_NAME="${WEB_ACL_ARN%/*}"
WEB_ACL_NAME="${WEB_ACL_NAME##*/}"
echo "Loading WAFv2 Web ACL..." >&2
aws wafv2 get-web-acl \
--profile "$PROFILE" \
--region us-east-1 \
--scope CLOUDFRONT \
--name "$WEB_ACL_NAME" \
--id "$WEB_ACL_ID" \
--output json > "$WEB_ACL_JSON"
WEB_ACL_METRIC_NAME="$(
jq -r '.WebACL.VisibilityConfig.MetricName // empty' "$WEB_ACL_JSON"
)"
WEB_ACL_METRICS_ENABLED="$(
jq -r '.WebACL.VisibilityConfig.CloudWatchMetricsEnabled // false' \
"$WEB_ACL_JSON"
)"
DEFAULT_ACTION_TYPE="$(
jq -r '.WebACL.DefaultAction | keys[0] | ascii_upcase' "$WEB_ACL_JSON"
)"
case "$DEFAULT_ACTION_TYPE" in
ALLOW) DEFAULT_ACTION_METRIC_NAME="AllowedRequests" ;;
BLOCK) DEFAULT_ACTION_METRIC_NAME="BlockedRequests" ;;
*) die "Unsupported Web ACL DefaultAction: $DEFAULT_ACTION_TYPE" ;;
esac
[ -n "$WEB_ACL_METRIC_NAME" ] ||
die "Web ACL VisibilityConfig.MetricName was not found."
# トップレベルルール一覧
jq -r '
[
"priority",
"rule_name",
"rule_metric_name",
"rule_type",
"configured_action",
"metrics_enabled"
],
(
.WebACL.Rules
| sort_by(.Priority)[]
| [
(.Priority | tostring),
.Name,
.VisibilityConfig.MetricName,
(
if .Statement.ManagedRuleGroupStatement then "ManagedRuleGroup"
elif .Statement.RuleGroupReferenceStatement then "RuleGroupReference"
else "DirectRule"
end
),
(
if .Action != null then (.Action | keys[0] | ascii_upcase)
elif .OverrideAction != null then
if (.OverrideAction | has("Count")) then "OVERRIDE_COUNT"
else "RULE_GROUP"
end
else "-"
end
),
(.VisibilityConfig.CloudWatchMetricsEnabled | tostring)
]
)
| @tsv
' "$WEB_ACL_JSON" > "$RULES_TSV"
RULE_COUNT="$(jq '.WebACL.Rules | length' "$WEB_ACL_JSON")"
[ "$RULE_COUNT" -gt 0 ] ||
die "The Web ACL has no top-level rules: $WEB_ACL_NAME"
printf 'report_date\n' > "$DAYS_TSV"
jq -nr --argjson epoch "$END_DATE_EPOCH" '
range(0; 7) | $epoch + ((. - 6) * 86400) | strftime("%Y-%m-%d")
' >> "$DAYS_TSV"
printf 'report_date\trule_name\tmetric_name\tmetric_value\n' \
> "$RULE_METRICS_TSV"
printf 'report_date\tmetric_name\tmetric_value\n' \
> "$DAILY_METRICS_TSV"
count_all_rule_datapoints() {
local candidate="$1" metric points total=0
for metric in CountedRequests BlockedRequests AllowedRequests; do
points="$(waf_get_sum "$metric" "$candidate" ALL | datapoint_count)"
total=$((total + points))
done
echo "$total"
}
echo "Detecting CloudWatch WebACL dimension..." >&2
METRIC_NAME_POINT_COUNT="$(count_all_rule_datapoints "$WEB_ACL_METRIC_NAME")"
if [ "$WEB_ACL_NAME" = "$WEB_ACL_METRIC_NAME" ]; then
WEB_ACL_NAME_POINT_COUNT="$METRIC_NAME_POINT_COUNT"
else
WEB_ACL_NAME_POINT_COUNT="$(count_all_rule_datapoints "$WEB_ACL_NAME")"
fi
if [ "$METRIC_NAME_POINT_COUNT" -gt 0 ] &&
[ "$METRIC_NAME_POINT_COUNT" -ge "$WEB_ACL_NAME_POINT_COUNT" ]; then
WEB_ACL_CW_DIMENSION="$WEB_ACL_METRIC_NAME"
elif [ "$WEB_ACL_NAME_POINT_COUNT" -gt 0 ]; then
WEB_ACL_CW_DIMENSION="$WEB_ACL_NAME"
else
# 対象期間にデータポイントが無い場合はメトリクス定義の存在で判定する。
WEB_ACL_CW_DIMENSION="$(
aws cloudwatch list-metrics \
--profile "$PROFILE" \
--region us-east-1 \
--namespace AWS/WAFV2 \
--dimensions "Name=Rule,Value=ALL" \
--output json |
jq -r \
--arg metric_name "$WEB_ACL_METRIC_NAME" \
--arg acl_name "$WEB_ACL_NAME" '
def has_webacl($candidate):
any(
.Metrics[]?;
(.Dimensions | map(.Name) | sort) == ["Rule", "WebACL"]
and any(
.Dimensions[];
.Name == "WebACL" and .Value == $candidate
)
);
if has_webacl($metric_name) then $metric_name
elif has_webacl($acl_name) then $acl_name
else empty
end
'
)"
[ -n "$WEB_ACL_CW_DIMENSION" ] ||
die "Could not detect the CloudWatch WebACL dimension."
fi
echo "Web ACL name: $WEB_ACL_NAME" >&2
echo "Web ACL configured MetricName: $WEB_ACL_METRIC_NAME" >&2
echo "CloudWatch WebACL dimension: $WEB_ACL_CW_DIMENSION" >&2
echo "Web ACL Default Action: $DEFAULT_ACTION_TYPE" >&2
echo "Default Action metric: $DEFAULT_ACTION_METRIC_NAME" >&2
echo "Detecting Default Action Rule dimension..." >&2
# Default ActionのRuleディメンションは、設定中のDefault Actionに対応する
# メトリクスだけを使って判定する。
DEFAULT_ACTION_POINT_COUNT="$(
waf_get_sum "$DEFAULT_ACTION_METRIC_NAME" "$WEB_ACL_CW_DIMENSION" \
"Default_Action" | datapoint_count
)"
WEB_ACL_METRIC_POINT_COUNT="$(
waf_get_sum "$DEFAULT_ACTION_METRIC_NAME" "$WEB_ACL_CW_DIMENSION" \
"$WEB_ACL_METRIC_NAME" | datapoint_count
)"
echo "Default Action candidate datapoints:" >&2
echo " Rule=Default_Action: $DEFAULT_ACTION_POINT_COUNT" >&2
echo " Rule=$WEB_ACL_METRIC_NAME: $WEB_ACL_METRIC_POINT_COUNT" >&2
if [ "$DEFAULT_ACTION_POINT_COUNT" -gt 0 ]; then
DEFAULT_ACTION_RULE_DIMENSION="Default_Action"
elif [ "$WEB_ACL_METRIC_POINT_COUNT" -gt 0 ]; then
DEFAULT_ACTION_RULE_DIMENSION="$WEB_ACL_METRIC_NAME"
else
# 対象期間にDefault Actionが0件の場合はAWS標準値へフォールバック。
DEFAULT_ACTION_RULE_DIMENSION="Default_Action"
echo "WARNING: No Default Action datapoints were found; falling back to Rule=Default_Action." >&2
fi
echo "Default Action Rule dimension: $DEFAULT_ACTION_RULE_DIMENSION" >&2
echo "Getting rule metrics for $START_DATE through $END_DATE JST..." >&2
tail -n +2 "$RULES_TSV" |
cut -f2,3,6 |
while IFS="$(printf '\t')" read -r \
RULE_NAME RULE_METRIC_NAME METRICS_ENABLED
do
if [ "$METRICS_ENABLED" != "true" ]; then
echo "Skipping disabled rule metric: $RULE_NAME" >&2
continue
fi
for METRIC_NAME in CountedRequests BlockedRequests AllowedRequests; do
echo " Rule: $RULE_NAME / $METRIC_NAME" >&2
waf_get_sum "$METRIC_NAME" "$WEB_ACL_CW_DIMENSION" "$RULE_METRIC_NAME" |
append_datapoints "$RULE_METRICS_TSV" "$METRIC_NAME" "$RULE_NAME"
done
done
echo "Getting CloudFront Requests..." >&2
cw_get_sum AWS/CloudFront Requests \
"Name=DistributionId,Value=$DISTRIBUTION_ID" \
"Name=Region,Value=Global" |
append_datapoints "$DAILY_METRICS_TSV" "CloudFrontRequests"
echo "Getting WAF daily totals with Rule=ALL..." >&2
for MAPPING in \
CountedRequests:WafCountMatches \
BlockedRequests:WafBlockedRequests \
AllowedRequests:WafAllowedRequests
do
waf_get_sum "${MAPPING%%:*}" "$WEB_ACL_CW_DIMENSION" ALL |
append_datapoints "$DAILY_METRICS_TSV" "${MAPPING#*:}"
done
# Default ActionはAllowとBlockを別々の列へ保存する。
if [ "$WEB_ACL_METRICS_ENABLED" = "true" ]; then
for MAPPING in \
AllowedRequests:WafDefaultAllowedRequests \
BlockedRequests:WafDefaultBlockedRequests
do
echo "Getting WAF Default Action / ${MAPPING%%:*}..." >&2
waf_get_sum "${MAPPING%%:*}" "$WEB_ACL_CW_DIMENSION" \
"$DEFAULT_ACTION_RULE_DIMENSION" |
append_datapoints "$DAILY_METRICS_TSV" "${MAPPING#*:}"
done
else
echo "Skipping WAF Default Action: Web ACL metrics are disabled." >&2
fi
echo "Creating report..." >&2
duckdb "$REPORT_DB" >/dev/null <<SQL
CREATE MACRO tsv(p) AS TABLE
SELECT * FROM read_csv(p, delim = '\t', header = true, all_varchar = true);
CREATE MACRO fmt_count(v) AS
CAST(CAST(ROUND(COALESCE(v, 0)) AS BIGINT) AS VARCHAR);
CREATE TABLE final_report AS
WITH
rule_totals AS (
SELECT * FROM (
PIVOT tsv('$RULE_METRICS_TSV')
ON metric_name IN ('CountedRequests', 'BlockedRequests', 'AllowedRequests')
USING SUM(TRY_CAST(metric_value AS DOUBLE))
GROUP BY report_date, rule_name
)
),
daily_totals AS (
SELECT * FROM (
PIVOT tsv('$DAILY_METRICS_TSV')
ON metric_name IN (
'CloudFrontRequests',
'WafCountMatches',
'WafBlockedRequests',
'WafAllowedRequests',
'WafDefaultAllowedRequests',
'WafDefaultBlockedRequests'
)
USING SUM(TRY_CAST(metric_value AS DOUBLE))
GROUP BY report_date
)
)
SELECT
d.report_date AS "Date (JST)",
TRY_CAST(r.priority AS INTEGER) AS "Priority",
r.rule_name AS "Rule",
r.rule_metric_name AS "MetricName",
r.rule_type AS "RuleType",
r.configured_action AS "ConfiguredAction",
CASE WHEN r.metrics_enabled <> 'true' THEN '-'
ELSE fmt_count(rt.CountedRequests) END AS "Count",
CASE WHEN r.metrics_enabled <> 'true' THEN '-'
ELSE fmt_count(rt.BlockedRequests) END AS "Block",
CASE WHEN r.metrics_enabled <> 'true' THEN '-'
ELSE fmt_count(rt.AllowedRequests) END AS "Allow",
fmt_count(dm.CloudFrontRequests) AS "CloudFrontRequests",
fmt_count(dm.WafCountMatches) AS "WafCountMatches",
fmt_count(dm.WafBlockedRequests) AS "WafBlockedRequests",
fmt_count(dm.WafAllowedRequests) AS "WafAllowedRequests",
'$DEFAULT_ACTION_TYPE' AS "WafDefaultAction",
CASE WHEN '$WEB_ACL_METRICS_ENABLED' <> 'true' THEN '-'
ELSE fmt_count(dm.WafDefaultAllowedRequests) END
AS "WafDefaultAllowedRequests",
CASE WHEN '$WEB_ACL_METRICS_ENABLED' <> 'true' THEN '-'
ELSE fmt_count(dm.WafDefaultBlockedRequests) END
AS "WafDefaultBlockedRequests"
FROM tsv('$DAYS_TSV') AS d
CROSS JOIN tsv('$RULES_TSV') AS r
LEFT JOIN rule_totals AS rt
ON rt.report_date = d.report_date
AND rt.rule_name = r.rule_name
LEFT JOIN daily_totals AS dm
ON dm.report_date = d.report_date;
-- CSVにはMetricNameとRuleTypeを含める。
COPY (
SELECT *
FROM final_report
ORDER BY "Priority" ASC NULLS LAST, "Rule" ASC, "Date (JST)" ASC
)
TO '$REPORT_CSV'
WITH (FORMAT CSV, HEADER);
SQL
echo "# Distribution: $DISTRIBUTION_ID ($DISTRIBUTION_DOMAIN)" >&2
echo "# WebACL: $WEB_ACL_NAME" >&2
echo "# Period (JST): $START_DATE - $END_DATE" >&2
echo "# CloudWatch UTC range: $START_UTC - $END_UTC (end exclusive)" >&2
echo "# CloudWatch WebACL dimension: $WEB_ACL_CW_DIMENSION" >&2
echo "# Default Action: $DEFAULT_ACTION_TYPE" >&2
echo "# Default Action Rule dimension: $DEFAULT_ACTION_RULE_DIMENSION" >&2
echo "# CSV report: $(pwd)/$REPORT_CSV" >&2
cat <<'EOF'
## 集計値に関する注意事項
- `CloudFrontRequests` は対象CloudFront DistributionへのViewer Request総数です。
- `Count` は非終端アクションです。同じリクエストが複数のCountや、後続のAllow／Block／Default Actionに重複して含まれる場合があります。
- `WafCountMatches`、`WafBlockedRequests`、`WafAllowedRequests` は、CloudWatchの `Rule=ALL` から取得した日別集計値です。
- `WafDefaultAction` はWeb ACLに設定されているDefault Action種別です。
- `WafDefaultAllowedRequests` と `WafDefaultBlockedRequests` は、Default ActionのAllow／Blockを分けて表示します。
- CAPTCHA／Challenge／Monetizeは現在の日別集計列には含まれていません。
- Managed Rule Group内部のCountは、トップレベルルールの `Count` で完全に網羅できない場合があります。
- 日別集計列は同じ日付の各ルール行に繰り返し格納されます。CSV全行で単純合計しないでください。
- WAF集計値の合計は、重複計上や未算入アクションにより `CloudFrontRequests` と一致するとは限りません。
## 日別・ルール別レポート
ソート順: `Priority` 昇順 → `Rule` 昇順 → `Date (JST)` 昇順
EOF
# Markdownでは横幅を抑えるため、MetricNameとRuleTypeを省略し、
# 日別集計列には短縮名を使用する。
duckdb "$REPORT_DB" -markdown -c "
SELECT
\"Date (JST)\",
\"Priority\",
\"Rule\",
\"ConfiguredAction\" AS \"Action\",
\"Count\",
\"Block\",
\"Allow\",
\"CloudFrontRequests\" AS \"CFReqs\",
\"WafCountMatches\" AS \"WafCount\",
\"WafBlockedRequests\" AS \"WafBlockedReqs\",
\"WafAllowedRequests\" AS \"WafAllowedReqs\",
\"WafDefaultAction\" AS \"WafDefAction\",
\"WafDefaultAllowedRequests\" AS \"WafDefAllowReqs\",
\"WafDefaultBlockedRequests\" AS \"WafDefBlockReqs\"
FROM final_report
ORDER BY \"Priority\" ASC NULLS LAST, \"Rule\" ASC, \"Date (JST)\" ASC;
"
4. 実行イメージ
$ bash cloudfront_waf_daily_metrics.sh -p dev-dummy-profile -d ABC123DEFGHIJK -e 2026-07-27
Resolving CloudFront distribution...
Loading WAFv2 Web ACL...
Detecting CloudWatch WebACL dimension...
Web ACL name: dummy-dev-shop-wacl
Web ACL configured MetricName: dummy-dev-shop-wacl
CloudWatch WebACL dimension: dummy-dev-shop-wacl
Web ACL Default Action: BLOCK
Default Action metric: BlockedRequests
Detecting Default Action Rule dimension...
Default Action candidate datapoints:
Rule=Default_Action: 0
Rule=dummy-dev-shop-wacl: 0
WARNING: No Default Action datapoints were found; falling back to Rule=Default_Action.
Default Action Rule dimension: Default_Action
Getting rule metrics for 2026-07-21 through 2026-07-27 JST...
Rule: Api-Block / CountedRequests
Rule: Api-Block / BlockedRequests
Rule: Api-Block / AllowedRequests
Rule: BasicAuthRule / CountedRequests
Rule: BasicAuthRule / BlockedRequests
Rule: BasicAuthRule / AllowedRequests
Rule: BlockGetHeaderRule / CountedRequests
Rule: BlockGetHeaderRule / BlockedRequests
Rule: BlockGetHeaderRule / AllowedRequests
Rule: BlockPostHeaderRule / CountedRequests
Rule: BlockPostHeaderRule / BlockedRequests
Rule: BlockPostHeaderRule / AllowedRequests
Rule: DevShopForMaintenanceRule / CountedRequests
Rule: DevShopForMaintenanceRule / BlockedRequests
Rule: DevShopForMaintenanceRule / AllowedRequests
Rule: AllowUploadsURIRule / CountedRequests
Rule: AllowUploadsURIRule / BlockedRequests
Rule: AllowUploadsURIRule / AllowedRequests
Rule: AWS-AWSManagedRulesKnownBadInputsRuleSet / CountedRequests
Rule: AWS-AWSManagedRulesKnownBadInputsRuleSet / BlockedRequests
Rule: AWS-AWSManagedRulesKnownBadInputsRuleSet / AllowedRequests
Rule: AWS-AWSManagedRulesAdminProtectionRuleSet / CountedRequests
Rule: AWS-AWSManagedRulesAdminProtectionRuleSet / BlockedRequests
Rule: AWS-AWSManagedRulesAdminProtectionRuleSet / AllowedRequests
Rule: DevShopAllowIpSetRule / CountedRequests
Rule: DevShopAllowIpSetRule / BlockedRequests
Rule: DevShopAllowIpSetRule / AllowedRequests
Getting CloudFront Requests...
Getting WAF daily totals with Rule=ALL...
Getting WAF Default Action / AllowedRequests...
Getting WAF Default Action / BlockedRequests...
Creating report...
# Distribution: ABC123DEFGHIJK (xxxxxxxxxxxxxx[.]cloudfront[.]net)
# WebACL: dummy-dev-shop-wacl
# Period (JST): 2026-07-21 - 2026-07-27
# CloudWatch UTC range: 2026-07-20T15:00:00Z - 2026-07-27T15:00:00Z (end exclusive)
# CloudWatch WebACL dimension: dummy-dev-shop-wacl
# Default Action: BLOCK
# Default Action Rule dimension: Default_Action
# CSV report: /home/bloguser/cloudfront_waf_daily_report_ABC123DEFGHIJK_2026-07-27.csv
## 集計値に関する注意事項
- `CloudFrontRequests` は対象CloudFront DistributionへのViewer Request総数です。
- `Count` は非終端アクションです。同じリクエストが複数のCountや、後続のAllow／Block／Default Actionに重複して含まれる場合があります。
- `WafCountMatches`、`WafBlockedRequests`、`WafAllowedRequests` は、CloudWatchの `Rule=ALL` から取得した日別集計値です。
- `WafDefaultAction` はWeb ACLに設定されているDefault Action種別です。
- `WafDefaultAllowedRequests` と `WafDefaultBlockedRequests` は、Default ActionのAllow／Blockを分けて表示します。
- CAPTCHA／Challenge／Monetizeは現在の日別集計列には含まれていません。
- Managed Rule Group内部のCountは、トップレベルルールの `Count` で完全に網羅できない場合があります。
- 日別集計列は同じ日付の各ルール行に繰り返し格納されます。CSV全行で単純合計しないでください。
- WAF集計値の合計は、重複計上や未算入アクションにより `CloudFrontRequests` と一致するとは限りません。
## 日別・ルール別レポート
ソート順: `Priority` 昇順 → `Rule` 昇順 → `Date (JST)` 昇順
| Date(JST) | Priority | Rule | Action | Count | Block | Allow | CFReqs | WafCount | WafBlockedReqs | WafAllowedReqs | WafDefAction | WafDefAllowReqs | WafDefBlockReqs |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| 2026-07-21 | 100 | Api-Block | BLOCK | 0 | 111111 | 0 | 123456 | 234567 | 345678 | 111111 | BLOCK | 0 | 0 |
| 2026-07-22 | 100 | Api-Block | BLOCK | 0 | 222222 | 0 | 123456 | 234567 | 345678 | 222222 | BLOCK | 0 | 0 |
| 2026-07-23 | 100 | Api-Block | BLOCK | 0 | 333333 | 0 | 123456 | 234567 | 345678 | 333333 | BLOCK | 0 | 0 |
| 2026-07-24 | 100 | Api-Block | BLOCK | 0 | 444444 | 0 | 123456 | 234567 | 345678 | 444444 | BLOCK | 0 | 0 |
| 2026-07-25 | 100 | Api-Block | BLOCK | 0 | 555555 | 0 | 123456 | 234567 | 345678 | 555555 | BLOCK | 0 | 0 |
| 2026-07-26 | 100 | Api-Block | BLOCK | 0 | 666666 | 0 | 123456 | 234567 | 345678 | 666666 | BLOCK | 0 | 0 |
| 2026-07-27 | 100 | Api-Block | BLOCK | 0 | 777777 | 0 | 123456 | 234567 | 345678 | 777777 | BLOCK | 0 | 0 |
| 2026-07-21 | 200 | BasicAuthRule | RULE_GROUP | 0 | 111111 | 0 | 123456 | 234567 | 345678 | 111111 | BLOCK | 0 | 0 |
| 2026-07-22 | 200 | BasicAuthRule | RULE_GROUP | 0 | 222222 | 0 | 123456 | 234567 | 345678 | 222222 | BLOCK | 0 | 0 |
| 2026-07-23 | 200 | BasicAuthRule | RULE_GROUP | 0 | 333333 | 0 | 123456 | 234567 | 345678 | 333333 | BLOCK | 0 | 0 |
| 2026-07-24 | 200 | BasicAuthRule | RULE_GROUP | 0 | 444444 | 0 | 123456 | 234567 | 345678 | 444444 | BLOCK | 0 | 0 |
| 2026-07-25 | 200 | BasicAuthRule | RULE_GROUP | 0 | 555555 | 0 | 123456 | 234567 | 345678 | 555555 | BLOCK | 0 | 0 |
| 2026-07-26 | 200 | BasicAuthRule | RULE_GROUP | 0 | 666666 | 0 | 123456 | 234567 | 345678 | 666666 | BLOCK | 0 | 0 |
| 2026-07-27 | 200 | BasicAuthRule | RULE_GROUP | 0 | 777777 | 0 | 123456 | 234567 | 345678 | 777777 | BLOCK | 0 | 0 |
| 2026-07-21 | 300 | BlockGetHeaderRule | BLOCK | 0 | 111111 | 0 | 123456 | 234567 | 345678 | 111111 | BLOCK | 0 | 0 |
| 2026-07-22 | 300 | BlockGetHeaderRule | BLOCK | 0 | 222222 | 0 | 123456 | 234567 | 345678 | 222222 | BLOCK | 0 | 0 |
| 2026-07-23 | 300 | BlockGetHeaderRule | BLOCK | 0 | 333333 | 0 | 123456 | 234567 | 345678 | 333333 | BLOCK | 0 | 0 |
| 2026-07-24 | 300 | BlockGetHeaderRule | BLOCK | 0 | 444444 | 0 | 123456 | 234567 | 345678 | 444444 | BLOCK | 0 | 0 |
| 2026-07-25 | 300 | BlockGetHeaderRule | BLOCK | 0 | 555555 | 0 | 123456 | 234567 | 345678 | 555555 | BLOCK | 0 | 0 |
| 2026-07-26 | 300 | BlockGetHeaderRule | BLOCK | 0 | 666666 | 0 | 123456 | 234567 | 345678 | 666666 | BLOCK | 0 | 0 |
| 2026-07-27 | 300 | BlockGetHeaderRule | BLOCK | 0 | 777777 | 0 | 123456 | 234567 | 345678 | 777777 | BLOCK | 0 | 0 |
| 2026-07-21 | 400 | BlockPostHeaderRule | BLOCK | 0 | 111111 | 0 | 123456 | 234567 | 345678 | 111111 | BLOCK | 0 | 0 |
| 2026-07-22 | 400 | BlockPostHeaderRule | BLOCK | 0 | 222222 | 0 | 123456 | 234567 | 345678 | 222222 | BLOCK | 0 | 0 |
| 2026-07-23 | 400 | BlockPostHeaderRule | BLOCK | 0 | 333333 | 0 | 123456 | 234567 | 345678 | 333333 | BLOCK | 0 | 0 |
| 2026-07-24 | 400 | BlockPostHeaderRule | BLOCK | 0 | 444444 | 0 | 123456 | 234567 | 345678 | 444444 | BLOCK | 0 | 0 |
| 2026-07-25 | 400 | BlockPostHeaderRule | BLOCK | 0 | 555555 | 0 | 123456 | 234567 | 345678 | 555555 | BLOCK | 0 | 0 |
| 2026-07-26 | 400 | BlockPostHeaderRule | BLOCK | 0 | 666666 | 0 | 123456 | 234567 | 345678 | 666666 | BLOCK | 0 | 0 |
| 2026-07-27 | 400 | BlockPostHeaderRule | BLOCK | 0 | 777777 | 0 | 123456 | 234567 | 345678 | 777777 | BLOCK | 0 | 0 |
| 2026-07-21 | 500 | DevShopForMaintenanceRule | RULE_GROUP | 0 | 111111 | 0 | 123456 | 234567 | 345678 | 111111 | BLOCK | 0 | 0 |
| 2026-07-22 | 500 | DevShopForMaintenanceRule | RULE_GROUP | 0 | 222222 | 0 | 123456 | 234567 | 345678 | 222222 | BLOCK | 0 | 0 |
| 2026-07-23 | 500 | DevShopForMaintenanceRule | RULE_GROUP | 0 | 333333 | 0 | 123456 | 234567 | 345678 | 333333 | BLOCK | 0 | 0 |
| 2026-07-24 | 500 | DevShopForMaintenanceRule | RULE_GROUP | 0 | 444444 | 0 | 123456 | 234567 | 345678 | 444444 | BLOCK | 0 | 0 |
| 2026-07-25 | 500 | DevShopForMaintenanceRule | RULE_GROUP | 0 | 555555 | 0 | 123456 | 234567 | 345678 | 555555 | BLOCK | 0 | 0 |
| 2026-07-26 | 500 | DevShopForMaintenanceRule | RULE_GROUP | 0 | 666666 | 0 | 123456 | 234567 | 345678 | 666666 | BLOCK | 0 | 0 |
| 2026-07-27 | 500 | DevShopForMaintenanceRule | RULE_GROUP | 0 | 777777 | 0 | 123456 | 234567 | 345678 | 777777 | BLOCK | 0 | 0 |
| 2026-07-21 | 600 | AllowUploadsURIRule | COUNT | 0 | 111111 | 0 | 123456 | 234567 | 345678 | 111111 | BLOCK | 0 | 0 |
| 2026-07-22 | 600 | AllowUploadsURIRule | COUNT | 0 | 222222 | 0 | 123456 | 234567 | 345678 | 222222 | BLOCK | 0 | 0 |
| 2026-07-23 | 600 | AllowUploadsURIRule | COUNT | 0 | 333333 | 0 | 123456 | 234567 | 345678 | 333333 | BLOCK | 0 | 0 |
| 2026-07-24 | 600 | AllowUploadsURIRule | COUNT | 0 | 444444 | 0 | 123456 | 234567 | 345678 | 444444 | BLOCK | 0 | 0 |
| 2026-07-25 | 600 | AllowUploadsURIRule | COUNT | 0 | 555555 | 0 | 123456 | 234567 | 345678 | 555555 | BLOCK | 0 | 0 |
| 2026-07-26 | 600 | AllowUploadsURIRule | COUNT | 0 | 666666 | 0 | 123456 | 234567 | 345678 | 666666 | BLOCK | 0 | 0 |
| 2026-07-27 | 600 | AllowUploadsURIRule | COUNT | 0 | 777777 | 0 | 123456 | 234567 | 345678 | 777777 | BLOCK | 0 | 0 |
| 2026-07-21 | 700 | AWS-AWSManagedRulesKnownBadInputsRuleSet | RULE_GROUP | 0 | 111111 | 0 | 123456 | 234567 | 345678 | 111111 | BLOCK | 0 | 0 |
| 2026-07-22 | 700 | AWS-AWSManagedRulesKnownBadInputsRuleSet | RULE_GROUP | 0 | 222222 | 0 | 123456 | 234567 | 345678 | 222222 | BLOCK | 0 | 0 |
| 2026-07-23 | 700 | AWS-AWSManagedRulesKnownBadInputsRuleSet | RULE_GROUP | 0 | 333333 | 0 | 123456 | 234567 | 345678 | 333333 | BLOCK | 0 | 0 |
| 2026-07-24 | 700 | AWS-AWSManagedRulesKnownBadInputsRuleSet | RULE_GROUP | 0 | 444444 | 0 | 123456 | 234567 | 345678 | 444444 | BLOCK | 0 | 0 |
| 2026-07-25 | 700 | AWS-AWSManagedRulesKnownBadInputsRuleSet | RULE_GROUP | 0 | 555555 | 0 | 123456 | 234567 | 345678 | 555555 | BLOCK | 0 | 0 |
| 2026-07-26 | 700 | AWS-AWSManagedRulesKnownBadInputsRuleSet | RULE_GROUP | 0 | 666666 | 0 | 123456 | 234567 | 345678 | 666666 | BLOCK | 0 | 0 |
| 2026-07-27 | 700 | AWS-AWSManagedRulesKnownBadInputsRuleSet | RULE_GROUP | 0 | 777777 | 0 | 123456 | 234567 | 345678 | 777777 | BLOCK | 0 | 0 |
| 2026-07-21 | 800 | AWS-AWSManagedRulesAdminProtectionRuleSet | RULE_GROUP | 0 | 111111 | 0 | 123456 | 234567 | 345678 | 111111 | BLOCK | 0 | 0 |
| 2026-07-22 | 800 | AWS-AWSManagedRulesAdminProtectionRuleSet | RULE_GROUP | 0 | 222222 | 0 | 123456 | 234567 | 345678 | 222222 | BLOCK | 0 | 0 |
| 2026-07-23 | 800 | AWS-AWSManagedRulesAdminProtectionRuleSet | RULE_GROUP | 0 | 333333 | 0 | 123456 | 234567 | 345678 | 333333 | BLOCK | 0 | 0 |
| 2026-07-24 | 800 | AWS-AWSManagedRulesAdminProtectionRuleSet | RULE_GROUP | 0 | 444444 | 0 | 123456 | 234567 | 345678 | 444444 | BLOCK | 0 | 0 |
| 2026-07-25 | 800 | AWS-AWSManagedRulesAdminProtectionRuleSet | RULE_GROUP | 0 | 555555 | 0 | 123456 | 234567 | 345678 | 555555 | BLOCK | 0 | 0 |
| 2026-07-26 | 800 | AWS-AWSManagedRulesAdminProtectionRuleSet | RULE_GROUP | 0 | 666666 | 0 | 123456 | 234567 | 345678 | 666666 | BLOCK | 0 | 0 |
| 2026-07-27 | 800 | AWS-AWSManagedRulesAdminProtectionRuleSet | RULE_GROUP | 0 | 777777 | 0 | 123456 | 234567 | 345678 | 777777 | BLOCK | 0 | 0 |
| 2026-07-21 | 900 | DevShopAllowIpSetRule | ALLOW | 0 | 0 | 111111 | 123456 | 234567 | 345678 | 111111 | BLOCK | 0 | 0 |
| 2026-07-22 | 900 | DevShopAllowIpSetRule | ALLOW | 0 | 0 | 222222 | 123456 | 234567 | 345678 | 222222 | BLOCK | 0 | 0 |
| 2026-07-23 | 900 | DevShopAllowIpSetRule | ALLOW | 0 | 0 | 333333 | 123456 | 234567 | 345678 | 333333 | BLOCK | 0 | 0 |
| 2026-07-24 | 900 | DevShopAllowIpSetRule | ALLOW | 0 | 0 | 444444 | 123456 | 234567 | 345678 | 444444 | BLOCK | 0 | 0 |
| 2026-07-25 | 900 | DevShopAllowIpSetRule | ALLOW | 0 | 0 | 555555 | 123456 | 234567 | 345678 | 555555 | BLOCK | 0 | 0 |
| 2026-07-26 | 900 | DevShopAllowIpSetRule | ALLOW | 0 | 0 | 666666 | 123456 | 234567 | 345678 | 666666 | BLOCK | 0 | 0 |
| 2026-07-27 | 900 | DevShopAllowIpSetRule | ALLOW | 0 | 0 | 777777 | 123456 | 234567 | 345678 | 777777 | BLOCK | 0 | 0 |
5. 最後に
DevOps Agentに調査やレポート作成を任せられる場面も増えてきましたが、
今回のScriptはそのような場合でも人間側での裏付け等に利用できたらと考えております。
このブログがどなたかの役に立てば幸いです。
クラスメソッドオペレーションズ株式会社について
クラスメソッドグループのオペレーション企業です。
運用・保守開発・サポート・情シス・バックオフィスの専門チームが、IT・AIをフル活用した「しくみ」を通じて、お客様の業務代行から課題解決や高付加価値サービスまでを提供するエキスパート集団です。
当社は様々な職種でメンバーを募集しています。
「オペレーション・エクセレンス」と「らしく働く、らしく生きる」を共に実現するカルチャー・しくみ・働き方にご興味がある方は、クラスメソッドオペレーションズ株式会社 コーポレートサイト をぜひご覧ください。※2026年1月 アノテーション㈱から社名変更しました。
