01-16-2024 11:35 PM - last edited on 04-01-2024 12:32 PM by Mercedes_O
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
Solved! Go to Solution.
01-18-2024 04:19 AM
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
01-18-2024 04:19 AM
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
01-17-2024 05:23 PM
01-17-2024 03:53 AM
You have to publish query and show what you want via excel table screenshot