Report AD groups and their members (incl. groups)

Sysmaker · ‎06-09-2023

Hi there

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

Mister_Nobody · ‎06-15-2023

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

View solution in original post

Mister_Nobody · ‎06-15-2023

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

RolandB · ‎06-14-2023

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

Mister_Nobody · ‎06-13-2023

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.

Mister_Nobody · ‎06-09-2023

You can see Built-in report

Users: AD users and their AD groups

Sysmaker · ‎06-09-2023

I actually want a report with all AD groups and their members. Their could be users and groups as members.

Report AD groups and their members (incl. groups)

New to Lansweeper?

Try Lansweeper For Free