KQL Search

Query Details

HomeAI ToolsDevice Query

Signin Logs Unevaluated Conditional Access Apps

Query

SigninLogs
| where ConditionalAccessPolicies == "[]" and HomeTenantId == ResourceTenantId and ResultType == 0 and ConditionalAccessStatus == "notApplied"
| summarize
    DistinctUsers = dcount(UserId),
    StartTime = min(TimeGenerated),
    EndTime = max(TimeGenerated),
    take_any(*)
    by AppDisplayName, ResourceDisplayName
| sort by ResourceDisplayName asc, DistinctUsers desc

Explanation

This query is looking at sign-in logs where certain conditions are met: there are no conditional access policies applied, the user's home tenant ID matches the resource tenant ID, the result type is 0, and the conditional access status is "not applied".

For these specific logs, it's summarizing distinct user IDs, the earliest and latest time the log was generated, and taking any other available data. This summary is grouped by the name of the application and the name of the resource.

Finally, the results are sorted in ascending order by the resource name and in descending order by the number of distinct users.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: August 17, 2023

Tables

SigninLogs

Keywords

SigninLogs,ConditionalAccessPolicies,HomeTenantId,ResourceTenantId,ResultType,ConditionalAccessStatus,DistinctUsers,UserId,StartTime,TimeGenerated,EndTime,AppDisplayName,ResourceDisplayName

Operators

SigninLogswhereConditionalAccessPoliciesHomeTenantIdResourceTenantIdResultTypeConditionalAccessStatussummarizedcountUserIdminTimeGeneratedmaxtake_anybyAppDisplayNameResourceDisplayNamesortascdesc.

Actions