Amazon Athenaのクエリメモ

WAFログ

テーブルを作成する

以下URL記載のものと同様。
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/waf-logs.html#create-waf-table

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<
                     struct<
                        rulegroupid:string,
                        terminatingrule:struct<
                           ruleid:string,
                           action:string,
                           rulematchdetails:string
                                               >,
                        nonterminatingmatchingrules:array<
                                                       struct<
                                                          ruleid:string,
                                                          action:string,
                                                          rulematchdetails:array<
                                                               struct<
                                                                  conditiontype:string,
                                                                  location:string,
                                                                  matcheddata:array<string>
                                                                     >
                                                                  >
                                                               >
                                                            >,
                        excludedrules:array<
                                         struct<
                                            ruleid:string,
                                            exclusiontype:string
                                               >
                                            >
                           >
                       >,
  `ratebasedrulelist` array<
                        struct<
                          ratebasedruleid:string,
                          limitkey:string,
                          maxrateallowed:int
                              >
                           >,
  `nonterminatingmatchingrules` array<
                                  struct<
                                    ruleid:string,
                                    action:string
                                        >
                                     >,
  `requestheadersinserted` string,
  `responsecodesent` string,
  `httprequest` struct<
                      clientip:string,
                      country:string,
                      headers:array<
                                struct<
                                  name:string,
                                  value:string
                                      >
                                   >,
                      uri:string,
                      args:string,
                      httpversion:string,
                      httpmethod:string,
                      requestid:string
                      >,
  `labels` array<
             struct<
               name:string
                   >
                  >
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
 'paths'='action,formatVersion,httpRequest,httpSourceId,httpSourceName,labels,nonTerminatingMatchingRules,rateBasedRuleList,requestHeadersInserted,responseCodeSent,ruleGroupList,terminatingRuleId,terminatingRuleMatchDetails,terminatingRuleType,timestamp,webaclId')
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://<WAFログの出力先バケット>/'

マネージドルールで、[Rule action]を有効にした場合のCount数を確認する

マネージドルールの設定内容としては以下の通り。

実行するクエリは以下の通り。

WITH test_dataset AS 
  (SELECT * FROM waf_logs
    CROSS JOIN UNNEST(rulegrouplist) AS t(allrulegroups))
SELECT COUNT(*) AS
  count, 
  "httprequest"."clientip", 
  "allrulegroups"."excludedrules",
  "allrulegroups"."ruleGroupId"
FROM test_dataset 
WHERE allrulegroups.excludedrules IS NOT NULL
GROUP BY "httprequest"."clientip", "allrulegroups"."ruleGroupId", "allrulegroups"."excludedrules"
ORDER BY count DESC

ちなみに、BlockとCountのみログ保存するログフィルタを設定している状態で、マネージドルールグループ内の個別ルールをCountモードにした場合、ログ出力されない。
→Allowもログ保存する必要がある。

マネージドルールで、[Override rule group action to count]を有効にした場合のCount数を確認する

マネージドルールの設定内容としては以下の通り。

実行するクエリは以下の通り。

WITH test_dataset AS 
  (SELECT * FROM waf_logs
    CROSS JOIN UNNEST(nonterminatingmatchingrules) AS t(allrulegroups))
SELECT count(*) AS count, 
  action,
  "httprequest"."clientip", 
  "allrulegroups"."ruleid"
FROM test_dataset 
GROUP BY action, "httprequest"."clientip", "allrulegroups"."ruleid"
ORDER BY count DESC

ちなみに、マネージドルールグループ全体を[Override rule group action to count]でCountモードにした場合は、BlockとCountのみログ保存するログフィルタを設定している状態でも、ログ出力される。

CloudFrontログ

テーブルを作成する

CREATE EXTERNAL TABLE IF NOT EXISTS default.cf_logs (
  `date` DATE,
  time STRING,
  location STRING,
  bytes BIGINT,
  request_ip STRING,
  method STRING,
  host STRING,
  uri STRING,
  status INT,
  referrer STRING,
  user_agent STRING,
  query_string STRING,
  cookie STRING,
  result_type STRING,
  request_id STRING,
  host_header STRING,
  request_protocol STRING,
  request_bytes BIGINT,
  time_taken FLOAT,
  xforwarded_for STRING,
  ssl_protocol STRING,
  ssl_cipher STRING,
  response_result_type STRING,
  http_version STRING,
  fle_status STRING,
  fle_encrypted_fields INT,
  c_port INT,
  time_to_first_byte FLOAT,
  x_edge_detailed_result_type STRING,
  sc_content_type STRING,
  sc_content_len BIGINT,
  sc_range_start BIGINT,
  sc_range_end BIGINT
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t'
LOCATION 's3://<CloudFrontログの出力先バケット>/'
TBLPROPERTIES ( 'skip.header.line.count'='2' )

特定のIPアドレスからのアクセス件数を確認する。

SELECT date, status, count(*) AS request_count
FROM default.cf_logs
WHERE request_ip = 'xxx.xxx.xxx.xxx'
GROUP BY 1,2
ORDER BY 1;

特定のIPアドレスからのアクセスログを確認する。

SELECT *
FROM default.cf_logs
WHERE request_ip = 'xxx.xxx.xxx.xxx';