Athena で VPC Flow Logs 結果 を JST で表示する

概要

VPC Flow Logs は S3 に出力して Athena で解析することができます。 詳細は AWS 公式ドキュメントを参照ください。

docs.aws.amazon.com

VPC Flow Logs の日時情報 ( starttime, endtime ) はデフォルトでは Unixtime になっています。*1 今回は Unixtime を JST 表示にするクエリの紹介です。

前提

• AWS 公式ドキュメントの通り、パーティションとして dt を YYYY-MM-DD 形式としています

クエリ

特定の通信開始時刻 ( JST ) を抽出する場合のクエリになります。

SELECT 
 version,
 account,
 interfaceid,
 sourceaddress,
 destinationaddress,
 sourceport,
 destinationport,
 protocol,
 numpackets,
 numbytes,
 DATE_FORMAT(FROM_UNIXTIME(starttime, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s') as starttimeJST,
 DATE_FORMAT(FROM_UNIXTIME(endtime, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s') as endtimeJST,
 action,
 logstatus,
 dt
FROM "DB名"."Table名"
WHERE dt='YYYY-MM-DD'
AND  DATE_FORMAT(FROM_UNIXTIME(starttime, 'Asia/Tokyo') ,'%Y-%m-%d %H:%i:%s') = 'YYYY-MM-DD HH:MM:SS'
ORDER BY starttimeJST
limit 100;

解説

Athena では、オープンソースの分散型クエリエンジンである Presto を使用してクエリを実行します。 そのため、DATE_FORMATとFROM_UNIXTIMEをそれぞれ利用しています。

おわり

JST でのデータ抽出にご活用ください(๑•ω•́ฅ✧

*1:https://docs.aws.amazon.com/ja_jp/vpc/latest/userguide/flow-logs.html