Application Insigts のリクエストデータを SQL Database に格納する

アプリケーションのログデータを永続化/可視化してくれる Application Insights だが、本ブログでは今まで過去2回にわたり、Web アプリのログに対して Stream Analytics を介し Power BI を利用した可視化を紹介していた。

上記は Java の Web アプリで行っていたが、今回は ASP.NET を利用したログ出力を行い、SQL Database へと Application Insights のログを永続化する方法を紹介する。

ASP.NET で Application Insights を利用する方法

こちらは非常にシンプルだ。以下のサイトを参考に Application Insights の SDK を有効化するだけでアプリケーションの監視が可能になる*1。
Application Insights SDK を追加して ASP.NET アプリを監視する

上記のステップを通して Application Insights の SDK を有効化したアプリケーションを、Azure Resource Manager で作成した Windows Server 2012 R2 仮想マシンの IIS にデプロイした。

Application Insights のログを SQL Database に出力するまでにステップ

以下の手順が必要になる。連続エクスポートの設定については特に記載は不要だと思うので、他の手順について紹介する。

Application Insights の連続エクスポートの有効化
ログ格納用に SQL Database のテーブル作成
Stream Analytics の設定

今回はサーバによって受信された要求である Request データを取り扱う。こちらは以下のようなファイル名でデータが格納される。

"appinsights name"_"your instrument key"/Requests/{date}/{time}/93ddecba-d09f-4027-aaed-f773de32495e_20151108_085830.blob

Request データのファイルの中身は以下のような形式だ。

{
    "context": {
        "application": {
            "version": "Unknown"
        }, 
        "cloud": {}, 
        "custom": {
            "dimensions": [], 
            "metrics": []
        }, 
        "data": {
            "eventTime": "2015-11-08T08:11:49.5138835Z", 
            "isSynthetic": false, 
            "samplingRate": 100.0
        }, 
        "device": {
            "browser": "Internet Explorer", 
            "browserVersion": "Internet Explorer 11.0", 
            "deviceModel": "Virtual Machine", 
            "deviceName": "Virtual Machine", 
            "id": "(my vm name)", 
            "locale": "en-US", 
            "network": "6", 
            "oemName": "Microsoft Corporation", 
            "os": "Windows", 
            "osVersion": "Windows 10", 
            "roleInstance": "(my vm name)", 
            "screenResolution": {}, 
            "type": "PC"
        }, 
        "location": {
            "city": "Tokyo", 
            "clientip": "119.242.23.0", 
            "continent": "Asia", 
            "country": "Japan", 
            "province": "Tōkyō"
        }, 
        "operation": {
            "id": "3579395009003726603", 
            "name": "GET Account/Register", 
            "parentId": "3579395009003726603"
        }, 
        "serverDevice": {}, 
        "session": {
            "id": "C1A7FDE1-AD1B-4D65-9F05-3DA2421E88FB", 
            "isFirst": false
        }, 
        "user": {
            "accountAcquisitionDate": "2015-11-08T04:20:59Z", 
            "anonAcquisitionDate": "0001-01-01T00:00:00Z", 
            "anonId": "16BADE4E-E5E6-4652-B518-8F012442C25D", 
            "authAcquisitionDate": "0001-01-01T00:00:00Z", 
            "isAuthenticated": false
        }
    }, 
    "internal": {
        "data": {
            "documentVersion": "1.61", 
            "id": "fcac8a5b-7615-4c53-9e51-28ccd6f40eac"
        }
    }, 
    "request": [
        {
            "count": 1, 
            "durationMetric": {
                "count": 1.0, 
                "max": 373751007.0, 
                "min": 373751007.0, 
                "sampledValue": 373751007.0, 
                "stdDev": 0.0, 
                "value": 373751007.0
            }, 
            "id": "3579395009003726603", 
            "name": "GET Account/Register", 
            "responseCode": 200, 
            "success": true, 
            "url": "http://(my vm public ip)/Account/Register", 
            "urlData": {
                "base": "/Account/Register", 
                "hashTag": "", 
                "host": "<my vm public ip>"
            }
        }
    ]
}

シンプルな JSON 形式だが、request 部分は中身が配列になっているので、Stream Analytics でクエリを書く際に注意が必要なポイントになる。

ログ格納用に SQL Database のテーブル作成

自分のサブスクリプションである必要はないが、SQL Database インスタンスを一つ作成（インスタンスサイズは何でも良いが、Standard: S0 以上を推奨する）し、以下のテーブルを作成する。

CREATE TABLE [dbo].[RequestTable](
    [eventTime] [datetime] NOT NULL,
    [isSynthetic] [nvarchar](50) NULL,
    [samplingRate] [real] NULL,

    [browser] [nvarchar](50) NULL,
    [browserVersion] [nvarchar](50) NULL,
    [deviceModel] [nvarchar](50) NULL,
    [deviceName] [nvarchar](50) NULL,
    [deviceId] [nvarchar](50) NULL,
    [locale] [nvarchar](50) NULL,
    [network] [nvarchar](50) NULL,
    [oemName] [nvarchar](50) NULL,
    [os] [nvarchar](50) NULL,
    [osVersion] [nvarchar](50) NULL,

    [roleInstance] [nvarchar](50) NULL,
    [type] [nvarchar](50) NULL,

    [clientIp] [nvarchar](50) NULL,
    [continent] [nvarchar](50) NULL,
    [country] [nvarchar](50) NULL,
    [province] [nvarchar](50) NULL,
    [city] [nvarchar](50) NULL,

    [operationId] [nvarchar](50) NULL,
    [operationName] [nvarchar](50) NULL,
    [parentId] [nvarchar](50) NULL,

    [sessionId] [nvarchar](max) NULL,
    [sessionIsFirst] [nvarchar](50) NULL,

    [requestName] [nvarchar](50) NULL,
    [responseCode] [nvarchar](50) NULL,
    [requestSuccess] [nvarchar](50) NULL,
    [requestUrl] [nvarchar](50) NULL
)

CREATE CLUSTERED INDEX [pvTblIdx] ON [dbo].[RequestTable]
(
    [eventTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

全ての JSON データを格納するテーブル構造となっていはいないが、もし必要な場合は自分で修正してほしい。

Stream Analytics の設定

まずは Stream Analytics の input として以下の形式で BLOB を設定する。

名前：input-blob
ソースタイプ：データストリーム
ソース：BLOBストレージ
パスパターン：(appinsights name)_(your instrument key)/Requests/{date}/{time}
日付の形式：YYYY-MM-DD
時刻の形式：HH

次に、Stream Analytics の output として以下を設定する。サーバ名、データベース名、ユーザ名、パスワード等は適切に設定すること。

名前：output-sqldb
シンク：SQL データベース
テーブル：RequestTable

上記の設定後、以下のクエリを Stream Analytics に設定する。

    SELECT 
      flat.ArrayValue.name as requestName
      ,flat.ArrayValue.responseCode as responseCode
      ,flat.ArrayValue.success as requestSuccess
      ,flat.ArrayValue.url as requestUrl

      ,A.context.data.eventTime as eventTime
      ,A.context.data.isSynthetic as isSynthetic
      ,A.context.data.samplingRate as samplingRate

      ,A.context.device.browser as browser
      ,A.context.device.browserVersion as browserVersion
      ,A.context.device.deviceModel as deviceModel
      ,A.context.device.deviceName as deviceName
      ,A.context.device.id as deviceId
      ,A.context.device.locale as locale
      ,A.context.device.network as network
      ,A.context.device.oemName as oemName
      ,A.context.device.os as os
      ,A.context.device.osVersion as osVersion
      ,A.context.device.roleInstance as roleInstance
      ,A.context.device.type as type

      ,A.context.location.clientip as clientIp
      ,A.context.location.continent as continent
      ,A.context.location.country as country
      ,A.context.location.province as province
      ,A.context.location.city as city

      ,A.context.operation.id as operationId
      ,A.context.operation.name as operationName
      ,A.context.operation.parentId as parentId

      ,A.context.session.id as sessionId
      ,A.context.session.isFirst as sessionIsFirst
    INTO
      [output-sqldb]
    FROM [input-blob] A
    CROSS APPLY GetElements(A.request) AS flat

上記の完了後、Stream Analytics ジョブを実行すると SQL Database 側にデータが格納されているはずだ。

実行結果の確認

今回は Visual Studio の SQL Server オブジェクトエクスプローラから確認する。サーバに接続し、テーブルを右クリックしてデータ表示を選択した結果は以下となる。
f:id:waritohutsu:20151108213952p:plain

さらに以下のクエリを発行し、国別のアクセスを確認してみる。

SELECT COUNTRY, COUNT(COUNTRY) as NUM FROM [dbo].[RequestTable] GROUP BY COUNTRY

f:id:waritohutsu:20151108214004p:plain

なぜか United States のリクエストがあり若干衝撃だが、こういったデータの取得も可能になる。

*1:ただし、依存関係の追跡 (および IIS パフォーマンスカウンター)も監視する場合には追加で手順が必要な点も記載がある

normalian blog

Let's talk about Microsoft Azure, ASP.NET and Java!