Posted on

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.

Create LogAnalytics Workspace.PNG

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.

API Access to Log Analytics with KQL

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

Azure AD Application Secret.PNG

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

Link Log Analytics Workspace to Azure AD Application.PNG

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

API Access to Log Analytics with KQL - Log Analytics Reader.PNG

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.

WorkspaceID for REST API Query.PNG

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.

Output Log Analytics to CSV.PNG

Posted on

A quick start guide to leveraging the Azure Graph API with PowerShell and oAuth 2.0

Introduction

In September 2016 I wrote this post detailing integrating with the Azure Graph API via PowerShell and oAuth 2.0.

Since that point in time I’ve found myself doing considerably more via PowerShell and the Graph API using oAuth. I regularly find myself leveraging previous scripts to generate a new script for the initial connection. To the point that I decided to make this simpler and provide a nice clean starting point for new scripts.

This blog post details a simple script to generate a couple of PowerShell Functions that can be the basis for integration with Graph API using PowerShell via a WebApp using oAuth2.

Overview

This script will request the necessary information required to call into the Graph API and establish a session. Specifically;

  • The API Endpoint. Historically there were many different API endpoints depending on what you are integrating with. Microsoft is moving to simplify this (great article here about the evolving API), but it is still a work in progress. For this example I’ll be using graph.microsoft.com which is where Microsoft are heading. If you need access to an API not currently on the Graph API see here to workout which API Endpoint fits your apps requirements. In short though typically all that changes between API’s is the Resource (API end-point) and the scope (what permissions your app will have). Variations to the primary Graph API endpoint is when you are integrating with applications such as OneNote (https://www.onenote.com/api), Office 365 Discovery Service (https://api.office.com/discovery/), One Drive etc.
  • The ClientID and the ClientSecret associated with your WebApp that you have registered in the Application Registration Portal
  • The Scope of the WebApp. To make it seamless this should be done via the WebApp registration in the Application Registration Portal and configured as part of the PowerShell web requests

Armed with this information the shell of a PowerShell script will be created that will;

  • Authenticate a user to Graph API via Powershell and oAuth 2.0
  • Request Authorization for the WebApp to access the Scope provided (if Admin approval scope is requested and the AuthN is performed by a non-admin an authorization failure message will appear detailing an Administrator must authorize).
  • Obtain and Authorization Code which will contain the Bearer Token and Refresh Token.
    • The Bearer token can be used to make Graph API calls for up to 1 hour.
    • The Refresh token will allow you to request a new token and allow your script to be used again to interact via Graph API without going through the Authentication process again.

The following graphic shows this flow.
active-directory-oauth-code-flow-native-app

Create/Register your Application

Go to the Application Registration Portal https://apps.dev.microsoft.com/ and sign in. This is the new portal for registering your apps. It will show any previous apps you registered within AzureAD and any of the new “Converged Apps” you’ve created via the new Application Registration Portal.

Select Add an app from the Converged applications list.

New Converged App.PNG

Give your app a name and select Create

AppReg2.PNG

Record the Application ID (previously known as the Client ID) and select Generate New Password.

AppReg3.PNG

You will be provided your Client Secret. Record this now as it is the only time you will see it. Select Ok.

AppReg4.PNG

By default you will get User.Read permissions on the API. That is enough for this sample. Depending on what you will do with the API you will probably need to come and change the permissions or do it dynamically via the values you supply the $resource setting in your API calls.

AppReg5

Select Platforms, select Web and add a reply URL of https://localhost

AppReg6

Scroll to the bottom of the Registration windows and select Save.

Generate your PowerShell Graph API oAuth Script

Copy the following script and put it into an Administrator PowerShell/PowerShell ISE session and run it.

It will ask you to choose a folder to output the resultant PowerShell Script to. You can create a new folder through this dialog window if require.

OutputFolder.PNG

The script will prompt you for the Client/Application ID, Client Secret and the Reply URL you obtained when registering the Web App in the steps above.

ScriptPrompts.PNG

The script will be written out to the folder you chose in the first step and it will be executed. It will prompt you to authenticate. Provide the credentials you used when you created the App in the Application Registration Portal.

OfficeAuthN1.png

You will be prompted to Authorize the WebApp. Select Accept

AuthNtoAuthZ.PNG

If you’ve executed the previous steps correctly you’ll receive an AuthCode in your PowerShell output window

AuthCode.PNG

You’ll then see the output for a sample query for your user account and below that the successful call for a refresh of the tokens.

UserQueryOutput.png

Summary

In the folder you chose you will find a PowerShell script with the name Connect-to-Microsoft-Graph.ps1. You will also find a file named refresh.token. You can use the script to authenticate with your new app, but more simply use the Get-NewTokens function to refresh your tokens and then write your own API queries to your app using the tokens. Unless you change the scope you don’t need to run Get-AzureAuthN again. Just use Get-NewTokens before your API calls.

e.g

Get-NewTokens  
$myManager = Invoke-RestMethod -Method Get -Headers @{Authorization = "Bearer $accesstoken"
 'Content-Type' = 'application/json'} `
 -Uri "https://graph.microsoft.com/v1.0/me/manager"

 $myManager

Change the scope of your app to get more information. If you add a scope that requires Admin consent (and you’re not an admin), when prompted to authenticate you will need to get an Admin to authenticate and authorize the scope. Because you’ve changed the scope you will need to run the Get-AzureAuthN function again after updating $scope (as per below) and the dependent $scopeEncoded.

As the screen shot below shows I added the Mail.Read permission. I changed the $scope in the script so that it reflected the changes e.g

#Scope
$scope = "User.Read Mail.Read"
$scopeEncoded = [System.Web.HttpUtility]::UrlEncode($scope)

MailRead.png

When running the script again (because of the change of scope) you will be prompted to confirm the change of access.

Scope Change.PNG

You can then query your inbox, e.g.

 $myMail = Invoke-RestMethod -Method Get -Headers @{Authorization = "Bearer $accesstoken"
 'Content-Type' = 'application/json'} `
 -Uri "https://graph.microsoft.com/v1.0/me/messages"
 $mymail

And there is mail messages from your inbox.

MailAPI.png

I hope that makes getting started with the oAuth2 Graph API via PowerShell a lot simpler than it was for me initially, with the differing endpoints, evolving API and the associated documentation somewhere in-between.