Amazon VPC フローログのテーブルを作成し、クエリする - Amazon Athena
vpc_flow_logs テーブルのクエリ例

Amazon VPC フローログのテーブルを作成し、クエリする

次の手順では、Amazon VPC フローログ用の Amazon VPC テーブルを作成します。カスタム形式でフローログを作成する場合は、フローログの作成時に指定したフィールドと一致するフィールドを、それらに指定したものと同じ順序で使用してテーブルを作成します。

Amazon VPC フローログ用の Athena テーブルを作成するには

  1. 考慮事項と制約事項 セクションのガイドラインに従って、次のような DDL ステートメントを Athena コンソールクエリエディタに入力します。サンプルステートメントは、「フローログレコード」に記載されているように、Amazon VPC フローログのバージョン 2 から 5 の列を持つテーブルを作成します。異なる列のセットまたは列の順序を使用する場合は、必要に応じてステートメントを変更します。

    CREATE EXTERNAL TABLE IF NOT EXISTS `vpc_flow_logs` (
  version int,
  account_id string,
  interface_id string,
  srcaddr string,
  dstaddr string,
  srcport int,
  dstport int,
  protocol bigint,
  packets bigint,
  bytes bigint,
  start bigint,
  `end` bigint,
  action string,
  log_status string,
  vpc_id string,
  subnet_id string,
  instance_id string,
  tcp_flags int,
  type string,
  pkt_srcaddr string,
  pkt_dstaddr string,
  region string,
  az_id string,
  sublocation_type string,
  sublocation_id string,
  pkt_src_aws_service string,
  pkt_dst_aws_service string,
  flow_direction string,
  traffic_path int
)
PARTITIONED BY (`date` date)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ' '
LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/'
TBLPROPERTIES ("skip.header.line.count"="1");

    以下の点に注意してください。

    • このクエリは ROW FORMAT DELIMITED を指定し、SerDe の指定が省略されます。これは、クエリが CSV、TSV、カスタム区切りファイル用の Lazy Simple SerDe を使用しているということです。このクエリでは、フィールドはスペースで終了します。

    • PARTITIONED BY 句は、date 型を使用します。これにより、クエリで数学演算子を使用して、特定の日付より古いものまたは新しいものを選択できます。

      注記

      date は DDL ステートメントの予約済みキーワードであるため、バックティック文字でエスケープされます。詳細については、「クエリで予約キーワードをエスケープする」を参照してください。

    • 異なるカスタム形式の VPC フローログの場合、フローログの作成時に指定したフィールドと一致するようにフィールドを変更します。

  2. ログデータが含まれる Amazon S3 バケットをポイントするように LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/' を変更します。

  3. Athena コンソールでクエリを実行します。クエリが完了すると、Athena が vpc_flow_logs テーブルを登録し、その中のデータに対してクエリを発行できるように準備します。

  4. 次のサンプルクエリのように、パーティションを作成してデータを読み取れるようにします。このクエリは、指定日の 1 つのパーティションを作成します。必要に応じて、日付と場所のプレースホルダーを置き換えます。

    注記

    このクエリは、指定日に対して単一のパーティションのみを作成します。プロセスを自動化するには、このクエリを実行し、この方法で year/month/day にパーティションを作成するスクリプトを使用するか、パーティション射影を指定する CREATE TABLE ステートメントを使用します。

    ALTER TABLE vpc_flow_logs
ADD PARTITION (`date`='YYYY-MM-dd')
LOCATION 's3://amzn-s3-demo-bucket/prefix/AWSLogs/{account_id}/vpcflowlogs/{region_code}/YYYY/MM/dd';

vpc_flow_logs テーブルのクエリ例

Athena コンソールのクエリエディタを使用して、作成したテーブルで SQL ステートメントを実行します。クエリを保存したり、以前のクエリを表示したり、クエリ結果を CSV 形式でダウンロードできます。次の例では、vpc_flow_logs をテーブルの名前に置き換えます。また、要件に応じて列の値やその他の変数を変更します。

次のクエリ例では、指定された日付に対して最大 100 のフローログを一覧表示します。

SELECT * 
FROM vpc_flow_logs 
WHERE date = DATE('2020-05-04') 
LIMIT 100;

次のクエリは、すべての拒否された TCP 接続を一覧表示します。新しく作成した日付パーティション列 date を使用して、該当イベントが発生した週の曜日を抽出します。

SELECT day_of_week(date) AS
  day,
  date,
  interface_id,
  srcaddr,
  action,
  protocol
FROM vpc_flow_logs
WHERE action = 'REJECT' AND protocol = 6
LIMIT 100;

最大数の HTTPS リクエストを受信しているサーバーを確認するには、次のクエリを使用します。HTTPS ポート 443 で受信したパケット数をカウントし、送信先 IP アドレス別にグループ分けして、過去 1 週間の上位 10 のサーバーを返します。

SELECT SUM(packets) AS
  packetcount,
  dstaddr
FROM vpc_flow_logs
WHERE dstport = 443 AND date > current_date - interval '7' day
GROUP BY dstaddr
ORDER BY packetcount DESC
LIMIT 10;