10-04-2023
04:55 PM
- last edited on
03-31-2024
03:58 PM
by
Mercedes_O
Hi all,
I was trying to make a report looking for the AD groups an user is member of. In the end I got the data I needed, but i'd be nicer if I can join all the groups in the same row for each user. Like below:
Users | Groups
AdUser1 | AdGroup1, AdGroup2, AdGroup3, etc...
This is my current code:
Select Top 1000000 tblADusers.Username,
tblADusers.Displayname,
Case tblADGroups.GroupType
When -2147483646 Then Concat(tblADGroups.Name, ' (Security - Global)')
When -2147483644 Then Concat(tblADGroups.Name, ' (Security - Local)')
When -2147483643 Then Concat(tblADGroups.Name, ' (Built-in)')
When -2147483640 Then Concat(tblADGroups.Name, ' (Security - Universal)')
When 2 Then Concat(tblADGroups.Name, ' (Distribution - Global)')
When 4 Then Concat(tblADGroups.Name, ' (Distribution - Local)')
When 8 Then Concat(tblADGroups.Name, ' (Distribution - Universal)')
End As [AD Group Name (Type)]
From tblADusers
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Order By tblADusers.Userdomain,
tblADusers.Username
Any help would be thankful! Kind regards,
Borja GC
Solved! Go to Solution.
10-05-2023 05:53 AM
Try this
Select Top 1000000 adu.Username,
adu.userdomain,
Stuff((Select ', ' + tblADGroups.Name As [text()]
From tblADMembership Inner Join tblADGroups On
tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID Inner Join
tblADusers On tblADMembership.ChildAdObjectID = tblADusers.ADObjectID
Where tblADusers.Username = adu.Username And tblADusers.Userdomain =
adu.userdomain For Xml Path('')), 1, 2, '') Groups
From tblADMembership As admem
Inner Join tblADGroups On admem.ParentAdObjectID = tblADGroups.ADObjectID
Inner Join tblADusers As adu On admem.ChildAdObjectID = adu.ADObjectID
Group By adu.Username,
adu.userdomain
10-05-2023 05:54 AM
* nested membership is not included
10-05-2023 05:53 AM
Try this
Select Top 1000000 adu.Username,
adu.userdomain,
Stuff((Select ', ' + tblADGroups.Name As [text()]
From tblADMembership Inner Join tblADGroups On
tblADMembership.ParentAdObjectID = tblADGroups.ADObjectID Inner Join
tblADusers On tblADMembership.ChildAdObjectID = tblADusers.ADObjectID
Where tblADusers.Username = adu.Username And tblADusers.Userdomain =
adu.userdomain For Xml Path('')), 1, 2, '') Groups
From tblADMembership As admem
Inner Join tblADGroups On admem.ParentAdObjectID = tblADGroups.ADObjectID
Inner Join tblADusers As adu On admem.ChildAdObjectID = adu.ADObjectID
Group By adu.Username,
adu.userdomain
10-05-2023 08:55 AM
Hi @Mister_Nobody ,
It worked perfectly as I wanted. You're the best! Thank you so much for your help 🙂
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now