03 Service Principal Context And Risk

Query

// Phase 3, query 3 of 4: Service principal context, Conditional Access outcome, and risk.
//
// *** DRAFT, UNTESTED *** -- see the repository README "Project status" warning.
// Column names below are confirmed against the Azure Monitor table reference
// (AADServicePrincipalSignInLogs, AADServicePrincipalRiskEvents), not against a
// live tenant's actual populated data -- confirm both tables are enabled and
// non-empty in your workspace before relying on this query.
//
// Purpose: supplies the evidence behind constraints[] of kind
// "conditionalAccessBlock" and "disabledIdentity", and behind the kill-switch
// confidence shown in the Blast Radius page of the workbook -- i.e. whether a
// proposed "disable this agent identity" action would actually be enforced
// immediately (blocked sign-ins already happening) or only take effect once
// any already-issued tokens expire.
//
// Populates (access-model-schema.json): constraints[] candidates (kind
// conditionalAccessBlock / disabledIdentity), and a secondary signal for
// credentials[].lastUsedAt.
//
// Required tables: AADServicePrincipalSignInLogs, AADServicePrincipalRiskEvents
// (both require Microsoft Entra Workload ID Premium for full risk/CA detail).
let LookbackWindow = 30d; // PLACEHOLDER
let SignInContext =
    AADServicePrincipalSignInLogs
    | where TimeGenerated > ago(LookbackWindow)
    | where isnotempty(ServicePrincipalId)
    | summarize
        LastSignInAttempt = max(TimeGenerated),
        SuccessCount = countif(ResultType == "0" or ResultType == "Success"), // PLACEHOLDER -- confirm ResultType's success encoding in your tenant; both "0" and "Success" have been observed across Entra sign-in log variants.
        FailureCount = countif(ResultType != "0" and ResultType != "Success"),
        ConditionalAccessBlockedCount = countif(ConditionalAccessStatus =~ "failure"),
        DistinctResources = dcount(ResourceDisplayName),
        IsAgenticSignIn = anyif(isnotempty(Agent), isnotempty(Agent)) // "Agent" column: "Details of agentic sign-in" per the AADServicePrincipalSignInLogs reference.
        by ServicePrincipalId, ServicePrincipalName, AppId;
let RiskContext =
    AADServicePrincipalRiskEvents
    | where TimeGenerated > ago(LookbackWindow)
    | summarize
        LatestRiskLevel = arg_max(DetectedDateTime, RiskLevel),
        LatestRiskState = arg_max(DetectedDateTime, RiskState),
        RiskEventCount = count()
        by ServicePrincipalId;
SignInContext
| join kind=leftouter (RiskContext) on ServicePrincipalId
| extend
    RecommendedConstraintKind = case(
        ConditionalAccessBlockedCount > 0, "conditionalAccessBlock",
        LatestRiskState =~ "atRisk" or LatestRiskState =~ "confirmedCompromised", "disabledIdentity", // PLACEHOLDER -- your governance process, not this query, decides whether risk alone justifies marking the identity disabled; this is a suggestion for human review, not an automatic constraint.
        "none"
    ),
    KillSwitchConfidence = case(
        FailureCount == 0 and SuccessCount > 0, "low",  // identity is actively succeeding sign-ins right now; disabling it will need token-expiry time to fully take effect, not just the disable API call.
        ConditionalAccessBlockedCount > 0,      "high", // already being blocked at sign-in time; a disable action is likely to be immediately effective.
        "medium"
    )
| project ServicePrincipalId, ServicePrincipalName, AppId, LastSignInAttempt, SuccessCount, FailureCount,
          ConditionalAccessBlockedCount, DistinctResources, IsAgenticSignIn,
          LatestRiskLevel, LatestRiskState, RiskEventCount, RecommendedConstraintKind, KillSwitchConfidence
| order by RiskEventCount desc, ConditionalAccessBlockedCount desc

Explanation

This query is designed to analyze the sign-in activity and risk events associated with service principals in Azure. It aims to provide insights into whether certain security actions, like blocking access or disabling identities, would be immediately effective or require additional time to take full effect. Here's a simplified breakdown of what the query does:

Lookback Period: It examines data from the last 30 days.
Sign-In Analysis:
- It gathers data from the AADServicePrincipalSignInLogs table.
- It calculates the last sign-in attempt, counts of successful and failed sign-ins, and how many times access was blocked due to conditional access policies.
- It also counts the distinct resources accessed and checks if the sign-in involved an agent.
Risk Analysis:
- It collects data from the AADServicePrincipalRiskEvents table.
- It identifies the most recent risk level and state, and counts the number of risk events for each service principal.
Joining Data:
- It combines the sign-in and risk data based on the service principal ID.
Recommendations and Confidence Levels:
- It suggests whether to apply a "conditionalAccessBlock" or "disabledIdentity" based on the data.
- It assesses the confidence level of a "kill-switch" action (disabling the identity) being immediately effective, based on current sign-in success and conditional access blocks.
Output:
- The query outputs relevant details like service principal ID, name, app ID, sign-in statistics, risk information, recommended actions, and confidence levels.
- It orders the results by the number of risk events and conditional access blocks, prioritizing those with higher counts.

Overall, this query helps administrators understand the security posture of service principals and make informed decisions about enforcing access controls.