Querying Application Insights using an Interactive Notebook

Although the most prominent use of interactive notebooks is in data science and statistics, to steal an idea from Rob Sewell, they also hold great potential for creating actionable documentation for common operational incidents.

A common operational scenario is the need to query application log data to understand where and when a fault is occurring.

This note book example uses the .Net Interactive shell to allow the use of C# code to query Azure Application Insights. Code is here.

The rest of this page is the actual notebook (with sample results) exported to Markdown.

Querying application insights - C# version

To run this notebook you need to have .Net Interactive installed on your machine:

Install .Net Interactive

• as a minimum install Net Core 3.1

• In an ordinary console, install the dotnet interactive global tool:

  dotnet tool install --global Microsoft.dotnet-interactive
  

WARNING This notebook does not work in Azure Data Studio. This is because it reads settings from a local environment file. Azure Data Studio sets the wrong value for current directory, see microsoft/azuredatastudio:12965.

It does work in nteract or Jupyter, see below for installing one or both of these tools.

Installing Jupyter

• as a minimum install Net Core 3.1

• install Anaconda

• Open the Anaconda Prompt (Windows) or Terminal (macOS) and verify that Jupyter is installed and present on the path:

  jupyter kernelspec list
      python3        ~\jupyter\kernels\python3
  

• Make sure you have .Net Interactive installed as at the top

• Install the .NET kernel to Jupyter by running the following within your Anaconda Prompt:

  dotnet interactive jupyter install
    [InstallKernelSpec] Installed kernelspec .net-csharp in ~\jupyter\kernels\.net-csharp
    .NET kernel installation succeeded
  
    [InstallKernelSpec] Installed kernelspec .net-fsharp in ~\jupyter\kernels\.net-fsharp
    .NET kernel installation succeeded
  
    [InstallKernelSpec] Installed kernelspec .net-powershell in ~\jupyter\kernels\.net-powershell
    .NET kernel installation succeeded
  

• You can verify the installation by running the following again in the Anaconda Prompt:

  jupyter kernelspec list
    .net-csharp    ~\jupyter\kernels\.net-csharp
    .net-fsharp    ~\jupyter\kernels\.net-fsharp
    .net-powershell ~\jupyter\kernels\.net-powershell
    python3        ~\jupyter\kernels\python3
  

• to run Jupyter

  • open an anaconda prompt
  • type jupyter notebook
  • wait for web browser to open to the local Jupyter server
  • browse to the notebook you want

Installing nteract

• download the desktop app installer
• run the installer
• install the .Net kernels
• run the app

What does this notebook do?

This notebook shows an example of querying Application Insights using C# and the helper library Microsoft.Azure.ApplicationInsights.Query, a wrapper over the Applicaiton Insights REST API

Install additional dependencies

#r "nuget: Microsoft.Azure.ApplicationInsights.Query, 1.0.0"

Read secrets from config file

To get an Application Id and API Secret go to your Applicaton Insights instance in the Azure portal, and access Configure \ API Access.

Add the values into a local file .env.local in the format KEY=VALUE

DO NOT check these values into source control.

using System;
using System.IO;

var here =  Directory.GetCurrentDirectory();
Console.WriteLine(here); // added this when testing  issues with Azure Data  Studio

var filePath = ".env.local";
if (!File.Exists(filePath)) 
{
    Console.Error.WriteLine("Cannot find env file");
}
else 
{
    foreach (var line in File.ReadAllLines(filePath))
    {
        var parts = line.Split(
            '=',
            StringSplitOptions.RemoveEmptyEntries);

        if (parts.Length != 2) continue;

        var x = $"Setting {parts[0]}";
        display(x);

        Environment.SetEnvironmentVariable(parts[0], parts[1]);
    }
}

C:\Users\JulianElve\source\repos\notebooks\appinsights

Setting AI_APPID_XRM2XERO

Setting AI_APIKEY_XRM2XERO

Setting AI_APPID_XRMDATAINGEST

Setting AI_APIKEY_XRMDATAINGEST

// substitute the  correct key values for your application details in your `.env.local` file

var AI_APPID =  Environment.GetEnvironmentVariable("AI_APPID_XRM2XERO");
var AI_APISECRET =  Environment.GetEnvironmentVariable("AI_APIKEY_XRM2XERO");

Create an authenticated client

using Microsoft.Azure.ApplicationInsights;
using Microsoft.Azure.ApplicationInsights.Query;

var creds = new ApiKeyClientCredentials(AI_APISECRET);
var client = new ApplicationInsightsDataClient(creds);
client.BaseUri = new Uri("https://api.applicationinsights.io/v1");

Query Application Insights via REST API

using System.Collections.Generic;
using System.Linq;
using Microsoft.Azure.ApplicationInsights.Query.Models;

// see https://dev.applicationinsights.io/documentation/Using-the-API/Events

var timespan = "P1D";

var topCount  = 100;

var filter  =  "startswith(customDimensions/Category, 'Function') and customDimensions/LogLevel  eq  'Error'";

var orderby  =  "timestamp desc";

EventsResults<EventsTraceResult> traces = 
    await client.Events.GetTraceEventsAsync(AI_APPID,  
                                            timespan: timespan, 
                                            top: topCount, 
                                            filter:  filter,
                                            orderby:  orderby);

//display(traces.Value);
var results  = 
    traces.Value
    .Select(x =>   
      new  {
        TimeStamp     = x.Timestamp,
        SeverityLevel = x.Trace.SeverityLevel,
        LogLevel      = x.CustomDimensions.TryGetValue("LogLevel", out var logLevel) ? logLevel : "", 
        Operation     = x.Operation.Name,
        Message       = x.Trace.Message,
        Category      = x.CustomDimensions.TryGetValue("Category", out var category) ? category : ""
      });
    
display(results);