Slide 26
Slide 26 text
Athena
WITH dataset AS
(SELECT from_unixtime(timestamp/1000,
'Asia/Tokyo') AS JST, waf_logs.action AS Action, waf_logs.rulegrouplist AS RulegroupList, rulegroups AS rulegroups, waf_logs.httprequest.clientip AS
ClientIP, waf_logs.httprequest.country AS Country, waf_logs.httprequest.httpmethod AS HttpMethod, waf_logs.httprequest.uri AS URI,
waf_logs.terminatingruleid, waf_logs.responsecodesent AS Response, waf_logs.nonterminatingmatchingrules, waf_logs.httprequest
FROM waf_logs, UNNEST(rulegrouplist) t(rulegroups)
WHERE rulegroups.excludedrules is NOT null
AND httpRequest.country = 'JP'
AND from_unixtime(timestamp/1000) > now() - interval '3' day )
SELECT count(*) * 100.0 /
(SELECT COUNT(*)
FROM waf_logs
WHERE from_unixtime(timestamp/1000) > now() - interval '3' day ) AS rate , excludedrules.ruleid AS ruleid
FROM dataset, UNNEST(rulegroups.excludedrules) t(excludedrules)
WHERE excludedrules.exclusiontype = 'EXCLUDED_AS_COUNT'
GROUP BY excludedrules.ruleid
ORDER BY rate DESC ;