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?

Chobom
  • 33
  • 1
  • 6
  • 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 02 '15 at 15:17

2 Answers2

0

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';
Reaces
  • 5,547
  • 4
  • 36
  • 46
facebook-10205946014948095
  • 1
  • 1
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")
Pierre.Vriens
  • 1,159
  • 34
  • 15
  • 19
Veeresh
  • 1