Rank active medium/high risk users
Finding
The initial search ranks medium/high at-risk identities and preserves the risk event types, IPs, and correlation context.
Identity risk defines the population; it does not by itself prove compromise.
View query
Q-01First searchRank active medium/high risk users
What this checks
Build the identity-first hunt population before looking at device-code flow.
KQL
AADUserRiskEvents
| where TimeGenerated >= ago(7d)
| where RiskLevel in~ ("medium","high")
| where RiskState in~ ("atRisk","confirmedCompromised")
| extend AccountUpn=tolower(UserPrincipalName)
| summarize
FirstRisk=min(TimeGenerated),
LastRisk=max(TimeGenerated),
RiskEvents=make_set(RiskEventType,50),
RiskLevels=make_set(RiskLevel,10),
RiskStates=make_set(RiskState,10),
SourceIPs=make_set(IpAddress,50),
Correlations=make_set(CorrelationId,50)
by AccountUpn, UserId
| order by LastRisk descSPL
index=<entra_risk_index> sourcetype=<entra_user_risk_sourcetype>
earliest=-7d
| eval
user=lower(coalesce(user,UserPrincipalName,user_principal_name)),
risk_type=coalesce(risk_type,RiskEventType),
risk_level=lower(coalesce(risk_level,RiskLevel)),
risk_state=lower(coalesce(risk_state,RiskState)),
src=coalesce(src,IpAddress),
correlation_id=coalesce(correlation_id,CorrelationId)
| where risk_level IN ("medium","high") AND risk_state IN ("atrisk","confirmedcompromised")
| stats
min(_time) as first_risk
max(_time) as last_risk
values(risk_type) as risk_events
values(risk_level) as risk_levels
values(risk_state) as risk_states
values(src) as source_ips
values(correlation_id) as correlations
by user
| convert ctime(first_risk) ctime(last_risk)
| sort - last_riskWhat to look for
A prioritized set of risky identities with risk types, IPs, timing, and correlation identifiers.
Technical details
Tested signal
Medium/high active or confirmed-compromised user-risk events.
Assumptions
- Entra ID Protection user-risk telemetry is available.
Data requirements and relevant fields
- identity
Microsoft Entra ID Protection user-risk events with identity, event type, risk level/state, source IP, and sign-in correlation identifiers.
TimeGeneratedActivityDateTimeDetectedDateTimeUserPrincipalNameUserIdIpAddressCorrelationIdRequestIdRiskEventTypeRiskLevelRiskStateRiskDetailDetectionTimingTypeActivity
KQL schema
Validate table availability, Entra ID Protection licensing, connector retention, and local field population.
SPL schema
Replace index/sourcetype placeholders and map user-risk and sign-in concepts to the local Entra export.
Limitations
- Risk can be benign or delayed; it is a starting population, not a verdict.
KQL uses Microsoft Entra / Defender XDR identity telemetry. SPL is a normalized raw-event scaffold and requires local field mapping.