How to Use KQL and Azure Log Analytics to Inspect Azure AD Sign-in Logs?

As you may already know KQL has become the standard for querying large data sets in Azure Log Analytics space. When you have thousands of users who are in Azure AD and when you have MFA and other Conditional Access Policies setup, next thing you will see is tons of sign in logs, activity logs, security logs and events and etc.

What will happen hear is with the help of Azure Log Analytics, Azure AD Sign in logs and Activity Logs, Risky Users, User Risk Events and tons more data can be streamed to an Azure Log Analytics Workspace and from there you can use KQL (Kusto Query Language) to query the necessary information that the standard UI can’t produce.

What do you need to set this up?

1. An Azure AD environment with Premium P2 license
2. An Azure Subscription
3. An Azure Log Analytics Workspace
4. KQL knowledge to query the data (I have added few links where you can gain some good KQL knowledge)

Setup the Azure Log Analytics Workspace

Given you have an Azure subscription, create the Log Analytics Workspace in the relevant resource group or create a new one.

• Create a new resource in your resource group. Search for Log Analytics Workspace

• Hit Create

• Complete the creation

Connect the Azure AD Logs with the Log Analytics Workspace

This is where you start stream the Azure AD logs to the Log Analytics Workspace you created earlier.

If you are using Microsoft Sentinel product, when you connect Azure AD as a data connector, it will start sending the events to the Log Analytics workspace, but in this case, we have to manually setup the Azure AD side of things.

1. Go to Azure AD > Azure Active Directory > Sign-in Logs > Export Data Settings

2. Click on Add diagnostics Setting

3. Set the name (Diagnostic setting name), select the required Logs categories, and select the Azure Subscription and the created Log Analytics Workspace

Once you press Save, the data will start stream in to the Log Analytics workspace and will be all ready to go in few minutes time.

Lets head to the Log Analytics Workspace

You can easily go to the Azure Log Analytics workspace straight from the Azure AD. Click on Log Analytics in Azure Active Directory

And you will see below. This is the space where you run all the queries using KQL (Kusto Query Language) and get your results

Below is just a simple query to query the AuditLogs table and to understand the activities performed for Azure AD identity and Access Management features

AuditLogs
| where TimeGenerated < ago(2d)
| where InitiatedBy !="{}"
| parse InitiatedBy with * ':' * 'userPrincipalName":"' InitiatedUPN '",' * 'ipAddress":"' InitiatedIP  '",' * 
| parse TargetResources with * ':' * 'displayName":"' WhatGotChanged '",' * 'type":"' ChangedType  '",' * 
| project OperationName, Category, InitiatedUPN, InitiatedIP, ActivityDisplayName, ActivityDateTime, WhatGotChanged, ChangedType

And the result will be show as below

Or maybe a query like this, which will will help you to understand the behaviour of the sign in patterns of a selected user after being enabled for MFA.

SigninLogs
| where UserPrincipalName =~ "XXXXXX"
| where ResultDescription contains "MFA" or ResultDescription contains "factor"
| project TimeGenerated, UserPrincipalName, ResultDescription, location

Useful links

KQL quick reference – https://docs.microsoft.com/en-us/azure/data-explorer/kql-quick-reference

KQL Cheat Sheet – https://techcommunity.microsoft.com/t5/azure-data-explorer-blog/azure-data-explorer-kql-cheat-sheets/ba-p/1057404

Final Words

I’m not going deep in to the KQL queries as it is a whole lot to learn which is quite interesting and easy at the same time. In this way you can query only the relevant details and narrow down your search using KQL rather than going through the logs manually. And because this is the new normal to query data out of Azure, you will need this in other places like Microsoft Sentinel, Microsoft Defender Advanced Threat Hunting activities as well.

Feature Image: Query Vectors by Vecteezy