cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
SBrassard
Engaged Sweeper II
Hi everyone,

I'm trying to run a report to show me all of the users that are not in one of the two groups that we use to define our screen lock time. The problem that I'm having is that I can't find a way to filter out all of the groups that aren't related to the two groups I'm looking for. My end goal is to get a list of just my users that aren't in one of those two groups, but I would also settle for a list of all users with just Internal, External, or Not Assigned. (Which is what I'm going for in the code below.)

Any help that anyone could offer is greatly appreciated. The code below is what I have so far if anyone want's to tell me what I'm doing wrong as well.

Thank you!

Select Distinct Top 1000000 tblADusers.Username,
tblADusers.Name,
tblADusers.OU,
tblADGroups.Name As [Group],
(Case
When tblADGroups.Name = 'InternalScreenLock'
Then 'internal'
When tblADGroups.Name = 'ExternalScreenlock'
Then 'External'
Else 'Not assigned'
End) As ScreenLock
From tblADGroups
Inner Join tblADMembership On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Inner Join tblADusers On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
This should get you started:
Select Top 1000000 tblADusers.Userdomain,
tblADusers.Username,
tblADusers.Name,
tblADusers.OU
From tblADusers
Where tblADusers.ADObjectID Not In (Select tblADMembership.ChildAdObjectID
From tblADMembership Inner Join tblADGroups On tblADGroups.ADObjectID =
tblADMembership.ParentAdObjectID
Where (tblADGroups.Name = 'InternalScreenLock') Or
(tblADGroups.Name = 'ExternalScreenlock'))

View solution in original post

2 REPLIES 2
SBrassard
Engaged Sweeper II
You are an amazing person! Thank you so much.
MikeMc
Champion Sweeper II
This should get you started:
Select Top 1000000 tblADusers.Userdomain,
tblADusers.Username,
tblADusers.Name,
tblADusers.OU
From tblADusers
Where tblADusers.ADObjectID Not In (Select tblADMembership.ChildAdObjectID
From tblADMembership Inner Join tblADGroups On tblADGroups.ADObjectID =
tblADMembership.ParentAdObjectID
Where (tblADGroups.Name = 'InternalScreenLock') Or
(tblADGroups.Name = 'ExternalScreenlock'))

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now