Conditional Access Gap Analyzer – without Log Analytics Integration

Recently, John Savill* uploaded a video on this very cool feature and I thought to give it a try when I realized I have no Log Analytics integration enabled, so no Workbooks for me 🙁
[*big fan of John’s videos, pure gold]

This is not fair to those who only use Microsoft 365 products or who are prevented from enabling the integration due to some circumstances – that’s why I decided to look for a workaround.

TL;DR

• When you have the correct licences (AAD P2 and any Defender licence I guess) there is a table in Advanced Hunting on the Microsoft 365 Defender portal, called “AADSignInEventsBeta” (MS doc)
• This table is intented to be a temporary offering, but has almost the same information as the SigninLogs table that you get with Log Analytics integration
• The queries used by the Gap Analyzer workbook are available on github, here
• Below you can find the queries aligned to the AADSignInEventsBeta table’s schema

Disclaimer: I tried to validate every query in my demo environment, but some may require fine-tuning (especially because there are some columns that are not well documented, so values are set as per my testing – see notes under each query). Also, don’t forget to set the query range according to your needs:

Users Signing-In Using Legacy vs. Modern Authentication

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project ClientAppUsed, ErrorCode, ConditionalAccessPolicies, AccountDisplayName
| where ConditionalAccessPolicies != "[]"
| where ErrorCode == 0
| extend filterClientApp = case(ClientAppUsed != "Browser" and ClientAppUsed != "Mobile Apps and Desktop clients", "Legacy Authentication", "Modern Authentication")
| summarize count() by AccountDisplayName, filterClientApp
| summarize Count = count() by filterClientApp

Note: The Gap Analyzer workbook uses the SignInLogs table which contains only the interactive sign-in logs while the AADSignInEventsBeta has both the interactive and the non-interactive logs – that’s why every query starts with a LogonType filter for InteractiveUser.

Users Using Legacy Authentication by Application

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project ClientAppUsed, Application, ConditionalAccessPolicies, ErrorCode, AccountDisplayName
| where ClientAppUsed != "Browser" and ClientAppUsed != "Mobile Apps and Desktop clients"
| where ConditionalAccessPolicies != "[]"
| where ErrorCode == 0
| summarize count() by AccountDisplayName, Application
| summarize Count = count() by Application 

Users using Legay Authentication

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project ClientAppUsed, Application, ConditionalAccessPolicies, ErrorCode, AccountDisplayName
| where ClientAppUsed != "Browser" and ClientAppUsed != "Mobile Apps and Desktop clients"
| where ConditionalAccessPolicies != "[]"
| where ErrorCode == 0
| summarize count() by AccountDisplayName, Application, ClientAppUsed
| project-away count_

{user} legacy authentication sign-ins to {app} – this is an additional query to the previous one. Make sure you insert the Application and the AccountDisplayName values in the app and user variables

let app = "<Application>";
let user = "<AccountDisplayName>";
AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project AccountDisplayName, ClientAppUsed, Application, ConditionalAccessPolicies, ErrorCode, Timestamp, CorrelationId, NetworkLocationDetails, DeviceName, AadDeviceId, OSPlatform, Browser, UserAgent
| where ClientAppUsed != "Browser" and ClientAppUsed != "Mobile Apps and Desktop clients"
| where ConditionalAccessPolicies != "[]"
| where ErrorCode == 0
| where Application == app
| where AccountDisplayName == user
| project Timestamp, CorrelationId

Number of Users Signing In to Applications with Conditional Access Policies Not Applied

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project Application, ConditionalAccessStatus, ErrorCode, AccountDisplayName, AuthenticationRequirement
| where ErrorCode == 0 // sign-in was successful
| where ConditionalAccessStatus == "2"
| where AuthenticationRequirement == "singleFactorAuthentication" // Sign-in was not MFA

| summarize Count = count() by Application, AccountDisplayName
| summarize count() by Application

Note: as per the documentation for the table, ConditionalAccessStatus == “2” means policies are not applied.
Note2: the original query has the following clause:
| where Status.additionalDetails != “MFA requirement satisfied by claim in the token” and Status.additionalDetails != “MFA requirement skipped due to remembered device” // Sign-in was not strong auth
This detail is not available in the AADSignInEventsBeta table, but I guess this means that MFA was not required – which can be filtered using | where AuthenticationRequirement == “singleFactorAuthentication”. To be precise: if sign-in was successful, I guess it is more relevant if single factor was required than the actual authentication strenght

{User} sign-ins to {App} without CA coverage – this is an additional query to the previous one. Make sure you insert the Application and the AccountDisplayName values in the App and User variables

let App = "<Application>";
let User = "<AccountDisplayName>";
AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project Application, AccountDisplayName, ConditionalAccessStatus, ErrorCode, Timestamp, CorrelationId, DeviceName, AadDeviceId, OSPlatform, Browser, UserAgent, AuthenticationRequirement
| where ErrorCode == 0 // sign-in was successful
| where AuthenticationRequirement == "singleFactorAuthentication" // Sign-in was not MFA
| where ConditionalAccessStatus == "2"
| where Application == App
| where AccountDisplayName == User
| project Timestamp, CorrelationId, OSPlatform

High Risk Sign-In Events Bypassing Conditional Access Policies

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project AccountDisplayName, ConditionalAccessStatus, RiskLevelDuringSignIn, ErrorCode, AuthenticationRequirement
| where AuthenticationRequirement == "singleFactorAuthentication" // Sign-in was not MFA
| where ErrorCode == 0 // sign-in was successful
| where RiskLevelDuringSignIn > 50
| summarize Count = count() by AccountDisplayName, RiskLevelDuringSignIn
| order by Count desc

Note: RiskLevelDuringSignIn column is not even documented, but based on my testing 10 = low, 50 = medium, 100 = high

Risky sign-Ins from {user} with no CA policies

let user = "<accountDisplayname>";
AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project Timestamp, ErrorCode, RiskLevelDuringSignIn, AccountDisplayName, Country, AuthenticationRequirement, DeviceName, AadDeviceId, OSPlatform, Browser, UserAgent, NetworkLocationDetails, Application, CorrelationId
| where ErrorCode == 0 // sign-in was successful
| where RiskLevelDuringSignIn > 0
| where AccountDisplayName == user
| where AuthenticationRequirement == "singleFactorAuthentication" // Sign-in was not MFA
| project Timestamp, Application, CorrelationId, Country, OSPlatform

Users With No Conditional Access Coverage by Location – summary

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project AccountDisplayName, ConditionalAccessStatus, AuthenticationRequirement, ErrorCode, Country
| where ConditionalAccessStatus == "2"
| where AuthenticationRequirement == "singleFactorAuthentication" // Sign-in was not MFA
| where ErrorCode == 0
| summarize count() by Country, AccountDisplayName
| summarize Count = count() by Country
| order by Count desc

Users With No Conditional Access Coverage by Location

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project AccountDisplayName, ConditionalAccessStatus, AuthenticationRequirement, ErrorCode, NetworkLocationDetails, Country
| where ErrorCode == 0 // sign-in was successful
| where ConditionalAccessStatus == "2"
| where AuthenticationRequirement == "singleFactorAuthentication" // Sign-in was not MFA
| extend Location = case(Country == "", "Unknown", Country)
| summarize Count = count() by AccountDisplayName, Location
| project-away Count

Named locations without Conditional Access Coverage

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project AccountDisplayName, ConditionalAccessStatus, AuthenticationRequirement, ErrorCode, NetworkLocationDetails
| where ConditionalAccessStatus == "2"
| where AuthenticationRequirement == "singleFactorAuthentication" // Sign-in was not strong auth
| where ErrorCode == 0
| extend test = parse_json(NetworkLocationDetails)
| mv-expand test
| project test
| extend ["Named Location"] = tostring(test["networkNames"])
| summarize ["Sign-in Count"]=count() by ["Named Location"]

Users sign-ins from IPv6 addresses not assigned to a Named Location

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project ConditionalAccessStatus, AccountUpn, Application, IPAddress, NetworkLocationDetails
| where IPAddress has ":"
| where NetworkLocationDetails has '[]'
| summarize ["Sign-in Count"] = count() by  IPAddress, NetworkLocationDetails
//| summarize Count = count() by IPAddress
| sort by ["Sign-in Count"] desc
| project-away NetworkLocationDetails

Users sign-ins from IPv6 addresses not assigned to a Named Location (Separated by application)

AADSignInEventsBeta
| where LogonType has "InteractiveUser"
| project ConditionalAccessStatus, AccountUpn, Application, IPAddress, NetworkLocationDetails
| where IPAddress has ":"
| where NetworkLocationDetails has '[]'
| summarize ["Sign-in Count"] = count() by  Application, IPAddress
//| summarize Count = count() by IPAddress
| sort by ["Sign-in Count"] desc
//| project-away NetworkNetworkLocationDetails

Happy hunting 🙂