AWS WAF ログの array 型と struct 型のカラムを Athena で検索する

2020年6月27日2021年1月28日

1 概要
2 Athena クエリ
3 参考

概要

やりたいこと

AWS WAF ログを分析するため、各カラムを指定するクエリをまとめる。

AWS WAF ログは Kinesis Firehose 経由で S3 に保存されており、AWS Glue Crawler を利用して Athena 用のテーブルを作成している。

Athena を使った AWS WAF ログを抽出する方法は下記。

S3 にエクスポートされた AWS WAF v2 ログを Athena で検索する

AWS WAF ログと型

AWS WAF ログのテーブル定義クエリを見ると、 string や int 等の定番の型以外に struct と array という型が存在する。

これらのカラムの値へのアクセスを知りたい。

CREATE EXTERNAL TABLE `waf_logs`(
  `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
                                        >
                                     >,
  `httprequest` struct<
                      clientip:string,
                      country:string,
                      headers:array<
                                struct<
                                  name:string,
                                  value:string
                                      >
                                   >,
                      uri:string,
                      args:string,
                      httpversion:string,
                      httpmethod:string,
                      requestid: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://athenawaflogs/WebACL/'

Athena でネストされた配列の検索

この辺りを読む。

array

以下のようなネストされた配列を検索したい（header カラム）。

[{name=Host, value=52.198.21.86}, {name=User-Agent, value=Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:77.0) Gecko/20100101 Firefox/77.0}, {name=Accept, value=text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8}]

UNNSET 演算子を利用し、ネストされた配列の要素を複数行に展開する。

SELECT 
    timestamp,
    header
FROM 
    waflog,
    UNNEST(httprequest.headers) t(header)

1つのログに対して、レコードが 3 つ返却される。

timestamp	header
1593341485433	{name=Host, value=52.198.21.86}
1593341485433	{name=User-Agent, value=Mozilla/5.0 (X11; Ubuntu; Linux x86_64; rv:77.0) Gecko/20100101 Firefox/77.0}
1593341485433	{name=Accept, value=text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,/;q=0.8}

struct

カラム名. オブジェクト名という指定で値を取り出せる。

SELECT
    column.object
FROM
    table

json

json_extract 演算子を利用する。

SELECT
    json_extract(json, '$.limitkey') AS limitkey
FROM
    table

Athena クエリ

ratebasedrulelist

このリクエストで動作したレートベースのルールのリスト。

  `ratebasedrulelist` array<
                        struct<
                          ratebasedruleid:string,
                          limitkey:string,
                          maxrateallowed:int
                              >
                           >,

Athena で SELECT * で引っかかるログサンプル。

[{ratebasedruleid=arn:aws:wafv2:ap-northeast-1:111111111111_MANAGED:regional/ipset/e47a0358-3c11-4794-bbd8-7933f451cdcc_4b8cf1f7-0805-4145-81dc-39425bca4b92_IPV4/4b8cf1f7-0805-4145-81dc-39425bca4b92, limitkey=IP, maxrateallowed=100}]

クエリ

SELECT 
    ratebasedrules,
    ratebasedrules.limitkey,
    ratebasedrules.maxrateallowed,
    ratebasedrules.ratebasedruleid
FROM
    waflog,
    UNNEST(ratebasedrulelist) t(ratebasedrules)

結果。

ratebasedrules	limitkey	maxrateallowed	ratebasedruleid
{ratebasedruleid=arn:aws:wafv2:ap-northeast-1:111111111111_MANAGED:regional/ipset/e47a0358-3c11-4794-bbd8-7933f451cdcc_4b8cf1f7-0805-4145-81dc-39425bca4b92_IPV4/4b8cf1f7-0805-4145-81dc-39425bca4b92, limitkey=IP, maxrateallowed=100}	IP	100	arn:aws:wafv2:ap-northeast-1:1111111111111_MANAGED:regional/ipset/e47a0358-3c11-4794-bbd8-7933f451cdcc_4b8cf1f7-0805-4145-81dc-39425bca4b92_IPV4/4b8cf1f7-0805-4145-81dc-39425bca4b92

nonterminatingmatchingrules

このリクエストにカウント（検知）したルールグループ内の終了しないルールのリスト。

  `nonterminatingmatchingrules` array<
                                  struct<
                                    ruleid:string,
                                    action:string
                                        >
                                     >,

Athena で SELECT * で引っかかるログサンプル。

[{"action":"COUNT","ruleid":"AWSManagedRulesAnonymousIpList"}]

クエリ。

SELECT 
    nontermrules,
    nontermrules.ruleid,
    nontermrules.action
FROM 
    waflog,
    UNNEST(nonterminatingmatchingrules) t(nontermrules)

結果

nontermrules	ruleid	action
{“action”:”COUNT”,”ruleid”:”AWSManagedRulesSQLiRuleSet”}	“AWSManagedRulesSQLiRuleSet”	“COUNT”

terminatingrulematchdetails

リクエストに一致した終了ルールに関する詳細情報。

  `terminatingrulematchdetails` array<
                                  struct<
                                    conditiontype:string,
                                    location:string,
                                    matcheddata:array<string>
                                        >
                                     >,

Athena で SELECT * で引っかかるログサンプル。

[{conditiontype=SQL_INJECTION, location=BODY, matcheddata=[999, and, 1, =, 1]}]

クエリ

SELECT 
    ruleMatchDetails,
    ruleMatchDetails.conditionType,
    ruleMatchDetails.location,
    ruleMatchDetails.matchedData
FROM 
    waflog,
    UNNEST(terminatingRuleMatchDetails) t(ruleMatchDetails)

結果。

ruleMatchDetails	conditionType	location	matchedData
{conditiontype=SQL_INJECTION, location=BODY, matcheddata=[999, and, 1, =, 1]}	SQL_INJECTION	BODY	[999, and, 1, =, 1]

rulegrouplist

リクエストで動作したルールグループのリスト。

公式ドキュメントで定義されていた構造体が下記。
この定義では rulegrouplist 内のカラムを Athena で指定できない。

  `rulegrouplist` array<string>,

クラスメソッドの記事で紹介されていた構造体が下記。
これなら rulegrouplist 内のネストされた値を Athena で指定できる。（Glueで定義する）。

`rulegrouplist` array<
                  struct<
                    ruleGroupId: string,
                    terminatingRule:
                      struct<
                        ruleId: string,
                        action: string
                            >,
                    nonTerminatingMatchingRules: array<
                                                   struct<
                                                     action: string,
                                                     ruleId: string
                                                          >
                                                      >,
                    excludedRules: array<
                                     struct<
                                       exclusionType: string,
                                       ruleId: string
                                           >
                                        >
                        >
                     >,

Athena で SELECT * で引っかかるログサンプル。

[{rulegroupid=AWS#AWSManagedRulesCommonRuleSet, terminatingrule=null, nonterminatingmatchingrules=[], excludedrules=null}, {rulegroupid=AWS#AWSManagedRulesKnownBadInputsRuleSet, terminatingrule=null, nonterminatingmatchingrules=[], excludedrules=null}, {rulegroupid=AWS#AWSManagedRulesAmazonIpReputationList, terminatingrule=null, nonterminatingmatchingrules=[], excludedrules=null}, {rulegroupid=AWS#AWSManagedRulesAnonymousIpList, terminatingrule=null, nonterminatingmatchingrules=[], excludedrules=null}, {rulegroupid=AWS#AWSManagedRulesSQLiRuleSet, terminatingrule={ruleid=SQLi_BODY, action=BLOCK}, nonterminatingmatchingrules=[], excludedrules=null}]

クエリ

SELECT 
    groupList,
    groupList.rulegroupid,
    groupList.terminatingrule,
    groupList.terminatingrule.ruleid,
    groupList.terminatingrule.action,
    groupList.nonterminatingmatchingrules,
    groupList.excludedrules
FROM 
    waflog,
    UNNEST(ruleGroupList) t(groupList)

結果

groupList	rulegroupid	terminatingRule	ruleId	action
{rulegroupid=AWS#AWSManagedRulesSQLiRuleSet, terminatingrule={ruleid=SQLi_BODY, action=BLOCK}, nonterminatingmatchingrules=[], excludedrules=null}	AWS#AWSManagedRulesSQLiRuleSet	{ruleid=SQLi_BODY, action=BLOCK}	SQLi_BODY	BLOCK

httprequest

リクエストに関するメタデータ。headers以外は普通に取れる。

  `httprequest` struct<
                      clientip:string,
                      country:string,
                      headers:array<
                                struct<
                                  name:string,
                                  value:string
                                      >
                                   >,
                      uri:string,
                      args:string,
                      httpversion:string,
                      httpmethod:string,
                      requestid:string
                      >

Athena で SELECT * で引っかかるログサンプル。

{clientip=126.161.124.35, country=JP, headers=[{name=Host, value=test-security-aws.net}, {name=Pragma, value=no-cache}, {name=Cache-Control, value=no-cache}, {name=User-Agent, value=Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.97 Safari/537.36}, {name=Origin, value=https://test-security-aws.net}, {name=Sec-WebSocket-Version, value=13}, {name=Accept-Encoding, value=gzip, deflate, br}, {name=Accept-Language, value=ja,en-US;q=0.9,en;q=0.8}, {name=Cookie, value=language=en; welcomebanner_status=dismiss; continueCode=E3OzQenePWoj4zk293aRX8KbBNYEAo9GL5qO1ZDwp6JyVxgQMmrlv7npKLVy; cookieconsent_status=dismiss; io=qAOYC_-1DVlUIRoFAHZ7}, {name=Sec-WebSocket-Key, value=pUT8hICo89P4LYUOaPbR5g==}, {name=Sec-WebSocket-Extensions, value=permessage-deflate; client_max_window_bits}], uri=/socket.io/, args=EIO=3&transport=websocket&sid=qAOYC_-1DVlUIRoFAHZ7, httpversion=HTTP/1.1, httpmethod=GET, requestid=null}

クエリ

SELECT 
    httprequest,
    httprequest.clientip,
    httprequest.country,
    httprequest.uri,
    httprequest.args,
    httprequest.httpMethod,
    httprequest.httpVersion
FROM 
　　waflog

結果。

clientip	country	uri	args	httpMethod	httpVersion
126.161.124.35	JP	/socket.io/	EIO=3&transport=polling&t=NAf3WRS	GET	HTTP/2.0

httprequest hearder

hearder が難しい。データが存在したりしなかったりするため、name, value の構造体となっている。

ざっくり heaer にどんな値が入っているか確認するクエリ。

SELECT 
    header
FROM 
    waflog,
    UNNEST(httprequest.headers) t(header)

UserAgent を取得したい場合。

SELECT 
    header,
    header.value
FROM 
    waflog,
    UNNEST(httprequest.headers) t(header)
WHERE
    header.name = 'user-agent'

検知モードで動かしているときに検知したリクエストログだけ取り出したい

いずれかのルールに検知されたログを取り出したい。

nonterminatingmatchingrules というカラムを利用する。こいつはリクエストがルールに一致するが終了しない、つまり検知（COUNT）したルールのリスト。

以下の条件に当てはまるもの。

nonterminatingmatchingrules が存在する
nonterminatingmatchingrules の action が COUNT

nontermrules.action に COUNT 以外の値が入るのかは疑問だが、WHERE で絞り込みを付けておく。

SELECT
    terminatingruleid,
    action,
    nontermrules
FROM 
    waflog,
    UNNEST(nonterminatingmatchingrules) t(nontermrules)
WHERE
    nontermrules.action = 'COUNT'