Query Details
Identity Find Guests Accessing Most Apps
Query
//Find the guests in your tenant connecting to the most applications. They are the biggest risk and the best target for additional controls like Conditional Access.
//Data connector required for this query - Azure Active Directory - Signin Logs
//Microsoft Sentinel query
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| where UserType == "Guest"
//Exclude the Microsoft apps for guest account management
| where AppDisplayName !in ("My Apps", "Microsoft App Access Panel", "My Access", "My Profile", "Microsoft Invitation Acceptance Portal")
| summarize
['Count of Applications']=dcount(AppDisplayName),
['List of Application']=make_set(AppDisplayName)
by UserPrincipalName
| sort by ['Count of Applications'] desc
//Advanced Hunting query
//Data connector required for this query - Advanced Hunting with Azure AD P2 License
AADSignInEventsBeta
| where Timestamp > ago(30d)
| where ErrorCode == 0
| where IsGuestUser == 1
//Exclude the Microsoft apps for guest account management
| where Application !in ("My Apps", "Microsoft App Access Panel", "My Access", "My Profile", "Microsoft Invitation Acceptance Portal")
| summarize
['Count of Applications']=dcount(Application),
['List of Application']=make_set(Application)
by AccountUpn
| sort by ['Count of Applications'] desc Explanation
This query is used to find the guests in your tenant who are connecting to the most applications. These guests are considered to be the biggest risk and should be targeted for additional controls like Conditional Access.
The query is divided into two parts, one for Microsoft Sentinel and one for Advanced Hunting.
In the Microsoft Sentinel query, it uses the Azure Active Directory - Signin Logs data connector. It filters the logs to include only the sign-in events that occurred within the last 30 days and have a result type of 0 (successful sign-ins). It further filters the results to include only guest users and excludes certain Microsoft apps related to guest account management. The query then summarizes the data by counting the unique applications each guest user has accessed and creates a list of those applications. Finally, the results are sorted in descending order based on the count of applications.
In the Advanced Hunting query, it uses the Advanced Hunting data connector with Azure AD P2 License. It filters the events to include only those that occurred within the last 30 days and have an error code of 0 (successful events). It further filters the results to include only guest users and excludes certain Microsoft apps related to guest account management. The query then summarizes the data by counting the unique applications each guest user has accessed and creates a list of those applications. Finally, the results are sorted in descending order based on the count of applications.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators