Application Insights - How do I output the total sessions per build

Question

I have an event named 'Crash'. I need to output the results showing 'Build #, Total Crash events for the build, and total sessions for the Build.

I think I am close, just cannot figure out how to get the total sessions for each 'Build' dimension

let usg_events = dynamic(["Crash"]);
let mainTable = union pageViews, customEvents, requests
    | extend name =replace("\n", "", name)
    | where '*' in (usg_events) or name in (usg_events)
;
let queryTable = mainTable;
let cohortedTable = queryTable
    | extend dimension = customDimensions["Build"]
    | extend dimension = iif(isempty(dimension), "<undefined>", dimension)
    | summarize hll = hll(itemId) by tostring(dimension)
    | extend Events = dcount_hll(hll)
    | order by Events desc
    | serialize rank = row_number()
    | extend dimension = iff(rank > 5, 'Other', dimension)
    | summarize merged = hll_merge(hll) by tostring(dimension)
    | project ["Build"] = dimension, Crashes = dcount_hll(merged), ["Build Sessions"] = "?";
cohortedTable

Answer 1

Here I have reproduced the whole scenario by creating dummy results by using below code.

Code:

using System;
using System.Collections.Generic;
using Microsoft.ApplicationInsights;
using Microsoft.ApplicationInsights.Extensibility;
using Microsoft.ApplicationInsights.DataContracts;

namespace MyApp
{
    class Program
    {
        static void Main(string[] args)
        {
            // Initialize Application Insights with your Instrumentation Key
            TelemetryConfiguration configuration = new TelemetryConfiguration();
            configuration.ConnectionString = "InstrumentationKey=**************";
            TelemetryClient telemetryClient = new TelemetryClient(configuration);

            // Simulate multiple sessions with different build numbers
            for (int i = 1; i <= 100; i++) // Simulate 100 sessions
            {
                // Start tracking a new session with a unique build number
                var properties = new Dictionary<string, string> { { "BuildNumber", $"Build{i % 10}" } }; // Build numbers from Build0 to Build9
                telemetryClient.TrackEvent("SessionStart", properties);

                // Simulate a crash event with the same build number
                TrackCustomEvent(telemetryClient, "Crash", properties);
            }

            // Flush telemetry before exit
            telemetryClient.Flush();
            // Allow some time for flushing before shutdown
            System.Threading.Thread.Sleep(5000);
        }

        static void TrackCustomEvent(TelemetryClient telemetryClient, string eventName, IDictionary<string, string> properties)
        {
            var eventTelemetry = new EventTelemetry(eventName);

            // Add custom properties to the event
            foreach (var property in properties)
            {
                eventTelemetry.Properties[property.Key] = property.Value;
            }

            telemetryClient.TrackEvent(eventTelemetry);
        }
    }
}

• Here is the Query used in Application Insights Analytics to retrieve the desired result:

let mainTable = union pageViews, customEvents
    | where timestamp > ago(7d); // Filter data for the last 7 days, adjust as needed
mainTable
| extend BuildNumber = tostring(customDimensions["BuildNumber"])
| summarize CrashEvents = countif(name == "Crash"), TotalSessions = countif(name == "SessionStart") by BuildNumber
| order by TotalSessions desc

Result:

I just optimized your query, it accurately calculates the crash events and total sessions for each build. Below is the updated query:

let usg_events = dynamic(["Crash"]);
let mainTable = union pageViews, customEvents, requests
| extend name = replace("\n", "", name)
| where '*' in (usg_events) or name in (usg_events);
mainTable
| extend BuildNumber = tostring(customDimensions["Build"])
| summarize Crashes = countif(name in (usg_events)), TotalSessions = countif(name == "SessionStart") by BuildNumber
| order by TotalSessions desc

• Removed unnecessary variables (queryTable, cohortedTable). Extracted the BuildNumber from custom dimensions directly. Simplified the calculation of Crashes by using countif(name in (usg_events)) to count occurrences of crash events.
• Calculated TotalSessions using countif(name == "SessionStart"). Ordered the results by TotalSessions in descending order.