Users in a specific Active Directory Group

[malbanese1]

I need to keep an eye on what users are in a specific active directory group which is security sensitive. I would like to create a report that i can add to my dashboard so that i see it when i log in.

I looked through the reports section but didn't see anything that would work for me. I just need to see the user's display name if they are listed as a member.

Thank you

[AZHockeyNut]

I am not sure what you mean by "linked back" do you mean if you click the username in the report then it allows you to pull up that user record in Lansweeper similar to clicking on an asset?
see this post if that is what you are asking. https://www.lansweeper.com/Forum/yaf_postst12383_Make-report-clickable.aspx#post44765

[malbanese1]

That worked perfectly! Thank you. I have one other question for you if you don't mind. How do I format the UserName field to be a link back to the user record? Any help would be appreciated.

THanks again for the help.

[AZHockeyNut · Accepted solution]

the Like gives you not exact matches as well as exact matches. Meaning (like from my example) if I replaced your MYGROUP with Domain I would get Domain Users as well as Domain Admins. So where you are looking forr a specific group use = rather than like.
Normally the searching isn't case sensitive in my experience so you should be able to copy the group name from AD and paste it into your quotes. So DOMAIN USERS and Domain Users return the same results in your query

Select Top 1000000 tblADusers.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
tblADusers.Displayname,
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 tblADusers
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name = 'DOMAIN USERS'
Order By tblADusers.Username,
ADGroupName

[malbanese1]

Hello and thanks so much for the help. Here is the code i am using which i modified with a LIKE statement to limit the results to one AD Group.

Select Top 1000000 tblADusers.Username,
tblADusers.Firstname,
tblADusers.Lastname,
tblADusers.Name,
tblADGroups.Name As ADGroupName,
tblADGroups.Description As ADGroupDescription,
tblADusers.Displayname,
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 tblADusers
Left Join tblADMembership On tblADMembership.ChildAdObjectID =
tblADusers.ADObjectID
Left Join tblADGroups On tblADMembership.ParentAdObjectID =
tblADGroups.ADObjectID
Where tblADGroups.Name Like '%MYGROUP%'
Order By tblADusers.Username,
ADGroupName

[AZHockeyNut]

show us your code please (you can alter the name of the group if it is sensitive.)
happy to help

otherwise here is a quick and dirty example that gives you any users in groups that start with "Domain " the % is a wildcard.

Select Distinct G.ADGroupID,
G.Name,
U.Userdomain,
U.Username,
G.ADObjectID,
U.ADUserID
From tblADGroups As G
Inner Join tblADMembership As M On M.ParentAdObjectID = G.ADObjectID Or
M.ChildAdObjectID = G.ADObjectID
Inner Join tblADusers As U On U.ADObjectID = M.ChildAdObjectID
where g.name like 'Domain%'

If you wanted just Domain Users

Select Distinct G.ADGroupID,
G.Name,
U.Userdomain,
U.Username,
G.ADObjectID,
U.ADUserID
From tblADGroups As G
Inner Join tblADMembership As M On M.ParentAdObjectID = G.ADObjectID Or
M.ChildAdObjectID = G.ADObjectID
Inner Join tblADusers As U On U.ADObjectID = M.ChildAdObjectID
where g.name = 'Domain Users'

Lastly if you wanted more than one group...Say domain users and Information systems managers... you could do this.

Select Distinct G.ADGroupID,
G.Name,
U.Userdomain,
U.Username,
G.ADObjectID,
U.ADUserID
From tblADGroups As G
Inner Join tblADMembership As M On M.ParentAdObjectID = G.ADObjectID Or
M.ChildAdObjectID = G.ADObjectID
Inner Join tblADusers As U On U.ADObjectID = M.ChildAdObjectID
where g.name = 'Domain Users' or G.name like 'Information%'

[malbanese1]

Update: I tried modifying the Users: AD users and their AD groups with a LIKE statement on the Group name but got a lot of duplicate records.