Active Directory Security Group including other security groups

[Michaelzip0]

Im trying to get a report that includes other security groups assigned to a security group, so security group A has 5 people and 4 security group/Job Roles assigned to them then I should get the 5 people and 4 groups listed but all im finding is the people assigned wich makes for an incomplete report sins there are people assigned to those other groups

[Mister_Nobody · Accepted solution]

Try this

OU you can add for user and computer. OU for group only from exchange scan

Select Top 1000000 tblADObjects.domain,
  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,
  (Select tblADObjects.domain From tblADObjects
    Where tblADObjects.ADObjectID = tblADGroups1.ADObjectID) As Group_Domain,
  tblADGroups1.Name,
  tblADGroups1.Description,
  Case tblADGroups1.GroupType
    When -2147483646 Then 'Security - Global'
    When -2147483644 Then 'Security - Local'
    When -2147483643 Then 'Built-in'
    When -2147483640 Then 'Security - Universal'
    When 2 Then 'Distribution - Global'
    When 4 Then 'Distribution - Local'
    When 8 Then 'Distribution - Universal'
  End As ADGroupType
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

[Michaelzip0]

[Mister_Nobody]

You have to publish query and show what you want via excel table screenshot

[Michaelzip0]

I just can't click on the group or user to get sublists but besides that it's perfect and I can do a different sort to get the bigger picture. thanks 🙂 

[Michaelzip0]

That's normally what I do but when there are a bunch of groups like 10 or 15 in a parent group plus directly assigned one off's it can take longer than others above me like.