Query Details
Multiple Suspicious Loginfrom Deleted External Identities
Query
let query_frequency = 1h;
let query_period = 1d;
AuditLogs
| where TimeGenerated > ago(query_frequency)
| where Category =~ "UserManagement" and OperationName =~ "Delete user"
| mv-expand TargetResource = TargetResources
| where TargetResource["type"] == "User" and TargetResource["userPrincipalName"] has "#EXT#"
| extend ParsedDeletedUserPrincipalName = extract(@"^[0-9a-f]{32}([^\#]+)\#EXT\#", 1, tostring(TargetResource["userPrincipalName"]))
| extend
Initiator = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["displayName"]), tostring(InitiatedBy["user"]["userPrincipalName"])),
InitiatorId = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["servicePrincipalId"]), tostring(InitiatedBy["user"]["id"])),
Delete_IPAddress = tostring(InitiatedBy[tostring(bag_keys(InitiatedBy)[0])]["ipAddress"])
| project
Delete_TimeGenerated = TimeGenerated,
Category,
Identity,
Initiator,
Delete_IPAddress,
OperationName,
Result,
ParsedDeletedUserPrincipalName,
InitiatedBy,
AdditionalDetails,
TargetResources,
InitiatorId,
CorrelationId
| join kind=inner (
SigninLogs
| where TimeGenerated > ago(query_period)
| summarize take_any(*) by UserPrincipalName
| extend ParsedUserPrincipalName = translate("@", "_", UserPrincipalName)
| project
SigninLogs_TimeGenerated = TimeGenerated,
UserPrincipalName,
UserDisplayName,
ResultType,
ResultDescription,
IPAddress,
LocationDetails,
AppDisplayName,
ResourceDisplayName,
ClientAppUsed,
UserAgent,
DeviceDetail,
UserId,
UserType,
OriginalRequestId,
ParsedUserPrincipalName
)
on $left.ParsedDeletedUserPrincipalName == $right.ParsedUserPrincipalName
| where Delete_TimeGenerated > SigninLogs_TimeGenerated
| project-away ParsedDeletedUserPrincipalName, ParsedUserPrincipalName
Explanation
This query is designed to track the deletion of user accounts in an audit log. It specifically looks for entries where the operation was "Delete user" under the category "UserManagement". It then expands the target resources to focus on "User" type resources and checks if the userPrincipalName contains "#EXT#".
The query also extracts the deleted user's principal name and determines who initiated the deletion, either an app or a user, and records their display name and ID. It also records the IP address from which the deletion was initiated.
The query then joins this data with sign-in logs, matching on the user principal name. It only includes sign-in logs where the sign-in occurred before the deletion.
Finally, it removes the parsed user principal names from the output.
The query runs every hour and looks at data from the past day.
In simpler terms, this query is looking for instances where a user account was deleted, who or what deleted it, and any sign-in activity from that user before the deletion.
Details
Jose Sebastián Canós
Released: May 9, 2023
Tables
Keywords
Operators