SQL SCCM 2012 Report Return Local Admins not in specified AD Group

Question

I've been looking over the internet and StackOverflow for a while and have come to a point where I am lost. Unfortunately, SQL is not one of my strong points. I am trying to get a report of Local Computer Admins. This part is working correctly. I need to filter this list by AD Groups, which is also working, and by members of the AD groups, not currently working.

Here's what I have:

SELECT DISTINCT
  v_gs_computer_system.Name0 as 'Computer'
  ,v_GS_LocalGroupMembers0.Account0 as 'Administrator'
  ,v_GS_LocalGroupMembers0.Category0 as 'Scope'
  ,v_GS_LocalGroupMembers0.Type0 as 'Type'
  ,U.User_Name0 as 'User'
  ,G.User_Group_Name0 as 'Group'
FROM
  v_GS_LocalGroupMembers0
  INNER JOIN v_gs_computer_system ON v_GS_LocalGroupMembers0.resourceID = v_gs_computer_system.resourceID
  FULL OUTER JOIN v_R_User U on v_GS_LocalGroupMembers0.Account0 = U.User_Name0
  FULL OUTER JOIN v_RA_User_UserGroupName G ON G.ResourceID = U.ResourceID
WHERE
  v_GS_LocalGroupMembers0.Name0 = 'Administrators'
AND NOT
  (v_GS_LocalGroupMembers0.Account0 = 'Domain Admins'
   OR G.User_Group_Name0 = 'Domain\GroupName'
)

The parts that are working:

• List all local admins on computers
• Rows for "Domain Admins" are removed from the results

What I need help with:

• Rows where users are a member of the 'Domain\GroupName' to be removed from the results

I see that what I am doing is wrong because it does remove the Row for the group of the user, but I want it to remove the user entirely from the report. Any help you can provide would be greatly appreciated.

Answer 1

So, after some time and tinkering, I was able to get what I wanted with the following:

SELECT DISTINCT
  v_gs_computer_system.Name0 as 'Computer',
  v_GS_LocalGroupMembers0.Account0 as 'Account Name',
  v_GS_LocalGroupMembers0.Category0 as 'Security Principle',
  v_GS_LocalGroupMembers0.Type0 as 'Scope'
FROM
  v_GS_LocalGroupMembers0 
INNER JOIN
  v_gs_computer_system ON v_GS_LocalGroupMembers0.resourceID = v_gs_computer_system.resourceID,
  v_R_User
WHERE
  v_GS_LocalGroupMembers0.Name0 = 'Administrators'
AND NOT (
  v_GS_LocalGroupMembers0.Account0 = 'Domain Admins' OR
  v_GS_LocalGroupMembers0.Account0 = 'Administrator'
)
AND (
  v_GS_LocalGroupMembers0.Account0 NOT IN (
    SELECT v_R_User.User_Name0
    FROM v_R_user 
    LEFT JOIN v_RA_User_UserGroupName ON v_R_User.ResourceID = v_RA_User_UserGroupName.ResourceID
    WHERE 
      v_RA_User_UserGroupName.User_Group_Name0 = 'Domain\Domain Admins'
  )
)