KQL Search

Query Details

Epm Unusued Service Principals Az ADSPI

Query

// 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

About this 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 (

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.