AWS Athenaで、WAFのcountログが絞り込めなくて苦戦した件

プログラミング
2020.11.03
スポンサーリンク
  1. はじめに
  2. 結論
  3. 詳細
  4. おわりに

はじめに

  • WAFでBLOCK設定するために、BLOCKしてほしくないアクセスを絞り込むためCOUNT設定
  • WAFのログを、S3にJsonで保存
  • COUNTになったアクセスを調査するために、ログに対して、Athenaでaction=’COUNT’ なクエリを書いたら、結果が0件
  • COUNTが無いってことはBLOCKをONにできるーと思ってたら、actionにCOUNTは出てこない模様
  • しかし、nonterminatingmatchingrulesカラムから取得できるとのことで、設定
  • 取得はできるようになったが、Mysqlのノリだけでは理解が甘く、WHERE文が通らなかったことの解決

結論

SELECT
  timestamp,
  FROM_UNIXTIME(timestamp / 1000) as unixtime_to_timestamp,
  nonterminatingmatchingrules,
FROM
  "default"."waflog2020"
CROSS JOIN
  UNNEST(nonterminatingmatchingrules) AS t (nonterminatingmatchingrule)
WHERE
  nonterminatingmatchingrule.action='COUNT'
ORDER BY timestamp DESC 
limit 1

詳細

取得対象のS3バケットを指定し、下記のようなテーブルを作る

CREATE EXTERNAL TABLE `waflog2020`(
  `timestamp` bigint COMMENT 'from deserializer', 
  `formatversion` int COMMENT 'from deserializer', 
  `webaclid` string COMMENT 'from deserializer', 
  `terminatingruleid` string COMMENT 'from deserializer', 
  `terminatingruletype` string COMMENT 'from deserializer', 
  `action` string COMMENT 'from deserializer', 
  `httpsourcename` string COMMENT 'from deserializer', 
  `httpsourceid` string COMMENT 'from deserializer', 
  `rulegrouplist` array<string> COMMENT 'from deserializer', 
  `ratebasedrulelist` array<struct<ratebasedruleid:string,limitkey:string,maxrateallowed:int>> COMMENT 'from deserializer', 
  `nonterminatingmatchingrules` array<struct<ruleid:string,action:string>> COMMENT 'from deserializer', 
  `httprequest` struct<clientip:string,country:string,headers:array<struct<name:string,value:string>>,uri:string,args:string,httpversion:string,httpmethod:string,requestid:string> COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
WITH SERDEPROPERTIES ( 
  'paths'='action,formatVersion,httpRequest,httpSourceId,httpSourceName,nonTerminatingMatchingRules,rateBasedRuleList,ruleGroupList,terminatingRuleId,terminatingRuleType,timestamp,webaclId') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://hogehoge/fuga'
TBLPROPERTIES (
  'transient_lastDdlTime'='1579835432')

bigint, int, stringなどのカラムの値は、Mysqlのノリでいけましたが、array型, struct型, それが入れ子になっていて詰みました
AthenaはFacebook社製のSQLエンジンPrestoを使っていて、Presto用の構文がよくわかりませんでした、、
struct型は、Key => Value形式で値を持てるみたいで、PrestoじゃなくてAthenaの機能っぽい・・・？

CROSS JOIN
  UNNEST(nonterminatingmatchingrules) AS t (unnest)
WHERE
  unnest.action='COUNT'

UNNEST関数でnonterminatingmatchingrulesカラムをarrayじゃない状態にする
unnestカラムとstructのKeyをドットで繋いで、Valueを指定すればいけました。
※ AS t (unnest)の引数で、カラムにも別名を付けてるらしい

おわりに

Prestoって結構前からあったみたいですね。アンテナ張っていかないと…

コメント