Query Details
Identity Visualize Controlsvs No Controls
Query
//Visualize signins to your Azure AD tenant with no controls (known device, known location or MFA) vs those with at least one of those controls
//Data connector required for this query - Azure Active Directory - Signin Logs
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| extend DeviceTrustType = tostring(DeviceDetail.trustType)
| summarize
['Signins with no controls']=countif(NetworkLocationDetails == '[]' and AuthenticationRequirement == "singleFactorAuthentication" and isempty(DeviceTrustType)),
['Signins with one or more controls']=countif(NetworkLocationDetails != '[]' or AuthenticationRequirement == "multiFactorAuthentication" or isnotempty(DeviceTrustType)) by
bin(TimeGenerated, 1d)
| render timechart with (title="Azure AD signins no controls vs one or more controls")Explanation
This query analyzes sign-ins to your Azure AD tenant and compares those with no controls (such as known device, known location, or multi-factor authentication) to those with at least one of these controls. It uses the Azure Active Directory - Signin Logs data connector. The query filters the sign-in logs for the past 30 days and only includes successful sign-ins. It then categorizes the sign-ins into two groups: those with no controls and those with one or more controls. Finally, it visualizes the data in a time chart, showing the trend of sign-ins with no controls versus those with one or more controls.
Details
Matt Zorich
Released: June 17, 2022
Tables
SigninLogs
Keywords
SigninLogs,TimeGenerated,ResultType,DeviceDetail,DeviceTrustType,NetworkLocationDetails,AuthenticationRequirement
Operators
whereago==extendtostringsummarizecountifandorisemptyisnotemptybybinrender