AWS WAF ログを分析する Athena クエリ例（期間指定・集計系）

概要
1. やりたいこと
Athenaクエリ
参考

概要

やりたいこと

Athena を分析するクエリをまとめる。期間指定・集計系のクエリがメイン。

検索系のクエリは以下でまとめてある。

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

Athenaクエリ

Timestamp を JST に変換

FROM_UNIXTIME 関数を利用して BIGINT（ただの数値型）からタイムゾーン付きの TIMESTAMP 型に変換する。

SELECT 
    timestamp,
    from_unixtime(timestamp/1000, 'Asia/Tokyo') AS JST
FROM 
    waflog

timestamp	JST
1591973564380	2020-06-12 23:52:44.000 Asia/Tokyo

Asia/Tokyo がついてて邪魔なため、 DATE_FORMAT 関数で変換する。

SELECT
    timestamp,
    FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') AS JST,
    DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') AS JST_DATE
FROM 
    waflog

結果。

timestamp	JST	JST_DATE
1593380040740	2020-06-29 06:34:00.000 Asia/Tokyo	2020-06-29 06:34:00

JST で期間を指定する

パーティションである程度指定できるが、timestamp を日本標準時間で絞れたほうがいい。

日にち

Presto の DATE 関数で日にちまでを取り出して比較している。

SELECT
    timestamp,
    from_unixtime(timestamp/1000, 'Asia/Tokyo') AS JST, 
FROM 
    waflog
WHERE 
    date(from_unixtime(timestamp/1000, 'Asia/Tokyo')) = date '2020-06-12'
ORDER BY
    timestamp;

DATE_FORMAT をイコール演算子はできないが、大なり小なりの比較はでも可能。

SELECT
    timestamp,
    FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') AS JST,
    DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') AS JST_DATE
FROM
    waflog
WHERE
    DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') > '2020-07-04'

時間

大なり小なりで絞る場合。

SELECT
    timestamp,
    FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') AS JST,
    DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') AS JST_DATE
FROM
    waflog
WHERE
    DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') > '2020-07-04 09:00:00'

BETWEEN で絞る場合。

SELECT
    timestamp,
    FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') AS JST,
    DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') AS JST_DATE
FROM
    waflog
WHERE
    DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') 
      BETWEEN '2020-07-04 09:00:00'
      AND '2020-07-04 09:05:00'

パーティションとの併用も可能。
ただし、パーティションは UTC で区切られているため、マイナス9時間として考えなければならない。。

SELECT
    timestamp,
    FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') AS JST,
    DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') AS JST_DATE
FROM
    waflog
WHERE
    year='2020'
    AND month='07'
    AND day='04'
    AND hour='00'
    AND DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo'), '%Y-%m-%d %H:%i:%s') 
      BETWEEN '2020-07-04 09:00:00'
      AND '2020-07-04 09:05:00'

日時で集計する

substr()関数で、時刻(time)を時・分までで切り取ってGROUP BY節に指定する。

日ごとに集計

区切る長さは 10 桁。

SELECT 
    substr(DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s'),1,10) AS every_day, 
    count(timestamp)
FROM 
    kinesis_parquet
GROUP BY
    substr(DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s'),1,10)
ORDER BY 
    every_day;

日毎のカウントを確認できる。

時間ごとの集計

区切る長さは 13 桁。

SELECT 
    substr(DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s'),1,13) AS every_hour, 
    count(timestamp)
FROM 
    waflog
GROUP BY
    substr(DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s'),1,13)
ORDER BY 
    every_hour;

時間ごとで集計した結果が確認できる。

分ごとの集計

区切る長さは 16 桁。

SELECT 
    SUBSTR(DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s'),1,16) AS every_minutes, 
    COUNT(timestamp) AS count
FROM 
    waflog
GROUP BY
    SUBSTR(DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s'),1,16)
ORDER BY 
    every_minutes;

より細かい結果が確認できる。

時間ごとのアクション結果を知りたい

時間ごとで起きたアクションを知りたい。

SELECT
    SUBSTR(DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s'),1,16) AS every_minutes, 
    terminatingruleid,
    COUNT(terminatingruleid) AS count
FROM 
    waflog
GROUP BY
    SUBSTR(DATE_FORMAT(FROM_UNIXTIME(timestamp/1000, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s'),1,16),
    	terminatingruleid
ORDER BY 
    every_minutes;

結果。