WAF のログ分析で使用したクエリまとめ
ログ全般（どのルールで検知されているか分からない状態）
fields @timestamp
# -------------------------------------------------------------------------
# 1. 絞り込み: アクションが BLOCK
# -------------------------------------------------------------------------
| filter action = "BLOCK"
# -------------------------------------------------------------------------
# 2. サブルールの抽出 (実際に BLOCK したルール名を SubRuleId として抽出)
# -------------------------------------------------------------------------
| parse @message /"terminatingRule":\{"ruleId":"(?<SubRuleId>[^"]+)"/
# -------------------------------------------------------------------------
# 3. 分析用情報の抽出
# -------------------------------------------------------------------------
| parse @message /(?i)"name"\s*:\s*"host"\s*,\s*"value"\s*:\s*"(?<HostHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"content-length"\s*,\s*"value"\s*:\s*"(?<HeaderBodySize>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"cookie"\s*,\s*"value"\s*:\s*"(?<CookieHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"referer"\s*,\s*"value"\s*:\s*"(?<Referer>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"user-agent"\s*,\s*"value"\s*:\s*"(?<UserAgent>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"accept"\s*,\s*"value"\s*:\s*"(?<AcceptHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"x-forwarded-for"\s*,\s*"value"\s*:\s*"(?<XForwardedFor>[^"]+)"/
| fields strlen(httpRequest.args) as QueryStringLength
| fields httpRequest.args as QueryString
| fields httpRequest.host as EnvelopeHost
| fields HostHeader as SpoofedHost
# -------------------------------------------------------------------------
# 4. 表示・ソート
# -------------------------------------------------------------------------
| display @timestamp, terminatingRuleId, SubRuleId, httpRequest.clientIp, httpRequest.httpMethod, httpRequest.country, EnvelopeHost, SpoofedHost, httpRequest.uri, QueryString, QueryStringLength, HeaderBodySize, CookieHeader, Referer, UserAgent, AcceptHeader, XForwardedFor
| sort @timestamp desc
特定のルールのログ
fields @timestamp
# -------------------------------------------------------------------------
# 1. 絞り込み: 特定のルールでの BLOCK
# -------------------------------------------------------------------------
| filter terminatingRuleId = "AWS-AWSManagedRulesCommonRuleSet"
# -------------------------------------------------------------------------
# 2. 分析用情報の抽出
# -------------------------------------------------------------------------
| parse @message /(?i)"name"\s*:\s*"host"\s*,\s*"value"\s*:\s*"(?<HostHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"content-length"\s*,\s*"value"\s*:\s*"(?<HeaderBodySize>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"cookie"\s*,\s*"value"\s*:\s*"(?<CookieHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"referer"\s*,\s*"value"\s*:\s*"(?<Referer>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"user-agent"\s*,\s*"value"\s*:\s*"(?<UserAgent>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"accept"\s*,\s*"value"\s*:\s*"(?<AcceptHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"x-forwarded-for"\s*,\s*"value"\s*:\s*"(?<XForwardedFor>[^"]+)"/
| fields strlen(httpRequest.args) as QueryStringLength
| fields httpRequest.args as QueryString
| fields httpRequest.host as EnvelopeHost
| fields HostHeader as SpoofedHost
# -------------------------------------------------------------------------
# 3. 表示・ソート
# -------------------------------------------------------------------------
| display @timestamp, terminatingRuleId, httpRequest.clientIp, httpRequest.httpMethod, httpRequest.country, EnvelopeHost, SpoofedHost, httpRequest.uri, QueryString, QueryStringLength, HeaderBodySize, CookieHeader, Referer, UserAgent, AcceptHeader, XForwardedFor
| sort @timestamp desc
特定のルールで更に条件付きのログ
fields @timestamp
# -------------------------------------------------------------------------
# 1. 絞り込み: 特定のルールでの BLOCK
# -------------------------------------------------------------------------
| filter terminatingRuleId = "AWS-AWSManagedRulesCommonRuleSet"
| filter httpRequest.uri = "/foo/bar" and httpRequest.httpMethod = "POST"
# -------------------------------------------------------------------------
# 2. 分析用情報の抽出
# -------------------------------------------------------------------------
| parse @message /(?i)"name"\s*:\s*"host"\s*,\s*"value"\s*:\s*"(?<HostHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"content-length"\s*,\s*"value"\s*:\s*"(?<HeaderBodySize>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"cookie"\s*,\s*"value"\s*:\s*"(?<CookieHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"referer"\s*,\s*"value"\s*:\s*"(?<Referer>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"user-agent"\s*,\s*"value"\s*:\s*"(?<UserAgent>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"accept"\s*,\s*"value"\s*:\s*"(?<AcceptHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"x-forwarded-for"\s*,\s*"value"\s*:\s*"(?<XForwardedFor>[^"]+)"/
| fields strlen(httpRequest.args) as QueryStringLength
| fields httpRequest.args as QueryString
| fields httpRequest.host as EnvelopeHost
| fields HostHeader as SpoofedHost
# -------------------------------------------------------------------------
# 3. 表示・ソート
# -------------------------------------------------------------------------
| display @timestamp, terminatingRuleId, httpRequest.clientIp, httpRequest.httpMethod, httpRequest.country, EnvelopeHost, SpoofedHost, httpRequest.uri, QueryString, QueryStringLength, HeaderBodySize, CookieHeader, Referer, UserAgent, AcceptHeader, XForwardedFor
| sort @timestamp desc
複数ルールのログ
fields @timestamp
# -------------------------------------------------------------------------
# 1. いずれかが "COUNT" として検知されたログだけを厳密に絞り込む
# -------------------------------------------------------------------------
| filter @message like /"ruleId":"(HostingProviderIPList|SizeRestrictions_BODY)","action":"COUNT"/
# -------------------------------------------------------------------------
# 2. 各ルールの COUNT 検知有無を「1」か「0」で個別の列として判定する
# （"action":"COUNT" まで含めることで、100%ルールの検知結果だけを拾います）
# -------------------------------------------------------------------------
| fields (@message like /"ruleId":"HostingProviderIPList","action":"COUNT"/) as Hit_HostingProviderIPList
| fields (@message like /"ruleId":"SizeRestrictions_BODY","action":"COUNT"/) as Hit_SizeRestrictions_BODY
# -------------------------------------------------------------------------
# 3. 分析用情報の抽出
# -------------------------------------------------------------------------
| parse @message /(?i)"name"\s*:\s*"host"\s*,\s*"value"\s*:\s*"(?<HostHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"content-length"\s*,\s*"value"\s*:\s*"(?<HeaderBodySize>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"cookie"\s*,\s*"value"\s*:\s*"(?<CookieHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"referer"\s*,\s*"value"\s*:\s*"(?<Referer>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"user-agent"\s*,\s*"value"\s*:\s*"(?<UserAgent>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"accept"\s*,\s*"value"\s*:\s*"(?<AcceptHeader>[^"]+)"/
| parse @message /(?i)"name"\s*:\s*"x-forwarded-for"\s*,\s*"value"\s*:\s*"(?<XForwardedFor>[^"]+)"/
| fields strlen(httpRequest.args) as QueryStringLength
| fields httpRequest.args as QueryString
| fields httpRequest.host as EnvelopeHost
| fields HostHeader as SpoofedHost
# -------------------------------------------------------------------------
# 4. 表示・ソート
# -------------------------------------------------------------------------
| display @timestamp, Hit_HostingProviderIPList, Hit_SizeRestrictions_BODY, httpRequest.clientIp, httpRequest.httpMethod, httpRequest.country, EnvelopeHost, SpoofedHost, httpRequest.uri, QueryString, QueryStringLength, HeaderBodySize, CookieHeader, Referer, UserAgent, AcceptHeader, XForwardedFor
| sort @timestamp desc