Application Insights and Azure Stream Analytics Query a custom JSON property

Question

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

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
          }
        }
      ]
    }
  }
}

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

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?

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.

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.

Regards

Answer 1

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 format:

{
    "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
                }
            }]
        },
        ...
    }
}

Query:

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')

Answer 2

For Custom Dimensions to be added as columns in a single row, this worked for me:

Under "Job Topology -> Functions" section of the Stream Analytics Job.

First,

add a custom function with the following properties

• Function Alias - flattenCustomDimensions (can be anything)
• Function Type - Javascript UDF
• Output Type - any

And replace the main function with the following

function main(dimensions) {
  let output = {};
  for(let i in dimensions) {
    let dim = dimensions[i];
    for(let key in dim) {
      output[key] = dim[key];
    }
  }
  return output;
}

Second,

Form the query as follows:

If we have custom dimensions like

Row1:

"context": {
  ...
  "custom": {
    "dimensions": [
      { "Dimension1": "Value1" },
      { "Dimension2": "Value2" }
    ]
  }
}

Row2:

"context": {
  ...
  "custom": {
    "dimensions": [
      { "Dimension1": "Value1.2" },
      { "Dimension3": "Value3" }
    ]
  }
}

Query would be

WITH temp as (
SELECT
    *,
    UDF.flattenCustomDimensions(I.context.custom.dimensions) as dim
    FROM [Input] as I
)

SELECT
    Dim1 = temp.dim.Dimension1,
    Dim2 = temp.dim.Dimension2,
    Dim3 = temp.dim.Dimension3
INTO [Output]
FROM temp

The output table would be

DIM1     |  DIM2  |  DIM3
----------------------------
Value1   | Value2 | null
Value1.2 | null   | Value3