The Identity Query Library

Daily triage queries

Run these at the start of every working day. They cover the highest-priority identity security questions: are there high-risk sign-ins that succeeded, are there users with unresolved risk, and are there sign-ins that bypassed Conditional Access entirely.

// DAILY-01: High-risk successful sign-ins (last 24h)
// Priority: investigate immediately | Source: Section 1.6
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| where RiskLevelDuringSignIn in ("medium", "high")
| project TimeGenerated, UserPrincipalName, AppDisplayName,
    IPAddress,
    Country = tostring(LocationDetails.countryOrRegion),
    RiskLevelDuringSignIn, RiskEventTypes_V2,
    ConditionalAccessStatus, RiskDetail
| order by RiskLevelDuringSignIn desc, TimeGenerated desc

Every result from DAILY-01 requires action. Check RiskDetail first: "userPassedMFADrivenByRiskBasedPolicy" means the system challenged the user and they passed — likely legitimate. "none" means no risk policy fired — the sign-in succeeded without challenge, which is the most dangerous outcome. Check ConditionalAccessStatus: "notApplied" means the sign-in also bypassed all CA policies, compounding the risk.

// DAILY-02: CA coverage gaps — sign-ins with zero policy evaluation
// Priority: review weekly, investigate if count increases | Source: Section 1.5
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| where ConditionalAccessStatus == "notApplied"
| summarize
    GapCount = count(),
    Users = dcount(UserPrincipalName),
    Apps = make_set(AppDisplayName, 10)
// Track GapCount over time — it should decrease as you deploy CA policies

The third daily query checks for users with unresolved risk. Query the AADRiskyUsers table for users where RiskLevel is medium or high and RiskState is "atRisk" — these accounts need investigation or remediation. Sort by RiskLastUpdatedDateTime to see the most recently flagged accounts first. The triage decision from Section 1.6 applies: investigate, remediate (password change + session revocation), or dismiss with documented justification.

Weekly monitoring queries

Run these every Monday as part of the broader trend analysis. They track authentication method adoption, geographic anomalies, service principal behavior, and failed sign-in patterns over the previous 7 days.

The authentication method distribution query from Section 1.4 classifies all successful sign-ins by phishing-resistant, phishing-capable MFA, and weak (password-only) tiers. Run weekly to track the adoption trend — the phishing-resistant percentage should increase over time as you deploy controls in EI2. If it stalls or decreases, users are reverting to weaker methods or new users are onboarding without phishing-resistant registration. The trend line is the metric you present to leadership: "Phishing-resistant authentication coverage increased from 8% to 34% this quarter." That's a story the numbers tell without argument.

The geographic anomaly query from Section 1.11 compares the current week's sign-in countries against the 30-day baseline. Users who signed in from a country not in their baseline appear in the results. Not all are threats — travel, VPN changes, and remote work from new locations are common causes — but each should be reviewed, particularly when combined with risk signals. A user in a new country with RiskLevelDuringSignIn: "none" is probably traveling. A user in a new country with RiskLevelDuringSignIn: "medium" and MFA satisfied by claim is probably compromised.

The service principal anomaly query from Section 1.3 uses leftanti join to find service principals that signed in from IP addresses not present in the 30-day baseline. Service principals are automation accounts — they don't travel, they don't change networks, and they don't adopt new browsers. A new IP for a service principal always means something changed in the infrastructure: a deployment moved, a credential was rotated, or an attacker is using stolen credentials from their own infrastructure. The Midnight Blizzard breach from Section 0.7 was invisible precisely because nobody monitored service principal sign-in patterns.

The failed sign-in spray detection query from Section 1.9 aggregates failed sign-ins by IP address and counts distinct targeted accounts. IPs with more than 20 failures targeting more than 5 distinct accounts in a week match the password spray profile. This query catches the slow sprays that per-user threshold rules miss — an attacker trying 2 passwords per account across 200 accounts produces only 2 failures per user (below any reasonable per-user threshold) but 400 failures from the same IP (well above the IP-level threshold).

Investigation queries

Run these on demand when investigating a specific user or incident. They're parameterized with let variables that you replace with the target user or timestamp.

// INVEST-01: Complete user sign-in timeline
// Source: Sections 1.2, 1.10
let targetUser = "user@domain.com";
union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName =~ targetUser
| project TimeGenerated,
    SignInType = iff(Type == "SigninLogs", "Interactive", "Non-Interactive"),
    AppDisplayName, IPAddress,
    Country = tostring(LocationDetails.countryOrRegion),
    ResultType, ConditionalAccessStatus,
    RiskLevelDuringSignIn
| order by TimeGenerated asc

INVEST-01 is the first query you run in any identity investigation. It produces a complete chronological view of a user's authentication activity across both interactive and non-interactive tables. Every sign-in event, labelled by type, with IP, country, result, CA status, and risk level. This is the timeline that anchors the investigation.

INVEST-02 correlates the suspicious sign-in with subsequent audit activity using the cross-table join from Section 1.10. Replace the targetUser and suspiciousTime variables with the specific user and timestamp from the risky sign-in, and the query returns everything that user did in AuditLogs within the following 4 hours — consent grants, role assignments, credential additions, policy changes.

INVEST-03 checks for concurrent sessions from different IPs using the arg_max and set_difference pattern from Section 1.10. This answers the critical token theft question: is someone else using this user's session from a different location?

Baseline queries

Run monthly. The comprehensive user baseline query from Section 1.11 produces one row per user with their 30-day behavioral profile across all five dimensions: geography, temporal, device, application, and IP. Save the result. Compare against the previous month's baseline to detect drift — users whose patterns changed legitimately (updated baseline) versus users whose patterns changed without explanation (investigate).

Saving queries and naming conventions

In your Sentinel workspace, navigate to Logs. After running a query, click "Save" → "Save as query." Use the naming convention EI-[CADENCE]-[NUMBER] [Description] — for example, "EI-DAILY-01 High Risk Sign-Ins" or "EI-INVEST-01 User Timeline." Organize them into a folder called "Identity Security — EI1."

The cadence prefix tells you when to run the query without reading it. The daily triage workflow becomes: "Run all EI-DAILY queries. Triage results. Escalate anything flagged as Critical or High." The naming convention scales as you progress through the course — EI3 adds EI-CA-01 through EI-CA-XX for Conditional Access verification queries, EI5 adds EI-RISK-XX for risk policy queries, EI9 adds EI-APP-XX for application audit queries, and EI13 adds EI-DETECT-XX for the automated detection rule library. By the time you complete EI17, your workspace will contain 100+ queries organized by module origin, operational cadence, and topic.

How saved queries become detection rules

This query library is the first operational artifact you've built in this course. It's valuable as a manual toolkit — but its real power comes when EI13 converts the patterns into scheduled Sentinel analytics rules that run automatically and generate incidents.

The conversion is straightforward. The DAILY-01 query (high-risk successful sign-ins) becomes an analytics rule that runs every hour. The WEEKLY-04 query (spray detection) becomes a rule that runs every 15 minutes with entity mapping on the source IP. The INVEST-02 pattern (post-sign-in audit correlation) becomes a rule that fires when a risky sign-in is followed by a consent grant or role assignment within 60 minutes. The query logic is identical — what changes is the scheduling, the alert severity, the entity mapping, and the incident creation configuration.

The distinction matters: saved queries find threats when someone remembers to look. Detection rules find threats when nobody is looking. The query library is the foundation. EI13 is the automation layer. Both are necessary — the library for manual triage and investigation, the detection rules for continuous automated monitoring.