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 descExplanation
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
Released: August 17, 2023
Tables
SigninLogs
Keywords
SigninLogsConditionalAccessPoliciesHomeTenantIdResourceTenantIdResultTypeConditionalAccessStatusDistinctUsersUserIdStartTimeTimeGeneratedEndTimeAppDisplayNameResourceDisplayName
Operators
SigninLogswhereConditionalAccessPoliciesHomeTenantIdResourceTenantIdResultTypeConditionalAccessStatussummarizedcountUserIdminTimeGeneratedmaxtake_anybyAppDisplayNameResourceDisplayNamesortascdesc.