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 05:48 AM - edited 06-15-2023 06:32 AM
Easy SQL 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,
tblADGroups1.Description
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
06-14-2023 10:16 AM
I need this feature also.... all members from the local administrator group per system with nested group
I need also this feature... all users from a group and nested group
06-13-2023 11:45 AM - edited 06-14-2023 05:16 AM
Please explain the result - for example in excel.
I don't know how to execute recursive SQL query in LS report builder to support nested groups.
06-09-2023 10:24 AM
You can see Built-in report
Users: AD users and their AD groups
06-09-2023 11:03 AM
I actually want a report with all AD groups and their members. Their could be users and groups as members.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now