5.4.2. KQL, Workbooks, and Identity Secure Score
💡 First Principle: Exported data earns its cost when questions become queries and queries become standing visibility. KQL is the question language, workbooks are questions made into dashboards, and Identity Secure Score is Microsoft asking its standing questions about your posture — three layers of the same practice.
KQL essentials at exam depth — read a query and predict its output. The Entra tables in Log Analytics: SigninLogs, AuditLogs, AADNonInteractiveUserSignInLogs, AADServicePrincipalSignInLogs, ProvisioningLogs, plus RiskyUsers/AADUserRiskEvents with P2. The verb pipeline reads left to right:
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != 0
| where Location == "RU" or RiskLevelDuringSignIn == "high"
| summarize FailedAttempts = count() by UserPrincipalName, AppDisplayName
| order by FailedAttempts desc
| take 20
where filters (ResultType 0 = success; nonzero codes are failures), summarize ... by aggregates, order by/take shape output. Common patterns: failed sign-ins by user, sign-ins by legacy auth client, CA policy results (ConditionalAccessPolicies is a parseable array), break-glass account activity. Alert rules in Azure Monitor turn a query crossing a threshold into email/Teams/SIEM action — the mechanism behind 5.3.3's break-glass alerting.
Workbooks are parameterized, shareable dashboards over the same workspace — Microsoft ships identity templates worth naming: Sign-in analysis, Conditional Access insights and reporting (2.2.4's report-only companion), sensitive operations report, legacy authentication usage (the tool for "find what breaks before we block legacy auth"). Usage & insights in the portal gives per-app sign-in health without KQL.
Identity Secure Score grades posture against Microsoft's recommendations — each improvement action (enable MFA broadly, block legacy auth, fewer Global Admins, enable risk policies) shows points, impact, and configuration steps; score trends feed leadership reporting. It's assessment, not enforcement: the score moves when you implement actions, and "compare our posture to peers / track improvement over time" stems land here rather than on any log.
⚠️ Exam Trap: Secure Score recommends; it changes nothing by itself, and score-chasing without change management (e.g., blocking legacy auth cold) causes the outages the legacy authentication workbook exists to prevent. Sequence: workbook to measure impact → remediate → score reflects it.
Reflection Question: Sketch the KQL skeleton (tables, key where clauses) for: "all successful sign-ins by the two break-glass accounts, ever" — then name the two configuration steps that had to exist before this query could return anything.
