Route 53 Resolver DNSクエリログ(S3)を DuckDB で分析

Route 53 Resolver DNSクエリログ(S3)を DuckDB で分析

#Amazon Route 53
#Amazon VPC
#Amazon S3
#DuckDB
#AWS
川原征大

川原征大

facebook logohatena logotwitter logo
Clock Icon2025.03.14

VPC内で発生したDNSクエリ(およびその応答)を確認するには Route 53 の Resolver DNSクエリログが役に立ちます。

https://docs.aws.amazon.com/ja_jp/Route53/latest/DeveloperGuide/resolver-query-logs.html

DNSクエリログの送信先として、 CloudWatch Logs ロググループ、 S3バケット、Kinesis Data Firehose 配信ストリームの3つから選べます。 安価にバッチ処理分析基盤を作りたい場合はS3が適しています。

今回は S3に保存している環境で、 DNSクエリログを DuckDB で分析してみます 。 DuckDBはオンライン分析処理(OLAP)に特化したオープンソースのデータベースシステムです。

セットアップ

Resolver DNSクエリログ

DNSクエリログ出力環境は事前にセットアップしている前提で進めます。 以下のように「S3バケットにクエリログを出力する設定」を入れています。

aws route53resolver list-resolver-query-log-configs --output yaml
# ResolverQueryLogConfigs:
# - Arn: arn:aws:route53resolver:ap-northeast-1:111111111111:resolver-query-log-config/rqlc-6548example
#   AssociationCount: 1
#   CreationTime: '2025-03-07T06:05:45.755793961Z'
#   CreatorRequestId: '17411111111111'
#   DestinationArn: arn:aws:s3:::EXAMPLE-DOC-BUCKET
#   Id: rqlc-6548example
#   Name: example-resolver-log
#   OwnerId: '111111111111'
#   ShareStatus: NOT_SHARED
#   Status: CREATED
# TotalCount: 1
# TotalFilteredCount: 1

aws route53resolver list-resolver-query-log-config-associations
# ResolverQueryLogConfigAssociations:
# - CreationTime: '2025-03-07T06:05:46.645329988Z'
#   Error: NONE
#   ErrorMessage: ''
#   Id: rqlca-1e1eexample
#   ResolverQueryLogConfigId: rqlc-6548example
#   ResourceId: vpc-0243example
#   Status: ACTIVE
# TotalCount: 1
# TotalFilteredCount: 1

ログは EXAMPLE-DOC-BUCKET/AWSLogs/111111111111/vpcdnsquerylogs/vpc-0243example/YYYY/MM/DD/ 配下に gz 圧縮されて格納されています。

aws s3 ls EXAMPLE-DOC-BUCKET/AWSLogs/111111111111/vpcdnsquerylogs/vpc-0243example/2025/03/10/ | head
# 2025-03-10 09:03:36      27685 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0000Z_9a091111.log.gz
# 2025-03-10 09:08:36      12669 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0000Z_c02a1111.log.gz
# 2025-03-10 09:13:36      14542 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0005Z_dce21111.log.gz
# 2025-03-10 09:08:36      26241 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0005Z_df331111.log.gz
# 2025-03-10 09:13:36      26573 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0010Z_07c11111.log.gz
# 2025-03-10 09:18:37      15084 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0010Z_3af31111.log.gz
# 2025-03-10 09:23:37      13881 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0015Z_59cd1111.log.gz
# 2025-03-10 09:18:36      27553 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0015Z_63721111.log.gz
# 2025-03-10 09:23:37      28174 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0020Z_6bfb1111.log.gz
# 2025-03-10 09:28:36      14041 vpc-02439example_vpcdnsquerylogs_111111111111_20250310T0020Z_b3fa1111.log.gz
ログファイルのサンプル
gzip -d vpc-0243...略...log.gz
head -n2 vpc-0243...略...log
# {"version":"1.100000","account_id":"111111111111","region":"ap-northeast-1","vpc_id":"vpc-0243example","query_timestamp":"2025-03-10T00:15:00Z","query_name":"xxx.example.com.","query_type":"A","query_class":"IN","rcode":"NOERROR","answers":[{"Rdata":"203.0.113.1","Type":"A","Class":"IN"},{"Rdata":"203.0.113.2","Type":"A","Class":"IN"}],"srcaddr":"192.0.2.1","srcport":"11800","transport":"UDP","srcids":{"instance":"i-0d7cexample"}}
# {"version":"1.100000","account_id":"111111111111","region":"ap-northeast-1","vpc_id":"vpc-0243example","query_timestamp":"2025-03-10T00:15:00Z","query_name":"xxx.example.com.","query_type":"A","query_class":"IN","rcode":"NOERROR","answers":[{"Rdata":"203.0.113.1","Type":"A","Class":"IN"},{"Rdata":"203.0.113.2","Type":"A","Class":"IN"}],"srcaddr":"192.0.2.2","srcport":"11800","transport":"UDP","srcids":{"instance":"i-30ajexample"}}
head -n1 vpc-0243...略...log | jq
# {
#   "version": "1.100000",
#   "account_id": "111111111111",
#   "region": "ap-northeast-1",
#   "vpc_id": "vpc-0243example",
#   "query_timestamp": "2025-03-10T00:15:00Z",
#   "query_name": "xxx.example.com.",
#   "query_type": "A",
#   "query_class": "IN",
#   "rcode": "NOERROR",
#   "answers": [
#     {
#       "Rdata": "203.0.113.1",
#       "Type": "A",
#       "Class": "IN"
#     },
#     {
#       "Rdata": "203.0.113.2",
#       "Type": "A",
#       "Class": "IN"
#     }
#   ],
#   "srcaddr": "192.0.2.1",
#   "srcport": "11800",
#   "transport": "UDP",
#   "srcids": {
#     "instance": "i-0d7cexample"
#   }
# }

DuckDBによる分析準備

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

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

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

以下コマンドで、特定時間(例: 2025/03/10 11時台(UTC))のクエリログを Parquet形式に変換してローカルに保存します。

---- S3連携の設定
INSTALL httpfs;
LOAD httpfs;

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

---- Parquet形式に変換してローカルに保存
COPY (
  SELECT *
  FROM read_json('s3://EXAMPLE-DOC-BUCKET/AWSLogs/111111111111/vpcdnsquerylogs/vpc-0243example/2025/03/10/vpc-0243example_vpcdnsquerylogs_111111111111_20250310T11*.log.gz')
) TO 'querylogs.parquet' (FORMAT PARQUET);
-- 100% ▕████████████████████████████████████████████████████████████▏

---- 確認
select * from 'querylogs.parquet' limit 10;
-- ┌──────────┬──────────────┬────────────────┬───┬─────────┬───────────┬──────────────────────┐
-- │ version  │  account_id  │     region     │ … │ srcport │ transport │        srcids        │
-- │ varchar  │   varchar    │    varchar     │   │ varchar │  varchar  │ struct(instance va…  │
-- ├──────────┼──────────────┼────────────────┼───┼─────────┼───────────┼──────────────────────┤
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 55123   │ UDP       │ {'instance': i-0512… │
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 60123   │ UDP       │ {'instance': i-0c12… │
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 39123   │ UDP       │ {'instance': i-0612… │
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 44123   │ UDP       │ {'instance': i-0012… │
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 34123   │ UDP       │ {'instance': i-0512… │
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 37123   │ UDP       │ {'instance': i-0712… │
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 55123   │ UDP       │ {'instance': i-0512… │
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 46123   │ UDP       │ {'instance': i-0d12… │
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 42123   │ UDP       │ {'instance': i-0b12… │
-- │ 1.100000 │ 111111111111 │ ap-northeast-1 │ … │ 59123   │ UDP       │ {'instance': i-0512… │
-- ├──────────┴──────────────┴────────────────┴───┴─────────┴───────────┴──────────────────────┤
-- │ 10 rows                                                              14 columns (6 shown) │
-- └───────────────────────────────────────────────────────────────────────────────────────────┘

DNSクエリログを分析する

列情報を確認する

クエリログの列情報を取得します。

-- デフォルト出力(duckbox)だと ウィンドウ幅次第で column_type が省略されるので、markdownテーブルで出力
.mode markdown
DESCRIBE 'querylogs.parquet';
出力サンプル
|   column_name   |                       column_type                        | null | key  | default | extra |
|-----------------|----------------------------------------------------------|------|------|---------|-------|
| version         | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| account_id      | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| region          | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| vpc_id          | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| query_timestamp | TIMESTAMP                                                | YES  | NULL | NULL    | NULL  |
| query_name      | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| query_type      | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| query_class     | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| rcode           | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| answers         | STRUCT(Rdata VARCHAR, "Type" VARCHAR, "Class" VARCHAR)[] | YES  | NULL | NULL    | NULL  |
| srcaddr         | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| srcport         | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| transport       | VARCHAR                                                  | YES  | NULL | NULL    | NULL  |
| srcids          | STRUCT(instance VARCHAR)                                 | YES  | NULL | NULL    | NULL  |

それぞれの列の内容については公式ドキュメントを参照ください。

https://docs.aws.amazon.com/ja_jp/Route53/latest/DeveloperGuide/resolver-query-logs-format.html

1レコードのみ取得する

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

.mode line
SELECT * FROM 'querylogs.parquet' LIMIT 1;
出力サンプル
        version = 1.100000
     account_id = 111111111111
         region = ap-northeast-1
         vpc_id = vpc-0243example
query_timestamp = 2025-03-10 11:00:00
     query_name = xxx.example.com
     query_type = A
    query_class = IN
          rcode = NOERROR
        answers = [{'Rdata': 203.0.113.1, 'Type': A, 'Class': IN}, {'Rdata': 203.0.113.2, 'Type': A, 'Class': IN}, {'Rdata': 203.0.113.3, 'Type': A, 'Class': IN}]
        srcaddr = 192.0.2.1
        srcport = 55123
      transport = UDP
         srcids = {'instance': i-0d5eexample}

answersやsrcidsは構造体です。 以下のように要素を抽出できます。

SELECT
  json_extract(answers, '$[*].Rdata') as Rdata,
  srcids.instance
FROM 'querylogs.parquet' LIMIT 1;

--    Rdata = ["203.0.113.1", "203.0.113.2", "203.0.113.3"]
-- instance = i-0d5eexample

多くクエリされているドメインを取得する

上位10項目を取得します。

SELECT
  query_name,
  COUNT(*) as query_count
FROM 'querylogs.parquet'
GROUP BY query_name
ORDER BY query_count DESC
LIMIT 10;
出力サンプル
┌────────────────────────────────────────┬─────────────┐
│               query_name               │ query_count │
│                 varchar                │    int64    │
├────────────────────────────────────────┼─────────────┤
│ aaa.example.com.                       │       24578 │
│ bbb.example.com.                       │        8765 │
│ sqs.ap-northeast-1.amazonaws.com.      │        5643 │
│ config.example.com.                    │        4219 │
│ api.example.com.                       │        3876 │
│ item.example.com.                      │        2543 │
│ order.example.com.                     │        1987 │
│ firehose.ap-northeast-1.amazonaws.com. │        1543 │
│ ccc.example.com.                       │        1234 │
│ logs.ap-northeast-1.amazonaws.com.     │        1098 │
├────────────────────────────────────────┴─────────────┤
│ 10 rows                                    2 columns │
└──────────────────────────────────────────────────────┘

クエリ応答に特定IPアドレスが含まれているものを取得する

SELECT
  query_name,
  COUNT(*) as query_count
FROM 'querylogs.parquet'
WHERE
  CAST(answers AS VARCHAR) LIKE '%203.0.113.1%'
GROUP BY query_name
ORDER BY query_count DESC
LIMIT 10;
出力サンプル
┌───────────────────┬─────────────┐
│    query_name     │ query_count │
│      varchar      │    int64    │
├───────────────────┼─────────────┤
│ item.example.com  │        1048 │
│ order.example.com │         953 │
│ api.example.com   │         818 │
│ ccc.example.com   │         567 │
└───────────────────┴─────────────┘

特定インスタンスがクエリしたものを取得する

SELECT
  query_name,
  COUNT(*) as query_count
FROM 'querylogs.parquet'
WHERE
  srcids.instance = 'i-0d5eexample'
GROUP BY query_name
ORDER BY query_count DESC
LIMIT 5;
出力サンプル
┌────────────────────────────────────────┬─────────────┐
│               query_name               │ query_count │
│                varchar                 │    int64    │
├────────────────────────────────────────┼─────────────┤
│ config.example.com.                    │         118 │
│ order.example.com.                     │         102 │
│ item.example.com.                      │         101 │
│ firehose.ap-northeast-1.amazonaws.com. │          82 │
│ ccc.example.com.                       │          67 │
└────────────────────────────────────────┴─────────────┘

応答無しのクエリログを取得する

SELECT
  query_timestamp,
  srcids.instance as instance,
  query_name,
  rcode,
  len(answers)
FROM 'querylogs.parquet'
WHERE len(answers) = 0
LIMIT 10;
出力サンプル
┌─────────────────────┬───────────────┬──────────────────────────────────────────────────────┬─────────┬──────────────┐
│   query_timestamp   │   instance    │                       query_name                     │  rcode  │ len(answers) │
│      timestamp      │    varchar    │                        varchar                       │ varchar │    int64     │
├─────────────────────┼───────────────┼──────────────────────────────────────────────────────┼─────────┼──────────────┤
│ 2025-03-10 11:00:02 │ i-097fexample │ example.aaaaaa.clustercfg.apne1.cache.amazonaws.com. │ NOERROR │            0 │
│ 2025-03-10 11:00:00 │ i-0d5eexample │ order.example.com.                                   │ NOERROR │            0 │
│ 2025-03-10 11:00:01 │ i-0439example │ example.aaaaaa.clustercfg.apne1.cache.amazonaws.com. │ NOERROR │            0 │
│ 2025-03-10 11:00:03 │ i-09faexample │ example.aaaaaa.clustercfg.apne1.cache.amazonaws.com. │ NOERROR │            0 │
│ 2025-03-10 11:00:01 │ i-09f1example │ query.example.com.                                   │ NOERROR │            0 │
│ 2025-03-10 11:00:04 │ i-0a85example │ example.aaaaaa.clustercfg.apne1.cache.amazonaws.com. │ NOERROR │            0 │
│ 2025-03-10 11:00:02 │ i-0383example │ order.example.com.                                   │ NOERROR │            0 │
│ 2025-03-10 11:00:02 │ i-0659example │ example.aaaaaa.clustercfg.apne1.cache.amazonaws.com. │ NOERROR │            0 │
│ 2025-03-10 11:00:06 │ i-0796example │ firehose.ap-northeast-1.amazonaws.com.               │ NOERROR │            0 │
│ 2025-03-10 11:00:03 │ i-0f6fexample │ ssm.ap-northeast-1.amazonaws.com.                    │ NOERROR │            0 │
├─────────────────────┴───────────────┴──────────────────────────────────────────────────────┴─────────┴──────────────┤
│ 10 rows                                                                                                   5 columns │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

おわりに

Route 53 Resolver DNSクエリログを DuckDB でサクッと分析してみました。

余談ですが 2025/03/12 に新機能「DuckDB Local UI」が出ました。 こちら、とても良いです。

https://duckdb.org/2025/03/12/duckdb-ui

ローカル上で実行したSQLとその結果をノートブックとして確認、整理できます。 気軽にSQLを修正、再実行できます。めっちゃいい体験です。

sc_2025-03-14_04-42-16_31707

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

参考

https://duckdb.org/docs/stable/
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/querying-r53-resolver-logs-example-queries.html
https://docs.aws.amazon.com/ja_jp/Route53/latest/DeveloperGuide/resolver-query-logs-format.html
https://dev.classmethod.jp/articles/vpc-dns-querylog-athena/

Share this article

facebook logohatena logotwitter logo

関連記事

Route53 가중치 기반 라우팅을 사용해보자!

Route53 가중치 기반 라우팅을 사용해보자!

User avatar
KeumSangwon
2025.03.05
Route53의 장애조치 라우팅 정책을 사용해보자!

Route53의 장애조치 라우팅 정책을 사용해보자!

User avatar
KeumSangwon
2025.02.27
Amazon EKSにデプロイしたGitLabのALB DNS名をExternalDNSを使ってRoute53に登録する

Amazon EKSにデプロイしたGitLabのALB DNS名をExternalDNSを使ってRoute53に登録する

User avatar
佐藤雅樹
2025.02.24
マルチアカウント環境におけるAmazon Route 53 Resolver エンドポイントの集約構成（複数リージョン対応）

マルチアカウント環境におけるAmazon Route 53 Resolver エンドポイントの集約構成（複数リージョン対応）

User avatar
平井裕二
2025.02.19
Classmethod's white logo
Facebook's logo
X's logo
YouTube's logo

© Classmethod, Inc. All rights reserved.