Notebook: query Application Insights using C# / .Net Interactive

query-ai-net.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "source": [
    "# Querying  application insights - C# version\n",
    "\n",
    "To run this notebook you need to have .Net Interactive installed on your machine:\n",
    "\n",
    "## Install .Net Interactive\n",
    "\n",
    "- as a  minimum  install [Net Core 3.1](https://dotnet.microsoft.com/download)\n",
    "\n",
    "- In an ordinary console, install the dotnet interactive global tool:\n",
    "  ```\n",
    "  dotnet tool install --global Microsoft.dotnet-interactive\n",
    "  ```\n",
    "\n",
    "**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](https://github.com/microsoft/azuredatastudio/issues/12965).\n",
    "\n",
    "It  **does**  work in `nteract` or  `Jupyter`,  see below for  installing one or both of these tools.\n",
    "\n",
    "\n",
    "## Installing Jupyter\n",
    "\n",
    "- as a  minimum  install [Net Core 3.1](https://dotnet.microsoft.com/download)\n",
    "- [install Anaconda](https://www.anaconda.com/products/individual)\n",
    "- Open the Anaconda Prompt (Windows) or Terminal (macOS) and verify that Jupyter is installed and present on the path:\n",
    "  ```\n",
    "  jupyter kernelspec list\n",
    "      python3        ~\\jupyter\\kernels\\python3\n",
    "  ```\n",
    "- Make sure  you have  .Net Interactive  installed  as at the top\n",
    "\n",
    "- Install the .NET kernel to Jupyter by running the following within your Anaconda Prompt:\n",
    "  ```\n",
    "  dotnet interactive jupyter install\n",
    "    [InstallKernelSpec] Installed kernelspec .net-csharp in ~\\jupyter\\kernels\\.net-csharp\n",
    "    .NET kernel installation succeeded\n",
    "  \n",
    "    [InstallKernelSpec] Installed kernelspec .net-fsharp in ~\\jupyter\\kernels\\.net-fsharp\n",
    "    .NET kernel installation succeeded\n",
    "   \n",
    "    [InstallKernelSpec] Installed kernelspec .net-powershell in ~\\jupyter\\kernels\\.net-powershell\n",
    "    .NET kernel installation succeeded\n",
    "   ```\n",
    "\n",
    "- You can verify the installation by running the following again in the Anaconda Prompt:\n",
    "  ```\n",
    "  jupyter kernelspec list\n",
    "    .net-csharp    ~\\jupyter\\kernels\\.net-csharp\n",
    "    .net-fsharp    ~\\jupyter\\kernels\\.net-fsharp\n",
    "    .net-powershell ~\\jupyter\\kernels\\.net-powershell\n",
    "    python3        ~\\jupyter\\kernels\\python3\n",
    "  ```\n",
    "  \n",
    "- to run  Jupyter\n",
    "  - open an anaconda prompt\n",
    "  - type `jupyter notebook`\n",
    "  - wait for web  browser to open to the local Jupyter server\n",
    "  - browse  to the notebook you want\n",
    "  \n",
    "## Installing nteract\n",
    "\n",
    "- [download the desktop app installer](https://nteract.io/applications)\n",
    "- run the installer\n",
    "- [install the .Net kernels](https://nteract.io/kernels/dotnet)\n",
    "- run the app"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# What does this notebook do?\n",
    "\n",
    "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\n",
    "\n",
    "### Install additional dependencies"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "azdata_cell_guid": "a2a2c63a-581d-42ca-a3bd-6671df9bf303",
    "execution": {
     "iopub.execute_input": "2021-05-20T15:01:42.491Z",
     "iopub.status.busy": "2021-05-20T15:01:42.482Z",
     "iopub.status.idle": "2021-05-20T15:01:42.508Z"
    }
   },
   "outputs": [],
   "source": [
    "#r \"nuget: Microsoft.Azure.ApplicationInsights.Query, 1.0.0\"\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Read secrets from config  file\n",
    "\n",
    "To get an Application Id and API Secret go to your Applicaton Insights instance in the Azure portal, and  access **Configure** \\ **API Access**.\n",
    "\n",
    "Add the values into a local file `.env.local` in  the format KEY=VALUE\n",
    "\n",
    "**DO NOT** check these values into source control."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "azdata_cell_guid": "75e57c57-141f-41de-82eb-6eeac6c62761",
    "execution": {
     "iopub.execute_input": "2021-05-20T14:18:58.125Z",
     "iopub.status.busy": "2021-05-20T14:18:58.112Z",
     "iopub.status.idle": "2021-05-20T14:19:00.062Z"
    }
   },
   "outputs": [],
   "source": [
    "using System;\n",
    "using System.IO;\n",
    "\n",
    "var here =  Directory.GetCurrentDirectory();\n",
    "Console.WriteLine(here); // added this when testing  issues with Azure Data  Studio\n",
    "\n",
    "var filePath = \".env.local\";\n",
    "if (!File.Exists(filePath)) \n",
    "{\n",
    "    Console.Error.WriteLine(\"Cannot find env file\");\n",
    "}\n",
    "else \n",
    "{\n",
    "    foreach (var line in File.ReadAllLines(filePath))\n",
    "    {\n",
    "        var parts = line.Split(\n",
    "            '=',\n",
    "            StringSplitOptions.RemoveEmptyEntries);\n",
    "\n",
    "        if (parts.Length != 2) continue;\n",
    "\n",
    "        var x = $\"Setting {parts[0]}\";\n",
    "        display(x);\n",
    "\n",
    "        Environment.SetEnvironmentVariable(parts[0], parts[1]);\n",
    "    }\n",
    "}\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T14:26:05.076Z",
     "iopub.status.busy": "2021-05-20T14:26:05.065Z",
     "iopub.status.idle": "2021-05-20T14:26:05.118Z"
    },
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "// substitute the  correct key values for your application details in your `.env.local` file\n",
    "\n",
    "var AI_APPID =  Environment.GetEnvironmentVariable(\"AI_APPID_XRM2XERO\");\n",
    "var AI_APISECRET =  Environment.GetEnvironmentVariable(\"AI_APIKEY_XRM2XERO\");\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create an authenticated  client"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T15:19:24.231Z",
     "iopub.status.busy": "2021-05-20T15:19:24.221Z",
     "iopub.status.idle": "2021-05-20T15:19:24.269Z"
    },
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "using Microsoft.Azure.ApplicationInsights;\n",
    "using Microsoft.Azure.ApplicationInsights.Query;\n",
    "\n",
    "var creds = new ApiKeyClientCredentials(AI_APISECRET);\n",
    "var client = new ApplicationInsightsDataClient(creds);\n",
    "client.BaseUri = new Uri(\"https://api.applicationinsights.io/v1\");\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Query Application Insights via REST API"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "execution": {
     "iopub.execute_input": "2021-05-20T16:01:19.883Z",
     "iopub.status.busy": "2021-05-20T16:01:19.872Z",
     "iopub.status.idle": "2021-05-20T15:53:29.290Z"
    },
    "jupyter": {
     "outputs_hidden": false,
     "source_hidden": false
    },
    "nteract": {
     "transient": {
      "deleting": false
     }
    }
   },
   "outputs": [],
   "source": [
    "using System.Collections.Generic;\n",
    "using System.Linq;\n",
    "using Microsoft.Azure.ApplicationInsights.Query.Models;\n",
    "\n",
    "// see https://dev.applicationinsights.io/documentation/Using-the-API/Events\n",
    "\n",
    "var timespan = \"P1D\";\n",
    "\n",
    "var topCount  = 100;\n",
    "\n",
    "var filter  =  \"startswith(customDimensions/Category, 'Function') and customDimensions/LogLevel  eq  'Error'\";\n",
    "\n",
    "var orderby  =  \"timestamp desc\";\n",
    "\n",
    "EventsResults<EventsTraceResult> traces = \n",
    "    await client.Events.GetTraceEventsAsync(AI_APPID,  \n",
    "                                            timespan: timespan, \n",
    "                                            top: topCount, \n",
    "                                            filter:  filter,\n",
    "                                            orderby:  orderby);\n",
    "\n",
    "//display(traces.Value);\n",
    "var results  = \n",
    "    traces.Value\n",
    "    .Select(x =>   \n",
    "      new  {\n",
    "        TimeStamp     = x.Timestamp,\n",
    "        SeverityLevel = x.Trace.SeverityLevel,\n",
    "        LogLevel      = x.CustomDimensions.TryGetValue(\"LogLevel\", out var logLevel) ? logLevel : \"\", \n",
    "        Operation     = x.Operation.Name,\n",
    "        Message       = x.Trace.Message,\n",
    "        Category      = x.CustomDimensions.TryGetValue(\"Category\", out var category) ? category : \"\"\n",
    "      });\n",
    "    \n",
    "display(results);"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": ".NET (C#)",
   "language": "C#",
   "name": ".net-csharp"
  },
  "language_info": {
   "file_extension": ".cs",
   "mimetype": "text/x-csharp",
   "name": "C#",
   "pygments_lexer": "csharp",
   "version": "9.0"
  },
  "nteract": {
   "version": "0.28.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}