→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JoshM
Engaged Sweeper
I know I can query for a list of users in an Active Directory group but can I get a list of groups in Active Directory that have no users?
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
I deleted your other post, as it appeared to be a duplicate of this one. I've included a sample report below that you can add to your installation by following these instructions.
Select Distinct Top 1000000 tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
Case tblADGroups.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 tblADMembership
Right Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Left Join tblADusers
On tblADusers.ADObjectID = tblADMembership.ChildAdObjectID
Where (tblADusers.ADObjectID Is Null) Or
(tblADusers.ADObjectID = '')
Order By ADGroupName

View solution in original post

4 REPLIES 4
Arjan
Engaged Sweeper II
Hi Susan,

This report is already very helpful, but in our situation we also have groups being member of a group.
I'd like to filter these out as well, but cannot figure out how.
As far as I have seen, LANsweeper needs at least a computer or user to be involved in a group to 'recognize' it.

Is the above possible?
SysIT
Engaged Sweeper II
Hello,

I noticed this shows empty OU's based on no users, I have several groups that contain computer objects in them and this report shows them as empty.

Can the report be adjusted to show empty, as in absolutely no objects with in them?
Susan_A
Lansweeper Alumni
SysIT wrote:
Hello,

I noticed this shows empty OU's based on no users, I have several groups that contain computer objects in them and this report shows them as empty.

Can the report be adjusted to show empty, as in absolutely no objects with in them?

I've included a sample report below that lists completely empty groups (no users and no computers).
Select Distinct Top 1000000 tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
Case tblADGroups.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 tblADMembership
Right Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Left Join tblADusers
On tblADusers.ADObjectID = tblADMembership.ChildAdObjectID
Left Join tblADMembership tblADMembership1
On tblADMembership1.ParentAdObjectID = tblADGroups.ADObjectID
Left Join tblADComputers On tblADComputers.ADObjectID =
tblADMembership1.ChildAdObjectID
Where (tblADusers.ADObjectID Is Null Or tblADusers.ADObjectID = '') And
(tblADComputers.ADObjectID Is Null Or tblADComputers.ADObjectID = '')
Order By ADGroupName
Susan_A
Lansweeper Alumni
I deleted your other post, as it appeared to be a duplicate of this one. I've included a sample report below that you can add to your installation by following these instructions.
Select Distinct Top 1000000 tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
Case tblADGroups.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 tblADMembership
Right Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Left Join tblADusers
On tblADusers.ADObjectID = tblADMembership.ChildAdObjectID
Where (tblADusers.ADObjectID Is Null) Or
(tblADusers.ADObjectID = '')
Order By ADGroupName