1

I'm trying to create a device collection in SCCM 2012 which contains only the devices who are used by the users who are members of a certain User AD Security Group. I tried to accomplish this by first making a query for all the users in the group, which worked fine:

select SMS_R_User.UserGroupName, 
SMS_R_User.UserName 
from  SMS_R_User where SMS_R_User.UserGroupName = "Domain\\AD_Group"

Then I created a collection of devices with a query rule where the criteria was that if the last logged on user of the device was part of the subselected values of the first group query I made, then those devices would be added to the collection. The query rule:

select *  from  SMS_R_System 
where SMS_R_System.LastLogonUserName 
in (select SMS_R_User.UserGroupName, SMS_R_User.UserName 
from  SMS_R_User where SMS_R_User.UserGroupName = "Domain\\AD_Group")

However when I try to save the query rule Configuration Manager says that the query is not valid. I'm not exactly sure what I'm doing wrong. Maybe there is a better approach?

  • What is you end goal? This seems rather close to what SCCM's User Device Affinity already implements. Is there a reason you can't use UDA to achieve your goal? – alx9r Jul 2 '15 at 15:17
1

Try this one:

SELECT * FROM SMS_R_SYSTEM 
  INNER JOIN SMS_R_uSER 
  ON SMS_r_system.LastLogonUserName = SMS_R_uSER.FullUserName 
WHERE SMS_R_uSER.SecurityGroupName = 'Domain\\AD_Group';
0

Try this one (it worked for me):

select *  from  SMS_R_System 
where SMS_R_System.LastLogonUserName 
in (select SMS_R_User.UserName 
from  SMS_R_User where SMS_R_User.UserGroupName = "Domain\\AD_Group")

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.