Blobに保存した診断設定のログをAzure Synapse Analyticsで検索する

Blobに保存した診断設定のログを T-SQLで検索する 第44回 Tokyo Jazug Night 2023/10/19 長瀬 マキ(@yuma_prog)

自己紹介 ● ● ● ● ● 長瀬マキ（X:@yuma_prog） 文系出身エンジニア 2020年にWeb系ベンチャーに新卒入社 APIサービスの開発・運用＋SREを担当 2022~2023 6月 Azure移行

やりたかったこと ● ● ● ● Azure Monitorの診断設定で取得できるログを長期間保持したい ログの閲覧頻度は低い ログを検索できる必要がある 低コストで保持＋検索できる

診断設定とは ● Azure Monitorの機能 ● Azure プラットフォームのメトリックとログを異なる宛先に送信するた めの設定 ● プラットフォーム ログ ○ リソース ログ、アクティビティ ログ、Microsoft Entra ログ ● リソース ログは、診断設定で宛先を設定しないと収集されない

リソースログ

宛先を選ぶ ストレージアカウントを選ぶのが 一番簡単でローコスト

診断設定で宛先を ストレージアカウントにす ると…？

ログの種類ごとに Blob Containerが作成される

どんなパスで保存される？

insights-logs-{{ログ名}}/ ResourceId=/SUBSCRIPTIONS/ {{サブスクリプションID}}/ {{リソースグループ名}}/PROVIDERS/ MICROSOFT.WEB/SITES/{{リソース名}}/ y=2023/m=06/d=26/h=16/m=00/ PT1H.json 14クリック

ログの階層深すぎ問題 ● 深界15層は並の人間には耐えられない ● いちいちこの階層まで辿り着いてファイルをダウンロードして調査… はやっていられない ● 1時間ごとに新規ファイルができるので、時間が特定できない場合の 調査は困難

BLOBをSQLで検索したい！

そんなあなたに、 Azure Synapse Analytics

Azure Synapse Analytics ● ビッグデータのデータ分析などで使用されるサービス ● CosmosDBなどのリアルタイムデータだけでなく、ストレージアカウン トのデータを必要な時だけ読み込んで分析することも可能 ● Synapse SQLという、T-SQLでデータを分析できる機能がある ● 専用とサーバーレスの2つの消費モデル

Synapse Analyticsを選んだ理由 ● ● ● サーバーレスの場合クエリを実行するために処理されたデータの量に対しての 課金になる サーバーレスSQLプールは保持にコストがかからない ○ 突発的・低頻度でBlob Storageにストアしたログ調査が必要になるという ケースにあっていた Synapse Analyticsでいつでも調査できる状態を保持した場合、一時データを格 納するためのファイルシステムとして必要なAzure Data Lake Storage Gen2の 保持で課金が発生 ○ 1日1円以下しかかかっていなかったので、必要な時にリソースを作成するのではなく常に設置 しておくことに

BlobをT-SQLで検索する

BlobをT-SQLで検索するまでの流れ 1. ワークスペースを作る a. 2. 3. 4. 5. 6. Data Lake Storage Gen２も作成 サーバーレスSQLデータベースを作成する Synapseワークスペースから検索先ストレージへのアクセス許可を設定 Azure Storage にあるファイルに対してクエリを実行するための資格情報を設定 外部データソース（今回はBlob）をサーバーレスSQLデータベースに作成 T-SQLで検索

1. Synapseワークス ペースを作成 ● ● ● ● クイックスタート: Synapse ワークス ペースを作成する - Azure Synapse Analytics | Microsoft Learn の手順に従って作成 「Data Lake Storage Gen 2 の選 択」で入力したファイルシステム名 ＝ADLSGEN2のContainer名 今回は「demo」という名前の Containerができる 作成完了すると、組み込みのサー バーレスSQLプールが作成される （置いておく分には無料）

• https://learn.microsoft.com/ja-jp/azure/synapse-analytics/quickstart-create-workspace

BlobをT-SQLで検索するまでの流れ 1. ワークスペースを作る a. 2. 3. 4. 5. 6. Data Lake Storage Gen２も作成 サーバーレスSQLデータベースを作成する Synapseワークスペースから検索先ストレージへのアクセス許可を設定 Azure Storage にあるファイルに対してクエリを実行するための資格情報を設定 外部データソース（今回はBlob）をサーバーレスSQLデータベースに作成 T-SQLで検索

2.サーバーレスSQL データベースを作成 1. 2. 3. Synapse Studioを開く 左のサイドバーのDevelopを開 き、＋マークを押して「SQLスクリ プト」を選択 開いたスクリプトタブでCREATE DATABASE mydbnameを実行 a. `mydbname`に外部データ ソース（今回はストレージ アカウント）が紐づく クイックスタート: サーバーレス SQL プー ルを使用する - Azure Synapse Analytics | Microsoft Learn

• https://learn.microsoft.com/ja-jp/azure/synapse-analytics/quickstart-serverless-sql-pool#create-database

BlobをT-SQLで検索するまでの流れ 1. ワークスペースを作る a. 2. 3. 4. 5. 6. Data Lake Storage Gen２も作成 サーバーレスSQLデータベースを作成する Synapseワークスペースから検索先ストレージへのアクセス許可を設定 Azure Storage にあるファイルに対してクエリを実行するための資格情報を設定 外部データソース（今回はBlob）をサーバーレスSQLデータベースに作成 T-SQLで検索

3. 検索したいストレージ側でアクセス許可 Synapse → ストレージへのファイル読み取りのアクセス許可方法 1. ロールベースのアクセス制御 (RBAC) ★今回はこれ 2. アクセス制御リスト (ACL) 3. Shared Access Signature (SAS) Azure Synapse Analytics でサーバーレス SQL プールのストレージ アカウント アクセスを制御する

• https://learn.microsoft.com/ja-jp/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=user-identity#storage-permissions

RBACでのアクセス許可 1. 検索したいストレージアカウントをポータルで開く 2. サイドバーの「アクセス制御（IAM）」> 追加 > ロールの割り当ての追加 3. ストレージ BLOB データ閲覧者、ストレージ BLOB データ共同作成者、ストレージ BLOB データ所有者のいずれかを選択 4. アクセスの割り当て先「ユーザー、グループ、またはサービス プリンシパル」を選択し、「メ ンバーを選択する」からSynapse ワークスペースの名前を入力し選択 5. 「レビューと割り当て」で割り当てて完了

BlobをT-SQLで検索するまでの流れ 1. ワークスペースを作る a. 2. 3. 4. 5. 6. Data Lake Storage Gen２も作成 サーバーレスSQLデータベースを作成する Synapseワークスペースから検索先ストレージへのアクセス許可を設定 Azure Storage にあるファイルに対してクエリを実行するための資格情報を設 定 外部データソース（今回はBlob）をサーバーレスSQLデータベースに作成 T-SQLで検索

4. Blob Storageのファイルにクエリを実行するため の資格情報を設定 ● ● ● ファイルが一般公開されていない場合、サーバーレスSQLプールにログインした ユーザーには、Azure Storageのファイルにアクセスしてクエリを実行する権限 が必要 ユーザーID、Shared Access Signature（SAS）、サービスプリンシパル、マネー ジドID(今回はこれ)のいずれかの認可を使用 サーバーレベル資格情報、データベーススコープ資格情報の2つを設定 ○ Azure Storage 内のファイルにアクセスするのに使用する OPENROWSET 関数を実行するため に必要

4-1. Managed Identityでサーバーレベルの資格情報 CREATE CREDENTIAL [https://<storage_account>.blob.core.windows.net/<container_name>] WITH IDENTITY='Managed Identity' 今回は複数のコンテナー内のファイルを検索したかったので末尾の `/<container_name>`を削除して実行 Azure Synapse Analytics でサーバーレス SQL プールのストレージ アカウント アクセスを制御する

• https://learn.microsoft.com/ja-jp/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=managed-identity#server-level-credential

4-2. データベーススコープ資格情報 CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<strong password>' CREATE DATABASE SCOPED CREDENTIAL <db_credential_name> WITH IDENTITY = 'Managed Identity';

BlobをT-SQLで検索するまでの流れ 1. ワークスペースを作る a. 2. 3. 4. 5. 6. Data Lake Storage Gen２も作成 サーバーレスSQLデータベースを作成する Synapseワークスペースから検索先ストレージへのアクセス許可を設定 Azure Storage にあるファイルに対してクエリを実行するための資格情報を設定 外部データソース（今回はBlob）をサーバーレスSQLデータベースに作成 T-SQLで検索

5. 外部データソースを作成 CREATE EXTERNAL DATA SOURCE <external_data_source_name> WITH ( LOCATION = 'https://<storage_account>.blob.core.windows.net/<container>/<path>', CREDENTIAL = <db_credential_name> ) ● ● ● containerを跨ぎたいので/<container>/<path>は削除して実行 LOCATION を忘れると、どの階層から検索できるのかわからなくなる SELECT*FROM sys.external_data_sources で外部データソースの設定内容を 確認できる

BlobをT-SQLで検索するまでの流れ 1. ワークスペースを作る a. 2. 3. 4. 5. 6. Data Lake Storage Gen２も作成 サーバーレスSQLデータベースを作成する Synapseワークスペースから検索先ストレージへのアクセス許可を設定 Azure Storage にあるファイルに対してクエリを実行するための資格情報を設定 外部データソース（今回はBlob）をサーバーレスSQLデータベースに作成 T-SQLで検索

Blobのjsonを1行で出力
外部データソースの LOCATION以下の
検索したいパス指定

SELECT TOP 10 *
FROM OPENROWSET(
BULK 'insights-logs-{{ログ名}}/ResourceId=/SUBSCRIPTIONS/{{サブスクリプション
ID}}/{{リソースグループ名}}/PROVIDERS/MICROSOFT.WEB/SITES/{{リソース
名}}/y=2023/m=06/d=26/h=*/m=00/PT1H.json',
DATA_SOURCE = '<external_data_source_name>'
, FORMAT='CSV'
jsonファイル出力の呪文
, FIELDTERMINATOR ='0x0b'
, FIELDQUOTE = '0x0b'
, ROWTERMINATOR = '0x0b'
) with (doc nvarchar(max)) as rows

Blobのjsonを1行で出力 1行で出力されても見にくいし実用的ではない… jsonファイルのプロパティを列にしたい！

jsonのプロパティを列にして表示

App ServiceのIPSecurity Audit logsのPT1H.json（診断設定のログファイル）
{
"time": "2023-06-22T02:43:31.2986200Z",
"ResourceId": "xxxxxxxx",
"Category": "AppServiceIPSecAuditLogs",
"OperationName": "Authorization",
"Properties": {
"Result": "Allowed",
"CsHost": "xxxx.azurewebsites.net",
"ServiceEndpoint": "False",
"CIp": "xxxx",
"XForwardedFor": "",
"XForwardedHost": "",
"XAzureFDID": "",
"XFDHealthProbe": "",
"Details": "Allowed by xxxx rule."
}
}
{…

SELECT TOP 100 * FROM OPENROWSET( BULK '/insights-logs-appserviceipsecauditlogs/ResourceId=/SUBSCRIPTIONS/{{サブスクリプション ID}}/RESOURCEGROUPS/{{リソースグループ 名}}/PROVIDERS/MICROSOFT.WEB/SITES/{{リソース名 }}/y=2023/m=08/d=03/h=02/m=00/PT1H.json', DATA_SOURCE = ‘{{外部データソース名 }}’, FORMAT = 'csv', FIELDTERMINATOR ='0x0b', FIELDQUOTE = '0x0b' ) WITH (doc NVARCHAR(max)) as rows CROSS APPLY OPENJSON (doc) WITH ( Time NVARCHAR(255) '$.time', ResourceId NVARCHAR(255) '$.ResourceId', Category NVARCHAR(255) '$.Category', OperationName NVARCHAR(255) '$.OperationName', Result NVARCHAR(255) '$.Properties.Result', CsHost NVARCHAR(255) '$.Properties.CsHost', ServiceEndpoint NVARCHAR(255) '$.Properties.ServiceEndpoint', CIp NVARCHAR(255) '$.Properties.CIp', XForwardedFor NVARCHAR(255) '$.Properties.XForwardedFor', XForwardedHost NVARCHAR(255) '$.Properties.XForwardedHost', XAzureFDID NVARCHAR(255) '$.Properties.XAzureFDID', XFDHealthProbe NVARCHAR(255) '$.Properties.XFDHealthProbe', Details NVARCHAR(255) '$.Properties.Details' ) jsonプロパティを列、一つのログごとに行になるように表示する SQL

PT1H.json
{
"time": "2023-06-22T02:43:31.2986200Z",
"ResourceId": "xxxxxxxx",
"Category": "AppServiceIPSecAuditLogs",
"OperationName": "Authorization",
"Properties": {
"Result": "Allowed",
"CsHost": "xxxx.azurewebsites.net",
"ServiceEndpoint": "False",
"CIp": "xxxx",
"XForwardedFor": "",
"XForwardedHost": "",
"XAzureFDID": "",
"XFDHealthProbe": "",
"Details": "Allowed by xxxx rule."
}
}
{…

PT1H.jsonをもとにプロパティに名前を付けていく

CROSS APPLY OPENJSON (doc)
WITH (
Time NVARCHAR(255) '$.time',
ResourceId NVARCHAR(255) '$.ResourceId',
Category NVARCHAR(255) '$.Category',
OperationName NVARCHAR(255) '$.OperationName',
Result NVARCHAR(255) '$.Properties.Result',
CsHost NVARCHAR(255) '$.Properties.CsHost',
ServiceEndpoint NVARCHAR(255) '$.Properties.ServiceEndpoint',
CIp NVARCHAR(255) '$.Properties.CIp',
XForwardedFor NVARCHAR(255) '$.Properties.XForwardedFor',
XForwardedHost NVARCHAR(255)
'$.Properties.XForwardedHost',
XAzureFDID NVARCHAR(255) '$.Properties.XAzureFDID',
XFDHealthProbe NVARCHAR(255) '$.Properties.XFDHealthProbe',
Details NVARCHAR(255) '$.Properties.Details'
)

この調子で他のログのSQLもつくろう！

JSONプロパティに合わせてクエリをつくる 1. 2. 3. 検索したいログのPT1H.jsonをダウンロードする ダウンロードしたJSONファイルを開いてプロパティを確認する "operationName": "Microsoft.Cdn/Profiles/AccessLog/Write" →operationName NVARCHAR(100) '$.operationName' "properties": {"httpMethod":"POST"} →httpMethod NVARCHAR(10) '$.properties.httpMethod' のように手動でJSONファイル内の各プロパティの型をWITH句に書いていく

FrontDoor Access Log 35 項目

やってられねぇ…

助けてChatGPT！

ChatGPTに投げる下準備 ● ダウンロードしたPT1H.jsonから2オブジェクト程度抜き出す ○ ● 抜き出したJSONオブジェクトから、SubscriptionIDやKeyなどの情報を変換する か削除する ○ ● 1オブジェクトでは値が取れていない場合のため プロパティ自体は削除せず、 値から推測される型も変わらないように する（WITH句内で型の指 定が必要なため） GPT-4でBrowse with BingかプラグインでWebPilotを選択する

後は任せた！ あなたはAzure Synapse SQLの専門家です。 次の形式のJSONファイルの各項目を列にして各項目の値が行になるよう表示するSQLを書いて ください。 段階的に考えて、プロパティを省略せずに書いてください。 SELECTは [TOP 10 *]で、結果からJSONの内容は省略してください。 次のURLの「OPENJSON を使用して JSON ファイルに対してクエリを実行する」をベースにしてく ださい。 ## URL https://learn.microsoft.com/ja-jp/azure/synapse-analytics/sql/query-json-files#query-jso n-files-using-openjson ## JSON {{ここに先ほど機密情報を抜いたJSONを張り付ける}}

ChatGPTの出力の後処理 ● ● ● 回答からWITH 句の中の値をコピーしてクエリを作成 Identityなどを列名に指定しているとエラーになる場合があるのでその場合は [Identity]のように大かっこでくくる 無視された子階層のプロパティがあるとエラーになるので、「全プロパティを考慮 してください」などと追加で質問をする

BLOBのjsonをプロパティごとに列で表示しT-SQLで 検索、成功！ ● ● ● WHERE HttpStatusCode ='500' のように列名で絞って検索可能 BULKで/y=2023/m=07/d=23/**とすればサブフォルダを含めて検索できる Githubと連携してクエリを管理することも！

おわりに ● Synapse Analytics、欲しい情報が分散していて学習が大変だった ○ BlobStorageをデータソースにしてjsonファイルを検索する為に必要な情報がいろんな場 所に ● 大変すぎて記事を書いたので、これからの方は沼への入り口としてぜひ！ ○ Azure Synapse Analyticsの概要をこれから学ぶ人にはこの順で学習してみてほしい

• https://zenn.dev/yuma_prog/articles/synapse-entry-learn