Conditional Access, Part Six: Turning Twenty-Five Queries Into an Operating Model

All right class.

Five posts, twenty-five queries. If you have been following along, you now have a folder of KQL that describes your Conditional Access estate better than the portal does.

Here is how that folder usually dies.

Somebody, reasonably, decides the queries are valuable and schedules all of them as analytic rules. Within a fortnight the security operations queue contains several hundred incidents about guest accounts that have not signed in recently, policies that are not enforcing anything, and devices whose compliance state is stale. None of it is wrong. All of it is true. And an analyst working a queue learns very quickly which rule names to close without reading, at which point the rule that actually mattered gets closed without reading too.

Twenty-five queries scheduled is twenty-five sources of noise. This post is about which ones become detections, which ones stay as hunts, which ones belong in a workbook, and how to build the handful that genuinely deserve to wake somebody up.

It is the last post in the series, so it also closes the argument the other five have been building.

The triage, and the test that decides it

Three destinations, and one question that sorts a query into the right one.

Ask what a competent analyst should do at three in the morning with this output. If the answer is a defined action, it is a detection. If the answer is "investigate, over the next day or two", it is a hunt. If the answer is "raise it at the next review", it is posture and belongs in a workbook.

That test is unforgiving and it should be. Most of the twenty-five fail it, and that is fine, because they were written to describe an estate rather than to catch an attacker.

Detections become scheduled analytic rules that create incidents. They need high fidelity, a bounded volume, a clear response, and an owner. Ten of the twenty-five qualify.

Hunts become saved queries run on a cadence by a human being who is thinking. They can be noisy, because a person is applying judgement rather than a threshold. Four qualify.

Posture goes into a workbook, gets read monthly, and drives project work rather than incident response. Eleven qualify, and they are not the poor relations. The coverage gap query from part one has changed more architecture than any detection I have written.

Here is how the series sorts.

Look at the shape of that. Nine of the ten detections are control plane changes and privileged anomalies. Only one is behavioural, and it is the hardest to build. That distribution is not an accident, and it is worth internalising: in identity, the reliable detections watch the configuration, not the users.

The worked example

The behavioural one is the session replay rule from part four, and the raw version in that post is a hunt rather than a detection. One session identifier seen from two countries is interesting. It is not, on its own, sufficient. A user connects through a corporate proxy in one country and a mobile network in another and produces exactly that pattern all day long.

So the rule has to combine weak signals rather than trust a single one. Here it is built properly, using the same detection doctrine I apply everywhere: ingestion-time bounding for the seed window, wide event-time reach so late arrivals are not lost, a per-account baseline that cannot poison itself, weighted scoring gated by a threshold, and a composite indicator string carried through as a custom detail.

let Detection = 1h;
let Baseline = 21d;
let LateArrival = 6h;
let FireThreshold = 5;
let W_MultiCountry = 3;
let W_UnseenCountry = 2;
let W_UnseenAddress = 1;
let W_UnmanagedDevice = 2;
let W_ClaimFromNewAddress = 2;
let W_UnseenAgent = 1;
let W_Privileged = 2;
let Identities = union isfuzzy=true
    (datatable(AccountUPN: string, AssignedRoles: dynamic, IsAccountEnabled: bool, TimeGenerated: datetime)[]),
    (IdentityInfo | where TimeGenerated > ago(Baseline) | summarize arg_max(TimeGenerated, *) by AccountUPN);
let PrivilegedAccounts = Identities
    | where IsAccountEnabled == true
    | where array_length(todynamic(AssignedRoles)) > 0
    | distinct AccountUPN;
let Seed = SigninLogs
    | where ingestion_time() > ago(Detection)
    | where TimeGenerated > ago(Detection + LateArrival)
    | where ResultType == 0
    | where isnotempty(SessionId)
    | distinct UserPrincipalName, SessionId;
let Baselines = SigninLogs
    | where TimeGenerated between (ago(Baseline) .. ago(Detection))
    | where ResultType == 0
    | summarize KnownCountries = make_set(tostring(LocationDetails.countryOrRegion), 50), KnownAddresses = make_set(IPAddress, 250), KnownAgents = make_set(UserAgent, 50) by UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(Detection + LateArrival)
| where ResultType == 0
| join kind=inner (Seed) on UserPrincipalName, SessionId
| extend ClaimRow = iff(tostring(AuthenticationDetails) contains "satisfied by claim", 1, 0)
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend TrustType = tostring(DeviceDetail.trustType)
| extend IsCompliant = tostring(DeviceDetail.isCompliant)
| summarize Countries = make_set(Country, 20), Addresses = make_set(IPAddress, 50), Agents = make_set(UserAgent, 20), Applications = make_set(AppDisplayName, 20), UnmanagedEvents = countif(isempty(TrustType) or IsCompliant != "true"), ClaimEvents = sum(ClaimRow), Events = count(), StartTime = min(TimeGenerated), EndTime = max(TimeGenerated) by UserPrincipalName, SessionId
| join kind=leftouter (Baselines) on UserPrincipalName
| extend UnseenCountries = set_difference(Countries, iff(isnull(KnownCountries), dynamic([]), KnownCountries))
| extend UnseenAddresses = set_difference(Addresses, iff(isnull(KnownAddresses), dynamic([]), KnownAddresses))
| extend UnseenAgents = set_difference(Agents, iff(isnull(KnownAgents), dynamic([]), KnownAgents))
| extend S_MultiCountry = iff(array_length(Countries) > 1, W_MultiCountry, 0)
| extend S_UnseenCountry = iff(array_length(UnseenCountries) > 0, W_UnseenCountry, 0)
| extend S_UnseenAddress = iff(array_length(UnseenAddresses) > 0, W_UnseenAddress, 0)
| extend S_Unmanaged = iff(UnmanagedEvents > 0, W_UnmanagedDevice, 0)
| extend S_Claim = iff(ClaimEvents > 0 and array_length(UnseenAddresses) > 0, W_ClaimFromNewAddress, 0)
| extend S_UnseenAgent = iff(array_length(UnseenAgents) > 0, W_UnseenAgent, 0)
| extend S_Privileged = iff(UserPrincipalName in (PrivilegedAccounts), W_Privileged, 0)
| extend RiskScore = S_MultiCountry + S_UnseenCountry + S_UnseenAddress + S_Unmanaged + S_Claim + S_UnseenAgent + S_Privileged
| where RiskScore >= FireThreshold
| extend RiskIndicators = strcat_array(set_difference(pack_array(iff(S_MultiCountry > 0, "MultipleCountriesInSession", ""), iff(S_UnseenCountry > 0, "UnseenCountry", ""), iff(S_UnseenAddress > 0, "UnseenAddress", ""), iff(S_Unmanaged > 0, "UnmanagedDevice", ""), iff(S_Claim > 0, "MfaClaimFromUnseenAddress", ""), iff(S_UnseenAgent > 0, "UnseenUserAgent", ""), iff(S_Privileged > 0, "PrivilegedAccount", "")), dynamic([""])), " | ")
| extend AccountName = tostring(split(UserPrincipalName, "@")[0])
| extend UPNSuffix = tostring(split(UserPrincipalName, "@")[1])
| extend PrimaryAddress = tostring(UnseenAddresses[0])
| project StartTime, EndTime, UserPrincipalName, AccountName, UPNSuffix, PrimaryAddress, RiskScore, RiskIndicators, Countries, UnseenCountries, UnseenAddresses, UnseenAgents, Applications, SessionId, Events
| order by RiskScore desc, StartTime desc

The unified control plane rule

Nine of the ten detections watch configuration. Rather than nine separate rules, most of that collapses into one, because the response is identical: somebody changed a security control, and either there is a change record or there is an incident.

let Detection = 1h;
let LateArrival = 4h;
let Sensitive = dynamic(["Conditional Access", "Authentication Methods", "CrossTenantAccess", "cross-tenant access", "tenant restrictions", "Update device", "DeviceCompliancePolicy", "Update policy", "Delete policy"]);
let EntraChanges = AuditLogs
    | where ingestion_time() > ago(Detection)
    | where TimeGenerated > ago(Detection + LateArrival)
    | where LoggedByService has_any ("Conditional Access", "Authentication Methods") or OperationName has_any (Sensitive)
    | extend ControlPlane = case(LoggedByService has "Conditional Access", "Conditional Access", LoggedByService has "Authentication Methods", "Authentication Methods", OperationName has_any ("cross-tenant", "CrossTenantAccess", "tenant restrictions"), "Cross-tenant access", OperationName has "device", "Device object", "Directory")
    | extend TargetName = tostring(TargetResources[0].displayName)
    | extend ActorUser = tostring(InitiatedBy.user.userPrincipalName)
    | extend ActorApp = tostring(InitiatedBy.app.displayName)
    | extend Actor = iff(isempty(ActorUser), ActorApp, ActorUser)
    | extend ActorIP = tostring(InitiatedBy.user.ipAddress)
    | extend Changes = tostring(TargetResources[0].modifiedProperties)
    | project TimeGenerated, ControlPlane, OperationName, TargetName, Actor, ActorIP, Result = tostring(Result), Changes;
let IntuneChanges = union isfuzzy=true
    (datatable(TimeGenerated: datetime, OperationName: string, Properties: string, ResultType: string)[]),
    (IntuneAuditLogs
    | where ingestion_time() > ago(Detection)
    | where TimeGenerated > ago(Detection + LateArrival)
    | where OperationName has_any ("Compliance", "DeviceCompliancePolicy"))
    | extend Payload = parse_json(Properties)
    | extend ControlPlane = "Intune compliance"
    | extend TargetName = tostring(Payload.TargetDisplayNames[0])
    | extend Actor = tostring(Payload.Actor.UPN)
    | extend ActorIP = ""
    | extend Result = tostring(ResultType)
    | extend Changes = tostring(Payload.TargetObjectIds)
    | project TimeGenerated, ControlPlane, OperationName, TargetName, Actor, ActorIP, Result, Changes;
union EntraChanges, IntuneChanges
| where isnotempty(Actor)
| extend IsAutomation = Actor has_any ("Microsoft Managed Policy Manager", "MS-PIM", "Microsoft Approval Management")
| extend AccountName = tostring(split(Actor, "@")[0])
| extend UPNSuffix = tostring(split(Actor, "@")[1])
| project TimeGenerated, ControlPlane, OperationName, TargetName, Actor, AccountName, UPNSuffix, ActorIP, IsAutomation, Result, Changes
| order by TimeGenerated desc

The workbook

Eleven posture queries want one screen, not eleven tiles. Design it as a scorecard with drill-through rather than a wall of charts.

The headline is a single row of numbers that an identity lead can read in ten seconds and a director can read in three.

let Window = 30d;
let TotalSignIns = toscalar(SigninLogs | where TimeGenerated > ago(Window) | where ResultType == 0 | count);
let Phishing = dynamic(["FIDO2 security key", "Passkey", "Passkey (device-bound)", "Windows Hello for Business", "X.509 Certificate (MultiFactor)"]);
let MethodShare = toscalar(SigninLogs
    | where TimeGenerated > ago(Window)
    | where ResultType == 0
    | extend Strong = iff(tostring(AuthenticationDetails) has_any (Phishing), 1, 0)
    | summarize Share = round(100.0 * countif(Strong > 0) / count(), 1));
let ManagedShare = toscalar(SigninLogs
    | where TimeGenerated > ago(Window)
    | where ResultType == 0
    | summarize Share = round(100.0 * countif(tostring(DeviceDetail.isCompliant) == "true") / count(), 1));
let CoveredShare = toscalar(SigninLogs
    | where TimeGenerated > ago(Window)
    | where ResultType == 0
    | extend Enforced = iff(tostring(ConditionalAccessPolicies) has_any ('"result":"success"', '"result": "success"', '"result":"failure"', '"result": "failure"'), 1, 0)
    | summarize Share = round(100.0 * countif(Enforced > 0) / count(), 1));
let ExternalTenants = toscalar(SigninLogs
    | where TimeGenerated > ago(Window)
    | where ResultType == 0
    | where isnotempty(CrossTenantAccessType) and CrossTenantAccessType != "none"
    | summarize Tenants = dcount(HomeTenantId));
print SuccessfulSignIns = TotalSignIns, PhishingResistantPercent = MethodShare, CompliantDevicePercent = ManagedShare, PolicyCoveragePercent = CoveredShare, ExternalTenantsPresent = ExternalTenants

Rule health, because silent rules are worse than no rules

A detection that stopped running is worse than one you never built, because you are counting on it.

let Window = 14d;
let RulePrefix = "CA-";
let Firing = SecurityAlert
    | where TimeGenerated > ago(Window)
    | where AlertName startswith RulePrefix
    | summarize Alerts = count(), Entities = dcount(tostring(Entities)), LastAlert = max(TimeGenerated) by AlertName, AlertSeverity;
let Health = union isfuzzy=true
    (datatable(TimeGenerated: datetime, SentinelResourceName: string, Status: string, Description: string, SentinelResourceType: string)[]),
    (SentinelHealth
    | where TimeGenerated > ago(Window)
    | where SentinelResourceType == "Analytics Rule"
    | summarize arg_max(TimeGenerated, *) by SentinelResourceName);
Health
| where SentinelResourceName startswith RulePrefix
| join kind=leftouter (Firing) on $left.SentinelResourceName == $right.AlertName
| extend DaysSinceAlert = iff(isnull(LastAlert), -1, datetime_diff("day", now(), LastAlert))
| project RuleName = SentinelResourceName, Status, LastRun = TimeGenerated, Alerts, DaysSinceAlert, AlertSeverity, Description
| order by Status asc, DaysSinceAlert desc

Tuning, without deleting the detection

When a rule is noisy, the reflex is to add an exclusion for whatever caused the noise. Do that four times and the rule has been quietly disabled by a thousand cuts, with no record of why.

Profile it instead.

let Window = 30d;
let RuleName = "CA-Behaviour-SessionAnomaly-TokenReplayIndicators";
SecurityAlert
| where TimeGenerated > ago(Window)
| where AlertName == RuleName
| extend Details = todynamic(ExtendedProperties)
| extend Custom = todynamic(tostring(Details["Custom Details"]))
| extend Score = toint(tostring(Custom.RiskScore[0]))
| extend Indicators = tostring(Custom.RiskIndicators[0])
| extend Verdict = tostring(Status)
| summarize Alerts = count(), Accounts = dcount(tostring(Entities)), Scores = make_set(Score, 20) by Indicators, Verdict
| extend IndicatorCount = array_length(split(Indicators, " | "))
| order by Alerts desc

Closing the series

Six posts. Twenty-five queries, a persona model, a naming convention, a baseline policy set, an authentication strength framework, a device control model, a token position, a cross-tenant architecture, and now an operating model to hold it together.

If you take one thing from all of it, take this. Conditional Access is not a list of policies. It is an access control system with an evaluation model, a coverage surface, and a set of assumptions that decay quietly if nobody measures them. The portal will happily show you twenty green policies while an entire population of your estate authenticates outside all of them, on devices nobody assessed, with a text message, into resources your design never considered.

None of the six posts describes a control that stops a determined attacker who owns an endpoint. That was worth saying in part four and it is worth repeating at the end. What this design does is make the easy attacks hard, make the hard attacks visible, and make the failures loud rather than quiet. That is a realistic ambition and it is achievable in about a quarter of deliberate work.

Start with the counting exercise from part one. Open the policy list, count what is there, and write down what each one is for. Everything else in these six posts follows from being able to finish that sentence.

Class dismissed.