Query Details
O Auth Summarize Current App Permissions
Query
//Summarize your Azure AD apps by what permissions they currently hold
//Data connector required for this query - Azure Active Directory - Audit Logs
//Find applications that have been deleted
let deletedapps=
AuditLogs
| where OperationName == "Remove service principal"
| extend ServicePrincipalId = tostring(TargetResources[0].id)
| project ServicePrincipalId;
let delegatedaccess=
AuditLogs
| where TimeGenerated > ago(365d)
| where OperationName has "Add delegated permission grant"
| extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[0].newValue)))
| extend ['Permissions granted'] = split(x, ' ')
| extend ServicePrincipalId = tostring(TargetResources[1].id)
| extend ['Permission type'] = strcat("Delegated")
| summarize arg_max(TimeGenerated, *) by ServicePrincipalId
//Exclude deleted applications
| where ServicePrincipalId !in (deletedapps)
| project
TimeGenerated,
['Permission type'],
['Permissions granted'],
ServicePrincipalId;
let appaccess=
AuditLogs
| where TimeGenerated > ago(365d)
| where OperationName has "Add app role assignment to service principal"
| extend x = tostring(parse_json(tostring(parse_json(tostring(TargetResources[0].modifiedProperties))[1].newValue)))
| extend ['Permissions granted'] = split(x, ' ')
| extend ['Permission type'] = strcat("Application")
| extend ServicePrincipalId = tostring(TargetResources[1].id)
| summarize arg_max(TimeGenerated, *) by ServicePrincipalId
//Exclude deleted applications
| where ServicePrincipalId !in (deletedapps)
| project
TimeGenerated,
ServicePrincipalId,
['Permission type'],
['Permissions granted'];
union delegatedaccess, appaccess
| mv-expand ['Permissions granted']
| where isnotempty( ['Permissions granted'])
//Extend new permission field
| summarize ['Permission List']=make_set(['Permissions granted']) by ['Permission type'], ServicePrincipalId
| extend ['Number of Permissions']=array_length(['Permission List'])
| sort by ServicePrincipalId desc, ['Permission type'] ascExplanation
This query summarizes Azure AD apps by the permissions they currently hold. It uses the Azure Active Directory - Audit Logs data connector.
First, it finds applications that have been deleted by filtering audit logs for the "Remove service principal" operation. It retrieves the service principal ID and stores it in the "deletedapps" variable.
Next, it finds delegated access permissions granted to apps within the past year. It filters audit logs for the "Add delegated permission grant" operation and extracts the permissions granted. It also retrieves the service principal ID and categorizes the permission type as "Delegated". It then summarizes the data, excluding deleted applications, and projects the relevant fields.
Similarly, it finds application access permissions granted to apps within the past year. It filters audit logs for the "Add app role assignment to service principal" operation and extracts the permissions granted. It categorizes the permission type as "Application" and retrieves the service principal ID. It summarizes the data, excluding deleted applications, and projects the relevant fields.
The query then combines the delegated access and application access data using the union operator. It expands the "Permissions granted" field into separate rows. It filters out empty permissions and extends a new field called "Permission List" which contains a set of unique permissions for each service principal and permission type. It also calculates the number of permissions for each service principal.
Finally, it sorts the results by service principal ID in descending order and permission type in ascending order.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators