VPCフローログをDuckDBでサクッと分析

VPCフローログをDuckDBでサクッと分析

#VPC Flow Logs
#DuckDB
#Amazon S3
#AWS
川原征大

川原征大

facebook logohatena logotwitter logo
Clock Icon2025.02.21

DuckDBはオンライン分析処理(OLAP)に特化したデータベースシステムです。

https://duckdb.org/

シンプル、豊富な機能、高速、無料で利用可能、といった特徴を持っています。 S3との連携も可能であり、 S3上のオブジェクトに対してクエリを実行することも可能です。

今回はDuckDBを使って VPCフローログ(Flow Logs)を分析してみます。 S3上に格納しているフローログをローカルに保存して、 DuckDBでいくつか分析するところまで実施します。

セットアップ

VPCフローログ

VPCフローログは事前にセットアップしている前提で進めます。 以下のようなフローログを設定していました。 LogFormat は デフォルトの設定です。

# aws ec2 describe-flow-logs --output yaml
FlowLogs:
- CreationTime: '2024-XX-XXT05:33:25.522000+00:00'
  DeliverLogsStatus: SUCCESS
  DestinationOptions:
    FileFormat: plain-text
    HiveCompatiblePartitions: true
    PerHourPartition: true
  FlowLogId: fl-0876example
  FlowLogStatus: ACTIVE
  LogDestination: arn:aws:s3:::EXAMPLE-DOC-BUCKET/plain-text/
  LogDestinationType: s3
  LogFormat: ${version} ${account-id} ${interface-id} ${srcaddr} ${dstaddr} ${srcport}
    ${dstport} ${protocol} ${packets} ${bytes} ${start} ${end} ${action} ${log-status}
  MaxAggregationInterval: 600
  ResourceId: vpc-01a7example
  TrafficType: ALL

こんな感じでフローログがS3に格納されています。

sc_2025-02-21_08-51-46_13781

logファイルのサンプル
gzip -d 111111111111_ap-northeast-1_...略..._600935e4.log.gz
head 111111111111_ap-northeast-1_...略..._600935e4.log
# version account-id interface-id srcaddr dstaddr srcport dstport protocol packets bytes start end action log-status
# 2 111111111111 eni-0544example 10.XX.XX.XX 35.XX.XX.XX 38773 443 6 15 6797 1739361273 1739361304 ACCEPT OK
# 2 111111111111 eni-0544example 10.XX.XX.XX 10.XX.XX.XX 443 36508 6 23 22076 1739361273 1739361304 ACCEPT OK
# 2 111111111111 eni-0544example 13.XX.XX.XX 10.XX.XX.XX 443 17813 6 20 17459 1739361273 1739361304 ACCEPT OK
# 2 111111111111 eni-0544example 10.XX.XX.XX 52.XX.XX.XX 39636 443 6 26 5880 1739361273 1739361304 ACCEPT OK
# 2 111111111111 eni-0544example 10.XX.XX.XX 10.XX.XX.XX 443 58318 6 48 36154 1739361273 1739361304 ACCEPT OK
# 2 111111111111 eni-0544example 10.XX.XX.XX 10.XX.XX.XX 37860 443 6 11 1789 1739361273 1739361304 ACCEPT OK
# 2 111111111111 eni-0544example 10.XX.XX.XX 35.XX.XX.XX 58590 443 6 24 2536 1739361273 1739361304 ACCEPT OK
# 2 111111111111 eni-0544example 10.XX.XX.XX 52.XX.XX.XX 30105 443 6 33 7702 1739361273 1739361304 ACCEPT OK
# 2 111111111111 eni-0544example 18.XX.XX.XX 10.XX.XX.XX 443 60422 6 15 5746 1739361273 1739361304 ACCEPT OK

DuckDBによる分析準備

以下ガイドのとおりに、 DuckDBをローカルにインストールします。

https://duckdb.org/docs/installation/

DuckDBのバージョン確認、起動
duckdb --version
# v1.2.0 5f5512b827
duckdb
# v1.2.0 5f5512b827
# Enter ".help" for usage hints.
# Connected to a transient in-memory database.
# Use ".open FILENAME" to reopen on a persistent database.
# D

自環境では VPCフローログは plain-text で保存されています。 分析効率を上げるために Parquet 形式に変換します。

以下コマンドで、特定時間(例: 2025/02/12 11時台(UTC))のフローログを Parquetとしてローカルに保存します。

Parquet形式に変換してローカルに保存
---- S3連携の設定
INSTALL httpfs;
LOAD httpfs;

CREATE SECRET secret (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN
);
-- ┌─────────┐
-- │ Success │
-- │ boolean │
-- ├─────────┤
-- │ true    │
-- └─────────┘

---- フローログをParquet形式に変換してローカルに保存
COPY (
  SELECT *
  FROM read_csv('s3://EXAMPLE-DOC-BUCKET/plain-text/AWSLogs/aws-account-id=111111111111/aws-service=vpcflowlogs/aws-region=ap-northeast-1/year=2025/month=02/day=12/hour=11/*.log.gz', delim=' ')
) TO 'flowlogs.parquet' (FORMAT PARQUET);
-- 100% ▕████████████████████████████████████████████████████████████▏

---- 確認
select * from 'flowlogs.parquet' limit 10;
-- ┌─────────┬──────────────┬──────────────────────┬───────────────┬───┬───────┬───────┬─────────┬───────┐
-- │ version │  account-id  │     interface-id     │    srcaddr    │ … │  day  │ hour  │  month  │ year  │
-- │  int64  │    int64     │       varchar        │    varchar    │   │ int64 │ int64 │ varchar │ int64 │
-- ├─────────┼──────────────┼──────────────────────┼───────────────┼───┼───────┼───────┼─────────┼───────┤
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 10.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 10.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 10.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 10.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 10.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 52.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 10.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 10.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 10.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- │       2 │ 111111111111 │ eni-080cfexample…    │ 10.XX.XX.XX   │ … │    12 │    11 │ 02      │  2025 │
-- ├─────────┴──────────────┴──────────────────────┴───────────────┴───┴───────┴───────┴─────────┴───────┤
-- │ 10 rows                                                                        21 columns (8 shown) │
-- └─────────────────────────────────────────────────────────────────────────────────────────────────────┘

VPCフローログをクエリする

列情報を確認する

フローログの列情報を確認します。

DESCRIBE 'flowlogs.parquet';
出力サンプル
┌────────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
│  column_name   │ column_type │  null   │   key   │ default │  extra  │
│    varchar     │   varchar   │ varchar │ varchar │ varchar │ varchar │
├────────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
│ version        │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ account-id     │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ interface-id   │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ srcaddr        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ dstaddr        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ srcport        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ dstport        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ protocol       │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ packets        │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ bytes          │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ start          │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ end            │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ action         │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ log-status     │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ aws-account-id │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ aws-region     │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ aws-service    │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ day            │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ hour           │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
│ month          │ VARCHAR     │ YES     │ NULL    │ NULL    │ NULL    │
│ year           │ BIGINT      │ YES     │ NULL    │ NULL    │ NULL    │
├────────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┤
│ 21 rows                                                    6 columns │
└──────────────────────────────────────────────────────────────────────┘

1レコードのみ取得する

1レコードのみ確認してみます。

.mode line
SELECT * FROM 'flowlogs.parquet' LIMIT 1;
出力サンプル
       version = 2
    account-id = 111111111111
  interface-id = eni-080cfexample
       srcaddr = 10.XX.XX.XX
       dstaddr = 54.XX.XX.XX
       srcport = 65453
       dstport = 443
      protocol = 6
       packets = 13
         bytes = 1709
         start = 1739357971
           end = 1739358002
        action = ACCEPT
    log-status = OK
aws-account-id = 111111111111
    aws-region = ap-northeast-1
   aws-service = vpcflowlogs
           day = 12
          hour = 11
         month = 02
          year = 2025

[Tips] 出力形式の line は「1レコードのみ確認したい」場合に便利です。 元に戻す(=デフォルトにする)には .mode duckbox を実行します。

特定時間のレコードを取得する

特定時間(例 2025-02-12 20:35 ~ 20:36 )のレコードを取得してみます。

SELECT
  CAST(to_timestamp("start") AS TIMESTAMPTZ) as start_jst,
  CAST(to_timestamp("end") AS TIMESTAMPTZ) as end_jst,
  srcaddr,
  dstaddr,
  srcport,
  dstport,
  protocol,
  action
FROM 'flowlogs.parquet'
WHERE
  "log-status" = 'OK' AND
  CAST(to_timestamp("start") AS TIMESTAMPTZ) BETWEEN
    CAST('2025-02-12 20:35:00+09:00' AS TIMESTAMPTZ) AND
    CAST('2025-02-12 20:36:00+09:00' AS TIMESTAMPTZ)
LIMIT 10;
出力サンプル
┌──────────────────────┬──────────────────────────┬───────────────┬───────────────┬─────────┬─────────┬──────────┬─────────┐
│      start_jst       │         end_jst          │    srcaddr    │    dstaddr    │ srcport │ dstport │ protocol │ action  │
│ timestamp with tim…  │ timestamp with time zone │    varchar    │    varchar    │ varchar │ varchar │ varchar  │ varchar │
├──────────────────────┼──────────────────────────┼───────────────┼───────────────┼─────────┼─────────┼──────────┼─────────┤
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 52.XX.XX.XX   │ 10.XX.XX.XXX  │ 443     │ 49624   │ 6        │ ACCEPT  │
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 13.XX.XX.XX   │ 10.XX.XX.XXX  │ 443     │ 43356   │ 6        │ ACCEPT  │
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 13.XX.XX.XX   │ 10.XX.XX.XXX  │ 443     │ 53756   │ 6        │ ACCEPT  │
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 10.XX.XX.XX   │ 3.XX.XX.XXX   │ 52526   │ 443     │ 6        │ ACCEPT  │
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 35.XX.XX.XX   │ 10.XX.XX.XXX  │ 443     │ 47608   │ 6        │ ACCEPT  │
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 10.XX.XX.XX   │ 54.XX.XX.XXX  │ 52664   │ 443     │ 6        │ ACCEPT  │
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 10.XX.XX.XX   │ 13.XX.XX.XXX  │ 49230   │ 443     │ 6        │ ACCEPT  │
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 10.XX.XX.XX   │ 13.XX.XX.XXX  │ 52512   │ 443     │ 6        │ ACCEPT  │
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 10.XX.XX.XX   │ 35.XX.XX.XXX  │ 48066   │ 443     │ 6        │ ACCEPT  │
│ 2025-02-12 20:35:5…  │ 2025-02-12 20:36:24+09   │ 10.XX.XX.XX   │ 13.XX.XX.XXX  │ 59404   │ 443     │ 6        │ ACCEPT  │
├──────────────────────┴──────────────────────────┴───────────────┴───────────────┴─────────┴─────────┴──────────┴─────────┤
│ 10 rows                                                                                                        8 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

送信元IPアドレス別のトラフィック量を取得する

送信元IPアドレス別にトラフィック量の多いもの TOP-N を出力します。

SELECT
  srcaddr,
  SUM(CAST(bytes AS BIGINT)) as total_bytes,
  COUNT(*) as connection_count
FROM 'flowlogs.parquet'
WHERE
  "log-status" = 'OK'
GROUP BY srcaddr
ORDER BY total_bytes DESC
LIMIT 10;
出力サンプル
┌───────────────┬─────────────┬──────────────────┐
│    srcaddr    │ total_bytes │ connection_count │
│    varchar    │    int128   │      int64       │
├───────────────┼─────────────┼──────────────────┤
│ 10.XX.XX,XX   │  121974315  │            47229 │
│ 10.XX.XX,XX   │  117440076  │            44237 │
│ 10.XX.XX,XX   │   73975047  │            25938 │
│ 10.XX.XX,XX   │   69495959  │            20204 │
│ 13.XX.XX,XX   │   51678514  │            22032 │
│ 52.XX.XX,XX   │   51673037  │            22028 │
│ 35.XX.XX,XX   │   51617107  │            22054 │
│ 54.XX.XX,XX   │    3876487  │             2210 │
│ 52.XX.XX,XX   │    3787873  │             2216 │
│ 52.XX.XX,XX   │    3122220  │             2224 │
├───────────────┴─────────────┴──────────────────┤
│ 10 rows                              3 columns │
└────────────────────────────────────────────────┘

REJECT/ACCEPTされたレコードの送信元IPアドレスを取得する

REJECT/ACCEPTされたレコードの件数順に 送信元IPアドレスを出力します。

SELECT
  srcaddr,
  COUNT(*) as num_of_access
FROM 'flowlogs.parquet'
WHERE
  "log-status" = 'OK' AND
  action = 'REJECT'
  -- もしくは action = 'ACCEPT'
GROUP BY srcaddr
ORDER BY num_of_access DESC
LIMIT 10;
出力サンプル
┌───────────────┬───────────────┐
│    srcaddr    │ num_of_access │
│    varchar    │     int64     │
├───────────────┼───────────────┤
│ 203.XX.XX.XX  │           472 │
│ 203.XX.XX.XX  │           442 │
│ 203.XX.XX.XX  │           259 │
│ 35.XX.XX.XX   │           220 │
│ 13.XX.XX.XX   │           220 │
│ 52.XX.XX.XX   │           220 │
│ 203.XX.XX.XX  │           202 │
│ 210.XX.XX.XX  │            27 │
│ 52.XX.XX.XX   │            22 │
│ 52.XX.XX.XX   │            22 │
├───────────────┴───────────────┤
│ 10 rows             2 columns │
└───────────────────────────────┘

おわりに

VPCフローログをDuckDBでサクッと分析してみました。

もちろんAthenaを使って分析する方法 もありますが、 こちらと比較すると「クイックに、ローカルで簡単に分析できる」利点が大きいです。 Athenaテーブルをわざわざ作る必要も無いのが嬉しいところです。

以上、参考になれば幸いです。

参考

Share this article

facebook logohatena logotwitter logo

関連記事

Amazon Athena를 활용하여 Amazon S3에 저장된 VPC Flow Logs를 출력해 봤습니다.

Amazon Athena를 활용하여 Amazon S3에 저장된 VPC Flow Logs를 출력해 봤습니다.

User avatar
Kim Jaewook
2025.02.17
Network Load Balancer에 VPC Flow Logs를 설정해 봤습니다.

Network Load Balancer에 VPC Flow Logs를 설정해 봤습니다.

User avatar
Kim Jaewook
2025.01.31
[アップデート]Amazon CloudWatch のテレメトリ設定を集中的に可視化できるようになりました #AWSreInvent

[アップデート]Amazon CloudWatch のテレメトリ設定を集中的に可視化できるようになりました #AWSreInvent

User avatar
おのやん
2024.12.11
Amazon VPC Flow ログをSplunkへ送信する際のベスプラだと思う構成

Amazon VPC Flow ログをSplunkへ送信する際のベスプラだと思う構成

User avatar
酒井剛
2024.12.07
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.