SCCM Machine collection based off of User group

[terraj01]

Looking for some help for SQL query to build a collection. I want to deploy software to a users Primary machine based on if they are in a specific security group. So far my query in sql works as expected but i have some users who have both a laptop and a desktop while others have just either a laptop or desktop. so  i want to limit it to something like if the user shows up twice then just add the users laptop to the collection.

This is what i have so far

SELECT 
    UserGroupName.ResourceID, 
    UserGroupName.User_Group_Name0,
    v_R_User.Mail0,
    v_R_User.User_Name0,
    v_R_System.Name0,
    v_R_System.DeviceOwner0,
    v_R_System.Is_Assigned_To_User0 as [Assigned],
    v_R_System.Last_Logon_Timestamp0,
    v_R_System.User_Name0 as [Sys_UserName]

FROM v_RA_User_UserGroupName as UserGroupName
  left join  v_R_User on v_R_User.ResourceID = UserGroupName.ResourceID
  left join v_R_UserGroup as Usergroup on Usergroup.ResourceID = UserGroupName.ResourceID
  left join  v_r_system on v_R_System.User_Name0 =  v_R_User.User_Name0
where UserGroupName.User_Group_Name0 like 'Domain\SecurityGroup%'

 

I know i can deploy directly to the user group but that will still yield the same results as it would deploy to both the users system if they are primary devices. i figured this would be the best way to do it. if someone has another way i am open to that also.

[GarthMJ]

So why are you not using the Primary device option within CMCB applications?

You can't use a SQL query for collections, only WQL can be used.

[terraj01]

i was just using SQL to see what kind of results I would be getting to verify if I was doing things right. that is how I learned I wasn't getting the results I wanted and need to filter out the users that have multiple machine that its only installed on one of their devices.

[GarthMJ]

ok that is a good idea on how to learn CM