In this module
EI1.12 The Identity Query Library
Figure EI1.12 — Operational workflow from input through documented output.
Figure — The Identity Query Library.
Your operational query library
This subsection collects the most important queries from EI1 into a single reference. Each query is categorized by operational use case, annotated with when to run it, and noted with the module where it was first introduced. Save these queries in your Sentinel workspace as named saved queries so you can run them with a single click.
Daily triage queries
These queries should be run at the start of every working day as part of the identity security triage workflow that EI14 formalizes.
// DAILY-01: High-risk successful sign-ins (last 24h)
// Priority: investigate immediately
// Source: EI1.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
| order by RiskLevelDuringSignIn desc, TimeGenerated desc// DAILY-02: Users at risk not yet remediated
// Priority: action within 4 hours
// Source: EI1.6
AADRiskyUsers
| where RiskLevel in ("medium", "high")
| where RiskState == "atRisk"
| project RiskLastUpdatedDateTime, UserPrincipalName,
RiskLevel, RiskDetail, RiskState
| order by RiskLastUpdatedDateTime desc// DAILY-03: Sign-ins with no conditional access evaluation
// Priority: review weekly, investigate if count increases
// Source: EI1.5
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| where ConditionalAccessStatus == "notApplied"
| summarize GapCount = count(),
Users = dcount(UserPrincipalName),
Apps = make_set(AppDisplayName, 10)
| project GapCount, Users, AppsWeekly monitoring queries
// WEEKLY-01: Authentication method distribution
// Track phishing-resistant adoption trend
// Source: EI1.4
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| extend AuthDetail = parse_json(AuthenticationDetails)
| extend PrimaryMethod = tostring(AuthDetail[0].authenticationMethod)
| extend AuthStrength = case(
PrimaryMethod in ("FIDO2 security key", "Passkey (Microsoft Authenticator)",
"Windows Hello for Business", "X.509 Certificate"), "Phishing-Resistant",
isnotempty(tostring(AuthDetail[1].authenticationMethod)), "Phishing-Capable (MFA)",
PrimaryMethod == "Password", "Weak (Password Only)",
"Other")
| summarize count() by AuthStrength// WEEKLY-02: Geographic anomalies — sign-ins from new countries
// Compare current week against 30-day baseline
// Source: EI1.11
let baseline = SigninLogs
| where TimeGenerated between (ago(30d) .. ago(7d))
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| summarize BaselineCountries = make_set(Country) by UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| where isnotempty(Country)
| join kind=inner baseline on UserPrincipalName
| where not(Country in (BaselineCountries))
| summarize NewCountries = make_set(Country), EventCount = count()
by UserPrincipalName// WEEKLY-03: Service principal anomalies — new IPs
// Source: EI1.3
let spBaseline = AADServicePrincipalSignInLogs
| where TimeGenerated between (ago(30d) .. ago(7d))
| where ResultType == 0
| distinct ServicePrincipalName, IPAddress;
AADServicePrincipalSignInLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| join kind=leftanti spBaseline on ServicePrincipalName, IPAddress
| summarize NewIPs = make_set(IPAddress, 10), EventCount = count()
by ServicePrincipalName, AppId
| where EventCount > 0// WEEKLY-04: Failed sign-in patterns (spray detection)
// Source: EI1.9
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != 0
| summarize
FailedCount = count(),
TargetedAccounts = dcount(UserPrincipalName),
FailureCodes = make_set(ResultType, 5)
by IPAddress
| where TargetedAccounts > 5 and FailedCount > 20
| order by TargetedAccounts desc// INVESTIGATE-01: Complete sign-in timeline for a specific user
// Source: EI1.2, EI1.10
let targetUser = "user@domain.com"; // Replace
union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(7d)
| where UserPrincipalName == targetUser
| project TimeGenerated,
Type = iff(Type == "SigninLogs", "Interactive", "Non-Interactive"),
AppDisplayName, IPAddress,
Country = tostring(LocationDetails.countryOrRegion),
ResultType, ConditionalAccessStatus,
RiskLevelDuringSignIn
| order by TimeGenerated asc// INVESTIGATE-02: What did a user do after a suspicious sign-in?
// Source: EI1.10
let targetUser = "user@domain.com"; // Replace
let suspiciousTime = datetime(2026-03-28T14:30:00Z); // Replace
AuditLogs
| where TimeGenerated between (suspiciousTime .. datetime_add('hour', 4, suspiciousTime))
| extend Actor = tostring(InitiatedBy.user.userPrincipalName)
| where Actor == targetUser
| project TimeGenerated, OperationName,
Target = tostring(TargetResources[0].displayName),
TargetType = tostring(TargetResources[0].type)
| order by TimeGenerated asc// INVESTIGATE-03: Concurrent sessions from different IPs
// Source: EI1.10
let targetUser = "user@domain.com"; // Replace
let timeWindow = 4h;
union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(timeWindow)
| where UserPrincipalName == targetUser
| where ResultType == 0
| summarize EventCount = count(),
Apps = make_set(AppDisplayName, 10)
by IPAddress, Type
| order by EventCount desc
// Multiple IPs for the same user = potential concurrent sessions
// Validate: are any IPs unexpected for this user?Saving queries in Sentinel
In your Sentinel workspace, navigate to Logs. After running a query, click "Save" → "Save as query." Give each query a descriptive name using the naming convention from this module: "EI-DAILY-01 High Risk Sign-Ins," "EI-WEEKLY-02 Geographic Anomalies," "EI-INVEST-01 User Timeline." Organize them into a folder called "Identity Security — EI1."
Query naming convention
Consistent naming makes the library usable at scale. As you progress through this course, the library grows — EI3 adds conditional access verification queries, EI5 adds risk policy queries, EI9 adds application audit queries, EI13 adds detection rule queries. Without a naming convention, the library becomes an unorganized list.
The convention used throughout this course:
How the library grows through the course
This EI1 query library is the foundation. Each subsequent module adds queries specific to its topic:
EI3 (Conditional Access Architecture) adds: policy evaluation verification queries, coverage gap analysis, named location validation. Naming: EI-CA-01, EI-CA-02, etc.
Try it yourself
Try It — Build Your Saved Query Library
Environment: Your M365 developer tenant with Sentinel workspace.
Exercise: Save the following queries in your Sentinel workspace as named saved queries:
1. DAILY-01: High-risk successful sign-ins 2. DAILY-03: CA coverage gaps 3. WEEKLY-01: Authentication method distribution 4. INVESTIGATE-01: Complete user timeline (with a placeholder UPN)
For each saved query, verify it runs successfully against your developer tenant data. Some queries may return zero results if your tenant does not have enough activity — that is expected. The queries are ready for when you need them.
This query library is the first operational artifact you have built in this course. By the time you complete EI17, you will have 100+ queries covering every aspect of identity security.
The myth: We saved all the EI1 queries in Sentinel. Our identity detection is set up.
The reality: Saved queries are reference tools — they run when a human clicks "Run." They do not detect anything on their own. Detection requires scheduled analytics rules that run automatically on a cadence (every 5 minutes, every hour) and create incidents when they match. EI13 converts the patterns from these saved queries into analytics rules. Until then, these queries are investigation tools and manual monitoring tools, not detection tools. The distinction matters: detection finds threats when nobody is looking. Manual queries find threats only when someone remembers to look.
NE operational context
This detection operates within NE's 18 GB/day Sentinel ingestion environment across 20 connected data sources. The rule's alert volume, TP rate, and SOC triage burden are calibrated for NE's 3-person SOC team handling 7-16 incidents per day. The detection engineer (Rachel) reviews this rule's health during the monthly tuning review (DE9.9) and adjusts thresholds, exclusions, and entity mapping as the environment evolves.
The rule's position in the overall detection library means it correlates with rules from adjacent kill chain phases — an alert from this rule gains significance when combined with alerts from earlier or later phases targeting the same entity.
You are reviewing NE's Entra ID security posture. You find 4 accounts with Global Administrator role, but NE's policy says maximum 2. The extra 2 were added during the AiTM incident for emergency response and never removed. Do you remove them?
Remove them — but through the proper process, not unilaterally. Notify the account owners that their emergency GA assignment is being revoked, confirm they have their standard role assignments restored, and document the removal with the rationale ('emergency assignment during INC-NE-2026-0227-001, no longer required'). Then add a PIR action item: 'Implement PIM time-limited role assignments for future incident response — emergency GA assignments auto-expire after 8 hours rather than persisting indefinitely.' The stale emergency assignment is a governance failure, not a technical failure — the fix is procedural.
You've mapped the identity threat landscape and learned to read sign-in logs.
EI0 established that every cloud attack starts with identity. EI1 took you through the signal that matters most — interactive, non-interactive, service principal, and managed identity sign-ins. Now you engineer the defences.
- 17 engineering modules — authentication methods, conditional access architecture, Identity Protection, PIM, token protection, application governance, and detection rules
- The Defense Design Method — the six-step framework applied to every identity control you'll build
- EI18 Capstone — Identity Security Architecture Design — design complete identity architectures for three realistic organisations (SMB, mid-market, regulated enterprise)
- Identity Security Toolkit lab pack — deployable conditional access policies, PIM configurations, and Identity Protection risk rules
- Cross-domain detection (EI16) — email-to-identity correlation and the full phishing-to-inbox-rule attack chain