
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-15-2018 09:47 PM
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
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-16-2018 06:13 PM
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
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
6 REPLIES 6

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-16-2018 08:52 PM
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
see this post if that is what you are asking. https://www.lansweeper.com/Forum/yaf_postst12383_Make-report-clickable.aspx#post44765

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-16-2018 08:32 PM
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.
THanks again for the help.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-16-2018 06:13 PM
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
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-16-2018 05:19 PM
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
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-16-2018 05:03 PM
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.
If you wanted just Domain Users
Lastly if you wanted more than one group...Say domain users and Information systems managers... you could do this.
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%'

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎03-16-2018 02:41 PM
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.
