Jump to content


terraj01

SCCM Machine collection based off of User group

Recommended Posts

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.

Share this post


Link to post
Share on other sites


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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...