Sample Queries to generate Azure Application Insight reports

Overview

Azure Application Insights is a feature that supports Application Performance Management (APM) for live web applications and offers different experiences to enhance your application performance, reliability, and quality. 

Use the sample queries given below to generate Application Insight reports.

Integrate Azure Application Insight with Cora Orchestration

To generate reports using the Azure Application Insight, you need to integrate the Azure Application Insight with your Cora Orchestration. 

In the Admin site, add the Portal.Azure.ApplicationInsights.InstrumentationKey application variable.
For details, see this article.

To generate reports, follow the steps below:

1. Log in to the Azure Application Insights.
2. Navigate to the User Flows.
3. Open the queries section.
4. Add query to generate a report.

Sample query 1

Get the raw data, such as timestamp, user id, load time and URL, from the pageview table.

let page_views = pageViews
| extend
    userId = tostring(customDimensions["userId"]), // get user id
    duration = toint(customDimensions["duration"]), // get duration
    tabTitle = tostring(customDimensions["tabTitle"]), // get tab title
    timestamp = todatetime(timestamp), // convert timestamp
    loadTime = toint(customDimensions["loadTime"]),
    url = url,// get url
    name = name
| where
    userId != ""
    //tabTitle <> "undefined" and userId != "" // filter records
;
page_views
//| project userId,tabTitle,loadTime
| project userId, tabTitle, timestamp, url,name,loadTime

Display users title/total duration query

Get the total focus time in minutes for each user Id.

pageViews
|extend userId1 = tostring(customDimensions["userId"]),Duration1 = toint(customDimensions["duration"]),TabTitle = tostring(customDimensions["navigationTabTitle"])
|where TabTitle <> 'undefined' and notempty(userId1)
//|where userId1 == '1133' and TabTitle == '3063'
|project userId1,Duration1,TabTitle
|summarize SumDurationMin=sum(Duration1)/60000.0 by userId1,TabTitle

Get page view duration by user and tab title query

Get the total focus time in minutes for a specified user Id.

let page_views = pageViews
| extend
    userId = tostring(customDimensions["userId"]),
    duration = toint(customDimensions["duration"]),
    tabTitle = tostring(customDimensions["navigationTabTitle"])
| where tabTitle <> "undefined" and userId != "" and userId == "1133"
;
page_views
| summarize
    sum_duration_min=round(sum(duration)/60000.0, 2)
    by userId, tabTitle

Get page view duration by user query

Get the total focus time in minutes and the average time per internal tab for a specified user Id.

let page_views = pageViews  
| extend
    userId = tostring(customDimensions["userId"]),
    duration = toint(customDimensions["duration"]),
    timestamp = todatetime(timestamp),
    url = url
| where userId != ""
;
page_views
| summarize
    sum_duration_min=round(sum(duration)/60000.0, 2),
    avg_duration_min=round(avg(duration)/60000.0, 2) // add average calculation
    by userId
| where userId == "724" // filter by user

Process page view data for analysis sample query 1

Get the raw data from the pageviews table with a calculated column of focus time in minutes (sum_duration_min)

let page_views = pageViews
| extend
    userId = tostring(customDimensions["userId"]), // get user id
    duration = toint(customDimensions["duration"]), // get duration time in millisecond
    tabTitle = tostring(customDimensions["navigationTabTitle"]), // get tab title
    timestamp = todatetime(timestamp), // convert timestamp
    url = url // get url
| where
    tabTitle <> "undefined" and userId != "" // filter records
;
// Summarize duration by various dimensions
page_views  
| summarize
    sum_duration_min=round(sum(duration)/60000.0, 2)
    by timestamp, id, tabTitle, url, duration, performanceBucket, itemType, userId

Other default columns:

• Duration = focus time in millisecond
• performanceBucket = fixed time range for the “duration” data point

Process page view data for analysis sample query 2

Geo location information per page view with focus time duration.

let page_views = pageViews
| extend
    userId = tostring(customDimensions["userId"]), // get user id
    duration = toint(customDimensions["duration"]), // get duration
    tabTitle = tostring(customDimensions["navigationTabTitle"]), // get tab title
    timestamp = todatetime(timestamp), // convert timestamp
    url = url // get url
| where
    tabTitle <> "undefined" and userId != "" // filter records
    and timestamp between(datetime(2023-01-01) .. datetime(2023-12-31)) //filter dates
;
// Summarize duration by various dimensions
page_views  
| summarize
    sum_duration_min=round(sum(duration)/60000.0, 2)
    by timestamp, id, tabTitle, url, duration, performanceBucket, itemType,tostring(customDimensions),tostring(customMeasurements),operation_Name,
    session_Id,client_Type,client_Model,client_OS,client_City,client_StateOrProvince,client_CountryOrRegion,client_Browser,sdkVersion,userId
// by tabTitle customDimensions,customMeasurements

• customDimensions
  • refUri: the URL
  • caseId: the workflow instance Id
  • tabTitle: the text displayed in the internal tab name if exists. “undefined” implies it’s the main tab.
  • userId: the user Id as in the employees table
• customMeasurements
  • user id (as in the employees table)
  • load time (in milli-seconds)