Privileged Identity Info

Query

id: fc353efc-4846-49ae-8328-39fabbbed5a6
Function:
  Title: 'Parser to get summarized overview of privileged identities from IdentityInfo table with all directory role assignments, enriched with details from my EntraOps classification and role definitions.'
  Version: '1.1.0'
  LastUpdated: '2025-06-23'
Category: Microsoft Defender XDR Function
FunctionName: PrivilegedIdentityInfo
FunctionAlias: PrivilegedIdentityInfo
FunctionQuery: |
    let PrivilegedIdentityInfo = (UserPrincipalName:string, ObjectId:string, EntraRoleDefinitionName:string="", EntraRolePermission:string="", LookbackTimestamp:datetime=datetime(now)) {
        let SensitiveEntraDirectoryRoles = externaldata(RoleName: string, RoleId: string, isPrivileged: bool, Categories:string, Classification: dynamic, RolePermissions: dynamic)["https://raw.githubusercontent.com/Cloud-Architekt/AzurePrivilegedIAM/main/Classification/Classification_EntraIdDirectoryRoles.json"] with(format='multijson')
        | project RoleDefinitionName = RoleName, RoleIsPrivileged = isPrivileged, Classification, RoleCategories = Categories, RolePermissions;
        let IdentityInfoUpdateInterval = -14;
        let IdentityInfoLookbackWindow = datetime_add('day', IdentityInfoUpdateInterval, LookbackTimestamp);
        let AllEntraPimRoles = IdentityInfo
            | where tolower(AccountUpn) contains tolower(UserPrincipalName) and AccountObjectId contains (ObjectId)
            | where TimeGenerated >(IdentityInfoLookbackWindow) and TimeGenerated <(LookbackTimestamp)
            | summarize arg_max(TimeGenerated, *) by AccountObjectId
            | mv-expand parse_json(PrivilegedEntraPimRoles)
            | extend RoleDefinitionName = tostring(bag_keys(PrivilegedEntraPimRoles)[0])
            | where RoleDefinitionName contains (EntraRoleDefinitionName)
            | extend PimAssignmentExpiration = tostring(PrivilegedEntraPimRoles[RoleDefinitionName][1])
            | extend PimAssignmentType = tostring(PrivilegedEntraPimRoles[RoleDefinitionName][0])
            | extend RoleAssignmentType = tostring(PrivilegedEntraPimRoles[RoleDefinitionName][2])
            | project AccountObjectId, AccountUpn, RoleDefinitionName, RoleAssignmentType, PimAssignmentType, PimAssignmentExpiration
            | sort by AccountUpn, RoleDefinitionName;    
        let EntraEligibleRoles = AllEntraPimRoles
            | where PimAssignmentType == "Eligible"
            | sort by AccountUpn, RoleDefinitionName;
        let EntraActiveRoles = IdentityInfo
            | where tolower(AccountUpn) contains tolower(UserPrincipalName) or AccountObjectId contains (ObjectId)
            | where TimeGenerated >(IdentityInfoLookbackWindow) and TimeGenerated <(LookbackTimestamp)
            | summarize arg_max(TimeGenerated, *) by AccountObjectId
            | where isnotempty(AssignedRoles)
            | mv-expand parse_json(AssignedRoles)
            | extend RoleDefinitionName = tostring(AssignedRoles)
            | where RoleDefinitionName contains (EntraRoleDefinitionName)
            | join kind = leftouter (
                AllEntraPimRoles
                | where PimAssignmentType == "Assigned"
            ) on AccountObjectId, RoleDefinitionName
            | extend PimAssignmentExpiration = coalesce(PimAssignmentExpiration, "Unknown")
            | extend PimAssignmentType = "Active"
            | extend RoleAssignmentType = coalesce(RoleAssignmentType, "Unknown")
            | project AccountObjectId, AccountUpn, RoleDefinitionName, RoleAssignmentType, PimAssignmentType, PimAssignmentExpiration
            | sort by AccountObjectId, RoleDefinitionName;
        let AllEntraRoles = union EntraEligibleRoles, EntraActiveRoles
            | where tolower(AccountUpn) contains tolower(UserPrincipalName) and AccountObjectId contains (ObjectId)
            | join kind=inner ( SensitiveEntraDirectoryRoles
                | where RolePermissions contains (EntraRolePermission)
            ) on RoleDefinitionName
            | extend AadDirectoryRoleTierLevels = parse_json(Classification.EAMTierLevelName)
            | extend Classification = case(
                AadDirectoryRoleTierLevels contains "ControlPlane", "ControlPlane",
                AadDirectoryRoleTierLevels contains "ManagementPlane", "ManagementPlane",
                AadDirectoryRoleTierLevels contains "WorkloadPlane", "WorkloadPlane",
                AadDirectoryRoleTierLevels contains "UserAccess", "UserAccess",
                "Unclassified"
            )
            | extend PimAssignmentType = iff(PimAssignmentType == "Assigned", "Active", PimAssignmentType)
            | project RoleAssignments = bag_pack_columns(RoleDefinitionName, RoleAssignmentType, PimAssignmentType, PimAssignmentExpiration, Classification, RoleIsPrivileged, RoleCategories, RolePermissions), AccountObjectId
            | summarize AssignedEntraRoles = make_set(RoleAssignments) by AccountObjectId;
        IdentityInfo
        | where tolower(AccountUpn) contains tolower(UserPrincipalName) and AccountObjectId contains (ObjectId)    
        | where TimeGenerated >(IdentityInfoLookbackWindow) and TimeGenerated <(LookbackTimestamp)
        | summarize arg_max(TimeGenerated, *) by AccountObjectId
        | join kind=inner ( AllEntraRoles ) on AccountObjectId
        | project-away ReportId, AssignedRoles, PrivilegedEntraPimRoles, AccountObjectId1
        | sort by AccountName asc
    };
    PrivilegedIdentityInfo(UserPrincipalName,ObjectId,EntraRoleDefinitionName,EntraRolePermission,LookbackTimestamp)

Explanation

This query is designed to provide a summarized overview of privileged identities from the IdentityInfo table, focusing on directory role assignments. It enriches this information with details from a classification system and role definitions. Here's a simplified breakdown of what the query does:

Data Source and Classification: It starts by pulling in a list of sensitive directory roles from an external JSON file, which includes role names, IDs, privilege status, categories, classifications, and permissions.
Time Window: It defines a lookback window of 14 days from a specified timestamp to filter relevant identity information.
Role Assignments:
- Eligible Roles: It identifies roles that users are eligible for but not currently active in.
- Active Roles: It identifies roles that users are actively assigned to, including those with unknown expiration or assignment types.
Role Enrichment: It combines eligible and active roles, filtering them based on user input and enriching them with classification details. This includes determining the role's tier level and whether it is part of the control, management, workload, or user access planes.
Output: The query outputs a structured summary of role assignments for each user, including role details, assignment types, expiration, classification, and privilege status. This is sorted by account name for easy review.

Overall, the query helps in understanding which users have privileged access, what roles they are assigned to, and the nature of these roles within the organization.

Details

Thomas Naunheim

Released: June 23, 2025

Tables

IdentityInfo

Keywords

PrivilegedIdentitiesIdentityInfoDirectoryRoleAssignmentsEntraOpsClassificationDefinitionsUserPrincipalNameObjectIdEntraRoleDefinitionNameEntraRolePermissionLookbackTimestampSensitiveEntraDirectoryRolesRoleNameRoleIdCategoriesRolePermissionsIdentityInfoUpdateIntervalIdentityInfoLookbackWindowAllEntraPimRolesAccountUpnAccountObjectIdTimeGeneratedPimAssignmentExpirationPimAssignmentTypeRoleAssignmentTypeEntraEligibleRolesEntraActiveRolesAssignedRolesAllEntraRolesAadDirectoryRoleTierLevelsRoleAssignmentsAssignedEntraRolesReportIdAccountName

Operators

letexternaldataprojectdatetime_addwheretolowercontainssummarizearg_maxmv-expandparse_jsonextendtostringsortjoincoalesceunioncaseiffbag_pack_columnsmake_setproject-away

Actions

GitHub

KQL Search