Query Details
Multiple User Reported Unusual Sign In Event As Not Legitimate
Query
AuditLogs
| where OperationName == "User reported unusual sign-in event as not legitimate"// and Category == "Authentication" and LoggedByService == "Authentication Methods"
| mv-apply AdditionalDetail = AdditionalDetails on (
summarize ParsedAdditionalDetails = make_bag(bag_pack(tostring(AdditionalDetail["key"]), tostring(AdditionalDetail["value"])))
)
| project
Report_TimeGenerated = TimeGenerated,
Report_UserId = tostring(ParsedAdditionalDetails["oid"]),
Report_IPAddress = tostring(ParsedAdditionalDetails["ipaddr"]),
Report_Initiator = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["displayName"]), tostring(InitiatedBy["user"]["userPrincipalName"])),
Report_InitiatorId = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["servicePrincipalId"]), tostring(InitiatedBy["user"]["id"])),
//IPAddress = tostring(InitiatedBy[tostring(bag_keys(InitiatedBy)[0])]["ipAddress"])
OriginalRequestId = tostring(TargetResources[0]["id"])
| join kind=leftouter SigninLogs on OriginalRequestId
//| where not(Report_IPAddress == IPAddress)
| project
Report_TimeGenerated,
Report_UserId,
Report_IPAddress,
Report_Initiator,
Report_InitiatorId,
OriginalRequestId,
CreatedDateTime,
UserPrincipalName,
UserDisplayName,
IPAddress,
Location,
AutonomousSystemNumber,
NetworkLocationDetails,
ResultType,
ResultDescription,
Status,
AuthenticationDetails,
AuthenticationRequirement,
ConditionalAccessStatus,
ConditionalAccessPolicies,
AppDisplayName,
ResourceDisplayName,
ClientAppUsed,
UserAgent,
DeviceDetail,
TokenIssuerType,
IncomingTokenType,
TokenProtectionStatusDetails,
RiskState,
RiskLevelAggregated,
RiskLevelDuringSignIn,
RiskEventTypes,
UniqueTokenIdentifier,
SessionId,
HomeTenantId,
ResourceTenantId,
CrossTenantAccessType,
UserType,
UserId,
AppId,
ResourceIdentity,
CorrelationId
Explanation
This query is designed to analyze audit logs related to user-reported unusual sign-in events that are flagged as not legitimate. Here's a simplified breakdown of what the query does:
-
Filter Audit Logs: It starts by filtering the
AuditLogstable to find entries where the operation name is "User reported unusual sign-in event as not legitimate." -
Extract Additional Details: It uses the
mv-applyoperator to extract and summarize additional details from theAdditionalDetailsfield, creating a structured bag of key-value pairs. -
Select and Rename Columns: The query projects (selects) specific columns, renaming them for clarity. It includes:
Report_TimeGenerated: The time the report was generated.Report_UserId: The user ID from the parsed additional details.Report_IPAddress: The IP address from the parsed additional details.Report_InitiatorandReport_InitiatorId: Information about who initiated the report, whether it's an app or a user.OriginalRequestId: The ID of the original request related to the sign-in event.
-
Join with Sign-in Logs: It performs a left outer join with the
SigninLogstable using theOriginalRequestIdto enrich the data with additional sign-in details. -
Project Final Output: The final output includes a comprehensive set of fields from both the audit logs and the sign-in logs, such as user details, IP address, location, authentication details, risk information, and more.
Overall, this query is used to correlate and analyze unusual sign-in events reported by users, providing a detailed view of the event and associated sign-in activity.
Details
Jose Sebastián Canós
Released: July 16, 2025
Tables
Keywords
Operators