Query Details
Multiple Azure AD Threat Intelligence
Query
let query_frequency = 5m;
let query_period = 2d;
AADUserRiskEvents
| where TimeGenerated > ago(query_period)
| where OperationName == "User Risk Detection" and Source == "IdentityProtection" and RiskEventType == "investigationsThreatIntelligence"
| summarize minTimeGenerated = min(TimeGenerated), arg_max(TimeGenerated, *) by Id
| where minTimeGenerated > ago(query_frequency)
| project
//TimeGenerated,
OperationName,
Source,
Activity,
UserDisplayName,
UserPrincipalName,
UserId,
CorrelationId,
RiskEventType,
RiskState,
RiskDetail,
RiskLevel
| as _Events
| lookup kind=leftouter (
(SigninLogs
| where TimeGenerated > ago(query_period)
| where CorrelationId in (toscalar(_Events | summarize make_list(CorrelationId))) and RiskState != "none"// and RiskEventTypes_V2 has "azureADThreatIntel"
| extend
DeviceDetail = tostring(DeviceDetail),
TimeReceived = _TimeReceived
)
| summarize
arg_max(TimeReceived, *)
by OriginalRequestId
| project
TimeGenerated,
Type,
// UserPrincipalName,
// UserDisplayName,
IPAddress,
AutonomousSystemNumber,
Location,
ResultType,
ResultDescription,
ClientAppUsed,
AppDisplayName,
ResourceDisplayName,
DeviceDetail,
UserAgent,
AuthenticationProtocol,
AuthenticationDetails,
ConditionalAccessStatus,
ConditionalAccessPolicies,
UserId,
OriginalRequestId,
CorrelationId
) on UserId, CorrelationId
| project
TimeGenerated,
OperationName,
Source,
Activity,
UserDisplayName,
UserPrincipalName,
RiskEventType,
RiskState,
RiskDetail,
RiskLevel,
Type,
IPAddress,
AutonomousSystemNumber,
Location,
ResultType,
ResultDescription,
ClientAppUsed,
AppDisplayName,
ResourceDisplayName,
DeviceDetail,
UserAgent,
AuthenticationProtocol,
AuthenticationDetails,
ConditionalAccessStatus,
ConditionalAccessPolicies,
UserId,
OriginalRequestId,
CorrelationId
Explanation
This query is designed to analyze user risk events from the Azure Active Directory (AAD) within the last 2 days (query_period). It specifically looks for events where the operation was "User Risk Detection", the source was "IdentityProtection", and the risk event type was "investigationsThreatIntelligence".
The query then filters these events to only include those that occurred within the last 5 minutes (query_frequency).
Next, the query projects or selects specific fields such as OperationName, Source, Activity, UserDisplayName, UserPrincipalName, UserId, CorrelationId, RiskEventType, RiskState, RiskDetail, and RiskLevel.
These events are then matched with SigninLogs from the same 2-day period based on UserId and CorrelationId. The SigninLogs are filtered to exclude those with a RiskState of "none".
The query then extends the data by adding DeviceDetail and TimeReceived fields.
It then summarizes the data by selecting the maximum TimeReceived for each OriginalRequestId.
Finally, it projects or selects a range of fields from both the AADUserRiskEvents and SigninLogs, including TimeGenerated, OperationName, Source, Activity, UserDisplayName, UserPrincipalName, RiskEventType, RiskState, RiskDetail, RiskLevel, Type, IPAddress, AutonomousSystemNumber, Location, ResultType, ResultDescription, ClientAppUsed, AppDisplayName, ResourceDisplayName, DeviceDetail, UserAgent, AuthenticationProtocol, AuthenticationDetails, ConditionalAccessStatus, ConditionalAccessPolicies, UserId, OriginalRequestId, and CorrelationId.
In simple terms, this query is designed to analyze recent user risk events and their associated sign-in logs, focusing on specific types of risk events and excluding those with no risk state. It then selects and displays a range of detailed information about these events and sign-ins.
Details
Jose Sebastián Canós
Released: June 8, 2023
Tables
Keywords
Operators