Conditional Access Report Only

Query

// Possible results: reportOnlyNotApplied, reportOnlyFailure, reportOnlySuccess
union
    (
    SigninLogs
    )
    // ,
    // (
    // AADNonInteractiveUserSignInLogs
    // | extend ConditionalAccessPolicies = todynamic(ConditionalAccessPolicies)
    // )
| mv-expand ConditionalAccessPolicy = ConditionalAccessPolicies
| extend
    DisplayName = tostring(ConditionalAccessPolicy["displayName"]),
    ConditionsNotSatisfied = toint(ConditionalAccessPolicy["conditionsNotSatisfied"]),
    ConditionsSatisfied = toint(ConditionalAccessPolicy["conditionsSatisfied"]),
    EnforcedGrantControls = tostring(ConditionalAccessPolicy["enforcedGrantControls"]),
    EnforcedSessionControls = tostring(ConditionalAccessPolicy["enforcedSessionControls"]),
    PolicyId = tostring(ConditionalAccessPolicy["id"]),
    PolicyResult = tostring(ConditionalAccessPolicy["result"])
| summarize
    Count_NotApplied = countif(PolicyResult == "reportOnlyNotApplied"),
    Count_Failure = countif(PolicyResult == "reportOnlyFailure"),
    Count_Success = countif(PolicyResult == "reportOnlySuccess"),
    DistinctUsers_NotApplied = dcountif(UserId, PolicyResult == "reportOnlyNotApplied"),
    DistinctUsers_Failure = dcountif(UserId, PolicyResult == "reportOnlyFailure"),
    DistinctUsers_Success = dcountif(UserId, PolicyResult == "reportOnlySuccess"),
    Example = take_any(ConditionalAccessPolicy),
    StartTime = min(TimeGenerated),
    EndTime = arg_max(TimeGenerated, DisplayName)
    by Type, PolicyId, EnforcedGrantControls, EnforcedSessionControls
| extend Count_Success = iff(EnforcedGrantControls == '["Block"]', int(null), Count_Success)
| extend DistinctUsers_Success = iff(EnforcedGrantControls == '["Block"]', int(null), DistinctUsers_Success)
| sort by DisplayName
| project-reorder DisplayName, Enforced*, Count_NotApplied, Count_Failure, Count_Success, DistinctUsers_NotApplied, DistinctUsers_Failure, DistinctUsers_Success, Example, PolicyId, StartTime, EndTime

Explanation

This query retrieves data from the SigninLogs table and expands the ConditionalAccessPolicies column. It then calculates various counts and distinct user counts based on the PolicyResult field. It also includes additional fields like DisplayName, EnforcedGrantControls, EnforcedSessionControls, PolicyId, StartTime, and EndTime. The results are sorted by DisplayName and the final output includes the specified columns in a specific order.