CloudFrontのログ解析をAthenaで実施してみた #S3

はじめに

運用時にCloudFrontなどのアクセスログの調査を行うことはよくあると思います。その場合は、ある特定の時間のログを確認することが多いと思います。

そんなとき、手っ取り早くアクセスログを確認したいときに便利かと思います。
今回は既にCloudFrontでログ取得設定をしている前提です

Athna初期設定(query実行結果格納場所の作成)

※初期設定が完了しているなら無視してOK

何も設定していない状態でクエリ実行しようとするとエラーになってしまいます。

Your query has the following error(s):

No output location provided. An output location is required either through the Workgroup result configuration setting or as an API input. (Service: AmazonAthena; Status Code: 400; Error Code: InvalidRequestException; Request ID: XXXXXXXXXXXXXXXXXXX)

なので、上図の"set up a query result location in Amazon S3."から設定が可能です。

CloudFront ログのテーブルの作成

(1)次の DDL ステートメントをコピーして Athena コンソール内に貼り付けます。ログを保存する Amazon S3 バケットの LOCATION を変更します。

CREATE EXTERNAL TABLE IF NOT EXISTS default.cloudfront_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_bucket_name/CloudFront/' ←ここを環境により変更する
TBLPROPERTIES ( 'skip.header.line.count'='2' )

ログのクエリ例

(1)日付指定でログを見たい場合

select requestip,date,time,host,uri
from ys_cf_logs ←自分の環境のテーブル名を記載
where "date" BETWEEN DATE '2020-04-25' AND DATE '2020-04-26'
order by time desc
LIMIT 100;

(2)特定のIPを表示させる
${SouceIP}の部分は置き換えてください

select requestip,date,time,host,uri
from ys_cf_logs
where "date" BETWEEN DATE '2020-04-25' AND DATE '2020-04-26'
AND "requestip" IN ('${SouceIP}','${SouceIP}')
order by time desc
LIMIT 100;

(3)特定のIP以外を表示させる
${SouceIP}の部分は置き換えてください

select requestip,date,time,host,uri
from ys_cf_logs
where "date" BETWEEN DATE '2020-04-25' AND DATE '2020-04-26'
AND "requestip" IN NOT ('${SouceIP}','${SouceIP}')
order by time desc
LIMIT 100;

何パターンかすぐ使えるように記載しましたが、SQLの文法なので、
これ以外もいろいろ使えるので使ってみてください。