KQL Search

Query Details

HomeAI ToolsDevice Query

3 Correlation Between CSPM And Identity Info

Query

let UnusuedHumanIdentities = arg("").securityresources | where type =~ "microsoft.security/assessments"
        | extend assessmentKey=extract(@"(?i)providers/Microsoft.Security/assessments/([^/]*)", 1, id)
        | extend resourceId = tostring(properties.resourceDetails.Id)
        | extend identityId = tostring(properties.additionalData.ResourceName)
        | extend identityType = tostring(properties.additionalData.ResourceType)
        | extend assessmentTitle = tostring(properties.displayName)        
        | extend assessmentSev = tostring(properties.metadata.severity)
        | extend portalUrl = tostring(properties.links.azurePortal)
        | where assessmentKey == "8b0bd683-bcfe-4ab1-96b9-f15a60eaa89d"
        | extend graphNodesEdges = parse_json(properties.risk.paths)
        | extend status=tostring(properties.status.code), resourceType = tostring(properties.additionalData.ResourceType)
        | where status == "Unhealthy"
        | project assessmentKey, assessmentTitle, assessmentSev, resourceId, subscriptionId, identityId, identityType, status, tenantId, portalUrl, graphNodesEdges;
UnusuedHumanIdentities
| join kind = inner ( IdentityInfo
      | where TimeGenerated > ago(14d)
      | summarize arg_max(TimeGenerated, *) by AccountObjectId
      | extend identityId = AccountObjectId
      | project identityId, AccountDisplayName, AccountObjectId, Tags, IsAccountEnabled, RiskLevel
) on identityId
// Correlation to direct role-assignments only (no nesting or group-based assignments)
| join hint.remote=left (arg("").authorizationresources
    | where type =~ 'microsoft.authorization/roleassignments'
    | extend roleDefinitionId = properties.roleDefinitionId
    | extend principalType = properties.principalType
    | extend identityId = tostring(properties.principalId)
    | extend roleAssignmentScope = properties.scope
    | project identityId, roleDefinitionId, roleAssignmentScope, subscriptionId
) on identityId, subscriptionId
| extend roleAssignment = bag_pack_columns(roleDefinitionId, roleAssignmentScope, graphNodesEdges)
| extend CiemDetail = bag_pack_columns(assessmentTitle, portalUrl, graphNodesEdges, roleAssignment)
| summarize CiemDetails = make_set(CiemDetail) by AccountDisplayName, AccountObjectId, Tags, IsAccountEnabled, RiskLevel, Status = status

Explanation

This query is designed to identify and analyze unused human identities within a Microsoft security environment. Here's a simplified breakdown of what the query does:

  1. Identify Unused Identities:

    • It starts by filtering security assessments to find those with a specific assessment key (8b0bd683-bcfe-4ab1-96b9-f15a60eaa89d) and a status of "Unhealthy". These assessments are related to unused human identities.
    • It extracts relevant details such as the identity ID, type, assessment title, severity, and a portal URL for further investigation.

  2. Join with Identity Information:

    • The query then joins this data with another dataset (IdentityInfo) to get more details about these identities, such as their display name, whether the account is enabled, and their risk level. This dataset is filtered to include only recent entries (from the last 14 days).

  3. Role Assignment Correlation:

    • It further joins with role assignment data to find direct role assignments for these identities. This helps in understanding the roles assigned to these identities and the scope of these assignments.

  4. Data Aggregation:

    • Finally, the query aggregates the data by creating a detailed summary (CiemDetails) for each identity. This summary includes information about the assessment, role assignments, and other relevant details.

The result is a comprehensive view of unused human identities, their associated risks, and their role assignments, which can be used for further security analysis and remediation.

Details

Thomas Naunheim profile picture

Thomas Naunheim

Released: June 4, 2025

Tables

securityresourcesIdentityInfoauthorizationresources

Keywords

SecurityResourcesAssessmentsIdentityInfoAuthorizationResourcesRoleAssignments

Operators

letarg|where=~extendextracttostringparse_jsonprojectjoinkindsummarizearg_maxagohint.remotebag_pack_columnsmake_set

Actions