KQL Search

Query Details

HomeAI ToolsDevice Query

Identity Summarize Unknown Locationno MFA

Query

//Find the applications with the most signins coming from unknown locations (not defined in Conditional Access) and only requiring single factor authentication

//Data connector required for this query - Azure Active Directory - Signin Logs

SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where NetworkLocationDetails == '[]'
| where AuthenticationRequirement == "singleFactorAuthentication"
| summarize ['Count of signins']=count(), ['Distinct user count']=dcount(UserPrincipalName) by AppDisplayName
| sort by ['Distinct user count'] desc

Explanation

This query is looking for applications that have the highest number of sign-ins from unknown locations and only require single-factor authentication. It uses the Azure Active Directory - Signin Logs data connector. The query filters the sign-in logs for the past 30 days and selects only successful sign-ins. It then filters for sign-ins with no network location details and requiring single-factor authentication. The query summarizes the count of sign-ins and the distinct user count for each application and sorts the results by the distinct user count in descending order.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SigninLogs

Keywords

Applications,SigninLogs,TimeGenerated,ResultType,NetworkLocationDetails,AuthenticationRequirement,UserPrincipalName,AppDisplayName

Operators

where|>ago()=='[]'"singleFactorAuthentication"summarizecount()dcount()bysort bydesc

Actions