💬 EN

What KQL join do I need to make alongside “authorizationresources where type == ‘microsoft.authorization/roleassignments’” to be able to have not only “properties.principalid” in my results, but also the “displayName” of that “principalId”

To include displayName alongside principalId in Azure Resource Graph queries for role assignments, you must cross-reference identity data from Azure AD. Since Azure Resource Graph does not natively store identity details like display names, you have two primary options:

Option 1: Use Azure Resource Graph + External Identity Mapping

1. Export role assignments using the authorizationresources table in Azure Resource Graph Explorer¹²:

   authorizationresources
   | where type == "microsoft.authorization/roleassignments"
   | extend principalId = tostring(properties.principalId)
   | extend principalType = tostring(properties.principalType)
   | join kind=inner (
    authorizationresources
    | where type =~ 'microsoft.authorization/roledefinitions'
    | extend roleDefinitionId = tolower(id), roleName = tostring(properties.roleName)
   ) on $left.roleDefinitionId == $right.roleDefinitionId
   | project principalId, principalType, roleName, scope = tostring(properties.scope)
   

2. Resolve display names externally:
   • PowerShell: Use Get-AzureADUser/Get-AzureADServicePrincipal with the principalId³.
   • Microsoft Graph API: Query /users/{id} or /servicePrincipals/{id} using the principalId⁴³.

Option 2: Use Log Analytics with Azure AD Audit Logs

If you need direct KQL integration, stream Azure AD audit logs to a Log Analytics workspace and join with AzureActivity/AuditLogs tables. Note: This requires diagnostic settings for Azure AD logs⁴⁵.

Example KQL for Log Analytics:

AuditLogs
| where OperationName == "Add member to role"
| extend principalId = tostring(parse_json(TargetResources)[^1_0].id)
| join kind=leftouter (
    AzureADUsers // Hypothetical table - requires Azure AD audit logs
    | extend principalId = ObjectId, displayName = DisplayName
) on principalId
| project principalId, displayName, Role = tostring(parse_json(TargetResources)[^1_0].displayName)

Key Limitations

• AzureADUsers table: Not available by default in Log Analytics⁴⁵. You must enable Azure AD audit log streaming.
• Resource Graph: Limited to RBAC metadata; no identity resolution¹⁶.
• Hybrid Approach: Export CSV from Resource Graph¹, then enrich with Graph API/PowerShell³⁶.

For immediate results, Option 1 is most practical. For automated monitoring, implement Option 2 with Azure AD diagnostics.

Good to know. What’s the code I’ll need if my outer table is “resourcecontainers where type == ‘microsoft.resources/subscriptions’” and I want to left-outer join them so that I can see all “authorizationresources where type == ‘microsoft.authorization/roleassignments’ and properties.roleDefinitionId == ‘providers/Microsoft.Authorizatoin/RoleDefinitions/8e3af657-a8ff-443c-a75c-2fe8c4bcb635’” (owners) that those subscriptions might have? I’d like to join on “resourcecontainers”’s “resourceId” column and “authorizationresources”’s “properties.scope” column.

Here’s the KQL query to join subscriptions with Owner role assignments using leftouter join:

resourcecontainers
| where type == 'microsoft.resources/subscriptions'
| project subscriptionId = resourceId, SubName = name
| join kind=leftouter (
    authorizationresources
    | where type == 'microsoft.authorization/roleassignments'
    | where properties.roleDefinitionId endswith '8e3af657-a8ff-443c-a75c-2fe8c4bcb635'
    | extend scope = tostring(properties.scope)
    | project principalId = tostring(properties.principalId), scope
) on $left.subscriptionId == $right.scope
| project-away subscriptionId1

Key components:

1. Left table: Subscriptions with resourceId aliased as subscriptionId
2. Right table: Owner role assignments filtered by role definition GUID
3. Join condition: Subscription ID matches role assignment scope
4. Output: Subscription names with principal IDs of owners (if any exist)

Note: This will show ALL subscriptions, with blank principalId fields for subscriptions without Owner assignments. Add | where isnotempty(principalId) before the final projection if you only want subscriptions with Owners.

For actual display names, you’ll still need to cross-reference principalId with Azure AD through separate queries as Azure Resource Graph doesn’t store identity information[^1^][^4^].

Modified version with role name:

resourcecontainers
| where type == 'microsoft.resources/subscriptions'
| project subscriptionId = resourceId, SubName = name
| join kind=leftouter (
    authorizationresources
    | where type == 'microsoft.authorization/roleassignments'
    | where properties.roleDefinitionId endswith '8e3af657-a8ff-443c-a75c-2fe8c4bcb635'
    | extend scope = tostring(properties.scope)
    | project principalId = tostring(properties.principalId), scope
) on $left.subscriptionId == $right.scope
| join kind=leftouter (
    authorizationresources
    | where type == 'microsoft.authorization/roledefinitions'
    | extend roleName = tostring(properties.roleName)
    | project roleDefinitionId = tolower(id), roleName
) on $right.roleDefinitionId == tolower(properties.roleDefinitionId)
| project-away subscriptionId1, roleDefinitionId1

The second join adds role names but requires role definitions to be accessible in your query scope[^2^][^4^]. Use | project-away to clean duplicate columns from joins[^3^].

For production use, consider:

1. Adding | summarize Owners=make_set(principalId) by SubName to group multiple owners
2. Filtering null principalIds with | where isnotempty(principalId)
3. Adding subscription IDs to final projection if needed (| project SubName, subscriptionId, principalId)

What if I just want “subscriptionName” and “subscriptionId” and something along the lines of “ownerPrincipalIds” as my final columns, but I want “ownerPrincipalIds” to be a list, so that I only end up with as many output rows as there are subscriptions?

Here’s the optimized KQL query to get subscription names/IDs with owner principal IDs as a list:

resourcecontainers
| where type == 'microsoft.resources/subscriptions'
| project subscriptionId = resourceId, subscriptionName = name
| join kind=leftouter (
    authorizationresources
    | where type == 'microsoft.authorization/roleassignments'
    | where properties.roleDefinitionId endswith '8e3af657-a8ff-443c-a75c-2fe8c4bcb635'
    | extend scope = tostring(properties.scope)
    | project principalId = tostring(properties.principalId), scope
) on $left.subscriptionId == $right.scope
| summarize ownerPrincipalIds = make_set(principalId) by subscriptionId, subscriptionName
| project subscriptionName, subscriptionId, ownerPrincipalIds

Key features:

1. make_set() aggregation - Creates distinct list of principal IDs per subscription[^6^][^7^]
2. Left outer join - Preserves subscriptions without owners (empty array)
3. Final projection - Clean output with exact requested columns

To convert empty arrays to “No Owners” text:

| extend ownerPrincipalIds = iff(isempty(ownerPrincipalIds), dynamic(["No Owners"]), ownerPrincipalIds)

Output example:

Important notes:

1. Empty arrays will show as [] by default
2. Principal ID resolution still requires external Azure AD lookup[^1^][^4^]
3. Role definition ID uses endswith for case-insensitive matching[^5^][^7^]

For CSV export, use Resource Graph Explorer’s export button or Search-AzGraph in PowerShell[^1^][^7^].

Thanks! No “signInName” available for the principal? (Checking again, but with a different vocab word)

Here’s the definitive answer about sign-in information in Azure Resource Graph:

No, Azure Resource Graph does not store any identity-related fields like signInName, userPrincipalName, or mail for security principals. The authorizationresources table only contains:

1. Principal ID (properties.principalId) - The GUID identifier
2. Principal Type (properties.principalType) - User/Group/ServicePrincipal
3. Role Assignment Metadata - Scope, roleDefinitionId, etc.

How to Get Sign-In Names

You must use external services to resolve principal IDs to human-readable names:

Option 1: Microsoft Graph API

GET https://graph.microsoft.com/v1.0/users/{principalId}
GET https://graph.microsoft.com/v1.0/servicePrincipals/{principalId}

Look for:

• Users: userPrincipalName (email-like) and mail
• Service Principals: displayName and appDisplayName

Option 2: Azure PowerShell

# For Users
Get-AzADUser -ObjectId "a1b2c3d4-..." | Select UserPrincipalName, Mail

# For Service Principals
Get-AzADServicePrincipal -ObjectId "e5f6g7h8-..." | Select DisplayName

Option 3: Azure CLI

# For Users
az ad user show --id "a1b2c3d4-..."

# For Service Principals
az ad sp show --id "e5f6g7h8-..."

Hybrid Solution Example

Combine Resource Graph export with PowerShell enrichment:

# 1. Get role assignments from Resource Graph
$assignments = Search-AzGraph -Query @'
authorizationresources
| where type == 'microsoft.authorization/roleassignments'
| where properties.roleDefinitionId endswith '8e3af657-a8ff-443c-a75c-2fe8c4bcb635'
| project principalId = tostring(properties.principalId)
'@

# 2. Resolve sign-in names
$results = $assignments | ForEach-Object {
    $principal = Get-AzADUser -ObjectId $_.principalId -ErrorAction SilentlyContinue
    if (-not $principal) {
        $principal = Get-AzADServicePrincipal -ObjectId $_.principalId
    }
    
    [pscustomobject]@{
        PrincipalId = $_.principalId
        SignInName  = $principal.UserPrincipalName ?? $principal.DisplayName
    }
}

Permanent Solution Architecture

For continuous monitoring:

1. Enable Azure AD audit logs to stream to Log Analytics
2. Link with Sentinel for identity correlation
3. Create a Logic App that:
   • Triggers on new role assignments
   • Resolves principal IDs using Microsoft Graph
   • Stores results in a storage account/Log Analytics

This gap exists because Azure Resource Graph intentionally doesn’t store PII (Personally Identifiable Information) for compliance reasons[^1^][^4^]. You must use the identity provider (Azure AD) directly for email-like identifiers.

What if I want to take all of the “principalId” values and pass them as a list to some sort of endpoint, to more efficiently get their UPNs?

Here’s how to efficiently resolve principal IDs to UPNs using batch processing:

1. Microsoft Graph API Batch Endpoint

Use the /$batch endpoint to process up to 20 requests per call:

POST https://graph.microsoft.com/v1.0/$batch
Content-Type: application/json

{
  "requests": [
    {
      "id": "1",
      "method": "GET",
      "url": "/users/a1b2c3d4-...?$select=userPrincipalName"
    },
    {
      "id": "2",
      "method": "GET",
      "url": "/servicePrincipals/e5f6g7h8-...?$select=appDisplayName"
    }
  ]
}

Key Features:

• Mixed entity types: Handle users/service principals in one request[^6^][^7^]
• Rate limit: 200 requests/sec per tenant (scale via multiple batches)
• Response example:

{
  "responses": [
    {
      "id": "1",
      "status": 200,
      "body": {
        "userPrincipalName": "[email protected]"
      }
    },
    {
      "id": "2",
      "status": 200,
      "body": {
        "appDisplayName": "App-Registration-Prod"
      }
    }
  ]
}

2. PowerShell Batch Processing

$principalIds = @("a1b2c3d4-...", "e5f6g7h8-...") # From your KQL results
$batchSize = 20
$results = @()

for ($i = 0; $i -lt $principalIds.Count; $i += $batchSize) {
    $batch = $principalIds[$i..($i + $batchSize - 1)]
    $batchResults = $batch | ForEach-Object -Parallel {
        $principal = Get-AzADUser -ObjectId $_ -ErrorAction SilentlyContinue
        if (-not $principal) {
            $principal = Get-AzADServicePrincipal -ObjectId $_
        }
        
        [pscustomobject]@{
            PrincipalId = $_
            UPN         = $principal.UserPrincipalName ?? $principal.DisplayName
        }
    } -ThrottleLimit 5

    $results += $batchResults
}

3. Hybrid KQL + Graph Solution

1. Export principal IDs from KQL to CSV
2. Scripted resolution: ```python

   Requires: pip install msal

   import csv from msal import ConfidentialClientApplication

app = ConfidentialClientApplication( client_id=”YOUR_CLIENT_ID”, client_credential=”YOUR_CLIENT_SECRET”, authority=”https://login.microsoftonline.com/YOUR_TENANT_ID” )

token = app.acquire_token_for_client(scopes=[“https://graph.microsoft.com/.default”])

Batch process from CSV

with open(‘principals.csv’) as f: reader = csv.DictReader(f) principal_ids = [row[‘principalId’] for row in reader]

url = “https://graph.microsoft.com/v1.0/$batch” headers = {‘Authorization’: f’Bearer {token[“access_token”]}’}

for i in range(0, len(principal_ids), 20): batch = { “requests”: [ { “id”: str(idx+1), “method”: “GET”, “url”: f”/users/{pid}?$select=userPrincipalName” } if idx < 10 else { # Example: First 10 as users, rest as SPs “id”: str(idx+1), “method”: “GET”, “url”: f”/servicePrincipals/{pid}?$select=displayName” } for idx, pid in enumerate(principal_ids[i:i+20]) ] }

response = requests.post(url, json=batch, headers=headers)
# Process responses ```

Key Considerations

• Authentication: Use app registration with User.Read.All and Application.Read.All permissions[^1^][^6^]
• Error Handling: Check for 404 Not Found (deleted principals) and 403 Forbidden (permission issues)
• Caching: Store results in Redis/Blob Storage to avoid redundant lookups
• Pagination: If returning >999 objects, implement @odata.nextLink handling[^7^]

For production systems, implement retry logic with exponential backoff and audit logging[^4^][^7^]. Use Azure Logic Apps with Managed Identity if needing low-code solutions[^7^].

So there’s really nothing in the graph API that’s equivalent to Get-MgUser -All and lets me just filter by a batch of UPNs?

Here’s the definitive answer based on current Microsoft Graph capabilities:

No native batch filtering by UPN list exists in Microsoft Graph’s stable API endpoints. However, you can achieve this through workarounds:

Workaround 1: Chained $filter with OR

GET https://graph.microsoft.com/v1.0/users?$filter=userPrincipalName eq '[email protected]' or userPrincipalName eq '[email protected]'

Limitations:

• Max 15 filters per query (practical limit before URI length restrictions)
• No array parameter support for batch UPN filtering[^3^][^7^]

Workaround 2: Batch API with Individual Requests

POST https://graph.microsoft.com/v1.0/$batch
Content-Type: application/json

{
  "requests": [
    {"id": "1", "method": "GET", "url": "/users/[email protected]"},
    {"id": "2", "method": "GET", "url": "/users/[email protected]"}
  ]
}

Advantages:

• 20 requests per batch (Microsoft’s limit)[^1^][^6^]
• Mixed entity types (users/service principals in same batch)

Workaround 3: Post-Processing Filter

$upns = @("[email protected]", "[email protected]")
$allUsers = Get-MgUser -All
$filteredUsers = $allUsers | Where-Object { $_.UserPrincipalName -in $upns }

Drawbacks:

• Inefficient for large directories (must retrieve ALL users first)[^2^][^6^]
• Rate limit risks with big tenant user counts[^7^]

Workaround 4: Advanced Query with Consistency Header

GET https://graph.microsoft.com/beta/users?$filter=userPrincipalName in ('[email protected]', '[email protected]')

Requirements:

• Beta endpoint (unsupported for production)[^1^][^3^]
• Header: ConsistencyLevel=eventual[^1^][^7^]
• Limited reliability - may not work consistently

Key Limitations

1. No IN operator for UPN arrays in stable API[^3^][^7^]
2. Beta endpoint restrictions:
   • Unsupported filter syntax variations
   • No SLA guarantees[^1^][^7^]
3. UPN vs Mail:

GET https://graph.microsoft.com/v1.0/users?$filter=mail eq '[email protected]'

Works for exact email matches, but not UPN suffix filtering[^4^][^5^]

For enterprise scenarios, implement client-side batch processing (20 UPNs per batch) with error handling and retry logic[^1^][^6^]. Microsoft’s official stance remains that complex UPN filtering requires client-side post-processing[^3^][^7^].

Production recommendation:

$upnBatch = @("[email protected]", "[email protected]") # Max 20
$batch = @{ requests = @() }

for ($i=0; $i -lt $upnBatch.Count; $i++) {
    $batch.requests += @{
        id = $i+1
        method = "GET"
        url = "/users/$($upnBatch[$i])"
    }
}

Invoke-MgGraphRequest -Method POST -Uri "https://graph.microsoft.com/v1.0/$batch" -Body $batch