AWS WAFのログをathenaで検索するためのテーブル作成忘備録

はじめに

「AWS WAFv2でAWS Managed RulesをひとまずCOUNT状態で設定した。なにやらCOUNTされてきているので正常通信かどうか調べて欲しい」

はい。

Athenaを使ったログとのにらめっこだが、前から使っているテーブルが古く「ログには記録されているがathenaでは引っかからない項目」というのが存在していたためテーブル作り直しを行った。

sqlは入門したかしてないかというレベルで、当然prestoもほとんどわかっていないスタートなので、今後ある程度コピペできるようにするための忘備録として...。

参照リンク

AWS WAFのログをAthenaで見るための前提などなど。

• S3にログを出力するAWS WAFの構築
  • year=2020/month=06/day=06/hour=10/XXXのようなカスタムパーティションを設定したログ出力をするための参照情報
  • https://dev.classmethod.jp/articles/alb-waf-s3-logging/
• Querying AWS WAF Logs
  • https://docs.aws.amazon.com/athena/latest/ug/waf-logs.html
• ログフォーマットについてのドキュメント
  • https://docs.aws.amazon.com/ja_jp/waf/latest/developerguide/logging.html#logging-fields
• Amazon AthenaでAWS WAF XSS/SQLiの詳細ログをクエリしてみた #reinvent
  • https://dev.classmethod.jp/articles/query-aws-waf-around-matched-logs-with-athena/

テーブル作成用query

補足は後述。前提として、S3へのログ出力時点でカスタムパーティションを設定しています。

athena_query

CREATE EXTERNAL TABLE `{作成する任意のテーブル名}`(
  `timestamp` bigint,
  `formatversion` int,
  `webaclid` string,
  `terminatingruleid` string,
  `terminatingruletype` string,
  `action` string,
  `terminatingrulematchdetails` array<
                                  struct<
                                    conditiontype:string,
                                    location:string,
                                    matcheddata:array<string>
                                        >
                                     >,
  `httpsourcename` string,
  `httpsourceid` string,
  `rulegrouplist` array<string>,
  `ratebasedrulelist` array<
                      struct<
                        ratebasedruleid:string,
                        limitkey:string,
                        maxrateallowed:int
                            >
                           >,
  `nonterminatingmatchingrules` array<
                                struct<
                                  ruleid:string,
                                  action:string,
                                  ruleMatchDetails:array<
                                        struct<
                                            conditiontype:string,
                                            location:string,
                                            matcheddata:array<string>
                                            >  
                                        >
                                      >
                                     >,
  `httprequest` struct<
                      clientip:string,
                      country:string,
                      headers:array<
                              struct<
                                name:string,
                                value:string
                                    >
                                   >,
                      uri:string,
                      args:string,
                      httpversion:string,
                      httpmethod:string,
                      requestid:string
                      > 
)
PARTITIONED BY (year STRING, month STRING, day STRING, hour STRING)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
 'paths'='action,formatVersion,httpRequest,httpSourceId,httpSourceName,nonTerminatingMatchingRules,rateBasedRuleList,ruleGroupList,terminatingRuleId,terminatingRuleMatchDetails,terminatingRuleType,timestamp,webaclId')
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://{対象バケット}/'

テーブル作成後、Athena側でパーティションを認識させるためにもうひとつクエリ実行。

MSCK REPAIR TABLE {作成した任意のテーブル名}

query内容の補足

• PARTITIONED BY ... の部分
  • S3へのログ出力時点でカスタムパーティションを設定した上で盛り込む必要がある
• nonterminatingmatchingrules 内の ruleMatchDetails
  • ここが20201009現在ドキュメントに盛り込まれていないっぽい追加部分。
  • SQLインジェクションあるいはクロスサイトスクリプティングの場合に限られる様だが、AWS Managed RulesをCOUNTで適用している時、COUNT判断となった箇所がここに記録される。
    • BLOCK運用の場合は terminatingrulematchdetails という別のカラムがある
  • ログに出力されていることを教えてくれたのはAWSサポート。ありがとう、星5つです。

これ以外はドキュメントのクエリ例をコピペしてます。

こんなかんじでとれまーーーーーーー

ーす

SELECT
ruleGroupList[3] AS 'ruleGroupListさんばんめ',
terminatingrulematchdetails, 
nonTerminatingMatchingRules,
COUNT(*) AS count
FROM hoge, --作成したtable
UNNEST(nonTerminatingMatchingRules) t(nonTermRule)
WHERE year = '2020'
AND month = '09'
AND day = '20'
AND hour = '12'
AND DATE_FORMAT(FROM_UNIXTIME(timestamp/1000) ,'%Y-%m-%d %H:%i') >= '2020-09-20 12:00'
AND DATE_FORMAT(FROM_UNIXTIME(timestamp/1000) ,'%Y-%m-%d %H:%i') <= '2020-09-20 13:00'
AND nonTermRule.ruleid in (
'AWS-AWSManagedRulesCommonRuleSet'
)
GROUP BY
ruleGroupList[3],
terminatingrulematchdetails, 
nonTerminatingMatchingRules
ORDER by count DESC

↓

ruleGroupListさんばんめ	terminatingrulematchdetails	nonTerminatingMatchingRules
{"nonterminatingmatchingrules":[],"rulegroupid":"AWS#AWSManagedRulesCommonRuleSet","terminatingrule":{"rulematchdetails":"null","action":"BLOCK","ruleid":"CrossSiteScripting_COOKIE"},"excludedrules":"null"}	[]	[{ruleid=AWS-AWSManagedRulesCommonRuleSet, action=COUNT, rulematchdetails=[{conditiontype=XSS, location=HEADER, matcheddata=[ONQ, =;]}]}]

まあこの部分をどうすればいいか、とかはわかんないんですけどね。

おまけ1

wafのログ、httprequest.headersの中がエグいことになっており、まるごとcsv出力してからちまちま置き換えたりしていたが一応抽出もできた。

split_part(httprequest.headers[6].value,',',1) AS "6番目に格納されたheaderの値"

尤も、headerの数がuriによってまちまちな場合はあまり役に立たない。畜生。

おまけ2

最近Re:dashというツールを知り、提供されているAMI経由で起動させて利用しているが、AWS内でathena叩くよりこっち経由した方がめちゃくちゃ捗るので感動している。
https://redash.io/

おわりに

wafのログ、blockかallowを確認する時とcountを確認する時とで確認先のカラムが全然違うので毎度混乱する。