DuckDBを使ってローカル環境でAWSWAFのログを分析する

# はじめに
「DuckDBというツールを使ってログを分析するのいいぞ！」というテックブログを目にする機会が増えました。
弊社では普段Athenaを利用してAWS WAFのログを検索していますが、DuckDBを試してみました。

# 準備: 環境構築
Macにインストールして使ってみます。
公式サイト）https://duckdb.org/docs/installation/index?version=stable&environment=cli&platform=macos&download_method=package_manager
```
brew install duckdb
```

今回は、WAFログはS3に出力されたものをローカルにダウンロードしてきました。
もちろんローカルにダウンロードしなくても、DuckDBから直接アクセスすることもできます。
参考）https://road288.hatenablog.com/entry/2024/11/06/113954

# DuckDBにテーブルを作成
1.DuckDBを起動
```bash
❯ duckdb
v1.1.3 19864453f7
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
```

2.テーブルを作成
```bash
D CREATE TABLE waflog AS SELECT * FROM read_json_auto('{ダウンロードしたローカルのパス}/**/*.log.gz', union_by_name=true);
```
この`read_json_auto`というのがとても便利で、自動でスキーマを推測してくれるようです。
しかも、圧縮されているログでも読み込んでくれます。

3.完成したテーブルを確認する
```bash
D DESCRIBE waflog;
┌──────────────────────┬───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─────────┬─────────┬─────────┬─────────┐
│     column_name      │                                                      column_type                                                      │  null   │   key   │ default │  extra  │
│       varchar        │                                                        varchar                                                        │ varchar │ varchar │ varchar │ varchar │
├──────────────────────┼───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────┼─────────┼─────────┼─────────┤
│ timestamp            │ BIGINT                                                                                                                │ YES     │         │         │         │
│ formatVersion        │ BIGINT                                                                                                                │ YES     │         │         │         │
│ webaclId             │ VARCHAR                                                                                                               │ YES     │         │         │         │
│ terminatingRuleId    │ VARCHAR                                                                                                               │ YES     │         │         │         │
│ terminatingRuleType  │ VARCHAR                                                                                                               │ YES     │         │         │         │
│ action               │ VARCHAR                                                                                                               │ YES     │         │         │         │
│ terminatingRuleMat…  │ STRUCT(conditionType VARCHAR, "location" VARCHAR, matchedData JSON, matchedFieldName VARCHAR)[]                       │ YES     │         │         │         │
│ httpSourceName       │ VARCHAR                                                                                                               │ YES     │         │         │         │
│ httpSourceId         │ VARCHAR                                                                                                               │ YES     │         │         │         │
│ ruleGroupList        │ STRUCT(ruleGroupId VARCHAR, terminatingRule STRUCT(ruleId VARCHAR, "action" VARCHAR, ruleMatchDetails JSON), nonTer…  │ YES     │         │         │         │
│ rateBasedRuleList    │ STRUCT(rateBasedRuleId VARCHAR, rateBasedRuleName VARCHAR, limitKey VARCHAR, maxRateAllowed BIGINT, evaluationWindo…  │ YES     │         │         │         │
│ nonTerminatingMatc…  │ STRUCT(ruleId VARCHAR, "action" VARCHAR, ruleMatchDetails JSON[])[]                                                   │ YES     │         │         │         │
│ requestHeadersInse…  │ JSON                                                                                                                  │ YES     │         │         │         │
│ responseCodeSent     │ JSON                                                                                                                  │ YES     │         │         │         │
│ httpRequest          │ STRUCT(clientIp VARCHAR, country VARCHAR, headers STRUCT("name" VARCHAR, "value" VARCHAR)[], uri VARCHAR, args VARC…  │ YES     │         │         │         │
│ ja3Fingerprint       │ UUID                                                                                                                  │ YES     │         │         │         │
│ requestBodySize      │ BIGINT                                                                                                                │ YES     │         │         │         │
│ requestBodySizeIns…  │ BIGINT                                                                                                                │ YES     │         │         │         │
│ oversizeFields       │ VARCHAR[]                                                                                                             │ YES     │         │         │         │
├──────────────────────┴───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────┴─────────┴─────────┴─────────┤
│ 19 rows                                                                                                                                                                    6 columns │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

```
確かにいい感じにテーブルになってますね！！！

# AWS WAFログのクエリ分析
あとはSQLで分析するだけです。いくつかサンプルを記載します。

例）国別のブロック数を検索する
```sql
D SELECT httpRequest.country, COUNT(*)
    FROM waflog
    WHERE action = 'BLOCK'
    GROUP BY httpRequest.country 
    ORDER BY COUNT(*) DESC;
┌─────────┬──────────────┐
│ country │ count_star() │
│ varchar │    int64     │
├─────────┼──────────────┤
│ JP      │         2356 │
│ IE      │          462 │
│ HK      │          102 │
│ SG      │           78 │
│ UA      │           48 │
│ US      │           13 │
│ FR      │           10 │
│ DE      │            5 │
│ PA      │            3 │
│ NL      │            2 │
│ GB      │            2 │
│ IN      │            1 │
├─────────┴──────────────┤
│ 12 rows      2 columns │
└────────────────────────┘
```

例）uri別のブロック数を検索する
```sql
D SELECT httpRequest.uri, COUNT(*)
  FROM waflog
  WHERE action = 'BLOCK'
  GROUP BY httpRequest.uri
  ORDER BY COUNT(*) DESC;
```

例）IPと国のブロック数を検索する
```sql
D SELECT httpRequest.clientIp, httpRequest.country, COUNT(*)
  FROM waflog
  WHERE action = 'BLOCK'
  GROUP BY httpRequest.clientIp, httpRequest.country
  ORDER BY COUNT(*) DESC;
```

# まとめ
DuckDBとってもいい感じです！
SQLは使い慣れている人も多いので、その点も良いと感じました。
今回は簡単なケースのみですので、もう少し使い込んでみようと思います。