Query Details
Managed Identity Assignments To Resource
Query
resources | where identity has 'SystemAssigned' or type == "microsoft.managedidentity/userassignedidentities" | extend IdentityType = iff(type == "microsoft.managedidentity/userassignedidentities", 'UserAssigned', 'SystemAssigned') | extend ObjectId = iff(IdentityType == 'SystemAssigned', tostring(identity.principalId), tostring(properties.principalId)) | project ObjectId, ResourceId = tostring(tolower(id)), ResourceName = name, ResourceType = type, ResourceTags = tags, ResourceTenantId = tenantId, IdentityType | join kind=leftouter (resources | where identity.type has 'UserAssigned' | mv-expand parse_json(identity.userAssignedIdentities) | extend ResourceId = tostring(tolower(bag_keys(identity_userAssignedIdentities)[0])) | project ResourceId, AssignedResourceId = id ) on ResourceId | project-away ResourceId1 | extend AssociatedWorkloadId = iff(IdentityType == "SystemAssigned", ResourceId, AssignedResourceId) | summarize AssociatedWorkloadId=make_set(AssociatedWorkloadId) by ObjectId, ResourceId, ResourceType, ResourceTenantId, IdentityType
Explanation
This query is looking at resources and identifying if they have a system assigned or user assigned identity. It then creates a new column called IdentityType to differentiate between the two. It also extracts the ObjectId based on the IdentityType. It then projects specific columns and joins with another set of resources to get AssignedResourceId for user assigned identities. It then summarizes the data by grouping it based on ObjectId, ResourceId, ResourceType, ResourceTenantId, and IdentityType.
Details
Thomas Naunheim
Released: July 16, 2024
Tables
resources
Keywords
Devices,Intune,User
Operators
wherehasor==extendifftostringprojectjoinkindleftoutermv-expandparse_jsonbag_keysproject-awayextendmake_setbysummarize