Azure AD Log Analytics KQL queries via API with PowerShell

Log Analytics is a fantastic tool in the Azure Portal that provides the ability to query Azure Monitor events. It provides the ability to quickly create queries using KQL (Kusto Query Language). Once you’ve created the query however you may want to run that query through automation negating the need to use the Azure Portal every time you want to get the associated report data.

In this post I detail;

• creating a Log Analytic Workspace
• enabling API Access
• querying Log Analytics using the REST API with PowerShell
• outputting data to CSV

Create a Workspace

We want to create a Workspace for our logs and queries. I created mine using the Azure Cloud Shell in the Azure Portal. I’m using an existing Resource Group. If you want it in a new Resource Group either create the RG through the portal or via the CLI using New-AzResourceGroup

$rgName = 'MYLogAnalytics-REPORTING-RG'

 $location = 'australiaeast'

 New-AzOperationalInsightsWorkspace -ResourceGroupName $rgName -Name Azure-Active-Directory-Logs -Location $location -Sku free

The Workspace will be created.

Next we need to get the logs into our Workspace. In the Azure Portal under Azure Active Directory => Monitoring => Diagnostic settings select + Add Diagnostic Setting and configure your Workspace to get the SignInLogs and AuditLogs.

API Access

In order to access the Log Analytics Workspace via API we need to create an Azure AD Application and assign it permissions to the Log Analytics API. I already had an Application I was using to query the Audit Logs so I added the Log Analytics to it.

On your Azure AD Application select Add a permission => APIs my organization uses and type Log Analytics => select Log Analytics API => Application permissions => Data.Read => Add permissions

Finally select Grant admin consent (for your Subscription) and take note of the API URI for your Log Analytics API endpoint (westus2.api.loganalytics.io) for me as shown below.

Under Certificates and secrets for your Azure AD Application create a Client Secret and record the secret for use in your script.

Link Log Analytics Workspace to Azure AD Application

On the Log Analytics Workspace that we created earlier we need to link our Azure AD App so that it has permissions to read data from Log Analytics.

On your Log Analytics Workspace select Access Control (IAM) => Add => Role = Reader and select your Azure AD App => save

I actually went back and also assigned Log Analytics Reader access to my Azure AD Application as I encountered a couple of instances of InsufficientAccessError – The provided credentials have insufficient access to perform the requested operation

Workspace ID

In order to query Log Analytics using KQL via REST API you will need your Log Analytics Workspace ID. In the Azure Portal search for Log Analytics then select your Log Analytics Workspace you want to query via the REST API and select Properties and copy the Workspace ID.

Querying Log Analytics via REST API

With the setup and configuration all done, we can now query Log Analytics via the REST API. I’m using my oAuth2 quick-start method to make the requests. For the first Authentication request use the Get-AzureAuthN function to authenticate and authorise the application. Subsequent authentication events can use the stored refresh token to get a new access token using the Get-NewTokens function. The script further below has the parameters for the oAuth AuthN/AuthZ process.

#Functions

Function Get-AuthCode {

...

}

function Get-AzureAuthN ($resource) {

...

}

function Get-NewTokens {

...

}



#AuthN

Get-AzureAuthN ($resource)

# Future calls can just refresh the token with the Get-NewTokens Function

Get-NewTokens

To call the REST API we use our Workspace ID we got earlier, our URI for our Log Analytics API endpoint, a KQL Query which we convert to JSON and we can then call and get our data.

$logAnalyticsWorkspace = "d03e10fc-d2a5-4c43-b128-a067efake"

$logAnalyticsBaseURI = "https://westus2.api.loganalytics.io/v1/workspaces"

$logQuery = "AuditLogs | where SourceSystem == `"Azure AD`" | project Identity, TimeGenerated, ResultDescription | limit 50"

$logQueryBody = @{"query" = $logQuery} | convertTo-Json



$result = invoke-RestMethod -method POST -uri "$($logAnalyticsBaseURI)/$($logAnalyticsWorkspace)/query" -Headers @{Authorization = "Bearer $($Global:accesstoken)"; "Content-Type" = "application/json"} -Body $logQueryBody

Here is a sample script that authenticates to Azure as the Application queries Log Analytics and then outputs the data to CSV.

Summary

If you need to use the power of KQL to obtain data from Log Analytics programatically, leveraging the REST API is a great approach. And with a little PowerShell magic we can output the resulting data to CSV. If you are just getting started with KQL queries this document is a good place to start.