06-09-2023 09:51 AM
Hi there
I need a report with all AD groups and their members including AD groups. Is this possible with Lansweeper?
Solved! Go to Solution.
06-15-2023 07:54 AM
Non-recursive 6 levels Query:
Select Top 1000000 tblADObjects.sAMAccountName,
Case
When tblADusers.ADObjectID Is Not Null Then 'User'
When tblADGroups.ADObjectId Is Not Null Then 'Group'
When tblADComputers.ADObjectID Is Not Null Then 'Computer'
Else 'Trash'
End type,
tblADObjects.domain,
tblADGroups1.Name As Group_L1,
tblADGroups2.Name As Group_L2,
tblADGroups3.Name As Group_L3,
tblADGroups4.Name As Group_L4,
tblADGroups5.Name As Group_L5,
tblADGroups6.Name As Group_L6
From tblADObjects
Inner Join tblADMembership On tblADObjects.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups tblADGroups1 On tblADMembership.ParentAdObjectID =
tblADGroups1.ADObjectID
Left Join tblADusers On tblADObjects.ADObjectID = tblADusers.ADObjectID
Left Join tblADGroups On tblADObjects.ADObjectID = tblADGroups.ADObjectId
Left Join tblADComputers On tblADObjects.ADObjectID =
tblADComputers.ADObjectID
Left Join tblADMembership tblADMembership1 On tblADGroups1.ADObjectID =
tblADMembership1.ChildAdObjectID
Left Join tblADGroups tblADGroups2 On tblADMembership1.ParentAdObjectID =
tblADGroups2.ADObjectID
Left Join tblADMembership tblADMembership2 On tblADGroups2.ADObjectID =
tblADMembership2.ChildAdObjectID
Left Join tblADGroups tblADGroups3 On tblADMembership2.ParentAdObjectID =
tblADGroups3.ADObjectID
Left Join tblADMembership tblADMembership3 On tblADGroups3.ADObjectID =
tblADMembership3.ChildAdObjectID
Left Join tblADGroups tblADGroups4 On tblADMembership3.ParentAdObjectID =
tblADGroups4.ADObjectID
Left Join tblADMembership tblADMembership4 On tblADGroups4.ADObjectID =
tblADMembership4.ChildAdObjectID
Left Join tblADGroups tblADGroups5 On tblADMembership4.ParentAdObjectID =
tblADGroups5.ADObjectID
Left Join tblADMembership tblADMembership5 On tblADGroups5.ADObjectID =
tblADMembership5.ChildAdObjectID
Left Join tblADGroups tblADGroups6 On tblADMembership5.ParentAdObjectID =
tblADGroups6.ADObjectID
Where tblADObjects.sAMAccountName = 'user or group or computer' And tblADObjects.domain = 'your_domain'
Order By Group_L6 Desc,
Group_L5 Desc,
Group_L4 Desc,
Group_L3 Desc,
Group_L2 Desc,
Group_L1 Desc
06-15-2023 12:05 PM
Check group membership cycling up to 6 levels
Select Distinct Top 1000000 tblADObjects.domain,
tblADObjects.sAMAccountName,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups1.ADObjectID) As Group_Domain,
tblADGroups1.Name,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups2.ADObjectID) As L2_Domain,
tblADGroups2.Name As L2,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups3.ADObjectID) As L3_Domain,
tblADGroups3.Name As L3,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups4.ADObjectID) As L4_Domain,
tblADGroups4.Name As L4,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups5.ADObjectID) As L5_Domain,
tblADGroups5.Name As L5,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups6.ADObjectID) As L6_Domain,
tblADGroups6.Name As L6
From tblADObjects
Inner Join tblADMembership On tblADObjects.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups tblADGroups1 On tblADMembership.ParentAdObjectID =
tblADGroups1.ADObjectID
Inner Join tblADGroups On tblADObjects.ADObjectID = tblADGroups.ADObjectId
Left Join tblADMembership tblADMembership1 On tblADGroups1.ADObjectID =
tblADMembership1.ChildAdObjectID
Left Join tblADGroups tblADGroups2 On tblADMembership1.ParentAdObjectID =
tblADGroups2.ADObjectID
Left Join tblADMembership tblADMembership2 On tblADGroups2.ADObjectID =
tblADMembership2.ChildAdObjectID
Left Join tblADGroups tblADGroups3 On tblADMembership2.ParentAdObjectID =
tblADGroups3.ADObjectID
Left Join tblADMembership tblADMembership3 On tblADGroups3.ADObjectID =
tblADMembership3.ChildAdObjectID
Left Join tblADGroups tblADGroups4 On tblADMembership3.ParentAdObjectID =
tblADGroups4.ADObjectID
Left Join tblADMembership tblADMembership4 On tblADGroups4.ADObjectID =
tblADMembership4.ChildAdObjectID
Left Join tblADGroups tblADGroups5 On tblADMembership4.ParentAdObjectID =
tblADGroups5.ADObjectID
Left Join tblADMembership tblADMembership5 On tblADGroups5.ADObjectID =
tblADMembership5.ChildAdObjectID
Left Join tblADGroups tblADGroups6 On tblADMembership5.ParentAdObjectID =
tblADGroups6.ADObjectID
Where (tblADGroups.ADObjectId = tblADGroups1.ADObjectId Or
tblADGroups.ADObjectId = tblADGroups2.ADObjectId Or tblADGroups.ADObjectId =
tblADGroups3.ADObjectId Or tblADGroups.ADObjectId = tblADGroups4.ADObjectId
Or tblADGroups.ADObjectId = tblADGroups5.ADObjectId Or
tblADGroups.ADObjectId = tblADGroups6.ADObjectId)
Order By tblADObjects.domain,
tblADObjects.sAMAccountName
06-15-2023 08:42 AM - edited 06-16-2023 11:24 AM
Special SQL-Query:
1. Show only when more 5 levels nested groups - such usage of AD in not recommended
2. Add groups domain for forest and trust domain
Select Top 1000000 tblADObjects.domain,
tblADObjects.sAMAccountName,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups1.ADObjectID) As Domain_L1,
tblADGroups1.Name As Group_L1,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups2.ADObjectID) As Domain_L2,
tblADGroups2.Name As Group_L2,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups3.ADObjectID) As Domain_L3,
tblADGroups3.Name As Group_L3,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups4.ADObjectID) As Domain_L4,
tblADGroups4.Name As Group_L4,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups5.ADObjectID) As Domain_L5,
tblADGroups5.Name As Group_L5,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups6.ADObjectID) As Domain_L6,
tblADGroups6.Name As Group_L6,
(Select tblADObjects.domain From tblADObjects
Where tblADObjects.ADObjectID = tblADGroups7.ADObjectID) As Domain_L7,
tblADGroups7.Name As Group_L7
From tblADObjects
Inner Join tblADMembership On tblADObjects.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups tblADGroups1 On tblADMembership.ParentAdObjectID =
tblADGroups1.ADObjectID
Inner Join tblADusers On tblADObjects.ADObjectID = tblADusers.ADObjectID
Left Join tblADMembership tblADMembership1 On tblADGroups1.ADObjectID =
tblADMembership1.ChildAdObjectID
Left Join tblADGroups tblADGroups2 On tblADMembership1.ParentAdObjectID =
tblADGroups2.ADObjectID
Left Join tblADMembership tblADMembership2 On tblADGroups2.ADObjectID =
tblADMembership2.ChildAdObjectID
Left Join tblADGroups tblADGroups3 On tblADMembership2.ParentAdObjectID =
tblADGroups3.ADObjectID
Left Join tblADMembership tblADMembership3 On tblADGroups3.ADObjectID =
tblADMembership3.ChildAdObjectID
Left Join tblADGroups tblADGroups4 On tblADMembership3.ParentAdObjectID =
tblADGroups4.ADObjectID
Left Join tblADMembership tblADMembership4 On tblADGroups4.ADObjectID =
tblADMembership4.ChildAdObjectID
Left Join tblADGroups tblADGroups5 On tblADMembership4.ParentAdObjectID =
tblADGroups5.ADObjectID
Left Join tblADMembership tblADMembership5 On tblADGroups5.ADObjectID =
tblADMembership5.ChildAdObjectID
Left Join tblADGroups tblADGroups6 On tblADMembership5.ParentAdObjectID =
tblADGroups6.ADObjectID
Left Join tblADMembership tblADMembership6 On tblADGroups6.ADObjectID =
tblADMembership6.ChildAdObjectID
Left Join tblADGroups tblADGroups7 On tblADMembership6.ParentAdObjectID =
tblADGroups7.ADObjectID
Where tblADGroups6.ADObjectID Is Not Null
Order By Group_L7 Desc,
Group_L6 Desc,
Group_L5 Desc,
Group_L4 Desc,
Group_L3 Desc,
Group_L2 Desc,
Group_L1 Desc
06-15-2023 07:54 AM
Non-recursive 6 levels Query:
Select Top 1000000 tblADObjects.sAMAccountName,
Case
When tblADusers.ADObjectID Is Not Null Then 'User'
When tblADGroups.ADObjectId Is Not Null Then 'Group'
When tblADComputers.ADObjectID Is Not Null Then 'Computer'
Else 'Trash'
End type,
tblADObjects.domain,
tblADGroups1.Name As Group_L1,
tblADGroups2.Name As Group_L2,
tblADGroups3.Name As Group_L3,
tblADGroups4.Name As Group_L4,
tblADGroups5.Name As Group_L5,
tblADGroups6.Name As Group_L6
From tblADObjects
Inner Join tblADMembership On tblADObjects.ADObjectID =
tblADMembership.ChildAdObjectID
Inner Join tblADGroups tblADGroups1 On tblADMembership.ParentAdObjectID =
tblADGroups1.ADObjectID
Left Join tblADusers On tblADObjects.ADObjectID = tblADusers.ADObjectID
Left Join tblADGroups On tblADObjects.ADObjectID = tblADGroups.ADObjectId
Left Join tblADComputers On tblADObjects.ADObjectID =
tblADComputers.ADObjectID
Left Join tblADMembership tblADMembership1 On tblADGroups1.ADObjectID =
tblADMembership1.ChildAdObjectID
Left Join tblADGroups tblADGroups2 On tblADMembership1.ParentAdObjectID =
tblADGroups2.ADObjectID
Left Join tblADMembership tblADMembership2 On tblADGroups2.ADObjectID =
tblADMembership2.ChildAdObjectID
Left Join tblADGroups tblADGroups3 On tblADMembership2.ParentAdObjectID =
tblADGroups3.ADObjectID
Left Join tblADMembership tblADMembership3 On tblADGroups3.ADObjectID =
tblADMembership3.ChildAdObjectID
Left Join tblADGroups tblADGroups4 On tblADMembership3.ParentAdObjectID =
tblADGroups4.ADObjectID
Left Join tblADMembership tblADMembership4 On tblADGroups4.ADObjectID =
tblADMembership4.ChildAdObjectID
Left Join tblADGroups tblADGroups5 On tblADMembership4.ParentAdObjectID =
tblADGroups5.ADObjectID
Left Join tblADMembership tblADMembership5 On tblADGroups5.ADObjectID =
tblADMembership5.ChildAdObjectID
Left Join tblADGroups tblADGroups6 On tblADMembership5.ParentAdObjectID =
tblADGroups6.ADObjectID
Where tblADObjects.sAMAccountName = 'user or group or computer' And tblADObjects.domain = 'your_domain'
Order By Group_L6 Desc,
Group_L5 Desc,
Group_L4 Desc,
Group_L3 Desc,
Group_L2 Desc,
Group_L1 Desc
06-15-2023 12:47 PM
that helped me well, thank you
06-15-2023 05:28 AM - edited 06-15-2023 05:31 AM
User in group direct membership query:
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
06-14-2023 03:41 PM
Thanks for the code, how do I get this code working in LS. For now I get an error
Error: Incorrect syntax near ')'.
06-15-2023 05:27 AM
I wrote:
"it does not work in report builder".
So you can run it in SQL Management Studio.
06-14-2023 01:25 PM - edited 06-14-2023 01:51 PM
We have written testing code, but it does not work in report builder
WITH UserGroups AS (
SELECT adu.Username,adu.Userdomain, ParentGroup.Name AS GroupName
,ParentGroup.ADObjectID
FROM tblADMembership AS ChildMembership
INNER JOIN tblADGroups AS ParentGroup ON ChildMembership.ParentAdObjectID = ParentGroup.ADObjectID
INNER JOIN tblADUsers AS ChildUser ON ChildMembership.ChildAdObjectID = ChildUser.ADObjectID
INNER JOIN tblADUsers AS adu ON ChildUser.Username = adu.Username and ChildUser.Userdomain = adu.Userdomain
WHERE adu.Userdomain='your_domain' and adu.username in ('user1', 'user2')
UNION ALL
SELECT UserGroups.Username,UserGroups.Userdomain, ParentGroup.Name AS GroupName, ParentGroup.ADObjectID
FROM UserGroups
INNER JOIN tblADMembership AS ChildMembership ON UserGroups.ADObjectID = ChildMembership.ChildAdObjectID
INNER JOIN tblADGroups AS ParentGroup ON ChildMembership.ParentAdObjectID = ParentGroup.ADObjectID
)
SELECT Username,userdomain, STUFF((SELECT ', ' + GroupName FROM UserGroups AS ug2 WHERE ug1.Username = ug2.Username and ug1.Userdomain = ug2.Userdomain FOR XML PATH('')), 1, 2, '') AS Groups
FROM UserGroups AS ug1
GROUP BY Username, Userdomain
06-14-2023 01:02 PM
I think about something like this
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now