cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Susan_A
Lansweeper Alumni
The report below lists scanned domain users and the Active Directory groups they belong to. You can filter the ADGroupName column within the report results to list members of a specific group. AD group scanning was added in Lansweeper 5.1. If you have an old Lansweeper installation, update to the latest release and rescan your users.

Domain users are automatically scanned when they log into a computer during a computer scan. Additional users can also be scanned with Domain User Scanning.

Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADusers.Displayname,
tblADusers.email,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
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
Order By tblADusers.Userdomain,
tblADusers.Username,
ADGroupName
3 REPLIES 3
Mister_Nobody
Honored Sweeper

you can change order of fields and change grouping order

a_pritschow
Engaged Sweeper
Try this:

Select Top 1000000 adu.Username As username,
(Select tblADGroups.Name + ', ' As 'data()'
From tblADMembership Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID Join tblADusers On tblADMembership.ChildAdObjectID
= tblADusers.ADObjectID
Where tblADusers.Username = adu.Username For Xml Path('')) Groups
From tblADMembership As admem
Join tblADGroups On admem.ParentAdObjectID = tblADGroups.ADObjectID
Join tblADusers As adu On admem.ChildAdObjectID = adu.ADObjectID
Group By adu.Username

As opposed to listing Users and their Groups, can you make it list Groups and their users?

Thanks!

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now