How to run an Azure Log Analytics query from a Powershell script non interactively?

Question

Given:

• I have an Azure account (MSDN benefits).
• I have a console application sending custom AppInsights metrics to my AppInsights workspace.

I would like to query these metrics from a PowerShell script.

I did try to find a solution by googling for it - no success. Not that there is no posts about the subject - I am just unable to make it work following these posts.

The gist of the problem is how to do it without user interaction.

Answer 1

You can do this with the application-insights extension to az cli.

az extension add -n application-insights
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)'

Here is a powershell script that can run a kusto query from a file in a given application insight instance and resource group and return the data as a powershell table:

<#
.SYNOPSIS

Run query in application insights and return Powershell table

.PARAMETER filename

File name of kusto query

.PARAMETER app 

Application Insights instance name

.PARAMETER rg

Resource group name

.EXAMPLE

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 | % {  $_.name }
$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]
    }
    $obj
}