Query Details
Identity Calculate Risky Users
Query
//Calculate the percentage for all your Azure AD users considered risky. Those requiring single factor authentication, coming from an unknown location and from an unknown device
//Data connector required for this query - Azure Active Directory - Signin Logs
SigninLogs
| where TimeGenerated > ago (30d)
| where ResultType == 0
//Include only member accounts if you want to ignore guest signins
| where UserType == "Member"
| extend DeviceTrustType = tostring(DeviceDetail.trustType)
| summarize
['Total Signins']=count(),
['At Risk Signins']=countif(NetworkLocationDetails == '[]' and isempty(DeviceTrustType) and AuthenticationRequirement == "singleFactorAuthentication")
by UserPrincipalName
| extend ['At Risk Percentage']=(todouble(['At Risk Signins']) * 100 / todouble(['Total Signins']))
| sort by ['At Risk Percentage'] desc Explanation
This query calculates the percentage of Azure AD users who are considered risky. These users are identified as those who require single factor authentication, are signing in from an unknown location, and are using an unknown device. The query uses the Azure Active Directory - Signin Logs data connector and filters the logs for the past 30 days. It only includes member accounts and not guest sign-ins. The query then counts the total sign-ins and the at-risk sign-ins for each user, and calculates the at-risk percentage. The results are sorted in descending order based on the at-risk percentage.
Details
Matt Zorich
Released: June 17, 2022
Tables
SigninLogs
Keywords
Devices,Intune,User,AzureAD
Operators
whereago==|count()countif()isempty()tostring()summarizebyextendtodouble()sort bydesc