Local Admin Group Contains Member

0

I need to make a report that will list computers missing a specific local user from the local admin groups. We have a variety of local users in the local admin group which is fine, but I need to ensure that a single local user is in all local admin groups across all computers. I can't figure out how to setup the report so that it filters out computers who contain that local user.

Cancel
login to comment
0

I have 2 similar SQL ones to offer, you can play around with them, maybe helps...

Show PC-s where users are in Local Admin group:

SELECT
   Computers.Name as "Computer Name",
   Computers.CurrentUser as "Current User",
   LocalGroupMembers.UserName as "Local Group Member Name",
   LocalGroupMembers.GroupName as "Local Group Member Group"
   FROM Computers
   INNER JOIN LocalGroupMembers ON LocalGroupMembers.ComputerId = Computers.ComputerId
WHERE <ComputerFilter>
   AND LocalGroupMembers.GroupName = 'Administrators'
   AND Computers.CurrentUser LIKE '%'||LocalGroupMembers.UserName||'%'
   AND NOT LocalGroupMembers.UserName LIKE 'Administrator'
   AND NOT LocalGroupMembers.UserName LIKE 'Domain Admins'

order by Computers.Name

All AD Members in Local PC Admin Group who are NOT supposed to be there or not Computer Default

SELECT
   Computers.Name as "Computer Name",
   Computers.CurrentUser as "Current User",
   LocalGroupMembers.UserName as "Local Group Member Name",
   Computers.ADDisplayName as "AD Full Name"
FROM Computers
   INNER JOIN LocalGroupMembers ON LocalGroupMembers.ComputerId = Computers.ComputerId
WHERE <ComputerFilter>
   AND LocalGroupMembers.GroupName = 'Administrators'
   AND Computers.CurrentUser LIKE '%'||LocalGroupMembers.UserName||'%'
   AND NOT LocalGroupMembers.UserName LIKE 'Administrator'
   AND NOT LocalGroupMembers.UserName LIKE 'Domain Admins'
   AND NOT LocalGroupMembers.UserName LIKE 'admin'
   AND NOT LocalGroupMembers.UserName LIKE 'MY AD GROUP'
   AND NOT LocalGroupMembers.UserName LIKE 'MY DIFFERENT AD GROUP'
Cancel
login to comment

Reply