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,
        ManagedIdentityAssociatedAzureResources,
        ManagedIdentityFederatedIdentityCredentials,
        AzureRoles = SPAzureRoleAssignments

Explanation

This query is designed to process and analyze data from the AzADServicePrincipalInsights_CL table in Microsoft Sentinel. Here's a simplified breakdown of what it does:

Data Source: It starts by accessing the AzADServicePrincipalInsights_CL table, which contains logs related to Azure Active Directory service principals.
Time Filter: It filters the data to include only entries generated in the last 14 days.
Data Aggregation: It uses the summarize function to group the data by ObjectId, selecting the most recent entry (arg_max) for each unique ObjectId.
Data Transformation:
- It extracts and converts specific JSON fields into string format to create new columns:
  - AppObjectId from the APP field.
  - SPObjectId from the SP field.
- It determines the type of workload identity (ManagedIdentity or Application) based on the ObjectType field.
Data Projection: It selects and renames specific columns to create a unified schema for workload identity analytics:
- WorkloadIdentityName: The display name of the service principal.
- WorkloadIdentityType: The type of identity (ManagedIdentity or Application).
- ServicePrincipalObjectId: The object ID of the service principal.
- ServicePrincipalOwners: Owners of the service principal.
- ServicePrincipalType: The type of service principal.
- ApplicationObjectId: The object ID of the application.
- ApplicationId: The application ID.
- ApplicationOwners: Owners of the application.
- EntraGroupMemberships: Memberships in Entra groups.
- ManagedIdentityAssociatedAzureResources: Associated Azure resources for managed identities.
- ManagedIdentityFederatedIdentityCredentials: Federated identity credentials for managed identities.
- AzureRoles: Azure role assignments for the service principal.

Overall, this query is used to create a standardized view of service principal data, which can be used for workload identity analytics in Microsoft Sentinel.

Details

Thomas Naunheim

Released: November 23, 2023

Tables

AzADServicePrincipalInsights_CL

Keywords

AzADServicePrincipalInsightsMicrosoftSentinelWorkloadIdentityAnalyticsRulesObjectIdAppObjectIdSPObjectIdWorkloadIdentityTypeWorkloadIdentityNameServicePrincipalObjectIdServicePrincipalOwnersServicePrincipalTypeApplicationObjectIdApplicationIdApplicationOwnersEntraGroupMembershipsManagedIdentityAssociatedAzureResourcesManagedIdentityFederatedIdentityCredentialsAzureRoles

Operators

AzADServicePrincipalInsights_CLwhereagosummarizearg_maxbyextendtostringparse_jsoniffcontainsproject

Actions

GitHub