Query Details
Most User Consent Application
Query
# List the top 10 external applications with the most consented users
## Query Information
#### Description
The query below lists the top 10 external applications with the most consented users. It is highly recommended to review newly added applications in which only user consent is given.
#### Risk
Individual users can allow applications that do not require admin consent to be active in your environment.
## Defender XDR
```KQL
let PrivilegeLevelInput = pack_array('Medium', 'High');
OAuthAppInfo
| where AppOrigin == "External"
| where ConsentedUsersCount > 0
| summarize arg_max(Timestamp, *) by OAuthAppId
| where PrivilegeLevel in (PrivilegeLevelInput)
| extend PublisherName = tostring(VerifiedPublisher.displayName), DateAdded = todatetime(VerifiedPublisher.addedDateTime)
| project AppName, OAuthAppId, ServicePrincipalId, AddedOnTime, PublisherName, AppOwnerTenantId, ConsentedUsersCount
| top 10 by ConsentedUsersCount
```
## Sentinel
```KQL
let PrivilegeLevelInput = pack_array('Medium', 'High');
OAuthAppInfo
| where AppOrigin == "External"
| where ConsentedUsersCount > 0
| summarize arg_max(TimeGenerated, *) by OAuthAppId
| where PrivilegeLevel in (PrivilegeLevelInput)
| extend PublisherName = tostring(VerifiedPublisher.displayName), DateAdded = todatetime(VerifiedPublisher.addedDateTime)
| project AppName, OAuthAppId, ServicePrincipalId, AddedOnTime, PublisherName, AppOwnerTenantId, ConsentedUsersCount
| top 10 by ConsentedUsersCount
```
Explanation
The query is designed to identify the top 10 external applications that have been granted user consent within an organization. Here's a simplified breakdown of what the query does:
-
Filter for External Applications: It looks at applications that originate from outside the organization ("External").
-
Check for User Consent: It only considers applications that have at least one user who has consented to their use.
-
Select the Latest Data: For each application, it selects the most recent record based on the timestamp.
-
Filter by Privilege Level: It focuses on applications with a privilege level of either 'Medium' or 'High', which could indicate a higher risk if misused.
-
Extract Relevant Information: It gathers details such as the application name, ID, service principal ID, the time it was added, the publisher's name, the tenant ID of the application owner, and the number of users who have consented.
-
Rank by Consented Users: Finally, it sorts the applications by the number of users who have consented to them and picks the top 10. The purpose of this query is to help administrators monitor and review external applications that have been granted user consent, especially those with higher privilege levels, to ensure they do not pose a security risk to the organization.
Details
Bert-Jan Pals
Released: April 14, 2025
Tables
Keywords
Operators