Log4j 脆弱性を緩和するAWS WAFの「Log4JRCE」DevelopersIOサイトの3日間の検出結果を紹介します

$ host 75.2.71.201 
201.71.2.75.in-addr.arpa domain name pointer a5b041b48e73d3807.awsglobalaccelerator.com.

$ host 52.194.15.214   
214.15.194.52.in-addr.arpa domain name pointer ec2-52-194-15-214.ap-northeast-1.compute.amazonaws.com.

$ host dev.classmethod.jp                                                                                                     
dev.classmethod.jp has address 75.2.71.201
dev.classmethod.jp has address 99.83.185.173
dev.classmethod.jp has IPv6 address 2406:da14:d37:ef12:9cfc:d7bb:1178:be60
dev.classmethod.jp has IPv6 address 2406:da14:d37:ef13:c7a8:b1f3:1bd9:9374
dev.classmethod.jp has IPv6 address 2406:da14:d37:ef11:45b4:dae7:d07:18f9

CREATE EXTERNAL TABLE `waf_logs`(
    `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',
    `terminatingrulematchdetails` array < struct < conditiontype :string,
    location :string,
    matcheddata :array < string > > > COMMENT 'from deserializer',
    `httpsourcename` string COMMENT 'from deserializer',
    `httpsourceid` string COMMENT 'from deserializer',
    `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 > > > > 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',
    `requestheadersinserted` string COMMENT 'from deserializer',
    `responsecodesent` 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',
    `labels` array < struct < name :string > > COMMENT 'from deserializer'
) PARTITIONED BY
    (
        `day` string
    ) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' 
    STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' 
    OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' 
    LOCATION 's3://<S3BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/WAFLogs/<REGION>/###-webacl' TBLPROPERTIES(
        'projection.day.format' = 'yyyy/MM/dd',
        'projection.day.interval' = '1',
        'projection.day.interval.unit' = 'DAYS',
        'projection.day.range' = '2021/01/01,NOW',
        'projection.day.type' = 'date',
        'projection.enabled' = 'true',
        'storage.location.template' = 's3://<S3BUCKET_NAME>/AWSLogs/<ACCOUNT_ID>/WAFLogs/<REGION>/###-webacl/${day}'
    )

SELECT
  COUNT(1) AS "検出数",
  date_format(from_unixtime(timestamp / 1000), '%Y/%m/%dT%H') AS "UTC日時"
FROM
  waf_logs
WHERE
  day BETWEEN '2021/12/12' AND '2021/12/14'
AND labels[1].name LIKE '%Log4JRCE%'
GROUP BY
  date_format(from_unixtime(timestamp / 1000), '%Y/%m/%dT%H')
ORDER BY
  date_format(from_unixtime(timestamp / 1000), '%Y/%m/%dT%H')

SELECT
  COUNT(1) AS "検出数",
  concat(regexp_extract("httprequest"."clientip", '([0-9]+\.[0-9]+\.[0-9]+\.)', 1), 'x') AS "接続元IP"
FROM
  "default"."waf_logs"
WHERE
  day BETWEEN '2021/12/12' AND '2021/12/14'
AND labels[1].name LIKE '%Log4JRCE%'
GROUP BY
  concat(regexp_extract("httprequest"."clientip", '([0-9]+\.[0-9]+\.[0-9]+\.)', 1), 'x')
ORDER BY
  COUNT(1)  DESC

SELECT
  COUNT(1) AS "検出数",
  "httprequest"."country" as "国"
FROM
  "default"."waf_logs"
WHERE
  day BETWEEN '2021/12/12' AND '2021/12/14'
AND labels[1].name LIKE '%Log4JRCE%'
GROUP BY
  "httprequest"."country"
ORDER BY
  COUNT(1) DESC

SELECT
  COUNT(1) AS "検出数",
  header.VALUE
FROM
  waf_logs
  CROSS JOIN
    UNNEST(httprequest.headers) AS t(header)
WHERE
  day BETWEEN '2021/12/12' AND '2021/12/14'
AND labels[1].name LIKE '%Log4JRCE%'
AND LOWER(header.name) = 'host'
GROUP BY
  header.VALUE
ORDER BY
  COUNT(1) DESC

SELECT
  COUNT(1) AS "検出数",
  header.name
FROM
  waf_logs
  CROSS JOIN
    UNNEST(httprequest.headers) AS t(header)
WHERE
  day BETWEEN '2021/12/12' AND '2021/12/14'
AND labels[1].name LIKE '%Log4JRCE%'
and header.VALUE like '%$%'
and header.VALUE like '%{%'
and header.VALUE like '%:%'
GROUP BY
  header.name
ORDER BY
  COUNT(1) DESC

SELECT
  COUNT(1) AS "検出数",
  SUBSTRING(header.VALUE, 1, 80)
FROM
  waf_logs
  CROSS JOIN
    UNNEST(httprequest.headers) AS t(header)
WHERE
  day BETWEEN '2021/12/12' AND '2021/12/14'
AND labels[1].name LIKE '%Log4JRCE%'
AND header.VALUE LIKE '%$%'
AND header.VALUE LIKE '%{%'
AND header.VALUE LIKE '%:%'
GROUP BY
  SUBSTRING(header.VALUE, 1, 80)
ORDER BY
  COUNT(1) DESC