Collection Query – AD group membership & filter for Add\Remove Programs

select
    R.* 
from 
    SMS_R_System R
where
    R.SystemGroupName != “GARTEK\\Domain Admins”
    and R.ResourceId not in (    select
                        R.ResourceId
                    from 
                        SMS_R_System R
                        inner join SMS_G_System_ADD_REMOVE_PROGRAMS ARP on ARP.ResourceId = R.ResourceId
                    where
                        ARP.DisplayName = “CCM Framework Tools”)