Stream Analytics query for ParsedStack of Exception from Azure Application Insights continuous export

Question

We have an azure cloud service which logs all exceptions to Azure Application Insights. We have set up continuous export of all exceptions to azure storage tables. On top of it, we have Azure Stream Analytics which pulls data from storage blobs and pushes it to Azure SQL Database. Now, the issue is that we are no able to cast/format properly ParsedStack from the Exception json to varchar(max), so that we can insert it to database.

This is the Stream Analytics query we used -

SELECT
     CASE 
        WHEN GetArrayLength(A.basicException) > 0
            THEN GetRecordPropertyValue(GetArrayElement(A.basicException, 0), 'assembly')
            ELSE ''
    END AS ExceptionAssembly 
    ,  
     CASE 
        WHEN GetArrayLength(A.basicException) > 0
            THEN GetRecordPropertyValue(GetArrayElement(A.basicException, 0), 'exceptionType')
            ELSE ''
    END AS ExceptionType 
   ,
     CASE 
        WHEN GetArrayLength(A.basicException) > 0
            THEN GetRecordPropertyValue(GetArrayElement(A.basicException, 0), 'parsedstack')
            ELSE ''
    END AS ParsedStack   
      ,A.context.device.id as DeviceId
      ,A.context.device.type as DeviceType
      ,A.context.device.browser as Browser
      ,A.context.device.browserVersion as BrowserVersion
      ,A.context.location.country as Country
      ,A.context.location.province as Province
      ,A.context.location.city as City
    INTO
      myexceptionsoutput
    FROM myexceptionsinput A

All values look as expected in SQL table, but the value of ParsedStack column is always Microsoft.EventProcessing.SteamR.Sql.ValueArray

Edit

Adding the Exception object json (The full version is very long, so trimmed it to be more clear)-

"basicException": [{
        "assembly": "Anonymously Hosted DynamicMethods Assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null",
        "exceptionType": "System.ServiceModel.CommunicationObjectFaultedException",
        "outerExceptionType": "System.ServiceModel.CommunicationObjectFaultedException",
        "failedUserCodeAssembly": "Anonymously Hosted DynamicMethods Assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null",
        "exceptionGroup": "System.ServiceModel.CommunicationObjectFaultedException at lambda_method",
        "count": 1,
        "outerExceptionMessage": "The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state."
    },
    {
        "parsedStack": [{
            "method": "System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage",
            "assembly": "mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
            "level": 0,
            "line": 0
        },
        {
            "method": "System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke",
            "assembly": "mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
            "level": 1,
            "line": 0
        },
        {
            "method": "System.IDisposable.Dispose",
            "assembly": "mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
            "level": 2,
            "line": 0
        }],
        "hasFullStack": true,
        "id": "22349146",
    }],
    "internal": {
        "data": {
            "id": "bd6f2355-ed02-4883-abb9-d8ed6ceba646",
            "documentVersion": "1.61"
        }
    }

What is the type of A.basicException, is it an array or array of arrays? I tried below query on an event that likes below, it works. The query you have is similar, it should work if A.basicException is an array. Event: {"ArrayColumn":[{"Id":255},{"Id":215}],"NormalColumn":40,"DeviceId":"D2"} ASA Query select NormalColumn, case when getarraylength(ArrayColumn) > 0 then GetRecordPropertyValue(getarrayelement(ArrayColumn,0), 'Id') else ' ' end [Id], System.Timestamp [EventTimeStamp], DeviceId into tableOutput from iotInput — Vignesh Chandramohan
@VigneshwaranChandramohan I've added the JSON object. BasicException here is an array of two objects. The second object again has two objects and in that the second object is ParsedStack. It becomes even more complex when I saw that ParsedStack is an array of objects whose size is always changing, based on the lines in stack trace of the exception — Mayur Dhingra

Vignesh Chandramohan Vignesh Chandramohan · Accepted Answer · 2016-02-29T18:10:29

Currently it is not possible to express serializing an array into a string in ASA query language. If you are fine with extracting parsed stack into individual rows, you can do below

with T1 as
(
select 
  GetArrayElement(iotInput.basicException,0) HighLevelDetails,
  GetRecordPropertyValue (GetArrayElement(iotInput.basicException,1), 'parsedStack') ParsedStack,
  internal.data.id Id
from 
    iotInput 
)

 select
   T1.id,
   T1.HighLevelDetails.assembly,
   T1.HighLevelDetails.exceptionType,
   ParsedStackArray.ArrayValue.method ParsedStackMethod,
   ParsedStackArray.ArrayValue.assembly ParsedStackAssembly,
   ParsedStackArray.ArrayValue.level ParsedStackLevel,
   ParsedStackArray.ArrayValue.line ParsedStackLine
 from 
  T1
 cross apply
   GetArrayElements(T1.ParsedStack) as ParsedStackArray

Based on the example event, writing high level details into a table and parsed stack into another table with "id" as common field might also work.

Stream Analytics query for ParsedStack of Exception from Azure Application Insights continuous export

1 Answers