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 use Azure Application Insights REST API to get these metrics.

Steps as below:

step 1: Get the Application ID and an API key.

Nav to your application insights -> API Access, see the screenshot(Please remember, when the api key is generated, write it down):

step 2: In powershell, input the following cmdlet(the example code for fetching customEvents count):

Invoke-WebRequest -Uri https://api.applicationinsights.io/v1/apps/your_application_id/metrics/customEvents/cou
nt?timespan=P20D -Headers @{"accept"="application/json"; "x-api-key"="your_api_key"}

Result as below:

The details of the REST API is here.

Answer 2

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
}

Answer 3

$WorkspaceName = 'weu-co-law-security-01'
$ResourceGroupName = 'aaa-co-rsg-security-01'
$Workspace = Get-AzOperationalInsightsWorkspace -ResourceGroupName $ResourceGroupName -Name $WorkspaceName
$QueryResults = Invoke-AzOperationalInsightsQuery -Workspace $Workspace -Query 'AuditLogs | where OperationName == "Add member to group" | project TargetResources[0].displayName'
$QueryResults.Results

Answer 4

To have it in one go: given you have $appInsResourceGroupName and $appInsName pointing to your Application Insights instance.

$component = Get-AzApplicationInsights -ResourceGroupName $appInsResourceGroupName -Name $appInsName
$apiKey = New-AzApplicationInsightsApiKey -ApplicationInsightsComponent $component -Permissions ReadTelemetry -Description "Collector"
$query = "requests | limit 5"
(Invoke-WebRequest -Method POST -Uri https://api.applicationinsights.io/v1/apps/$($component.AppId)/query -ContentType application/json -Body $('{"query":"' + $query + '"}') -Headers @{"X-Api-Key"=$apiKey.ApiKey}).Content

to clean up / remove unused API keys

Get-AzApplicationInsightsApiKey -ApplicationInsightsComponent $component | ?{$_.Description -eq "Collector"} | %{Remove-AzApplicationInsightsApiKey -ApplicationInsightsComponent $component -ApiKeyId $_.Id}

if you're using any domestic clouds you need to account for that; e.g. for China you need to change the URL to api.applicationinsights.azure.cn