→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
solutioncenter
Engaged Sweeper
Hey all;

So we are trying to create a report of machines where the domain admins group isnt a member of the local administrators group. We can create the positive result on this query below but how do we get the other result we want, I am aware that we might get nulls since maybe the machine isnt scanned or we cant because of access right's

SELECT TOP (10000000) dbo.tblcomputers.ComputerUnique, dbo.tblUsersInGroup.Computername, dbo.tblUsersInGroup.Groupname, dbo.tblUsersInGroup.Domainname,
dbo.tblUsersInGroup.Username, dbo.tblUsersInGroup.Lastchanged
FROM dbo.tblUsersInGroup INNER JOIN
dbo.tblcomputers ON dbo.tblUsersInGroup.Computername = dbo.tblcomputers.Computername
WHERE (dbo.tblUsersInGroup.Username = 'domain admins') AND (dbo.tblUsersInGroup.Groupname = 'administrators')
ORDER BY dbo.tblUsersInGroup.Computername
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Try something like this:

Select Top (10000000) dbo.tblcomputers.ComputerUnique,
dbo.tblcomputers.Computername
From dbo.tblcomputers
Where dbo.tblcomputers.Computername Not In (Select tblusersingroup.Computername
From tblusersingroup
Where tblusersingroup.Groupname = 'administrators' And
tblusersingroup.Username = 'domain admins')

View solution in original post

2 REPLIES 2
solutioncenter
Engaged Sweeper
Most Excellent thx!
Hemoco
Lansweeper Alumni
Try something like this:

Select Top (10000000) dbo.tblcomputers.ComputerUnique,
dbo.tblcomputers.Computername
From dbo.tblcomputers
Where dbo.tblcomputers.Computername Not In (Select tblusersingroup.Computername
From tblusersingroup
Where tblusersingroup.Groupname = 'administrators' And
tblusersingroup.Username = 'domain admins')