Streaming Application Insights 'availability' telemetry to SQL

2
votes

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.

Input

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:

Input Details

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

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

Ouput

The output is configured to the Azure SQL DB table tt.AvailabilityRequests. Testing succeeds:

Output testing

This table has the following schema:

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

Query

I have the following query configured:

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

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

Query

1
azure-application-insightsazure-stream-analytics
I have the same issue, have you succeeded pushing any data to SQL? - Alex Choroshin
@AlexChoroshin unfortunately not. Will keep you posted - Dave New
I successfully pushed some data to SQL,it was related to the way I constructed my input Path pattern. can you send your path ? - Alex Choroshin
@AlexChoroshin Mine is defined as myappinsights_xyz/Availability/{date}/{time} with YYYY-MM-DD as the date format and HH as the time format. But I don't believe this is my problem since I am able to pull "Sample Data" successfully. Does your query differ from mine by any chance? - Dave New
@AlexChoroshin It came down to my BIT column. After changing it to INT, all works well. - Dave New

1 Answers

1
votes

Stream Analytics does not support the "boolean" type; BIT in SQL Server.

Data is being pushed after changing the [IsSuccessful] field to an [int].