Exporting Data from Application Insight to SQL and Power BI with Web Jobs

Continuous export moves your telemetry data into Azure Storage in JSON format. We’ll parse the JSON objects using Azure Stream Analytics and create rows in a database table.

(More generally, Continuous Export is the way to do your own analysis of the telemetry your apps send to Application Insights. You could adapt this code sample to do other things with the exported telemetry, such as aggregation of data.)

We’ll start with the assumption that you already have the app you want to monitor.

In this example, we will be using the page view data, but the same pattern can easily be extended to other data types such as custom events and exceptions.

Add Application Insights to your application

To get started:

1. Set up Application Insights for your web pages.

(In this example, we’ll focus on processing page view data from the client browsers, but you could also set up Application Insights for the server side of your Java or ASP.NET app and process request, dependency and other server telemetry.)

2. Publish your app, and watch telemetry data appearing in your Application Insights resource.

Create storage in Azure

Continuous export always outputs data to an Azure Storage account, so you need to create the storage first.

1. Create a storage account in your subscription in the Azure portal.
2. Create a container
3. Copy the storage access key

You’ll need it soon to set up the input to the stream analytics service.

Start continuous export to Azure storage

1. In the Azure portal, browse to the Application Insights resource you created for your application.
2. Create a continuous export.

Select the storage account you created earlier:

Set the event types you want to see:

3. Let some data accumulate. Sit back and let people use your application for a while. Telemetry will come in and you’ll see statistical charts in metric explorerand individual events in diagnostic search.

And also, the data will export to your storage.

4. Inspect the exported data, either in the portal – choose Browse, select your storage account, and then Containers– or in Visual Studio. In Visual Studio, choose View / Cloud Explorer, and open Azure / Storage. (If you don’t have this menu option, you need to install the Azure SDK: Open the New Project dialog and open Visual C# / Cloud / Get Microsoft Azure SDK for .NET.)

Make a note of the common part of the path name, which is derived from the application name and instrumentation key.

The events are written to blob files in JSON format. Each file may contain one or more events. So we’d like to read the event data and filter out the fields we want. There are all kinds of things we could do with the data, but our plan today is to use Stream Analytics to move the data to a SQL database. That will make it easy to run lots of interesting queries.

Comments

comments