はじめに
「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を起動
❯ 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.テーブルを作成
D CREATE TABLE waflog AS SELECT * FROM read_json_auto('{ダウンロードしたローカルのパス}/**/*.log.gz', union_by_name=true);
このread_json_autoというのがとても便利で、自動でスキーマを推測してくれるようです。
しかも、圧縮されているログでも読み込んでくれます。
3.完成したテーブルを確認する
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で分析するだけです。いくつかサンプルを記載します。
例）国別のブロック数を検索する
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別のブロック数を検索する
D SELECT httpRequest.uri, COUNT(*)
FROM waflog
WHERE action = 'BLOCK'
GROUP BY httpRequest.uri
ORDER BY COUNT(*) DESC;
例）IPと国のブロック数を検索する
D SELECT httpRequest.clientIp, httpRequest.country, COUNT(*)
FROM waflog
WHERE action = 'BLOCK'
GROUP BY httpRequest.clientIp, httpRequest.country
ORDER BY COUNT(*) DESC;
まとめ
DuckDBとってもいい感じです！
SQLは使い慣れている人も多いので、その点も良いと感じました。
今回は簡単なケースのみですので、もう少し使い込んでみようと思います。