Sign in Subscribe

Conditional Access Health Check: What Your Policies Actually Do

Last updated on 
Conditional Access Health Check: What Your Policies Actually Do

All right class.

This is highly technical post referencing KQL logic and my own (which is even worse than KQL), feel free to skip to the end for the ready KQL to copy & paste.

You open the Conditional Access blade and it sits there smugly telling you every policy is "On" and "Configured." Green checkmarks everywhere. Everything looks fine.

Meanwhile, three users are locked out of Teams, the helpdesk is drowning in MFA tickets, your "Require compliant device" policy hasn't matched a sign-in in six weeks, and nobody can tell you which policies are doing real work and which ones are expensive furniture.

The portal shows you policy configuration. It does not show you policy effectiveness (though you can of course run the “What If tool!”). Those are wildly different things.

This KQL query grades every Conditional Access policy in your tenant based on what it actually did over the last 14 days. Not what it's configured to do. Not what someone named it. What it did.

What this query really does

  • Pulls all sign-ins that ever touched Conditional Access:
    • Interactive (humans) from SigninLogs
    • Non-interactive token noise from AADNonInteractiveUserSignInLogs
    • Service principal sign-ins from AADServicePrincipalSignInLogs
  • Normalises the ConditionalAccessPolicies JSON across three different schemas because Microsoft couldn’t keep one type 😃
  • Explodes every CA policy evaluation into its own row.
  • Counts unique sign-ins per policy correctly using dcountif(CorrelationId, ...).
  • Calculates three numbers that actually matter:
    • ScopePct - how much of your tenant this policy really touches.
    • BlockRate - when it matches, how often it blocks instead of granting.
    • RO_DisruptPct - in Report-Only mode, how many sign-ins would break if you turned it on.
  • Classifies and grades every policy with an emoji and a blunt note.

The end result is a table that tells you, for each CA policy:

“This is a block policy, it touches 70% of sign-ins, blocks 2%, and if you enable the report-only copy you’ll lock out 35% of users. Also 95% of its traffic is service principals so you’re debugging the wrong thing.”

Let’s walk through the important parts.

Step 1 - Grab every sign-in type CA touches

First block:

let timeRange = 14d;
let CAResultTypes = dynamic([
"0", "53000", "53001", "53002", "53003",
"50074", "50076", "50079", "500121"
]);
let AllSignins =
union isfuzzy=true
(SigninLogs | extend SignInType = "Interactive"),
(AADNonInteractiveUserSignInLogs | extend SignInType = "Non-Interactive"),
(AADServicePrincipalSignInLogs | extend SignInType = "Service-Principal")
| where TimeGenerated > ago(timeRange)
| where ResultType in (CAResultTypes)

Why this matters:

You can’t grade CA on just SigninLogs.

Non-interactive and service principal traffic absolutely runs through CA and live in different tables. If you only look at interactive, you miss most of the “real world” impact.

CAResultTypes is a filter for:

  • 0 - plain “Allowed”
  • 53000-53003 - CA device/compliance/block outcomes
  • 50074/50076/50079/500121 - MFA interactions and failures

Basically: cases where CA mattered (you can of course amend that to your liking)

This already cuts out tons of sign-ins where CA never even showed up. Good.

Step 2 - Survive Microsoft’s schema roulette

This is where people’s queries die quietly:

| extend ConditionalAccessPolicies = coalesce(
todynamic(column_ifexists("ConditionalAccessPolicies_string", "")),
column_ifexists("ConditionalAccessPolicies_dynamic", dynamic(null)),
column_ifexists("ConditionalAccessPolicies", dynamic(null))
)
| where isnotempty(ConditionalAccessPolicies);

Reality check:

In some workspaces, ConditionalAccessPolicies is stored as a string.

In others, it’s already dynamic.

With DCRs and “Unified sign-in logs”, you can even end up with suffixed columns (*_string, *_dynamic).

If you just do todynamic(ConditionalAccessPolicies) and the column is already dynamic, Kusto throws a fit.

If you treat it as dynamic, but it’s actually a string in another table, all your unioned rows go to null, and you swear CA is broken.

This coalesce + column_ifexists trick is what makes the query tenant-agnostic:

  • Try ConditionalAccessPolicies_string (string > todynamic)
  • Else try ConditionalAccessPolicies_dynamic (already dynamic)
  • Else fall back to bare ConditionalAccessPolicies

If that whole thing is empty, you really don’t have CA data. If it’s not, you’re good.

Step 3 - Explode the CA array without lying to yourself

Next:

AllSignins
| mv-expand CAP = ConditionalAccessPolicies
| extend
PolicyName = tostring(CAP.displayName),
Result = tostring(CAP.result),
GrantCtrls = tostring(CAP.enforcedGrantControls),
SessionCtrls = tostring(CAP.enforcedSessionControls)
| where isnotempty(PolicyName)
| where Result in (
"success", "failure", "notApplied",
"reportOnlySuccess", "reportOnlyFailure", "reportOnlyNotApplied",
"notEnabled"
)

Every sign-in can have multiple policies evaluated.

ConditionalAccessPolicies is an array, so:

  • mv-expand turns one sign-in > N rows (one per policy).

You pull the fields you care about:

  • displayName
  • result (success, failure, notApplied, reportOnly*, notEnabled)
  • which grant and session controls actually fired

That last bit matters because you’re not guessing policy type from the name. You’re reading what it actually did.

Step 4 - Count correctly

This part is sneaky but critical:

| summarize
Int_Granted = dcountif(CorrelationId, SignInType == "Interactive" and Result == "success"),
...
Total = dcountif(CorrelationId, Result != "notEnabled")
by PolicyName

You use dcountif(CorrelationId, …) everywhere.

Why?

Because after mv-expand, each sign-in appears once per policy.

If you use countif, you’re counting rows, not unique sign-ins.

Example:

One sign-in is evaluated against 3 policies.

With mv-expand, that’s 3 rows.

countif would tell you “3 sign-ins”.

dcountif(CorrelationId, …) still says “1”.

If you don’t do this, your percentages climb above 100%, and everything lies.

Step 5 - Build metrics that actually mean something

After the first summarize, you roll everything up:

| extend
AllGranted = Int_Granted + NI_Granted + SPN_Granted,
AllBlocked = Int_Blocked + NI_Blocked + SPN_Blocked,
AllNotApplied = Int_NotApplied + NI_NotApplied + SPN_NotApplied,
Enforced = Int_Granted + NI_Granted + SPN_Granted + Int_Blocked + NI_Blocked + SPN_Blocked,
InScope = Int_Granted + NI_Granted + SPN_Granted + Int_Blocked + NI_Blocked + SPN_Blocked + RO_Pass + RO_Fail,
RO_Total = RO_Pass + RO_Fail + RO_Skip
| extend
ScopePct = iff(Total > 0, round(todouble(InScope) / todouble(Total) * 100, 1), 0.0),
BlockRate = iff(Enforced > 0, round(todouble(AllBlocked) / todouble(Enforced) * 100, 1), 0.0),
RO_DisruptPct = iff((RO_Pass + RO_Fail) > 0, round(todouble(RO_Fail) / todouble(RO_Pass + RO_Fail) * 100, 1), 0.0)

These three are the core:

ScopePct

Of all sign-ins that CA even cared about for this policy, how many were in scope?Grant policy with ScopePct = 5%? Your MFA policy protects almost nobody. Block policy with ScopePct = 5%? Perfect, that’s what you want.

BlockRate

When the policy matches and is enforced (not report-only), how often does it block instead of granting?
High BlockRate on a broad Grant policy = your users are failing the controls.
Low BlockRate on a targeted Block policy = it’s doing surgical work.

RO_DisruptPct

In report-only mode, if you turned this on right now, what % of in-scope sign-ins would fail?
30%+ is a Friday-afternoon suicide button.
1–5% is “fixable” with some exclusions and tuning.

You’re no longer guessing from “result: success” in the portal. You have real ratios.

Step 6 - Classify what the policy actually is

This bit is clever:

| extend
HasBlockCtrl = BlockCtrlSeen > 0,
HasGrantCtrl = GrantCtrlSeen > 0,
HasSessionCtrl = SessionCtrlSeen > 0,
IsRareCondition = (ScopePct < 5 and AllNotApplied > 100)
| extend PolicyType = case(
Disabled_Count > 0 and Total == 0, "⚪ Disabled",
Enforced == 0 and RO_Total > 0, "📊 Report-Only",
HasBlockCtrl, "🚫 Block",
HasGrantCtrl and HasSessionCtrl, "✅ Grant + ⏱️ Session",
HasSessionCtrl and not(HasGrantCtrl), "⏱️ Session Control",
"✅ Grant/Require"
)

Instead of trusting the admin’s policy name (“MFA for admins” lol), you classify on behavior:

  • If there’s a block grant control → Block policy.
  • If there are grant controls without block → Grant/Require.
  • If there are only session controls → Session policy.
  • If CA only shows up in reportOnly* results → Report-Only.
  • If all results are notEnabled and there’s no traffic → Disabled.

IsRareCondition flags niche stuff (risk-based policies, tiny scoped groups, weird OS):

IsRareCondition = (ScopePct < 5 and AllNotApplied > 100)

So a low ScopePct doesn’t automatically get painted as “broken”; it might just be doing its job quietly.

Step 7- Grade the damn thing

This chunk is long, but the idea is simple:

  • Grant/Session policies: higher scope is good, insane block rate is bad.
  • Block policies: low block rate is actually good.
  • Report-Only: graded entirely on disruption risk (RO_DisruptPct).

Examples from the Grade logic:

“🟢 A - Strong coverage”

  • Grant policy, ScopePct ≥ 80, BlockRate < 50.

“🟠 C - Broad scope but high block rate - users failing controls”

  • Grant policy, ScopePct ≥ 50, BlockRate ≥ 50.

Translation: “you pushed this wide and people are face-planting MFA/compliance.”

“🔵⚠️ High disruption (>30% would be blocked)”

  • Report-Only, RO_DisruptPct ≥ 30.

This is the “do not enable in prod until you like pain” bucket.

“👻 0 Hits (Broken configuration OR no risk events triggered)”

  • Grant/Session policy with zero in-scope hits.

Could be a healthy risk policy, could be completely busted.

This is what you throw in front of your identity team instead of the default CA workbook.

Step 8 - Add context so humans don’t misread it

Last bit decorates the output:

| extend
NI_Pct = iff(Total > 0, todouble(NI_Granted + NI_Blocked + NI_NotApplied) / todouble(Total) * 100, 0.0),
SPN_Pct = iff(Total > 0, todouble(SPN_Granted + SPN_Blocked + SPN_NotApplied) / todouble(Total) * 100, 0.0)
| extend Note = case(
...
SPN_Pct > 90,
"ℹ️ >90% service principal traffic - these metrics reflect workload identities, not humans.",
NI_Pct > 90,
"ℹ️ >90% non-interactive traffic - you're mostly looking at background token refreshes here, not real logons.",
(SPN_Granted + SPN_Blocked) > 0,
strcat("ℹ️ ", tostring(SPN_Granted + SPN_Blocked), " service principal sign-ins evaluated against this policy."),
""
)

This matters because:

A policy that “blocks 80% of sign-ins” sounds terrifying until you realise 95% of its traffic is service principals.

That’s an app problem, not a user problem.

A policy with 90% non-interactive traffic is mostly touching token refresh noise, not actual user logons. You don’t fix that the same way.

Those notes keep the discussion sane.

How to actually use this in a real tenant

Don’t just paste this into a workbook and admire it. Use it to drive decisions.

Identify zombie policies:

  • Filter PolicyType == "⚪ Disabled" or Grade contains "0 Hits".
  • That’s your list of “either safely delete or actually configure” items.

Find fake coverage:

  • Grant/Session policies with ScopePct < 10 and Grade like “🟠 C - Narrow scope - verify targeting” or “🔴 D - Low scope”.
  • Those are the ones marketing says “we require MFA” for, but in reality only 5% of sign-ins ever go through them.

Report-Only triage:

  • PolicyType == "📊 Report-Only" ordered by RO_DisruptPct desc.
  • 10–30% - discuss roll-out plan.
  • < 5% with decent ScopePct - legit candidates to flip to On.

Block policies sanity check:

  • PolicyType == "🚫 Block" sorted by BlockRate.
  • Low ScopePct + very low BlockRate = targeted and healthy.
  • High ScopePct + high BlockRate = “who approved this change?”
  • 30%+ disruption – dangerous, needs tuning.

What you do after running it

Use the output as a punchlist with your identity team:

  • Export the table to CSV or pin as a workbook.
  • Sit down with the CA owners.

For each policy:

  • Is the type what they think it is? (Block vs Grant vs Session vs RO)
  • Does the ScopePct match the stated intent?
  • Is the BlockRate sane for that intent?
  • For Report-Only, is RO_DisruptPct acceptable?

Then decide, per policy:

  • Keep as-is
  • Tighten scope / exclusions
  • Relax controls
  • Delete / merge
  • Promote Report-Only → On (only when the numbers say “safe”)

And yes, you can stick this behind a workbook tab called “CA Health” and check it monthly instead of when the CIO asks “is our MFA actually doing anything?”.

Next steps

If you want to level it up further:

  • Parameterise timeRange so you can quickly flip between 7, 14, 30 [...] days.
  • Add a filter on PolicyName and PolicyType at the top of the query for the workbook UI.
  • Clone the logic into a scheduled rule to alert when:
    • A Grant policy’s ScopePct suddenly crashes (someone excluded half the org).
    • A Block policy’s BlockRate spikes above 80%.
    • A Report-Only policy’s RO_DisruptPct jumps above 30%.

But even without that, this single query already does what the portal won’t: it tells you, with real numbers, whether your Conditional Access setup is actually protecting anyone or just generating chaos.

Full KQL:

// =================================================================
// Conditional Access Policy Health Check
// Author: itprofessor.cloud
//
// Grades every CA policy by activity
// Behavior-only classification
// Covers Interactive, Non-Interactive, Service Principal, and Report-Only traffic.
// =================================================================
let timeRange = 14d;
// "0" = Allowed. "53000-53002" = Device/App compliance blocks. "53003" = Hard CA block.
// "50074/50076/50079/500121" = MFA interruptions, the policy fired, it did its job.
let CAResultTypes = dynamic([
    "0", "53000", "53001", "53002", "53003",
    "50074", "50076", "50079", "500121"
]);
// 1. Pull from all three sign-in tables
// CA evaluates humans (Interactive), background apps (Non-Interactive), and
// workload identities (Service Principals), so we need all three.
let AllSignins =
    union isfuzzy=true
        (SigninLogs | extend SignInType = "Interactive"),
        (AADNonInteractiveUserSignInLogs | extend SignInType = "Non-Interactive"),
        (AADServicePrincipalSignInLogs | extend SignInType = "Service-Principal")
    | where TimeGenerated > ago(timeRange)
    | where ResultType in (CAResultTypes)
    // The CA policies JSON lives in different columns depending on workspace age and DCR config.
    // This coalesce handles all three variants so the query doesn't silently return nothing.
    // If you're getting 0 rows, check whether your workspace stores this as _string or _dynamic.
    // Also worth knowing: malformed JSON in this column returns null from todynamic() with no error.
    // If data looks missing, run a raw parse check on the source table before blaming the query :D.
    | extend ConditionalAccessPolicies = coalesce(
        todynamic(column_ifexists("ConditionalAccessPolicies_string", "")),
        column_ifexists("ConditionalAccessPolicies_dynamic", dynamic(null)),
        column_ifexists("ConditionalAccessPolicies", dynamic(null))
      )
    | where isnotempty(ConditionalAccessPolicies);
// 2. Expand - one row per policy per sign-in
// mv-expand blows up the CA array so each evaluated policy gets its own row.
// We can pull the actual controls that fired - this is how we classify intent later.
AllSignins
| mv-expand CAP = ConditionalAccessPolicies
| extend
    PolicyName   = tostring(CAP.displayName),
    Result       = tostring(CAP.result),
    GrantCtrls   = tostring(CAP.enforcedGrantControls),
    SessionCtrls = tostring(CAP.enforcedSessionControls)
| where isnotempty(PolicyName)
| where Result in (
    "success", "failure", "notApplied",
    "reportOnlySuccess", "reportOnlyFailure", "reportOnlyNotApplied",
    "notEnabled"
  )
// 3. Aggregate per policy
// dcountif(CorrelationId) not countif, mv-expand creates multiple rows per sign-in.
// countif would overcount and blow your percentages past 100%. Don't use it here.
| summarize
    // Int_ = humans at a keyboard
    Int_Granted    = dcountif(CorrelationId, SignInType == "Interactive" and Result == "success"),
    Int_Blocked    = dcountif(CorrelationId, SignInType == "Interactive" and Result == "failure"),
    Int_NotApplied = dcountif(CorrelationId, SignInType == "Interactive" and Result == "notApplied"),
    // NI_ = background token refreshes - high volume, low signal, keep separate
    NI_Granted     = dcountif(CorrelationId, SignInType == "Non-Interactive" and Result == "success"),
    NI_Blocked     = dcountif(CorrelationId, SignInType == "Non-Interactive" and Result == "failure"),
    NI_NotApplied  = dcountif(CorrelationId, SignInType == "Non-Interactive" and Result == "notApplied"),
    // SPN_ = workload identities - a blocked SPN is an app problem, not a user problem
    SPN_Granted    = dcountif(CorrelationId, SignInType == "Service-Principal" and Result == "success"),
    SPN_Blocked    = dcountif(CorrelationId, SignInType == "Service-Principal" and Result == "failure"),
    SPN_NotApplied = dcountif(CorrelationId, SignInType == "Service-Principal" and Result == "notApplied"),
    // RO_ = Report-Only mode
    // RO_Pass = would have passed. RO_Fail = WOULD HAVE BLOCKED. RO_Skip = didn't match at all.
    // RO_Fail is the one that matters - it tells you how many users break if you enable this today.
    RO_Pass        = dcountif(CorrelationId, Result == "reportOnlySuccess"),
    RO_Fail        = dcountif(CorrelationId, Result == "reportOnlyFailure"),
    RO_Skip        = dcountif(CorrelationId, Result == "reportOnlyNotApplied"),
    // Internal signals for classifying what the policy actually does
    Disabled_Count  = dcountif(CorrelationId, Result == "notEnabled"),
    BlockCtrlSeen   = dcountif(CorrelationId, GrantCtrls has "block"),
    SessionCtrlSeen = dcountif(CorrelationId, isnotempty(SessionCtrls) and SessionCtrls != "[]"),
    GrantCtrlSeen   = dcountif(CorrelationId, isnotempty(GrantCtrls) and not(GrantCtrls has "block") and GrantCtrls != "[]"),
    // notEnabled rows are excluded, disabled policies should show Total = 0, not inflate the count
    Total          = dcountif(CorrelationId, Result != "notEnabled")
    by PolicyName
// Disabled policies have Total = 0 and get filtered out without the second condition.
// Keep them - knowing what's disabled is part of the health picture.
| where Total > 10 or Disabled_Count > 0
// 4. Roll up the totals
| extend
    AllGranted    = Int_Granted + NI_Granted + SPN_Granted,
    AllBlocked    = Int_Blocked + NI_Blocked + SPN_Blocked,
    AllNotApplied = Int_NotApplied + NI_NotApplied + SPN_NotApplied,
    // Enforced = policy fired and produced an outcome (pass or block)
    Enforced      = Int_Granted + NI_Granted + SPN_Granted + Int_Blocked + NI_Blocked + SPN_Blocked,
    // InScope = policy conditions matched, regardless of what happened next
    // RO_Skip is excluded - those sign-ins didn't match even in simulation mode
    // For pure Report-Only policies, lean on RO_DisruptPct not ScopePct
    InScope       = Int_Granted + NI_Granted + SPN_Granted + Int_Blocked + NI_Blocked + SPN_Blocked + RO_Pass + RO_Fail,
    RO_Total      = RO_Pass + RO_Fail + RO_Skip
// 5. Calculate the three metrics that matter
| extend
    // ScopePct: what % of total sign-ins does this policy actually touch?
    // Low ScopePct on a Grant policy = problem. Low ScopePct on a Block policy = good.
    ScopePct      = iff(Total > 0, round(todouble(InScope) / todouble(Total) * 100, 1), 0.0),
    // BlockRate: when the policy matches, how often does it block?
    // Denominator is Enforced not Total - targeted block policies shouldn't get punished for low volume
    BlockRate     = iff(Enforced > 0, round(todouble(AllBlocked) / todouble(Enforced) * 100, 1), 0.0),
    // RO_DisruptPct: the number that tells you whether it's safe to flip this policy on.
    // 30%+ means roughly 1 in 3 affected users would get locked out. Don't enable that on a Friday ^^
    RO_DisruptPct = iff((RO_Pass + RO_Fail) > 0, round(todouble(RO_Fail) / todouble(RO_Pass + RO_Fail) * 100, 1), 0.0)
// 6. Classify by behavior
// We read what the policy actually enforced from the JSON. Works (well, it should) in any language,
// any naming convention, any tenant. A policy in staged rollout can have both
// Enforced > 0 and RO_Total > 0, it correctly classifies as live and RO columns stay visible.
| extend
    HasBlockCtrl    = BlockCtrlSeen > 0,
    HasGrantCtrl    = GrantCtrlSeen > 0,
    HasSessionCtrl  = SessionCtrlSeen > 0,
    // IsRareCondition catches policies that fire rarely by design, risk-based policies,
    // small groups, uncommon OS. A risk policy with 0 hits isn't broken, the environment is clean.
    IsRareCondition = (ScopePct < 5 and AllNotApplied > 100)
| extend PolicyType = case(
    Disabled_Count > 0 and Total == 0,    "⚪ Disabled",
    Enforced == 0 and RO_Total > 0,       "📊 Report-Only",
    HasBlockCtrl,                         "🚫 Block",
    HasGrantCtrl and HasSessionCtrl,      "✅ Grant + ⏱️ Session",
    HasSessionCtrl and not(HasGrantCtrl), "⏱️ Session Control",
                                          "✅ Grant/Require"
  )
// 7. Grade - logic inverts depending on policy type
// Grant/Session: high scope = good. Block: low hit rate = good. Report-Only: low disruption = safe.
// IsRareCondition must be checked before ScopePct >= 5 - it requires ScopePct < 5,
// so putting ScopePct >= 5 first makes IsRareCondition permanently unreachable.
| extend Grade = case(
    PolicyType == "⚪ Disabled",
        "⚪ Disabled - evaluate if still needed",
    PolicyType in ("✅ Grant/Require", "✅ Grant + ⏱️ Session", "⏱️ Session Control")
        and ScopePct >= 80 and BlockRate < 50,
        "🟢 A - Strong coverage",
    // Wide reach but users are getting blocked - the scope isn't the issue, the controls are
    PolicyType in ("✅ Grant/Require", "✅ Grant + ⏱️ Session", "⏱️ Session Control")
        and ScopePct >= 50 and BlockRate >= 50,
        "🟠 C - Broad scope but high block rate - users failing controls",
    PolicyType in ("✅ Grant/Require", "✅ Grant + ⏱️ Session", "⏱️ Session Control")
        and ScopePct >= 50,
        "🟡 B - Partial scope - review exclusions",
    PolicyType in ("✅ Grant/Require", "✅ Grant + ⏱️ Session", "⏱️ Session Control")
        and IsRareCondition,
        "🔵 Rare Condition (Risk-based, targeted group, or rare OS)",
    PolicyType in ("✅ Grant/Require", "✅ Grant + ⏱️ Session", "⏱️ Session Control")
        and ScopePct >= 5,
        "🟠 C - Narrow scope - verify targeting",
    // ScopePct between 0-5 but doesn't qualify as IsRareCondition - small group or misconfigured
    PolicyType in ("✅ Grant/Require", "✅ Grant + ⏱️ Session", "⏱️ Session Control")
        and ScopePct > 0,
        "🔴 D - Low scope (small group or possible misconfiguration)",
    // 0 hits - could be a healthy risk policy or a completely broken config. Investigate.
    PolicyType in ("✅ Grant/Require", "✅ Grant + ⏱️ Session", "⏱️ Session Control"),
        "👻 0 Hits (Broken configuration OR no risk events triggered)",
    PolicyType == "🚫 Block" and InScope == 0,
        "🟢 A - Clean environment (no violations)",
    PolicyType == "🚫 Block" and BlockRate < 20,
        "🟢 A - In scope but rarely blocks",
    PolicyType == "🚫 Block" and BlockRate < 50,
        "🟡 B - Moderate blocking",
    PolicyType == "🚫 Block" and BlockRate < 80,
        "🟠 C - High block rate - investigate",
    PolicyType == "🚫 Block",
        "🔴 D - Blocking >80% of in-scope",
    // Report-Only graded on disruption risk only - ScopePct is misleading here
    PolicyType == "📊 Report-Only" and RO_DisruptPct >= 30,
        "🔵⚠️ High disruption (>30% would be blocked)",
    PolicyType == "📊 Report-Only" and RO_DisruptPct >= 10,
        "🔵 Moderate disruption - review before enabling",
    PolicyType == "📊 Report-Only" and ScopePct >= 50,
        "🔵 Broad scope, low disruption - consider enabling",
    PolicyType == "📊 Report-Only" and ScopePct >= 10,
        "🔵 Moderate scope",
    PolicyType == "📊 Report-Only",
        "🔵 Low projected impact",
    "⚪ Unclassified"
  )
// 8. Surface the things worth acting on
// NI_Pct and SPN_Pct are split intentionally - "background token refreshes"
// and "service principal traffic" are different problems requiring different responses.
| extend
    NI_Pct  = iff(Total > 0, todouble(NI_Granted + NI_Blocked + NI_NotApplied) / todouble(Total) * 100, 0.0),
    SPN_Pct = iff(Total > 0, todouble(SPN_Granted + SPN_Blocked + SPN_NotApplied) / todouble(Total) * 100, 0.0)
| extend Note = case(
    PolicyType == "⚪ Disabled", "",
    IsRareCondition and ScopePct == 0 and PolicyType !in ("🚫 Block", "📊 Report-Only"),
        "ℹ️ 0 hits - could be a healthy risk-based policy or a broken config. Open the portal and check.",
    IsRareCondition and ScopePct > 0 and PolicyType !in ("🚫 Block", "📊 Report-Only"),
        "ℹ️ Low hits - probably scoped to a small group, rare OS, or specific risk conditions.",
    PolicyType in ("✅ Grant/Require", "✅ Grant + ⏱️ Session", "⏱️ Session Control")
        and BlockRate > 50 and Enforced > 50,
        "⚠️ Over half of in-scope sign-ins are getting blocked - users are struggling with these controls.",
    PolicyType == "🚫 Block" and BlockRate >= 80 and Enforced > 50,
        "⚠️ Blocking >80% of in-scope traffic - either the scope is too wide or something is actively wrong.",
    PolicyType == "📊 Report-Only" and RO_DisruptPct >= 30,
        strcat("⚠️ Enabling this would break ~", tostring(round(RO_DisruptPct, 0)), "% of affected users. Don't touch it until you know why."),
    SPN_Pct > 90,
        "ℹ️ >90% service principal traffic - these metrics reflect workload identities, not humans.",
    NI_Pct > 90,
        "ℹ️ >90% non-interactive traffic - you're mostly looking at background token refreshes here, not real logons.",
    (SPN_Granted + SPN_Blocked) > 0,
        strcat("ℹ️ ", tostring(SPN_Granted + SPN_Blocked), " service principal sign-ins evaluated against this policy."),
    ""
  )
// 9. Sort and output
| extend _SortKey = case(
    PolicyType == "⚪ Disabled", 999.0,
    PolicyType == "🚫 Block", -BlockRate,
    PolicyType == "📊 Report-Only", -RO_DisruptPct,
    ScopePct
  )
| project
    PolicyName, PolicyType, Grade, ScopePct, BlockRate,
    Int_Granted, Int_Blocked, Int_NotApplied,
    NI_Granted, NI_Blocked, NI_NotApplied,
    SPN_Granted, SPN_Blocked,
    RO_Pass, RO_Fail,
    Total, Note, _SortKey
| sort by PolicyType asc, _SortKey asc
| project-away _SortKey

Class dismissed.

Consent Preferences