I’ve recently been working on reviewing conditional access policies in Azure AD. Thankfully this process has become much easier than the early days with the introduction of Azure Monitor and Report-Only mode conditional access policies which allow you to properly pilot a configuration before going live.
I needed to grab an export of all sign-ins that were failing a particular report-only policy that was set up to block legacy authentication. This led me down the path of Azure Monitor and writing my first KQL query.
Note that this process depends on having set up streaming of Azure AD logs into Azure Monitor.
This KQL query grabs all sign-ins that have failed a report-only conditional access policy, and outputs the sign-in data alongside information about the policy in question:
Here’s the KQL query code:
|// Get Sign-in logs for any Report-Only Conditional Access policies where the result = ReportOnlyFailure|
|| mvexpand ConditionalAccessPolicies|
|| where ConditionalAccessPolicies["result"] == "reportOnlyFailure"|
|| project TimeGenerated, Identity, UserPrincipalName, AzureADApplication = AppDisplayName, ClientApplication = ClientAppUsed, ClientBrowser = DeviceDetail.browser, ClientOperatingSystem = DeviceDetail.operatingSystem, ClientIPAddress = IPAddress , ClientUserAgent = UserAgent , ConditionalAccessPolicyName = ConditionalAccessPolicies["displayName"], ConditionalAccessPolicyID = ConditionalAccessPolicies["id"]|
To explain what the query does:
- Retrieves all sign-in logs
- Uses mvexpand to expand the ConditionalAccessPolicies collection that’s included along with each sign-in’s data. The collection contains one object per conditional access policy in the Azure AD environment
- Narrows down the list to only sign-ins where the result of a policy was a “reportOnlyFailure”
- Uses the ‘project’ operator to retrieve only the data we’re interested in
From here, you can export the data to CSV and work your magic with it.