Azure AppInsights- Extracting Application Insight data using PowerShell & REST API

6 min readFeb 11, 2024

Introduction:

Application Insights is a powerful tool provided by Microsoft for monitoring applications and gaining insights into their performance and usage. While the Azure portal offers a user-friendly interface for querying and visualizing Application Insights data, sometimes you may need to automate data retrieval and analysis tasks.

In this article, we will explore how you can leverage PowerShell to query Application Insights data programmatically and process the results.

Problem Statement- Why outside Application Insight?

During testing, a common question arises: why opt for PowerShell instead of utilizing the logging options within Application Insights itself? One of the common scenario if you want to take quick action as a you can use this PowerShell method even though AppInsight provides Smart Detection. Apart from that as this is REST API based you can integrate directly on to your code.

Background:

Choosing to work outside of Application Insights provides extensive automation and scripting capabilities. This enables the automation of repetitive tasks, scheduling queries for specific intervals, and seamlessly integrating Application Insights data retrieval into existing automation workflows. Programmatically accessing and manipulating query results facilitates further analysis, custom report generation, or feeding data into external systems or dashboards for visualization and reporting purposes.

Steps to achieve:

Lets see how we can achieve this from outside, first you need to make use of REST API’s which were provided by Microsoft. First we need to authenticate against our Azure Tenant with resource as https://api.applicationinsights.io/

as mentioned in this article: https://learn.microsoft.com/en-us/azure/azure-monitor/app/app-insights-azure-ad-api

Pre-requisites:

Querying Application Insights Data with PowerShell: To begin, we need to construct an HTTP request to the Application Insights API to retrieve the desired data. We’ll use PowerShell’s Invoke-RestMethod cmdlet to make the HTTP request. Here's a breakdown of the script:

First step is getting Authorized bearer token from Azure AD with resource as : https://api.applicationinsights.io/ . Make sure you pass required headers to get token as first step.

#$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
#$headers.Add("Content-Type", "application/x-www-form-urlencoded")
#$headers.Add("ClientID", "Secret")

$token = (az account get-access-token --resource https://api.applicationinsights.io/ | ConvertFrom-Json).accessToken
$token

2. Next step we need to set up HTTP Headers: We create a dictionary object to hold the HTTP headers required for the request. These headers include the content type and the authorization token obtained from Azure Active Directory.

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/json")
$headers.Add("Authorization", "Bearer $token")

3. Construct Request Body: We define the Kusto query we want to execute within the request body. In this example, we’re querying for ‘requests’ data. Currently for testing I am going to use only “requests”. However lot of options you can use such as

exceptions
pageViews
requests

Currently in this scenario I am testing with few requests to find out failed results.

$body = @"
{
  `"query`": `"requests | where success == false | summarize failedCount=sum(itemCount), impactedUsers=dcount(user_Id) by operation_Name | order by failedCount desc`"
}
"@

$body = @"
{
  `"query`": `"requests | where timestamp > ago(14d) and success == false | join kind= inner (   exceptions   | where timestamp > ago(14d) ) on operation_Id | project exceptionType = type,failedMethod = method, requestName = name, requestDuration = duration, timestamp = timestamp | summarize count() by exceptionType, failedMethod, requestName, timestamp`"
}
"@

4. Make API Request: Next step is we need to use Invoke-RestMethod, we send a POST request to the Application Insights API endpoint, passing the headers and request body.

$response = Invoke-RestMethod 'https://api.applicationinsights.io/v1/apps/AppInsight-APIKey/query?timespan=P1D' -Method 'POST' -Headers $headers -Body $body

Recently Microsoft has released a new API URL : https://api5.applicationinsights.io you can make use of this as well.

To find AppInsight API Key from Azure Portal → Go to your Azure Portal → open your AppInsight blade → Configure → API Access. Copy displayed Application ID.

5. Processing the Response: Final step is to get required response output in proper format, we can check if our response contains any tables. If tables are present, we iterate through each table, parsing the rows and columns to construct PowerShell objects. Finally, we format the data into a table for easy readability.

If you dont use $response.table format then the you will see output something like this

As the response is not Parsed & not structured properly, resulting in unreadable output. To parse and display the response in a readable format, you need to properly handle the structure of the response, especially the tables array.

and then I have to use Format-Table cmdlet.

if ($response.tables) {
    foreach ($table in $response.tables) {
        Write-Output "Table Name: $($table.name)"
        if ($table.rows) {
            $rows = @()
            foreach ($row in $table.rows) {
                $rowData = [ordered]@{}
                for ($i = 0; $i -lt $table.columns.Count; $i++) {
                    $columnName = $table.columns[$i].name
                    $columnValue = $row[$i]
                    $rowData[$columnName] = $columnValue
                }
                $rows += New-Object PSObject -Property $rowData
            }
            $rows | Format-Table -AutoSize
        }
    }
}

After doing this I got the response in proper (readable) table format, here is the quick screenshot.

Here is the full script which you can use it

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/x-www-form-urlencoded")
$headers.Add("ClientID", "Client-Secret")

$token = (az account get-access-token --resource https://api.applicationinsights.io/ | ConvertFrom-Json).accessToken


$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/json")
$headers.Add("Authorization", "Bearer $token")

$body = @"
{
  `"query`": `"requests | where success == false | summarize failedCount=sum(itemCount), impactedUsers=dcount(user_Id) by operation_Name | order by failedCount desc`"
}
"@

$response = Invoke-RestMethod 'https://api5.applicationinsights.io/v1/apps/ApplicationInsight-APIKey/query?timespan=P1D' -Method 'POST' -Headers $headers -Body $body

# Check if response contains tables
if ($response.tables) {
    foreach ($table in $response.tables) {
        Write-Output "Table Name: $($table.name)"
        if ($table.rows) {
            $rows = @()
            foreach ($row in $table.rows) {
                $rowData = [ordered]@{}
                for ($i = 0; $i -lt $table.columns.Count; $i++) {
                    $columnName = $table.columns[$i].name
                    $columnValue = $row[$i]
                    $rowData[$columnName] = $columnValue
                }
                $rows += New-Object PSObject -Property $rowData
            }
            $rows | Format-Table -AutoSize
        }
    }
}

Also you can test the same functionality from Postman as well.

Conclusion:

Using PowerShell to query Application Insights data offers flexibility and automation capabilities, enhancing the efficiency of monitoring and analyzing application performance. By following the outlined steps and leveraging PowerShell’s functionalities, users can streamline data retrieval and processing tasks effectively.

Things to Remember:

When passing Kusto queries inside the request body, use backticks (`) rather than single quotes (‘) to ensure proper formatting and interpretation. Backticks serve as escape characters in PowerShell, facilitating the handling of special characters or variables inside double-quoted strings. More information about backticks and single quotes can be found at: (https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.core/about/about_quoting_rules?view=powershell-7.4)

Single Quotes ‘: Single quotes are used to denote literal strings in many programming languages. When you enclose text within single quotes, escape sequences or special characters are not interpreted. For example, ‘requests ‘ will be treated as the literal string “requests “, including the space character.
Backticks `: Backticks are often used as escape characters in PowerShell. They are used to escape special characters or interpret variables inside double-quoted strings. In your example, you’re using them to escape the double quotes “ within the JSON-like string. So, “query” becomes “query”, ensuring that the JSON string is properly formatted.