cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Michaelzip0
Engaged Sweeper III

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

1 ACCEPTED SOLUTION
Mister_Nobody
Honored Sweeper II

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

View solution in original post

5 REPLIES 5
Mister_Nobody
Honored Sweeper II

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
Engaged Sweeper III

Michaelzip0_0-1705508603288.png

 

Mister_Nobody
Honored Sweeper II

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

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 🙂 

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.