KQL Search

Query Details

HomeAI ToolsDevice Query

Epm Unusued Service Principals Az ADSPI

Query

arg("").securityresources
| extend AssessmentName = "Unused identities in your Azure environment should be removed"
| where id contains "7af29efb-41cc-47b6-81b8-800a0888f9a2"
| extend PermissionsCreepIndex = parse_json(tostring(parse_json(tostring(properties.additionalData)).permissionsCreepIndex))
| extend ResourceDetails = parse_json(tostring(properties.resourceDetails))
| project AssessmentName,
    AssessmentPrincipalId = tostring(properties.displayName),
    PrincipalType = tolower(tostring(properties.category)),
    TimeGenerated = properties.timeGenerated,    
    Severity = tostring(properties.status.severity),
    Status  =  tostring(properties.status.code),
    ResourcePlatform = tostring(ResourceDetails.source),
    SubscriptionId = tostring(ResourceDetails.id),
    PciScore = PermissionsCreepIndex.score
    | where PrincipalType == "serviceprincipal"
    | join kind=inner (
       // Requires ingestion of AzADServicePrincipalInsights as custom table
       // Follow steps: https://www.cloud-architekt.net/entra-workload-id-advanced-detection-enrichment/#integration-of-azadserviceprincipalinsights-as-custom-table
       AzADSPI
        | extend AssessmentPrincipalId = ServicePrincipalObjectId
        | mv-expand AzureRoles
        // EPM supports only SubscriptionId scope at the moment
        | extend SubscriptionId = iff((tostring(AzureRoles.roleAssignment.scope) startswith "/subscriptions/"), tolower(extract(@"/subscriptions/([^/]+)", 0, tostring(AzureRoles.roleAssignment.scope))),"")
        | where isnotempty(SubscriptionId)
     ) on AssessmentPrincipalId, SubscriptionId

Explanation

This query retrieves information about unused identities in your Azure environment. It filters the results based on a specific ID and then extracts various properties such as the assessment name, principal ID, principal type, time generated, severity, status, resource platform, subscription ID, and PCI score. It also joins the results with another table called AzADSPI to get additional information about service principals.

Details

Thomas Naunheim profile picture

Thomas Naunheim

Released: February 10, 2024

Tables

arg("").securityresourcesAzADSPI

Keywords

Devices,Intune,User

Operators

arg("")securityresourcesextendAssessmentName="Unused identities in your Azure environment should be removed"whereidcontains"7af29efb-41cc-47b6-81b8-800a0888f9a2"extendPermissionsCreepIndex=parse_json(tostring(parse_json(tostring(properties.additionalData))))extendResourceDetails=parse_json(tostring(properties.resourceDetails))projectAssessmentNameAssessmentPrincipalId=tostring(properties.displayName)PrincipalType=tolower(tostring(properties.category))TimeGenerated=properties.timeGeneratedSeverity=tostring(properties.status.severity)Status=tostring(properties.status.code)ResourcePlatform=tostring(ResourceDetails.source)SubscriptionId=tostring(ResourceDetails.id)PciScore=PermissionsCreepIndex.scorewherePrincipalType=="serviceprincipal"joinkind=innerAzADSPIextendAssessmentPrincipalId=ServicePrincipalObjectIdmv-expandAzureRolesextendSubscriptionId=iff(tostring(AzureRoles.roleAssignment.scope)startswith"/subscriptions/")tolower(extract(@"/subscriptions/([^/]+)"0tostring(AzureRoles.roleAssignment.scope)))""whereisnotempty(SubscriptionId)onAssessmentPrincipalIdSubscriptionId.

Actions