🌟

ログインジェスト API で CommonSecurityLog テーブルにログを書き込む

k.sato

2023/11/07に公開

はじめに

Sentinel でプロキシやファイアウォールのログを格納する CommonSecurityLog テーブルは
ログインジェスト API 経由で直接ログを書き込むことができ、ログ欠損時のリカバリなどが可能です。

しかしながら、CommonSecurityLog テーブルを含む Azure 組み込みのテーブル (標準テーブル) にログインジェスト API 経由でログを書き込むための設定ガイドがどこにもありません。
なので、検証・動作確認しました。

データ収集ルール設定

下記を参考にします。

API に対して認証を行う Microsoft Entra アプリケーションを作成します。
データを受信するためのデータコレクションエンドポイント (DCE) を作成する。
データをターゲットテーブルに転送するデータ収集ルール (DCR) を作成する。
AD アプリケーションに DCR へのアクセス権を付与する。
サンプルコードを使用して、ログインジェスト API を使用してデータを送信し、動作確認します。

1,2 はドキュメントそのままなので、本記事では割愛します。

3 の DCR を作成していきます。カスタムテーブルの場合、テーブル作成時に DCR を同時に作成できるのですが、Azure 組み込みのテーブルはできないので、ARM テンプレートを使います。カスタムテーブル用のサンプルはこちらにあるのですが、いくつか手直しをしていきます。

変更が必要となる箇所

streamDeclarations の stream 名の箇所を Custom-CommonSecurityLogStream に変更 (Custom- のプレフィックスがついていればよいので後続で一致していればハイフン以降は任意)
columns に CommonSecurityLog テーブルのカラムを定義 (一部のみしか使用しない場合は必要なカラムだけ)
dataFlows の streams に Custom-CommonSecurityLogStream を入力
outputStream に Microsoft-CommonSecurityLog を入力 (これは固定)

完成形は以下です。

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "dataCollectionRuleName": {
            "type": "String"
        },
        "location": {
            "type": "String"
        },
        "workspaceResourceId": {
            "type": "String"
        },
        "endpointResourceId": {
            "type": "String"
        },
		"workspaceName": {
            "type": "String"
        }
    },
    "variables": {},
    "resources": [
        {
            "type": "Microsoft.Insights/dataCollectionRules",
            "apiVersion": "2022-06-01",
            "name": "[parameters('dataCollectionRuleName')]",
            "location": "[parameters('location')]",
            "properties": {
                "dataCollectionEndpointId": "[parameters('endpointResourceId')]",
                "streamDeclarations": {
                    "Custom-CommonSecurityLogStream": {
                        "columns": [
                            {"name": "TimeGenerated", "type": "datetime"},
                            {"name": "DeviceVendor", "type": "string"},
                            {"name": "DeviceProduct", "type": "string"},
                            {"name": "DeviceVersion", "type": "string"},
                            {"name": "DeviceEventClassID", "type": "string"},
                            {"name": "Activity", "type": "string"},
                            {"name": "LogSeverity", "type": "string"},
                            {"name": "OriginalLogSeverity", "type": "string"},
                            {"name": "AdditionalExtensions", "type": "string"},
                            {"name": "DeviceAction", "type": "string"},
                            {"name": "ApplicationProtocol", "type": "string"},
                            {"name": "EventCount", "type": "int"},
                            {"name": "DestinationDnsDomain", "type": "string"},
                            {"name": "DestinationServiceName", "type": "string"},
                            {"name": "DestinationTranslatedAddress", "type": "string"},
                            {"name": "DestinationTranslatedPort", "type": "int"},
                            {"name": "CommunicationDirection", "type": "string"},
                            {"name": "DeviceDnsDomain", "type": "string"},
                            {"name": "DeviceExternalID", "type": "string"},
                            {"name": "DeviceFacility", "type": "string"},
                            {"name": "DeviceInboundInterface", "type": "string"},
                            {"name": "DeviceNtDomain", "type": "string"},
                            {"name": "DeviceOutboundInterface", "type": "string"},
                            {"name": "DevicePayloadId", "type": "string"},
                            {"name": "ProcessName", "type": "string"},
                            {"name": "DeviceTranslatedAddress", "type": "string"},
                            {"name": "DestinationHostName", "type": "string"},
                            {"name": "DestinationMACAddress", "type": "string"},
                            {"name": "DestinationNTDomain", "type": "string"},
                            {"name": "DestinationProcessId", "type": "int"},
                            {"name": "DestinationUserPrivileges", "type": "string"},
                            {"name": "DestinationProcessName", "type": "string"},
                            {"name": "DestinationPort", "type": "int"},
                            {"name": "DestinationIP", "type": "string"},
                            {"name": "DeviceTimeZone", "type": "string"},
                            {"name": "DestinationUserID", "type": "string"},
                            {"name": "DestinationUserName", "type": "string"},
                            {"name": "DeviceAddress", "type": "string"},
                            {"name": "DeviceName", "type": "string"},
                            {"name": "DeviceMacAddress", "type": "string"},
                            {"name": "ProcessID", "type": "int"},
                            {"name": "EndTime", "type": "datetime"},
                            {"name": "ExternalID", "type": "int"},
                            {"name": "ExtID", "type": "string"},
                            {"name": "FileCreateTime", "type": "string"},
                            {"name": "FileHash", "type": "string"},
                            {"name": "FileID", "type": "string"},
                            {"name": "FileModificationTime", "type": "string"},
                            {"name": "FilePath", "type": "string"},
                            {"name": "FilePermission", "type": "string"},
                            {"name": "FileType", "type": "string"},
                            {"name": "FileName", "type": "string"},
                            {"name": "FileSize", "type": "int"},
                            {"name": "ReceivedBytes", "type": "long"},
                            {"name": "Message", "type": "string"},
                            {"name": "OldFileCreateTime", "type": "string"},
                            {"name": "OldFileHash", "type": "string"},
                            {"name": "OldFileID", "type": "string"},
                            {"name": "OldFileModificationTime", "type": "string"},
                            {"name": "OldFileName", "type": "string"},
                            {"name": "OldFilePath", "type": "string"},
                            {"name": "OldFilePermission", "type": "string"},
                            {"name": "OldFileSize", "type": "int"},
                            {"name": "OldFileType", "type": "string"},
                            {"name": "SentBytes", "type": "long"},
                            {"name": "EventOutcome", "type": "string"},
                            {"name": "Protocol", "type": "string"},
                            {"name": "Reason", "type": "string"},
                            {"name": "RequestURL", "type": "string"},
                            {"name": "RequestClientApplication", "type": "string"},
                            {"name": "RequestContext", "type": "string"},
                            {"name": "RequestCookies", "type": "string"},
                            {"name": "RequestMethod", "type": "string"},
                            {"name": "ReceiptTime", "type": "string"},
                            {"name": "SourceHostName", "type": "string"},
                            {"name": "SourceMACAddress", "type": "string"},
                            {"name": "SourceNTDomain", "type": "string"},
                            {"name": "SourceDnsDomain", "type": "string"},
                            {"name": "SourceServiceName", "type": "string"},
                            {"name": "SourceTranslatedAddress", "type": "string"},
                            {"name": "SourceTranslatedPort", "type": "int"},
                            {"name": "SourceProcessId", "type": "int"},
                            {"name": "SourceUserPrivileges", "type": "string"},
                            {"name": "SourceProcessName", "type": "string"},
                            {"name": "SourcePort", "type": "int"},
                            {"name": "SourceIP", "type": "string"},
                            {"name": "StartTime", "type": "datetime"},
                            {"name": "SourceUserID", "type": "string"},
                            {"name": "SourceUserName", "type": "string"},
                            {"name": "EventType", "type": "int"},
                            {"name": "DeviceEventCategory", "type": "string"},
                            {"name": "DeviceCustomIPv6Address1", "type": "string"},
                            {"name": "DeviceCustomIPv6Address1Label", "type": "string"},
                            {"name": "DeviceCustomIPv6Address2", "type": "string"},
                            {"name": "DeviceCustomIPv6Address2Label", "type": "string"},
                            {"name": "DeviceCustomIPv6Address3", "type": "string"},
                            {"name": "DeviceCustomIPv6Address3Label", "type": "string"},
                            {"name": "DeviceCustomIPv6Address4", "type": "string"},
                            {"name": "DeviceCustomIPv6Address4Label", "type": "string"},
                            {"name": "DeviceCustomFloatingPoint1", "type": "real"},
                            {"name": "DeviceCustomFloatingPoint1Label", "type": "string"},
                            {"name": "DeviceCustomFloatingPoint2", "type": "real"},
                            {"name": "DeviceCustomFloatingPoint2Label", "type": "string"},
                            {"name": "DeviceCustomFloatingPoint3", "type": "real"},
                            {"name": "DeviceCustomFloatingPoint3Label", "type": "string"},
                            {"name": "DeviceCustomFloatingPoint4", "type": "real"},
                            {"name": "DeviceCustomFloatingPoint4Label", "type": "string"},
                            {"name": "DeviceCustomNumber1", "type": "int"},
                            {"name": "FieldDeviceCustomNumber1", "type": "long"},
                            {"name": "DeviceCustomNumber1Label", "type": "string"},
                            {"name": "DeviceCustomNumber2", "type": "int"},
                            {"name": "FieldDeviceCustomNumber2", "type": "long"},
                            {"name": "DeviceCustomNumber2Label", "type": "string"},
                            {"name": "DeviceCustomNumber3", "type": "int"},
                            {"name": "FieldDeviceCustomNumber3", "type": "long"},
                            {"name": "DeviceCustomNumber3Label", "type": "string"},
                            {"name": "DeviceCustomString1", "type": "string"},
                            {"name": "DeviceCustomString1Label", "type": "string"},
                            {"name": "DeviceCustomString2", "type": "string"},
                            {"name": "DeviceCustomString2Label", "type": "string"},
                            {"name": "DeviceCustomString3", "type": "string"},
                            {"name": "DeviceCustomString3Label", "type": "string"},
                            {"name": "DeviceCustomString4", "type": "string"},
                            {"name": "DeviceCustomString4Label", "type": "string"},
                            {"name": "DeviceCustomString5", "type": "string"},
                            {"name": "DeviceCustomString5Label", "type": "string"},
                            {"name": "DeviceCustomString6", "type": "string"},
                            {"name": "DeviceCustomString6Label", "type": "string"},
                            {"name": "DeviceCustomDate1", "type": "string"},
                            {"name": "DeviceCustomDate1Label", "type": "string"},
                            {"name": "DeviceCustomDate2", "type": "string"},
                            {"name": "DeviceCustomDate2Label", "type": "string"},
                            {"name": "FlexDate1", "type": "string"},
                            {"name": "FlexDate1Label", "type": "string"},
                            {"name": "FlexNumber1", "type": "int"},
                            {"name": "FlexNumber1Label", "type": "string"},
                            {"name": "FlexNumber2", "type": "int"},
                            {"name": "FlexNumber2Label", "type": "string"},
                            {"name": "FlexString1", "type": "string"},
                            {"name": "FlexString1Label", "type": "string"},
                            {"name": "FlexString2", "type": "string"},
                            {"name": "FlexString2Label", "type": "string"},
                            {"name": "RemoteIP", "type": "string"},
                            {"name": "RemotePort", "type": "string"},
                            {"name": "MaliciousIP", "type": "string"},
                            {"name": "ThreatSeverity", "type": "int"},
                            {"name": "IndicatorThreatType", "type": "string"},
                            {"name": "ThreatDescription", "type": "string"},
                            {"name": "ThreatConfidence", "type": "string"},
                            {"name": "ReportReferenceLink", "type": "string"},
                            {"name": "MaliciousIPLongitude", "type": "real"},
                            {"name": "MaliciousIPLatitude", "type": "real"},
                            {"name": "MaliciousIPCountry", "type": "string"},
                            {"name": "Computer", "type": "string"},
                            {"name": "SourceSystem", "type": "string"},
                            {"name": "SimplifiedDeviceAction", "type": "string"}
                        ]
                    }
                },
                "dataSources": {},
                "destinations": {
                    "logAnalytics": [
                        {
                            "workspaceResourceId": "[parameters('workspaceResourceId')]",
                            "name": "[parameters('workspaceName')]"
                        }
                    ]
                },
                "dataFlows": [
                    {
                        "streams": [
                            "Custom-CommonSecurityLogStream"
                        ],
                        "destinations": [
                            "[parameters('workspaceName')]"
                        ],
                        "transformKql": "source",
                        "outputStream": "Microsoft-CommonSecurityLog"
                    }
                ]
            }
        }
    ]
}

カスタムテンプレートからデプロイしていきます。

デプロイ成功後、JSON ビューで正しく設定されていることを確認します。immutableId はあとで使うのでコピーしておきます。

このあと、[4. AD アプリケーションに DCR へのアクセス権を付与する。] を忘れずに実施します。(ここもドキュメントに記載があるので割愛します。)

動作確認

PowerShell を使用して API 経由でログを書き込みます。以下のサンプルコードを使用します。

$streamName には Custom-CommonSecurityLogStream を指定
$staticData には書き込みたいデータを json 形式で記載

### Step 0: Set variables required for the rest of the script.

# information needed to authenticate to AAD and obtain a bearer token
$tenantId = "00000000-0000-0000-00000000000000000" #Tenant ID the data collection endpoint resides in
$appId = " 000000000-0000-0000-00000000000000000" #Application ID created and granted permissions
$appSecret = "0000000000000000000000000000000000000000" #Secret created for the application

# information needed to send data to the DCR endpoint
$dceEndpoint = "https://logs-ingestion-xxxx.xxxxxxx.ingest.monitor.azure.com" #the endpoint property of the Data Collection Endpoint object
$dcrImmutableId = "dcr-00000000000000000000000000000000" #the immutableId property of the DCR object
$streamName = "Custom-CommonSecurityLogStream" #name of the stream in the DCR that represents the destination table

### Step 1: Obtain a bearer token used later to authenticate against the DCE.

$scope= [System.Web.HttpUtility]::UrlEncode("https://monitor.azure.com//.default")   
$body = "client_id=$appId&scope=$scope&client_secret=$appSecret&grant_type=client_credentials";
$headers = @{"Content-Type"="application/x-www-form-urlencoded"};
$uri = "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token"

$bearerToken = (Invoke-RestMethod -Uri $uri -Method "Post" -Body $body -Headers $headers).access_token


### Step 2: Create some sample data. 

$currentTime = Get-Date ([datetime]::UtcNow) -Format O
$staticData = @"
[
{
    "TimeGenerated": "$currentTime",
    "DeviceVendor": "Mock-test",
    "DeviceProduct": "Mock",
    "DeviceVersion": "1.0",
    "DeviceEventClassID": "end",
    "Activity": "TRAFFIC",
    "LogSeverity": "1",
    "ExtID": "ing-0",
    "Computer": "Computer1"
},
{
    "TimeGenerated": "$currentTime",
    "DeviceVendor": "Mock-test",
    "DeviceProduct": "Mock",
    "DeviceVersion": "1.0",
    "DeviceEventClassID": "end",
    "Activity": "TRAFFIC",
    "LogSeverity": "1",
    "ExtID": "ing-1",
    "Computer": "Computer1"
}
]
"@;


### Step 3: Send the data to the Log Analytics workspace via the DCE.

$body = $staticData;
$headers = @{"Authorization"="Bearer $bearerToken";"Content-Type"="application/json"};
$uri = "$dceEndpoint/dataCollectionRules/$dcrImmutableId/streams/$($streamName)?api-version=2021-11-01-preview"

$uploadResponse = Invoke-RestMethod -Uri $uri -Method "Post" -Body $body -Headers $headers

上記を実行すると、以下のようにログが書き込み出来ているのが確認できます。

不具合 (調査中)

int 型や long 型のカラムに対する書き込みができていません。
2 バイト文字が文字化けします。 (文字コード変えても化ける)

今回は動作確認のみですが、本格的にログ欠損時に使用することを考慮する場合、 PowerShell スクリプト内の json データをファイルから読み取る形式にする必要があります。また、ログインジェスト API はスロットリングがあるので、こちらも注意が必要です。

Microsoft (有志)Publication

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