Audit Logs User Statechangedfrom Guestto Member

Query

let _ExpectedInitiators = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "UserTypeChange"
    | summarize make_list(ActorId)
);
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
    OldType = coalesce(tostring(modifiedProperties["UserType"]["oldValue"]), tostring(modifiedProperties["TargetId.UserType"]["oldValue"])),
    NewType = coalesce(tostring(modifiedProperties["UserType"]["newValue"]), tostring(modifiedProperties["TargetId.UserType"]["newValue"])),
    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"]),
    Target = tostring(TargetResource["userPrincipalName"]),
    TargetId = tostring(TargetResource["id"])
| where not(InitiatorId in (_ExpectedInitiators))
| project
    TimeGenerated,
    LoggedByService,
    Category,
    AADOperationType,
    Initiator,
    IPAddress,
    OperationName,
    Result,
    Target,
    OldType,
    NewType,
    InitiatorId,
    TargetId,
    InitiatedBy,
    AdditionalDetails,
    TargetResources,
    CorrelationId

Explanation

This KQL query is designed to identify and analyze changes in user types within an organization's audit logs, specifically focusing on unexpected changes. Here's a simplified breakdown of what the query does:

Define Expected Initiators:
- It first retrieves a list of expected initiators (ActorIds) from a watchlist named "Activity-ExpectedSignificantActivity" where the activity is "UserTypeChange". This list is stored in a variable called _ExpectedInitiators.
Filter Audit Logs:
- It filters the AuditLogs table to include only entries related to "UserManagement" activities.
- It further narrows down the entries to those involving target resources that are either "Guest" users or contain "#EXT#", indicating external users.
Expand and Analyze Modified Properties:
- The query expands the TargetResources to analyze each resource individually.
- It applies a transformation to extract and summarize the modified properties of each target resource, focusing on changes in user type.
Identify User Type Changes:
- It checks if there is a change in the user type by comparing the old and new values of the user type properties.
- It only considers entries where a change in user type is detected.
Exclude Expected Initiators:
- It excludes any changes initiated by expected initiators (those listed in _ExpectedInitiators).
Project Relevant Information:
- Finally, it selects and projects relevant information such as the time of the event, initiator details, IP address, operation name, result, target user details, old and new user types, and additional details for further analysis.

In summary, the query identifies unexpected changes in user types for guest or external users by filtering out changes initiated by known expected actors, allowing for the detection of potentially unauthorized or suspicious activities.

Details

Jose Sebastián Canós

Released: August 8, 2025

Tables

_GetWatchlistAuditLogs

Keywords

UserManagementActivityLogsAuditTargetResourcesInitiatorIPAddressOperationNameResult

Operators

lettoscalar_GetWatchlistwheresummarizemake_listhas_anymv-expandmv-applymake_bagbag_packtostringtrimcaseisnotemptycoalesceiifbag_keysnotinproject

Actions

GitHub

KQL Search