Analytics Entra ID Role Assignments

Query

// This query can help you to detect Entra ID role assignments.
//
// You can call this function by running this same query code, or if you save this function as "EntraIDRoleAssignments", by simply calling:
//
// EntraIDRoleAssignments
//
//let Function = (){
let _PIM_ids = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "PrivilegedIdentityManagement" and Notes has "[App]"
    | summarize make_list(ActorId)
);
let _PrivEntraIDRoles = toscalar(
    _GetWatchlist("RegEx-PrivEntraIDRoles")
    | summarize RegEx = make_list(RegEx)
    | extend RegEx = strcat(@'^(', strcat_array(RegEx, '|'), @')$')
);
let _RoleManagementPIM = materialize(
    AuditLogs
    | where Category == "RoleManagement" and LoggedByService == "PIM" and OperationName has_any ("to role", "from role", "role assignment")
    | mv-apply AdditionalDetail = AdditionalDetails on (
        summarize ParsedAdditionalDetails = make_bag(bag_pack(tostring(AdditionalDetail["key"]), tostring(AdditionalDetail["value"])))
    )
    | mv-apply TargetResource = TargetResources on (
        summarize TargetResource = make_list(TargetResource) by TargetResourceType = tostring(TargetResource["type"])
        | extend TargetResource = iff(array_length(TargetResource) == 1, TargetResource[0], TargetResource)
        | summarize ParsedTargetResources = make_bag(bag_pack(TargetResourceType, TargetResource))
    )
    | project
        TimeGenerated,
        LoggedByService,
        Category,
        AADOperationType,
        OperationName,
        Result,
        ResultReason,
        ActivateOperation = OperationName has_any ("activation", "deactivate"),
        EligibleOperation = OperationName has "eligible",
        RemoveOperation = OperationName has "remove",
        PermanentOperation = OperationName has "permanent",
        // Information about the actor
        ActorIdentity = Identity,
        // Information about the actor, if it was a user
        ActorUserId = tostring(InitiatedBy["user"]["id"]),
        ActorUserPrincipalName = tolower(tostring(InitiatedBy["user"]["userPrincipalName"])),
        ActorUserIPAddress = tostring(coalesce(InitiatedBy["user"]["ipAddress"], ParsedAdditionalDetails["ipaddr"])),
        ActorUserRoles = tostring(InitiatedBy["user"]["roles"]),
        // Information about the actor, if it was an application
        ActorAppName = tostring(InitiatedBy["app"]["displayName"]),
        ActorAppId = tostring(InitiatedBy["app"]["appId"]),
        ActorAppServicePrincipalName = tostring(InitiatedBy["app"]["servicePrincipalName"]),
        ActorAppServicePrincipalId = tostring(InitiatedBy["app"]["servicePrincipalId"]),
        // Information about the role
        RoleProvider = tostring(ParsedTargetResources["Provider"]["displayName"]),
        TargetRoleDisplayName = tostring(ParsedTargetResources["Role"]["displayName"]),
        TargetRoleDefinitionOriginType = tostring(ParsedAdditionalDetails["RoleDefinitionOriginType"]),
        TargetRoleDefinitionOriginId = tostring(ParsedAdditionalDetails["RoleDefinitionOriginId"]), // (!= TargetRoleTemplateId == TargetRoleDefitionObjectId for CustomRoles)
        TargetRoleTemplateId = tostring(ParsedAdditionalDetails["TemplateId"]),
        // Information about the target object
        TargetType = tostring(coalesce(ParsedTargetResources["User"]["type"], ParsedTargetResources["ServicePrincipal"]["type"])),
        TargetId = tostring(coalesce(ParsedTargetResources["User"]["id"], ParsedTargetResources["ServicePrincipal"]["id"])),
        TargetDisplayName = tostring(coalesce(ParsedTargetResources["User"]["displayName"], ParsedTargetResources["ServicePrincipal"]["displayName"])),
        TargetUserPrincipalName = tolower(tostring(ParsedTargetResources["User"]["userPrincipalName"])),
        // Other information
        ExpirationTime = todatetime(ParsedAdditionalDetails["ExpirationTime"]),
        TriggeredByTargetSubType = tostring(ParsedAdditionalDetails["TriggeredByTargetSubType"]),
        // JSONs containers
        InitiatedBy,
        AdditionalDetails,
        TargetResources,
        CorrelationId
    | summarize
        StartTime = min(TimeGenerated),
        EndTime = max(TimeGenerated),
        RequestedTimeGenerated = make_list_if(TimeGenerated, OperationName has "requested"),
        CompletedTimeGenerated = make_list_if(TimeGenerated, not(OperationName has "requested") and Result == "success"),
        RequestedCount = countif(OperationName has "requested"),
        CompletedCount = countif(not(OperationName has "requested") and Result == "success"),
        arg_max(TimeGenerated, *)
        by CorrelationId, ActivateOperation, EligibleOperation, RemoveOperation, PermanentOperation, TargetRoleDefinitionOriginId, TargetRoleTemplateId, TargetId
    | extend
        RequestedTimeGenerated = iff(array_length(RequestedTimeGenerated) == 0, dynamic(null), RequestedTimeGenerated),
        RequestedCount = iff(RequestedCount == 0, int(null), RequestedCount)
);
let _RoleManagementCoreDirectory = materialize(
    AuditLogs
    | where Category == "RoleManagement" and LoggedByService == "Core Directory" and AADOperationType in ("Assign", "Unassign")
    | mv-apply ModifiedProperty = TargetResources[0]["modifiedProperties"] on (
        summarize ModifiedProperties = make_bag(bag_pack(tostring(ModifiedProperty["displayName"]), replace_string(tostring(iff(OperationName has "remove", ModifiedProperty["oldValue"], ModifiedProperty["newValue"])), '"', "")))
    )
    | project
        TimeGenerated,
        LoggedByService,
        Category,
        AADOperationType,
        OperationName,
        EligibleOperation = OperationName has "eligible",
        RemoveOperation = OperationName has "remove",
        Result,
        // Information about the actor
        ActorIdentity = Identity,
        // Information about the actor, if it was a user
        ActorUserId = tostring(InitiatedBy["user"]["id"]),
        ActorUserPrincipalName = tolower(tostring(InitiatedBy["user"]["userPrincipalName"])),
        ActorUserIPAddress = tostring(InitiatedBy["user"]["ipAddress"]),
        ActorUserRoles = tostring(InitiatedBy["user"]["roles"]),
        // Information about the actor, if it was an application
        ActorAppName = tostring(InitiatedBy["app"]["displayName"]),
        ActorAppId = tostring(InitiatedBy["app"]["appId"]),
        ActorAppServicePrincipalName = tostring(InitiatedBy["app"]["servicePrincipalName"]),
        ActorAppServicePrincipalId = tostring(InitiatedBy["app"]["servicePrincipalId"]),
        // Information about the role
        TargetRoleDisplayName = tostring(coalesce(ModifiedProperties["Role.DisplayName"], ModifiedProperties["RoleDefinition.DisplayName"])),
        //TargetRoleObjectId = tostring(ModifiedProperties["Role.ObjectID"]),
        TargetRoleDefinitionOriginId = tostring(ModifiedProperties["RoleDefinition.ObjectID"]),
        TargetRoleTemplateId = tostring(ModifiedProperties["Role.TemplateId"]),
        TargetRoleWellKnownObjectName = tostring(ModifiedProperties["Role.WellKnownObjectName"]),
        // Information about the target object
        TargetType = tostring(TargetResources[0]["type"]),
        TargetId = tostring(TargetResources[0]["id"]),
        TargetDisplayName = tostring(TargetResources[0]["displayName"]), // In case of service principal added to role
        TargetUserPrincipalName = tolower(TargetResources[0]["userPrincipalName"]), // In case of user added to role
        // JSONs containers
        InitiatedBy,
        AdditionalDetails,
        TargetResources,
        CorrelationId
    | extend
        PermanentOperation = iff(ActorIdentity == "MS-PIM" and ActorAppServicePrincipalId in (_PIM_ids), bool(null), true),
        TargetRoleDefinitionOriginType = iff(isnotempty(TargetRoleWellKnownObjectName), "BuiltInRole", "")
);
union
    (
    _RoleManagementPIM
    | extend TargetRoleKey = iff(TargetRoleDefinitionOriginType == "CustomRole", TargetRoleDefinitionOriginId, TargetRoleTemplateId)
    | lookup kind=leftouter (
        _RoleManagementCoreDirectory
        | extend TargetRoleKey = iff(isempty(TargetRoleTemplateId), TargetRoleDefinitionOriginId, TargetRoleTemplateId)
        | where ActorAppName == "MS-PIM" and ActorAppServicePrincipalId  in (_PIM_ids) and isnotempty(TargetRoleKey) and isnotempty(TargetId) and isnotempty(EligibleOperation) and isnotempty(RemoveOperation)
        | project-rename
            CoreDirectory_TimeGenerated = TimeGenerated,
            CoreDirectory_CorrelationId = CorrelationId,
            CoreDirectory_TargetRoleDisplayName = TargetRoleDisplayName,
            CoreDirectory_InitiatedBy = InitiatedBy,
            CoreDirectory_AdditionalDetails = AdditionalDetails,
            CoreDirectory_TargetResources = TargetResources
        | project-away LoggedByService, Category, AADOperationType, OperationName, PermanentOperation, Result, ActorIdentity, ActorUserId, ActorUserPrincipalName, ActorUserIPAddress, ActorUserRoles, ActorAppId, ActorAppName, ActorAppServicePrincipalId, ActorAppServicePrincipalName, TargetRoleDefinitionOriginType, TargetRoleDefinitionOriginId, TargetRoleTemplateId, TargetType, TargetDisplayName, TargetUserPrincipalName
        ) on TargetRoleKey, TargetId, EligibleOperation, RemoveOperation
    | where not(isnotempty(CoreDirectory_TimeGenerated) and CoreDirectory_TimeGenerated > EndTime)
    | summarize arg_max(CoreDirectory_TimeGenerated, *) by TimeGenerated, CorrelationId, TargetRoleKey, TargetId, EligibleOperation, RemoveOperation
    | project-away CoreDirectory_TimeGenerated, TargetRoleKey
    | project-rename
        PIM_CorrelationId = CorrelationId,
        PIM_InitiatedBy = InitiatedBy,
        PIM_AdditionalDetails = AdditionalDetails,
        PIM_TargetResources = TargetResources
    ),
    (
    _RoleManagementCoreDirectory
    | extend TargetRoleKey = iff(isempty(TargetRoleTemplateId), TargetRoleDefinitionOriginId, TargetRoleTemplateId)
    | where not(ActorAppName == "MS-PIM" and ActorAppServicePrincipalId  in (_PIM_ids) and isnotempty(TargetRoleKey) and isnotempty(TargetId) and isnotempty(EligibleOperation) and isnotempty(RemoveOperation))
    | project-rename
        CoreDirectory_CorrelationId = CorrelationId,
        CoreDirectory_TargetRoleDisplayName = TargetRoleDisplayName,
        CoreDirectory_InitiatedBy = InitiatedBy,
        CoreDirectory_AdditionalDetails = AdditionalDetails,
        CoreDirectory_TargetResources = TargetResources
    | project-away TargetRoleKey
    )
| where not(LoggedByService == "PIM" and isnotempty(CompletedCount) and CompletedCount == 0)
| extend
    PrivilegedRole = TargetRoleDisplayName matches regex _PrivEntraIDRoles or CoreDirectory_TargetRoleDisplayName  matches regex _PrivEntraIDRoles,
    WorkingTime = IsWorkingTime(TimeGenerated),
    AlertName = strcat(
        iff(RemoveOperation, "Remove", "Add"),
        " ",
        iff(PermanentOperation, "permanent", iff(not(ActivateOperation), "temporary", "activated")),
        " ",
        iff(EligibleOperation, "eligible ", ""),
        "member ",
        iff(RemoveOperation, "from", "to"),
        " Entra ID role"),
    AlertDescription = strcat(
        'This rule detects operations with Entra ID roles.\n\nThe ',
        case(TargetType == "User", 'user', TargetType == "ServicePrincipal", 'service principal', 'member'),
        case(TargetType == "User", strcat(' "', TargetDisplayName, '" (', TargetUserPrincipalName, ')'), strcat(' "', coalesce(TargetDisplayName, TargetId), '"')),
        ' was ',
        iff(RemoveOperation, 'removed from', 'added to'),
        ' role "',
        coalesce(TargetRoleDisplayName, CoreDirectory_TargetRoleDisplayName),
        '" by "',
        coalesce(ActorUserPrincipalName, ActorAppName, ActorIdentity),
        '".\n'
    )
| extend AlertSeverity = case(
    // Direct Core Directory operations
    not(LoggedByService == "PIM"), "High",
    // Non-user target
    not(TargetType == "User"), "High",
    // Remove operations by Azure AD PIM (assumedly automatic)
    RemoveOperation and ActorIdentity == "Azure AD PIM" and (ActorUserId in (_PIM_ids) or ActorAppServicePrincipalId in (_PIM_ids)), "Informational",
    // Voluntary deactivation operations
    AADOperationType == "DeactivateRole", "Informational",
    // Activation
    AADOperationType == "ActivateRole" and not(WorkingTime), "Medium",
    AADOperationType == "ActivateRole" and WorkingTime, "Informational",
    // Non-working hours
    not(WorkingTime), "High",
    // Working hours
    WorkingTime and PrivilegedRole and PermanentOperation, "Medium",
    WorkingTime and PrivilegedRole and not(PermanentOperation), "Medium",
    WorkingTime and not(PrivilegedRole) and PermanentOperation, "Low",
    WorkingTime and not(PrivilegedRole) and not(PermanentOperation), "Informational",
    "High"
    )
| project
    TimeGenerated,
    StartTime,
    EndTime,
    LoggedByService,
    Category,
    AADOperationType,
    OperationName,
    Result,
    ResultReason,
    // WorkingTime,
    // ActivateOperation,
    // EligibleOperation,
    // RemoveOperation,
    // PermanentOperation,
    ActorIdentity,
    ActorUserId,
    ActorUserPrincipalName,
    ActorUserIPAddress,
    ActorUserRoles,
    ActorAppName,
    ActorAppId,
    ActorAppServicePrincipalName,
    ActorAppServicePrincipalId,
    // RoleProvider,
    TargetRoleDisplayName,
    CoreDirectory_TargetRoleDisplayName,
    TargetRoleWellKnownObjectName,
    TargetRoleDefinitionOriginType,
    TargetRoleDefinitionOriginId,
    TargetRoleTemplateId,
    //PrivilegedRole,
    TargetType,
    TargetId,
    TargetDisplayName,
    TargetUserPrincipalName,
    ExpirationTime,
    TriggeredByTargetSubType,
    PIM_CorrelationId,
    PIM_InitiatedBy,
    PIM_AdditionalDetails,
    PIM_TargetResources,
    CoreDirectory_CorrelationId,
    CoreDirectory_InitiatedBy,
    CoreDirectory_AdditionalDetails,
    CoreDirectory_TargetResources,
    AlertName,
    AlertSeverity,
    AlertDescription
//};
//Function()

Explanation

This query is designed to detect role assignments in Entra ID (formerly Azure Active Directory) by analyzing audit logs. Here's a simplified breakdown of what the query does:

Initialization:
- It defines a function that can be saved and reused to detect Entra ID role assignments.
- It retrieves specific watchlists that contain information about significant activities and privileged roles.
Data Collection:
- It gathers audit logs related to role management activities, specifically those logged by Privileged Identity Management (PIM) and the Core Directory.
- It processes these logs to extract detailed information about the operations, such as who performed the action, what role was affected, and the target of the role assignment.
Data Processing:
- The query processes the logs to identify various operations like role activation, deactivation, eligibility changes, and removals.
- It distinguishes between operations performed by users and applications, and whether these operations were permanent or temporary.
Data Analysis:
- It combines data from PIM and Core Directory logs to provide a comprehensive view of role assignments.
- It checks if the role assignments involve privileged roles and whether they occurred during working hours.
Alert Generation:
- The query generates alerts based on the type of operation, the nature of the role (privileged or not), and the timing of the operation (working hours or not).
- It assigns a severity level to each alert (e.g., High, Medium, Low, Informational) based on these factors.
Output:
- The final output includes detailed information about each role assignment operation, such as the time it occurred, the actor involved, the role affected, and a descriptive alert message.

This query is useful for monitoring and auditing role assignments in Entra ID, helping organizations detect unauthorized or unusual role changes.