Streaming Application Insights 'availability' telemetry to SQL
<p>I can't seem to get Availability telemetry pushed to a Azure SQL DB using Stream Analytics. There is NO error. It's just that no data is inserted.</p>

<h3>Input</h3>

<p>I have the input set up to the blob location where Application Insights exports too. I have tested this successfully with the "Sample Data" feature:</p>

<p><a href="https://i.stack.imgur.com/sVMI1.png" rel="nofollow"><img src="https://i.stack.imgur.com/sVMI1.png" alt="Input Details"></a></p>

<p>Here is an example from the sample data pulled (note that I have simplified the content):</p>

<pre><code>[
    {
        "availability": [ {
            "testRunId": "75e32865-36fa-4853-b9be-43e315a63f6e",
            "testTimestamp": "2016-10-19T11:38:58.7370000Z",
            "testName": "mytest-prod-pingtest",
            "runLocation": "BR : Sao Paulo",
            "durationMetric": {
                "name": "duration",
                "value": 14610000.0,
                "count": 1.0,
                "min": 14610000.0,
                "max": 14610000.0,
                "stdDev": 0.0,
                "sampledValue": 14610000.0
            },
            "result": "Pass",
            "count": 1
        } ],
        "internal": {...},
        "context": {...},
        "EventProcessedUtcTime": "2016-10-19T11:45:53.9144151Z"
    }
]
</code></pre>

<h2>Ouput</h2>

<p>The output is configured to the Azure SQL DB table <code>tt.AvailabilityRequests</code>. Testing succeeds:</p>

<p><a href="https://i.stack.imgur.com/xiSPf.png" rel="nofollow"><img src="https://i.stack.imgur.com/xiSPf.png" alt="Output testing"></a></p>

<p>This table has the following schema:</p>

<pre><code>CREATE TABLE [tt].[AvailabilityRequests](
    [Id] [uniqueidentifier] NOT NULL DEFAULT (newsequentialid()),
    [Timestamp] [datetime] NOT NULL,
    [AppInsightsTestRunId] [varchar](max) NULL,
    [TestName] [varchar](max) NULL,
    [RunLocation] [varchar](max) NULL,
    [IsSuccessful] [bit] NULL,
    [DurationInMilliseconds] [int] NULL,
    [InsertedDate] [datetime] NOT NULL DEFAULT (getdate())
)
</code></pre>

<h2>Query</h2>

<p>I have the following query configured:</p>

<pre><code>SELECT
    Flat.ArrayValue.testTimestamp as [Timestamp],
    Flat.ArrayValue.testRunId as [AppInsightsTestRunId],
    Flat.ArrayValue.testName as [TestName],
    Flat.ArrayValue.runLocation as [RunLocation],
    CASE WHEN Flat.ArrayValue.result = 'Pass' THEN 1 ELSE 0 END as [IsSuccessful],
    CAST(Flat.ArrayValue.durationMetric.value / Flat.ArrayValue.durationMetric.count / 10000 AS BIGINT) as [DurationInMilliseconds]
INTO
    availabilityRequests
FROM
    availability A
CROSS APPLY 
    GetElements(A.[availability]) as Flat
</code></pre>

<p>I am doing a similar thing with Request telemetry, and this works just fine:</p>

<p><a href="https://i.stack.imgur.com/FYntA.png" rel="nofollow"><img src="https://i.stack.imgur.com/FYntA.png" alt="Query"></a></p>