none
Use Stream Analytics to process exported data from Application Insights RRS feed

  • Question

  • I am referring this to export AI data to azure storage, and using Stream analytics to supply the data to PowerBI for analytics.

    Question:

    Why Continuous export stored the custom properties as nested json array, though the custom properties are related to single record? it is very difficult to flatten the custom dimensions in single record with different column for each dimension for example Json looks like this: 

    {

    "event": [

        {

          "name": "ProductPolicy",

          "count": 1

        }

      ],

      "internal": {

        "data": {

          "id": "82495a94-b5f1-11e6-8a60-19b04cb4e9e0",

          "documentVersion": "1.61"

        }

      },

      "context": {

        "data": {

          "eventTime": "2016-11-29T05:05:21.897Z",

          "isSynthetic": false,

          "samplingRate": 100.0

        },

        "device": {

          "type": "PC",

          "screenResolution": {}

        },

        "user": { "isAuthenticated": false },

        "session": { "isFirst": false },

        "operation": {},

        "location": {

          "clientip": "10.10.10.0",

          "continent": "Asia",

          "country": "India",

          "province": "Maharashtra",

          "city": "Pune"

        },

        "custom": {

          "dimensions": [

            { "ApplicationID": "TestApp" },        

            { "UserId": "100" }

          ]

        }

      }

    }

    If we use query in the example for custom dimension it results in two records like this.

    WITH flat AS (
        SELECT
          MySource.context.data.eventTime as eventTime,
          ApplicationId = MyDimension.ArrayValue.ApplicationID,
          UserId = MyDimension.ArrayValue.UserId,
        FROM MySource
        OUTER APPLY GetArrayElements(MySource.context.custom.dimensions) MyDimension
        )
        SELECT
         eventTime,
         ApplicationId,
         UserId
        INTO AIOutput
        FROM flat

    Event                 ApplicationID    UserId

    ProductPolicy    TestApp                  null

    ProductPolicy     null                       100

    I want output to result in single record like this:

    Event                 ApplicationID    UserId

    ProductPolicy    TestApp                  100

     

    Niranjan

    Friday, December 23, 2016 12:01 PM

Answers

All replies

  • What is the use of storing the custom dimensions in array? where each property stored in different array location like this:

     {
          "dimensions": [

            { "ApplicationID": "TestApp" },        

            { "UserId": "100" }

          ]
    }     

    Instead it should be like this:

    {
          "dimensions": [
            { "ApplicationID": "TestApp", "UserId": "100" }
          ]
    }

    Is it a bug or is there any reason behind storing the properties in this way?

    I want to use the Application insights data in PowerBI for showing the Analytics. Data is of no use without custom properties for showing various analytics.


    Niranjan


    Monday, December 26, 2016 8:23 AM
  • I decided to use fixed no of custom properties to access them by their Index. Assuming that there index will not change. but every Json in blob from Continous Export has different sequence for Custom dimensions. So i can't access them even by there index. 

    Following is my query:

    SELECT
        GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 0), 'ApplicationID') AS AppId,
        GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 1), 'UserId') AS UserId,
        GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 2), 'TestProp1') AS Test1,
        GetRecordPropertyValue(GetArrayElement(MySource.context.custom.dimensions, 3), 'TestProp2') AS Test2,
        
    INTO
      [Details]
    FROM
      [Common-Output] MySource
    


    Niranjan

    Friday, December 30, 2016 2:31 PM
  • Hi Niranjan,

    Thanks for the feedback on this.  We can't change the Continuous Export data format at this point without a version change or else we'd break others that rely on the format. And I don't have a suggestion how to do this better with Stream Analytics... 

    Please add a suggestion at https://visualstudio.uservoice.com/forums/357324-application-insights/category/161994-continuous-export with the rationale for an improved custom dimension format.

    Thanks

    Dale

    Thursday, January 19, 2017 6:47 PM