I have this setup as a scheduled report run on Monday mornings. Basically we have a bypass group we use for Multi Factor. When setting up a new user we chuck them in this group but we don't want to forget about it, so once a week this emails the our service desk queue if there an any entries so we can remove them.
Select Top 1000000 tblADusers.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADGroups.Name As ADGroupName,
Case tblADGroups.GroupType
When -2147483646 Then 'Security - Global'
When -2147483644 Then 'Security - Local'
When -2147483643 Then 'Built-in'
When -2147483640 Then 'Security - Universal'
When 2 Then 'Distribution - Global'
When 4 Then 'Distribution - Local'
When 8 Then 'Distribution - Universal'
End As ADGroupType
From tblADusers
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADusers.Username Not Like 'svc%' And tblADusers.Username Not Like
'EMERGENCYACCT' And tblADGroups.Name Like 'Azure-MFA-Bypass'
Order By tblADusers.Userdomain,
tblADusers.Username,
ADGroupName
