🐕

DuckDB でストレージアカウントにある診断ログを見る

Tsubasa Nomura

2024/11/07に公開

Azure

tech

TL;DR

こちらの記事を見て Azure のストレージアカウントでもできるのではと思いやってみました。

DuckDB とは

いま話題になっている DuckDB ですが、SQLite 的な使い方ができる OLAP データベースです。列指向のデータベースで、SQLite のようにトランザクションをゴリゴリ扱うような処理は苦手ですが分析用途でパフォーマンスを発揮するように設計されています。
Why DuckDB については、オフィシャルのページや他の方が書いた記事を参照してください。
https://duckdb.org/why_duckdb

ストレージアカウントの診断ログを見るモチベーション

Azure のリソースには診断ログの出力を設定できます。分析したい場合通常は Log Analytics を使うことが一般的ですが、料金や、その他やんごとなき理由でストレージアカウントのみに出力することがあります。

ストレージアカウントに出力されるログは JSON として出力されるため、分かりやすく可視化したり分析したりするには一工夫必要です。Power BI もストレージアカウントのファイルを読み込むことが出来るので、分かりやすく可視化という意味では Power BI が手っ取り早いことがあるのですが、他のツールとの連携や Log Analytics の KQL 的に SQL でクエリを書きたい場合の選択肢として DuckDB はありかと思います。

やってみる

今回は DuckDB の CLI を使ってみます。DuckDB には、拡張として Azure のストレージアカウントを扱う拡張があるのでそれを使います。

今回は直接ストレージアカウントにアクセスするためにこの拡張を使っていますが、JSON をダウンロードしてそれを DuckDB で読み込むという方法もありかと思います。

以下詳細をステップで書いていきますがコマンドだけまとめると以下のようになります。

# 認証(所有者など以外にストレージ BLOB データ閲覧者の権限が必要)
> az login
# duckdb CLI を起動
> 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.
D
D
# シークレットの作成
D create SECRET secret1(
    type azure,
    provider credential_chain
    );
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘
D
# curl に変更
D SET azure_transport_option_type = 'curl';
# ストレージ アカウントの診断ログを読み込む
D select * from 'az://tsunomurtemp.blob.core.windows.net/insights-logs-azurefirewall/resourceId=/SUBSCRIPTIONS/C173F171-5FE0-4DA8-A696-1C299495246F/RESOURCEGROUPS/RG-VWAN-AVS-1024/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/**/*.json

1. インストール

こちらのページに書いてあるようにインストールします。

2. 認証

DuckDB からストレージアカウントへアクセスするために認証が必要です。アカウントキーやサービスプリンシパルを使うこともできますがセキュアな方法ではないので CREDENTIAL_CHAIN を使って、Azure CLI でログインしているユーザーの認証情報を使います。

ですので、DuckDB の CLI を起動する前に az login でログインしておきます。

CREDENTIAL_CHAIN は Azure の SDK で使われている認証方法で、Azure CLI でログインしているユーザーの認証情報以外にも環境変数やマネージド ID などを使うこともできます。

# duckdb CLI を起動
> 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.
D
D
# シークレットの作成
D create SECRET secret1(
    type azure,
    provider credential_chain
    );
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘
D

3. HTTP トランスポートアダプターの変更

デフォルトだと証明書のエラーが出るため、HTTP トランスポートアダプターを curl に変更します。

# 以下のようなエラーが出る
D select * from 'az://tsunomurtemp.blob.core.windows.net/insights-logs-azurefirewall/resourceId=/SUBSCRIPTIONS/C173F171-5FE0-4DA8-A696-1C299495246F/RESOURCEGROUPS/RG-VWAN-AVS-1024/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/**/*.json';
Invalid Error: Fail to get a new connection for: https://tsunomurtemp.blob.core.windows.net. Problem with the SSL CA cert (path? access rights?)
D
D
# curl に変更
D SET azure_transport_option_type = 'curl';

4. ストレージアカウントの診断ログを読み込む

あとは SQL でクエリを書いていくだけです。FROM に az:// から始まるストレージアカウントへのパスを書くと自動的に拡張が使われます。他のパスの指定の仕方はドキュメントを参照してください。

ここでは Azure Firewall の診断ログをすべて読み込んでみます。

# パスのグロブ(**)が使える
D select * from 'az://tsunomurtemp.blob.core.windows.net/insights-logs-azurefirewall/resourceId=/SUBSCRIPTIONS/C173F171-5FE0-4DA8-A696-1C299495246F/RESOURCEGROUPS/RG-VWAN-AVS-1024/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/**/*.json';

resourceId が空になってしまいます。これはハイブパーティショニングが自動検出されており、パーティションキーが resourceId になっているためです。自動検知を無効にして読み込んでみます。

D select * from read_json('az://tsunomurtemp.blob.core.windows.net/insights-logs-azurefirewall/resourceId=/SUBSCRIPTIONS/C173F171-5FE0-4DA8-A696-1C299495246F/RESOURCEGROUPS/RG-VWAN-AVS-1024/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/**/*.json', hive_partitioning=false);

resourceId が取得できました。

毎回ストレージアカウントから取得するのは転送料と時間がかかるのでテーブルを作ってしまうとよいかもしれません。
もちろん、レコードが追加された場合はテーブルを作り直す必要があります。

D CREATE TABLE azfw_diagnostics_log AS
  SELECT * FROM read_json(
      'az://tsunomurtemp.blob.core.windows.net/insights-logs-azurefirewall/resourceId=/SUBSCRIPTIONS/C173F171-5FE0-4DA8-A696-1C299495246F/RESOURCEGROUPS/RG-VWAN-AVS-1024/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/**/*.json',
      hive_partitioning = false
  );

D show tables;
┌──────────────────────┐
│         name         │
├──────────────────────┤
│ azfw_diagnostics_log │
└──────────────────────┘

では作ったテーブルからもうちょっと複雑なクエリを書いてみます。

D WITH FilteredLogs AS (
      SELECT
          time AS TimeGenerated,
          operationName AS OperationName,
          json_extract(properties, '$.msg') AS msg,
          category AS Category
      FROM azfw_diagnostics_log
      WHERE Category IN ('AzureFirewallNetworkRule', 'AzureFirewallApplicationRule')
  ),
  ParsedLogs AS (
      SELECT
          TimeGenerated,
          OperationName,
          msg AS msg_original,
          regexp_extract(msg, '^"(.*?) request from', 1) AS Protocol,
          regexp_extract(msg, 'request from ([^:]+):([0-9]+)', 1) AS SourceIP,
          regexp_extract(msg, 'request from ([^:]+):([0-9]+)', 2) AS SourcePort,
          regexp_extract(msg, 'to ([^:]+):([0-9]+)', 1) AS Target,
          regexp_extract(msg, 'to ([^:]+):([0-9]+)', 2) AS TargetPort,
          regexp_extract(msg, 'Action: ([^\\.]+)', 1) AS Action,
          regexp_extract(msg, 'Policy: ([^\\.]+)', 1) AS Policy,
          regexp_extract(msg, 'Rule Collection Group: ([^\\.]+)', 1) AS RuleCollectionGroup,
          regexp_extract(msg, 'Rule Collection: ([^\\.]+)', 1) AS RuleCollection,
          regexp_extract(msg, 'Rule: ([^\\.]+)"$', 1) AS Rule
      FROM FilteredLogs
  )
  SELECT
      msg_original,
      TimeGenerated,
      Protocol,
      SourceIP,
      SourcePort,
      Target,
      TargetPort,
      Action,
      OperationName,
      Policy,
      RuleCollectionGroup,
      RuleCollection,
      Rule
  FROM ParsedLogs
  ORDER BY TimeGenerated LIMIT 10;

送信元IPアドレスやポート番号、送信先IPアドレス、プロトコルなどをそれぞれのカラムに出力し、見やすく出来ました。

ちなみにこの SQL は Azure Firewall のログの画面に出てくる KQL のサンプルクエリを参考に書いてみました。

[参考] KQL のクエリ

// Azure Firewall log data 
// Start from this query if you want to parse the logs from network rules, application rules, NAT rules, IDS, threat intelligence and more to understand why certain traffic was allowed or denied. This query will show the last 100 log records but by adding simple filter statements at the end of the query the results can be tweaked. 
// Parses the azure firewall rule log data. 
// Includes network rules, application rules, threat intelligence, ips/ids, ...
AzureDiagnostics
| where Category == "AzureFirewallNetworkRule" or Category == "AzureFirewallApplicationRule"
//optionally apply filters to only look at a certain type of log data
//| where OperationName == "AzureFirewallNetworkRuleLog"
//| where OperationName == "AzureFirewallNatRuleLog"
//| where OperationName == "AzureFirewallApplicationRuleLog"
//| where OperationName == "AzureFirewallIDSLog"
//| where OperationName == "AzureFirewallThreatIntelLog"
| extend msg_original = msg_s
// normalize data so it's eassier to parse later
| extend msg_s = replace(@'. Action: Deny. Reason: SNI TLS extension was missing.', @' to no_data:no_data. Action: Deny. Rule Collection: default behavior. Rule: SNI TLS extension missing', msg_s)
| extend msg_s = replace(@'No rule matched. Proceeding with default action', @'Rule Collection: default behavior. Rule: no rule matched', msg_s)
// extract web category, then remove it from further parsing
| parse msg_s with * " Web Category: " WebCategory
| extend msg_s = replace(@'(. Web Category:).*','', msg_s)
// extract RuleCollection and Rule information, then remove it from further parsing
| parse msg_s with * ". Rule Collection: " RuleCollection ". Rule: " Rule
| extend msg_s = replace(@'(. Rule Collection:).*','', msg_s)
// extract Rule Collection Group information, then remove it from further parsing
| parse msg_s with * ". Rule Collection Group: " RuleCollectionGroup
| extend msg_s = replace(@'(. Rule Collection Group:).*','', msg_s)
// extract Policy information, then remove it from further parsing
| parse msg_s with * ". Policy: " Policy
| extend msg_s = replace(@'(. Policy:).*','', msg_s)
// extract IDS fields, for now it's always add the end, then remove it from further parsing
| parse msg_s with * ". Signature: " IDSSignatureIDInt ". IDS: " IDSSignatureDescription ". Priority: " IDSPriorityInt ". Classification: " IDSClassification
| extend msg_s = replace(@'(. Signature:).*','', msg_s)
// extra NAT info, then remove it from further parsing
| parse msg_s with * " was DNAT'ed to " NatDestination
| extend msg_s = replace(@"( was DNAT'ed to ).*",". Action: DNAT", msg_s)
// extract Threat Intellingence info, then remove it from further parsing
| parse msg_s with * ". ThreatIntel: " ThreatIntel
| extend msg_s = replace(@'(. ThreatIntel:).*','', msg_s)
// extract URL, then remove it from further parsing
| extend URL = extract(@"(Url: )(.*)(\. Action)",2,msg_s)
| extend msg_s=replace(@"(Url: .*)(Action)",@"\2",msg_s)
// parse remaining "simple" fields
| parse msg_s with Protocol " request from " SourceIP " to " Target ". Action: " Action
| extend 
    SourceIP = iif(SourceIP contains ":",strcat_array(split(SourceIP,":",0),""),SourceIP),
    SourcePort = iif(SourceIP contains ":",strcat_array(split(SourceIP,":",1),""),""),
    Target = iif(Target contains ":",strcat_array(split(Target,":",0),""),Target),
    TargetPort = iif(SourceIP contains ":",strcat_array(split(Target,":",1),""),""),
    Action = iif(Action contains ".",strcat_array(split(Action,".",0),""),Action),
    Policy = case(RuleCollection contains ":", split(RuleCollection, ":")[0] ,Policy),
    RuleCollectionGroup = case(RuleCollection contains ":", split(RuleCollection, ":")[1], RuleCollectionGroup),
    RuleCollection = case(RuleCollection contains ":", split(RuleCollection, ":")[2], RuleCollection),
    IDSSignatureID = tostring(IDSSignatureIDInt),
    IDSPriority = tostring(IDSPriorityInt)
| project msg_original,TimeGenerated,Protocol,SourceIP,SourcePort,Target,TargetPort,URL,Action, NatDestination, OperationName,ThreatIntel,IDSSignatureID,IDSSignatureDescription,IDSPriority,IDSClassification,Policy,RuleCollectionGroup,RuleCollection,Rule,WebCategory
| order by TimeGenerated
| limit 100

Tips

表示されるレコード数を増やす

デフォルトでは 20 行までしか表示されませんがその制限を増やすことができます。

出力モードを変更する

デフォルトではテーブル形式で出力されますが、他にも CSV や Markdown などの形式で出力できます。そのままコピーしてドキュメントに貼り付けるなどに使えます。

D .mode markdown
D select * from read_json('az://tsunomurtemp.blob.core.windows.net/insights-logs-azurefirewall/resourceId=/SUBSCRIPTIONS/C173F171-5FE0-4DA8-A696-1C299495246F/RESOURCEGROUPS/RG-VWAN-AVS-1024/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/FW-EAST/y=2024/m=11/d=06/h=16/m=00/PT1H.json', hive_partitioning=false) LIMIT 10;
|               time               |                                                               resourceId                                                               |        operationName        |                                                                                                                                                                                                                     properties                                                                                                                                                                                                                     |         category         |
|----------------------------------|----------------------------------------------------------------------------------------------------------------------------------------|-----------------------------|----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------------------|
| 2024-11-06T16:05:00.038619+00:00 | /SUBSCRIPTIONS/C173F171-5FE0-4DA8-A696-1C299495246F/RESOURCEGROUPS/RG-VWAN-AVS-1024/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/FW-EAST | AzureFirewallNetworkRuleLog | {'msg': TCP request from 172.17.4.5:60271 to 20.60.248.97:443. Action: Allow.. Policy: pol-east. Rule Collection Group: fw-example-rules. Rule Collection: network_rules1. Rule: network_rule_collection1_rule1, 'Protocol': NULL, 'SourceIp': NULL, 'SourcePort': NULL, 'DestinationIp': NULL, 'DestinationPort': NULL, 'Action': NULL, 'Pol | AzureFirewallNetworkRule |
| 2024-11-06T16:05:00.070381+00:00 | /SUBSCRIPTIONS/C173F171-5FE0-4DA8-A696-1C299495246F/RESOURCEGROUPS/RG-VWAN-AVS-1024/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/FW-EAST | AzureFirewallNetworkRuleLog | {'msg': TCP request from 172.17.4.5:55355 to 20.89.10.57:443. Action: Allow.. Policy: pol-east. Rule Collection Group: fw-example-rules. Rule Collection: network_rules1. Rule: network_rule_collection1_rule1, 'Protocol': NULL, 'SourceIp': NULL, 'SourcePort': NULL, 'DestinationIp': NULL, 'DestinationPort': NULL, 'Action': NULL, 'Poli | AzureFirewallNetworkRule |
| 2024-11-06T16:05:00.166738+00:00 | /SUBSCRIPTIONS/C173F171-5FE0-4DA8-A696-1C299495246F/RESOURCEGROUPS/RG-VWAN-AVS-1024/PROVIDERS/MICROSOFT.NETWORK/AZUREFIREWALLS/FW-EAST | AzureFirewallNetworkRuleLog | {'msg': ICMP Type=8 request from 10.0.3.3:0 to 192.168.1.200:0. Action: Allow.. Policy: pol-east. Rule Collection Group: fw-example-rules. Rule Collection: network_rules1. Rule: network_rule_collection1_rule1, 'Protocol': NULL, 'SourceIp': NULL, 'SourcePort': NU

データを永続化する

CLI の起動時にファイルパスを指定することでデータをファイルに保存できます。

❯ duckdb azfw_logs.duckdb
v1.1.3 19864453f7
Enter ".help" for usage hints.
D
D .sh ls -lh azfw_logs.duckdb
-rw-rw-r-- 1 tsubasa tsubasa 19M Nov  7 08:27 azfw_logs.duckdb

CREATE TABLE でストレージアカウントのデータを読み込むとファイルサイズが大きくなっていることが確認できます。
次回起動時は同じようにファイルパスを指定することで保存したテーブルを読み込みます。もちろん既にファイルに存在するデータに対するクエリは、ストレージアカウントへの接続は不要なため、認証や HTTP トランスポートアダプターの設定は不要です。

まとめ

KQL よりも SQL の方が慣れている、他のツールと組み合わせたいみたいなときに役に立ちそうですね。Wasm 版もあるのでフロントエンドを付けてみると面白そうです。

Microsoft (有志)Publication

Microsoft Azureをはじめとする最新技術情報をお届けします。 ※このPublicationは日本マイクロソフトまたは米Microsoft所属社員による個人の見解であり、所属する組織の公式見解ではありません。 ※Publicationに参加希望の社員は @07JP27までご連絡ください。