Query Details
Identity Info Find Userswithmany Groups
Query
//Find user accounts that are members of over 150 groups. These can cause issues with SAML claims. //See https://docs.microsoft.com/en-us/azure/active-directory/hybrid/how-to-connect-fed-group-claims //Data connector required for this query - Microsoft Sentinel UEBA IdentityInfo | where TimeGenerated > ago(30d) | summarize arg_max(TimeGenerated, *) by AccountUPN | extend ['Group Count']=array_length(GroupMembership) | sort by ['Group Count'] desc | where ['Group Count'] > 150
Explanation
This query is looking for user accounts that are members of more than 150 groups. This can potentially cause issues with SAML claims. It uses the Microsoft Sentinel UEBA data connector and retrieves information from the IdentityInfo table. The query filters the results to include only data from the past 30 days and then groups the data by the user's account UPN (User Principal Name). It calculates the number of groups each user is a member of and sorts the results in descending order based on the group count. Finally, it filters the results to include only users with a group count greater than 150.
Details
Matt Zorich
Released: June 28, 2022
Tables
IdentityInfo
Keywords
User,Accounts,Members,Groups,SAML,Claims,DataConnector,MicrosoftSentinelUEBA
Operators
wheresummarizearg_maxbyextendarray_lengthsortdesc