Users: AD users and their AD groups (Built-in)

[Susan_A]

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

[Mister_Nobody]

you can change order of fields and change grouping order

[a_pritschow]

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

[Stretch605]

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

Thanks!