Running Azure Insights Log Queries Locally

October 29, 2019 - 1 minute read

Application Insights is a tool to monitor your application running in Azure cloud.

Azure Application Insights Architecture

Custom queries to Application Insights logs are written in Kusto. You can run these queries in Azure Portal, but for this Stack Overflow question I wrote a solution how to execute these locally.

You need the Application Insights extension to az:

az extension add -n application-insights

Then you can run queries like this:

az monitor app-insights query --apps "$my-app-name" --resource-group "$my-resource-group" --offset 24H --analytics-query 'requests | summarize count() by bin(timestamp, 1h)'

I wrote a script to run kusto scripts from a file and get the result as a Powershell object:



Run query in application insights and return Powershell table

.PARAMETER filename

File name of kusto query


Application Insights instance name


Resource group name


Search-AppInsights -filename file.kusto -app my-app-name -rg my-resource-group-name

param([string] $filename, [string]$app, [string]$rg)

$query = Get-Content $filename
$data = az monitor app-insights query --apps "$app" --resource-group "$rg" --offset 48H --analytics-query "$query" | ConvertFrom-Json
$cols = $data.tables.columns | % {  $ }
$data.tables.rows | % {
    $obj = New-Object -TypeName psobject
    for ($i=0; $i -lt $cols.Length; $i++) {
	$obj | Add-Member -MemberType NoteProperty -Name $cols[$i] -Value $_[$i]

For example if we use the demo instance data we could count the 3 hour request counts for each URI:

$ cat count_rows.kusto
| summarize rows=count() by url, name, bin(timestamp, 3h)
$ Search-AppInsights.ps1 -app my-application-insights-instance -rg resource-group-name -filename count_rows.kusto
timestamp                       url                                             name		        rows
------                          ----                                            ---------               ----
9/13/2019, 6:00:00.000 AM	GET Home/Index	62	
9/13/2019, 6:00:00.000 AM	GET /Scripts/applicationinsights-channel-js.js	1	
9/13/2019, 6:00:00.000 AM	GET /Scripts/jquery-ui-1.8.11.js	1	
9/13/2019, 6:00:00.000 AM	GET /Scripts/knockout.mapping-latest.js	1	
9/13/2019, 6:00:00.000 AM	GET /Content/themes/base/jquery.ui.all.css	1	
9/13/2019, 6:00:00.000 AM	GET Customers/Details	15

For the Emacs users out there, I also wrote a kusto-mode for syntax highlighting.

Tagged under: azure, powershell