Application Insights和Azure流分析查询自定义JSON属性 [英] Application Insights and Azure Stream Analytics Query a custom JSON property

问题描述

我正在尝试使用流分析将Application Insights导出到SQL表中.

I am trying to read my Application Insights export into a SQL Table using stream analytics.

这些是我试图捕获的Custom和Metric事件，因此JSON的一部分是custom或Metric事件的名称"(例如TestMethod1)，并且JSON如下所示:

These are Custom and Metric events I'm trying to capture so part of the JSON is the "name" of the custom or metric event (e.g. TestMethod1) and the JSON looks like this:

{
  "metric": [ ],
  "internal": 
  .. host of other json data...
    "context": {
      "custom": {
      "metrics": 
      [
        {
          "TestMethod1": 
          {
            "value": 42.8207,
            "count": 1.0,
            "min": 42.8207,
            "max": 42.8207,
            "stdDev": 0.0
          }
        }
      ]
    }
  }
}

使用类似语言的Sql语言，我尝试使用与下面类似的语法将数据传输到SQL表中(这仍然是我尝试各种方式和方法来实现此目标……)

Using analytics Sql like language I try and transfer my data to a SQL Table using a syntax similar to below (this is still me trying various ways and means to achieve this...)

SELECT A.internal.data.id as id
, dimensions.ArrayValue.EventName as eventName
, metrics.[value] as [value]
, A.context.data.eventTime as eventtime
, metrics.count as [count]
INTO
  MetricsOutput
FROM AppMetrics A
CROSS APPLY GetElements(A.[context].[custom].[metrics[0]]) as metrics
 CROSS APPLY GetElements(A.[context].[custom].[dimensions]) as dimensions

问题是，由于使用自定义事件名称，所以没有填充我的[value]列和[count]列.目前，我在metrics.value上收到错误消息不存在具有该名称的列".

The problem is, due to the custom event name, neither my [value] nor [count] columns are being populated. At the moment I'm getting an error "column with such name does not exist" on metrics.value.

关于如何实现这一目标的任何想法?

Any ideas on how I can achieve this?

我想输出几种不同方法的指标和自定义事件，列名称并不重要.但是从应用数据洞察导出的一个Blob文件将包含5个或6个不同的自定义事件和指标的事件.

I want to ouput my metrics and custom events for several different methods and the column name is not important. but one blob file from the app insights export will contain events for 5 or 6 different custom events and metrics.

所以我可以有一个包含TestMethod1，TestMethod2和TestMethod3的blob文件，并希望将该一个文件解析到表中，而不必求助于代码和辅助角色.

So i could have one blob file containing TestMethod1, TestMethod2 and TestMethod3 and want to parse that one file into the table without having to resort to code and a worker role.

致谢

推荐答案

您不希望对尺寸使用CROSS APPLY，因为这样会将每个尺寸放在不同的行中.您想要的是将所有内容整理成一行.为此，请使用下面演示的功能GetRecordPropertyValue和GetArrayElement.

You dont' want to use CROSS APPLY for your dimensions because then it will put each dimension on a different row. What you want is to flatten everything out into a single row. To do this use the functions GetRecordPropertyValue and GetArrayElement as demoed below.

JSON格式:

{
    "event": [{...}],
    "internal": {...},
    "context": {
        ...
        "data": {
            "isSynthetic": false,
            "eventTime": "2015-12-14T17:38:35.37Z",
            "samplingRate": 100.0
        },
        ...
        "custom": {
            "dimensions": 
            [
                { "MyDimension1": "foo" }, 
                { "MyDimension2": "bar" }
            ],
            "metrics": [{
                "MyMetric1": {
                    "value": 0.39340400471142523,
                    "count": 1.0,
                    "min": 0.39340400471142523,
                    "max": 0.39340400471142523,
                    "stdDev": 0.0
                }
            }]
        },
        ...
    }
}

查询:

SELECT
    MySource.internal.data.id AS ID,
    MySource.context.data.eventTime AS EventTime,
    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 0), 'MyDimension1') AS MyDimension1,
    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 1), 'MyDimension2') AS MyDimension2,
    avg(CASE WHEN MyMetrics.arrayvalue.MyMetric1.value IS NULL THEN 0 ELSE   MyMetrics.arrayvalue.MyMetric1.value END) as MetricAverage
INTO
   [output-stream]
FROM
  [input-stream] MySource
OUTER APPLY 
    GetElements(MySource.context.custom.metrics) as MyMetrics
GROUP BY 
    SlidingWindow(minute, 1), 
    MySource.internal.data.id AS ID,
    MySource.context.data.eventTime AS EventTime,
    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 0), 'MyDimension1'),
    GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 1), 'MyDimension2')

这篇关于Application Insights和Azure流分析查询自定义JSON属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！