Stream Analytic Query for storing the custom metric of application insight to SQL Db

Question

I want to store all my custom metrics which are being logged in App Ins, move to SQL database.

I have enabled the cont export on App Ins, which is dumping the App Ins Custom metrics in blob.

From here want a I want Stream Analytic to dump the data in SQL Azure.

The issue is I am not able to write the transformation query in SA.

We will have 100s of custom metric which will be logged.

I want to store them in SQL like this

Time        Metric           Value
-------------------------------------

I am trying to achieve this with query:

SELECT 
    flat.PropertyName,
    flat.PropertyValue
INTO
    [outputdb-ai3]
FROM 
    [storage-ai] A
OUTER APPLY  
    GetRecordProperties(A.[context].[custom]) AS flat

But no luck, please suggest.

Thanks

Answer 1

Here is the query to get the desired result.

SELECT
    Input.internal.data.id,
    Input.context.data.eventtime,
    recordProperty.PropertyName AS Name,
    recordProperty.PropertyValue.Value
INTO
    [outputdb]
FROM
    [storage-ai] AS Input TIMESTAMP BY Input.context.data.eventtime 
    CROSS APPLY GetElements(Input.[context].[custom].[metrics]) AS flat
    CROSS APPLY GetRecordProperties(Flat.ArrayValue) AS recordProperty