KQL Search

Query Details

HomeAI ToolsDevice Query

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:

  1. 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.

  2. 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.

  3. 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.

  4. 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.

  5. 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.

  6. 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.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: June 23, 2025

Tables

AuditLogs

Keywords

EntraIDRoleAssignmentsAuditLogsPrivilegedIdentityManagementActorUserApplicationRoleTargetResourcesAlert

Operators

toscalarsummarizemake_listmake_bagbag_packstrcatstrcat_arraymaterializewherehashas_anymv-applyonprojecttostringtolowercoalesceiffarray_lengthtodatetimeisnotemptyisemptyinreplace_stringlookupkindleftouterproject-renameproject-awayarg_maxunionmatchesregexIsWorkingTimecasecountifmake_list_if

Actions