KQL Search

Query Details

HomeAI ToolsDevice Query

Identity Apps With More Guests

Query

//Find Azure AD applications that have more guests than members accessing them

//Data connector required for this query - Azure Active Directory - Signin Logs

//Microsoft Sentinel Query
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == "0"
| summarize Guests=dcountif(UserPrincipalName,UserType == "Guest"), Members=dcountif(UserPrincipalName,UserType == "Member") by AppDisplayName
| where Guests > Members
| sort by Guests desc 

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting with Azure AD P2 License

AADSignInEventsBeta
| where Timestamp > ago(30d)
| where LogonType == @"[""interactiveUser""]"
| where ErrorCode == "0"
| summarize Guests=dcountif(AccountUpn,IsGuestUser == "true"), Members=dcountif(AccountUpn,IsGuestUser == "false") by Application
| where Guests > Members
| sort by Guests desc

Explanation

This query is used to find Azure AD applications that have more guests than members accessing them. It looks at the sign-in logs or events from the past 30 days and filters for successful sign-ins. It then counts the number of guests and members accessing each application and compares the counts. The query returns the applications where the number of guests is greater than the number of members, sorted in descending order by the number of guests.

Details

Matt Zorich profile picture

Matt Zorich

Released: June 17, 2022

Tables

SigninLogsAADSignInEventsBeta

Keywords

AzureAD,SigninLogs,TimeGenerated,ResultType,UserPrincipalName,UserType,AppDisplayName,Guests,Members,AADSignInEventsBeta,Timestamp,LogonType,ErrorCode,AccountUpn,IsGuestUser,Application

Operators

whereago==summarizedcountifbysort

Actions