各種ログAthenaSELECTクエリメモ

■TGWのVPCフローログ

dateとtimeが別カラムのため、CONCATする

SELECT
  CAST(CONCAT(CAST(date AS VARCHAR),' ', time, ' +00:00') AS TIMESTAMP WITH TIME ZONE) AT TIME ZONE '+09:00' AS dt_jst,
  request_ip,
  request_protocol,
  host_header,
  uri,
  method,
  status
FROM <table>
WHERE 
  CAST(CONCAT(CAST(date AS VARCHAR),' ', time, ' +00:00') AS TIMESTAMP WITH TIME ZONE) >= CAST('<YYYY-MM-DD HH:mm:SS> +09:00' AS TIMESTAMP WITH TIME ZONE)
  AND CAST(CONCAT(CAST(date AS VARCHAR),' ', time, ' +00:00') AS TIMESTAMP WITH TIME ZONE) < CAST('<YYYY-MM-DD HH:mm:SS> +09:00' AS TIMESTAMP WITH TIME ZONE)
ORDER BY dt_jst;

■WAFログ

timestampは/1000する

SELECT 
  FROM_UNIXTIME(timestamp/1000, 9, 0) AS timestamp_jst,
  terminatingruleid,
  action,
  httprequest.clientip,
  httprequest.country,
  CAST(httprequest.headers AS JSON) AS headers,
  httprequest.uri,
  httprequest.httpmethod,
  labels
FROM table
ORDER BY timestamp;

■CloudFrontログ

-は'で囲む

SELECT
  start,
  srcaddr,
  dstaddr,
  srcport,
  dstport,
  'flow-direction',
  protocol,
  'packets-lost-no-route' AS noroute,
  'packets-lost-blackhole' AS blackhole,
  'packets-lost-mtu-exceeded' AS mtuexceeded,
  'packets-lost-ttl-expired' AS ttlexpired
FROM <table>;