Az ADSPI

Query

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

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

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