Report AD groups and their members (incl. groups)

[Sysmaker]

Hi there

I need a report with all AD groups and their members including AD groups. Is this possible with Lansweeper?

[Mister_Nobody]

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

[Mister_Nobody]

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

[Mister_Nobody · Accepted solution]

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

[Sysmaker]

that helped me well, thank you

[Mister_Nobody]

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

[Sysmaker]

Thanks for the code, how do I get this code working in LS. For now I get an error

Error: Incorrect syntax near ')'.

[Mister_Nobody]

I wrote:
"it  does not work in report builder".

So you can run it in SQL Management Studio.

[Mister_Nobody]

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

[Sysmaker]

I think about something like this