Query Details
Audit Logs User Statechangedfrom Guestto Member
Query
AuditLogs
| where Category == "UserManagement"// and OperationName has_any ("Update user")
| where TargetResources has_any ("Guest", "#EXT#")
| mv-expand TargetResource = TargetResources
// | where TargetResource["type"] == "User"
| mv-apply modifiedProperty = TargetResource["modifiedProperties"] on (
summarize modifiedProperties = make_bag(
bag_pack(tostring(modifiedProperty["displayName"]),
bag_pack("oldValue", trim(@'[\"\s]+', tostring(modifiedProperty["oldValue"])),
"newValue", trim(@'[\"\s]+', tostring(modifiedProperty["newValue"])))))
)
| where case(
isnotempty(modifiedProperties["TargetId.UserType"]["oldValue"]) and tostring(modifiedProperties["TargetId.UserType"]["oldValue"]) != tostring(modifiedProperties["TargetId.UserType"]["newValue"]), true,
tostring(modifiedProperties["UserType"]["oldValue"]) != "[]" and tostring(modifiedProperties["UserType"]["oldValue"]) != tostring(modifiedProperties["UserType"]["newValue"]), true,
tostring(modifiedProperties["UserPrincipalName"]["oldValue"]) != "[]" and tostring(modifiedProperties["UserPrincipalName"]["oldValue"]) != tostring(modifiedProperties["UserPrincipalName"]["newValue"]), true,
false
)
| 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"])),
IPAddress = tostring(InitiatedBy[tostring(bag_keys(InitiatedBy)[0])]["ipAddress"]),
TargetUserPrincipalName = tostring(TargetResource["userPrincipalName"]),
TargetId = tostring(TargetResource["id"])
| project
TimeGenerated,
Category,
Identity,
Initiator,
IPAddress,
OperationName,
Result,
TargetUserPrincipalName,
InitiatorId,
TargetId,
InitiatedBy,
AdditionalDetails,
TargetResources,
CorrelationId
Explanation
This query is looking at audit logs for user management activities. Specifically, it's looking for instances where a user's properties have been modified, such as their user type or user principal name.
The query checks if the target resources include "Guest" or "#EXT#", and then expands the target resources. It then applies a function to summarize the modified properties, including the old and new values.
The query then checks if the old and new values of certain properties are different, indicating a change has been made.
Finally, it extracts additional information about the initiator of the change (either a user or an app), the IP address from which the change was made, and details about the target user.
The results are then projected to include the time the change was made, the category of the change, the identity of the initiator, the operation name, the result of the operation, and other relevant details.
Details
Jose Sebastián Canós
Released: May 4, 2023
Tables
Keywords
Operators