Slide 15
Slide 15 text
©2024 Metaps Holdings, Inc.
athenaクエリ
クエリの内容
SELECT
header.value || httprequest.uri AS url,
httprequest.clientip || ';' || httprequest.country AS ip_country,
terminatingruleid || ';' || rulegroup.terminatingrule.ruleid AS rule,
count(httprequest.uri) as count
FROM
"waf_logs",
UNNEST(httprequest.headers) t(header),
UNNEST(rulegrouplist) t(rulegroup)
WHERE
DAY = 'yyyy/mm/dd'
AND timestamp > 'xxxxxxxxxx'
AND action = 'BLOCK'
AND header.name = 'Host'
AND rulegroup.terminatingrule.ruleid != ''
GROUP BY
concat(header.value, httprequest.uri),
concat(concat(httprequest.clientip, ';'), httprequest.country),
concat(concat(terminatingruleid, ';'), rulegroup.terminatingrule.ruleid)
ORDER BY
count desc,
ip_country
LIMIT 10