Querying data from multiple application insights

Overview:

There are times where you would need to query data from multiple application insight whether from the same resource group or other resource groups. Below are the few use cases for this.

Use case 1:

Suppose you have an application which has multiple app services(or websites) and function apps in the same resource groups which are handling different tasks and receive huge number of requests.

So it is a good idea to create separate application insight for logging, tracking requests and monitoring these services based on your need. For example, You can club together the websites for one application insight where as you can have another application insight which will monitor the function apps. This way, your application insight will not be overloaded with data, which would make it complex to monitor or troubleshoot or query data, of individual resources.

When you use multiple application insights inside a resource group, there might be a need to collaborate the data of these app insight to get information about the application such as total number of failed request or total number of users or total number of errors and so on.

Use Case 2:

When you migrate one application from

1. on premise server to azure or
2. other cloud platform to azure or
3. one resource group in azure to another resource group

You would need your previous logs and custom events from the old application insight. There are use cases where the data collected from application insight are pushed into power bi reports which are critical to clients to keep track of their customers.

In such cases you have to query data from multiple application insight which are present in different resource groups.

Querying multiple App Insights:

Application insight analytics uses Kusto query language to retrieve data.

A Kusto query is a read-only request, to process data, and return results. The request is stated in plain text, using a data-flow model. The query uses schema entities similar to SQL’s: databases, tables, and columns.The query consists of a sequence of query statements, delimited by a semicolon (;) and flows through a set of data transformation operators that are bound together through the use of the pipe (|) delimiter.

You can query from multiple application insight analytics by using “union” option available in the Kusto query. You can use application insight name or app id (available in the API Access blade in app insight) in the query.

Syntax:

union table1, table2

This is very vague syntax. So I am providing you with some of the examples which will help you in forming your own query based on your need.

Example 1 :

If you want to query all the requests from both application insight:

union 
app('ApplicationName1').Requests, 
app('ApplicationName2').Requests

or

union 
app('App ID of ApplicationName1').Requests, 
app('App ID of ApplicationName2').Requests

Example 2 :

If you want to query all the Exceptionsfrom both application insight:

union 
app('ApplicationName1').Exceptions, 
app('ApplicationName2').Exceptions

or

union 
app('App ID of ApplicationName1').Exceptions, 
app('App ID of ApplicationName2').Exceptions

Example 3 :

If you have applied telemetry client in the code to log some custom events and want to query them from both application insight:

union 
app('ApplicationName1').customEvents, 
app('ApplicationName2').customEvents

or

union 
app('App ID of ApplicationName1').customEvents, 
app('App ID of ApplicationName2').customEvents

Example 4:

If you want to apply some data transformation or conditions:

union 
app('d8ebxxxe-xxxx-xxxx-xxxx-fxxxx854xxxx').customEvents,
app('6abxxxx7-xxxx-xxxx-xxxx-bxxx5827xxxx').customEvents               
                | where timestamp >= startofday(now(), -1) and timestamp < endofday(now(), -1)                             
                | order by timestamp asc

The above example collects data of custom events from both application insights and then applies the conditions on the collective result.

If you want to apply different conditions on each app insight you can do the same as follows.

union 
(app('d8ebxxxe-xxxx-xxxx-xxxx-fxxxx854xxxx').customEvents
  | where timestamp >= startofday(now(), -90) and timestamp < endofday(now(), -1) 
  | order by timestamp asc),
(app('6abxxxx7-xxxx-xxxx-xxxx-bxxx5827xxxx').customEvents 
  | where timestamp >= startofday(now(), -30) and timestamp < endofday(now(), -1) 
  | order by timestamp asc)

The above example is getting 90 days data from the first application insight and 30 days data from the second application insight.

You can customize the query as per you need, I have given few of the example just to let you know how you can use the union function in different scenario. Hope you will get benefit from it.

 

Note: This doesn’t work if the two application insights are in different tenants.

Happy learning 🙂 .