Service Principal Assigned App Role With Sensitive Access

Requête originale, sans modification.

// Add other permissions to this list as needed
let permissions = dynamic([".All", "ReadWrite", "Mail.", "offline_access", "Files.Read", "Notes.Read", "ChannelMessage.Read", "Chat.Read", "TeamsActivity.Read",
"Group.Read", "EWS.AccessAsUser.All", "EAS.AccessAsUser.All"]);
let auditList = 
AuditLogs
| where OperationName =~ "Add app role assignment to service principal"
| mv-expand TargetResources[0].modifiedProperties
| extend TargetResources_0_modifiedProperties = column_ifexists("TargetResources_0_modifiedProperties", '')
| where isnotempty(TargetResources_0_modifiedProperties)
;
let detailsList = auditList
| where TargetResources_0_modifiedProperties.displayName =~ "AppRole.Value" or TargetResources_0_modifiedProperties.displayName =~ "DelegatedPermissionGrant.Scope"
| extend Permissions = split((parse_json(tostring(TargetResources_0_modifiedProperties.newValue))), " ")
| where Permissions has_any (permissions)
| summarize AddedPermissions=make_set(Permissions,200) by CorrelationId
| join kind=inner auditList on CorrelationId
| extend InitiatingAppName = tostring(InitiatedBy.app.displayName)
| extend InitiatingAppServicePrincipalId = tostring(InitiatedBy.app.servicePrincipalId)
| extend InitiatingUserPrincipalName = tostring(InitiatedBy.user.userPrincipalName)
| extend InitiatingAadUserId = tostring(InitiatedBy.user.id)
| extend InitiatingIPAddress = tostring(InitiatedBy.user.ipAddress)
| extend InitiatedBy = tostring(iff(isnotempty(InitiatingUserPrincipalName),InitiatingUserPrincipalName, InitiatingAppName))
| extend displayName = tostring(TargetResources_0_modifiedProperties.displayName), newValue = tostring(parse_json(tostring(TargetResources_0_modifiedProperties.newValue)))
| where displayName == "ServicePrincipal.ObjectID" or displayName == "ServicePrincipal.DisplayName"
| extend displayName = case(displayName == "ServicePrincipal.ObjectID", "ServicePrincipalObjectID", displayName == "ServicePrincipal.DisplayName", "ServicePrincipalDisplayName", displayName)
| project TimeGenerated, CorrelationId, Id, AddedPermissions = tostring(AddedPermissions), InitiatingAadUserId, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingIPAddress, InitiatingUserPrincipalName, InitiatedBy, displayName, newValue
;
detailsList | project Id, displayName, newValue
| evaluate pivot(displayName, make_set(newValue))
| join kind=inner detailsList on Id
| extend ServicePrincipalObjectID = todynamic(column_ifexists("ServicePrincipalObjectID", "")), ServicePrincipalDisplayName = todynamic(column_ifexists("ServicePrincipalDisplayName", ""))
| mv-expand ServicePrincipalObjectID, ServicePrincipalDisplayName
| project-away Id1, displayName, newValue
| extend ServicePrincipalObjectID = tostring(ServicePrincipalObjectID), ServicePrincipalDisplayName = tostring(ServicePrincipalDisplayName)
| summarize FirstSeen = min(TimeGenerated), LastSeen = max(TimeGenerated), EventIds = make_set(Id,200) by CorrelationId, AddedPermissions, InitiatingAadUserId, InitiatingAppName, InitiatingAppServicePrincipalId, InitiatingIPAddress, InitiatingUserPrincipalName, InitiatedBy, ServicePrincipalDisplayName, ServicePrincipalObjectID
| extend InitiatingAccountName = tostring(split(InitiatingUserPrincipalName, "@")[0]), InitiatingAccountUPNSuffix = tostring(split(InitiatingUserPrincipalName, "@")[1])