08 Datalake Agent Identity Inventory Job

Query

// Phase 3, query 8 of 9: native Agent 365 identity inventory projection.
//
// The workbook queries the source tables directly through its Data lake data
// source. Use this file as an OPTIONAL one-time or scheduled KQL job when you
// want to retain a narrow projection in the analytics tier, join it to
// analytics-only sources, or reduce repeated lake-query cost. Suggested table:
//   AgentIdentityInventory_KQL_CL
//
// Sources (all confirmed data-lake schemas):
// - EntraAgentIdentityBlueprints
// - EntraAgentIdentities
// - EntraAgentUsers
//
// Security semantics:
// - The identity -> blueprint join uses
//   EntraAgentIdentities.agentAppId == EntraAgentIdentityBlueprints.appId.
// - The user -> identity join uses
//   EntraAgentUsers.agentIdentitySPID == EntraAgentIdentities.id.
// - Blueprint appRoles/oauth2PermissionScopes are retained as JSON for
//   inventory only. They are permissions EXPOSED by the blueprint app, not
//   proof of resource permissions GRANTED to it.
// - Do not calculate effective access from this output alone. Supplement it
//   with AgentsInfo.Permissions or authoritative Microsoft Graph grant reads.

let LatestBlueprints =
    EntraAgentIdentityBlueprints
    | summarize arg_max(_SnapshotTime, *) by id
    | project
        BlueprintObjectId = id,
        BlueprintAppId = appId,
        BlueprintName = displayName,
        BlueprintCreatedDateTime = createdDateTime,
        BlueprintDisabled = isDisabled,
        BlueprintDisabledByMicrosoftStatus = disabledByMicrosoftStatus,
        BlueprintSignInAudience = signInAudience,
        BlueprintPublisherDomain = publisherDomain,
        BlueprintAppRolesJson = tostring(appRoles),
        BlueprintOauth2PermissionScopesJson = tostring(oauth2PermissionScopes),
        BlueprintPreAuthorizedApplicationsJson = tostring(preAuthorizedApplications),
        BlueprintTagsJson = tostring(tags),
        BlueprintSnapshotTime = _SnapshotTime;
let LatestUsers =
    EntraAgentUsers
    | summarize arg_max(_SnapshotTime, *) by id
    | summarize
        AgentUserCount = count(),
        EnabledAgentUserCount = countif(accountEnabled),
        AgentUsers = make_set(
            bag_pack(
                "id", id,
                "displayName", displayName,
                "userPrincipalName", userPrincipalName,
                "accountEnabled", accountEnabled,
                "agentIdentityBlueprintId", agentIdentityBlueprintId
            ),
            100
        )
        by AgentIdentityObjectId = agentIdentitySPID
    | extend AgentUsersJson = tostring(AgentUsers)
    | project-away AgentUsers;
EntraAgentIdentities
| summarize arg_max(_SnapshotTime, *) by id
| project
    AgentIdentityObjectId = id,
    AgentIdentityAppId = appId,
    AgentName = displayName,
    AgentCreatedDateTime = createdDateTime,
    CreatedByAppId = createdByAppId,
    BlueprintJoinAppId = agentAppId,
    AgentAccountEnabled = accountEnabled,
    AgentStatus = iff(accountEnabled, "active", "disabled"),
    ServicePrincipalType = servicePrincipalType,
    AgentTagsJson = tostring(tags),
    AgentLifecycleJson = tostring(lifecycle),
    TenantId = tenantId,
    OrganizationId = organizationId,
    AgentSnapshotTime = _SnapshotTime,
    SourceWorkspace = _Workspace
| join kind=leftouter (LatestBlueprints)
    on $left.BlueprintJoinAppId == $right.BlueprintAppId
| join kind=leftouter (LatestUsers)
    on AgentIdentityObjectId
| extend
    BlueprintJoinStatus = iff(isnotempty(BlueprintObjectId), "matched", "unmatched"),
    AgentUserCount = coalesce(AgentUserCount, long(0)),
    EnabledAgentUserCount = coalesce(EnabledAgentUserCount, long(0)),
    DataAsOf = case(
        isnull(BlueprintSnapshotTime), AgentSnapshotTime,
        isnull(AgentSnapshotTime), BlueprintSnapshotTime,
        AgentSnapshotTime >= BlueprintSnapshotTime, AgentSnapshotTime,
        BlueprintSnapshotTime
    )
| project
    DataAsOf,
    AgentIdentityObjectId,
    AgentIdentityAppId,
    AgentName,
    AgentStatus,
    AgentAccountEnabled,
    ServicePrincipalType,
    AgentCreatedDateTime,
    CreatedByAppId,
    AgentTagsJson,
    AgentLifecycleJson,
    BlueprintObjectId,
    BlueprintAppId,
    BlueprintName,
    BlueprintJoinStatus,
    BlueprintDisabled,
    BlueprintDisabledByMicrosoftStatus,
    BlueprintSignInAudience,
    BlueprintPublisherDomain,
    BlueprintCreatedDateTime,
    BlueprintAppRolesJson,
    BlueprintOauth2PermissionScopesJson,
    BlueprintPreAuthorizedApplicationsJson,
    BlueprintTagsJson,
    AgentUserCount,
    EnabledAgentUserCount,
    AgentUsersJson,
    TenantId,
    OrganizationId,
    SourceWorkspace
| order by BlueprintName asc, AgentName asc

Explanation

This KQL (Kusto Query Language) script is designed to create a detailed inventory of identities related to Agent 365, focusing on the integration and projection of data from several sources. Here's a simplified breakdown of what the query does:

Purpose: The query is used to generate a narrow projection of identity data for analytics purposes. It helps in joining this data with other analytics-only sources and reduces the cost of repeated queries to the data lake.
Data Sources: The query pulls data from three confirmed data lake schemas:
- EntraAgentIdentityBlueprints
- EntraAgentIdentities
- EntraAgentUsers
Data Processing:
- Latest Blueprints: It extracts the most recent snapshot of each blueprint, capturing details like the blueprint's ID, app ID, name, creation date, status, and various JSON-encoded attributes.
- Latest Users: It gathers the latest snapshot of each user, counting the total and enabled users, and compiles user details into a JSON format.
- Agent Identities: It retrieves the latest snapshot of each agent identity, including details like ID, app ID, name, creation date, status, and associated tags and lifecycle information.
Joins:
- It performs a left outer join between agent identities and blueprints based on matching app IDs.
- Another left outer join is performed between agent identities and users based on identity IDs.
Data Enrichment:
- It adds additional fields to indicate whether a blueprint match was found and calculates user counts.
- It determines the most recent data snapshot time for each record.
Output:
- The query projects a comprehensive set of fields, including identity and blueprint details, user counts, and JSON-encoded attributes.
- The results are ordered by blueprint name and agent name for easy reference.
Security Note: The query emphasizes that the permissions listed in the blueprint are those exposed by the app, not necessarily those granted to it. For effective access calculations, additional data sources like AgentsInfo.Permissions or Microsoft Graph should be consulted.

In summary, this query is a tool for creating a detailed inventory of Agent 365 identities, integrating data from multiple sources, and preparing it for further analysis or integration with other datasets.