KQL Search

Query Details

HomeAI ToolsDevice Query

Az ADSPI

Query

id: 136d1433-aca3-4f83-820d-ee2fa5a6ab39
Function:
  Title: Parser for AzADServicePrincipalInsights to get unified schema for Workload Identity Analytics Rules 
  Version: '1.0.0'
  LastUpdated: '2023-11-11'
Category: Microsoft Sentinel Parser
FunctionName: AzADSPI
FunctionAlias: AzADSPI
FunctionQuery: |
    AzADServicePrincipalInsights_CL
    | where TimeGenerated > ago(14d)
    | summarize arg_max(TimeGenerated, *) by ObjectId
    | extend AppObjectId = tostring(parse_json(APP)[0].APPObjectId)
    | extend SPObjectId = tostring(parse_json(SP)[0].SPObjectId)
    | extend WorkloadIdentityType = iff(ObjectType contains "SP MI", "ManagedIdentity", "Application")
    | project 
        WorkloadIdentityName = tostring(parse_json(SP)[0].SPDisplayName),
        WorkloadIdentityType,
        ServicePrincipalObjectId = SPObjectId,
        ServicePrincipalOwners = SPOwners,
        ServicePrincipalType = ObjectType,
        ApplicationObjectId = AppObjectId,
        ApplicationId = tostring(parse_json(SP)[0].SPAppId),
        ApplicationOwners = APPAppOwners,
        EntraGroupMemberships = APPAppOwners,
        AzureRoles = SPAzureRoleAssignments

Explanation

This query retrieves information from the AzADServicePrincipalInsights_CL table in Microsoft Sentinel. It filters the data to include only records from the past 14 days. It then performs various transformations and calculations to extract specific fields and create a unified schema for workload identity analytics rules. The resulting fields include the workload identity name, type, service principal object ID, service principal owners, service principal type, application object ID, application ID, application owners, enterprise group memberships, and Azure roles.

Details

Thomas Naunheim profile picture

Thomas Naunheim

Released: November 11, 2023

Tables

AzADServicePrincipalInsights_CL

Keywords

Devices,Intune,User,App,ServicePrincipal,WorkloadIdentity,ObjectId,TimeGenerated,AppObjectId,SPObjectId,ObjectType,SPDisplayName,SPOwners,ApplicationObjectId,ApplicationId,APPAppOwners,EntraGroupMemberships,SPAzureRoleAssignments

Operators

summarizeextendiffproject

Actions