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.

Details

Jose Sebastián Canós

Released: June 7, 2023

Tables

SigninLogs

Keywords

DevicesIntuneUser

Operators

unionmv-expandextendsummarizecountifdcountiftake_anyminarg_maxbyiffsortproject-reorder

Actions

GitHub

KQL Search