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

14K Views

October 25, 23

スライド概要

第44回 Tokyo Jazug Night の発表スライド
・診断設定のログをストレージアカウントに保存する
・保存された複数JSONファイルをAzure Synapse Analyticsを使ってT-SQLで検索する

profile-image

バックエンドエンジニア。  主にC#, Azure, Terraform, GitHub Actionsをいじっています。

シェア

またはPlayer版

埋め込む »CMSなどでJSが使えない場合

(ダウンロード不可)

関連スライド

各ページのテキスト
1.

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

2.

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

3.

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

4.

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

5.

リソースログ

6.

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

7.

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

8.

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

9.

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

10.

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

11.

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

12.

BLOBをSQLで検索したい!

13.

そんなあなたに、 Azure Synapse Analytics

14.

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

15.

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

16.

BlobをT-SQLで検索する

17.

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

18.

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

19.

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

20.

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

21.

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

22.

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

23.

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

24.

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

25.

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

26.

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 プールのストレージ アカウント アクセスを制御する

27.

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

28.

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

29.

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 で外部データソースの設定内容を 確認できる

30.

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

31.
[beta]
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

32.

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

33.

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

34.
[beta]
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."
}
}
{…

35.

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

36.
[beta]
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'
)

37.

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

38.

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句に書いていく

39.

FrontDoor Access Log 35 項目

40.

やってられねぇ…

41.

助けてChatGPT!

42.

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

43.

後は任せた! あなたは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を張り付ける}}

44.

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

46.

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

47.

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