‎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
‎01-19-2024 06:03 PM
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 🙂
‎04-23-2024 06:14 PM
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.
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now