none
SCCM 2012 buid computer collection based on user group membership / primary user RRS feed

  • Question

  • Hello,

    i try to deploy a special task sequence to a specific user group, unfortunately its not possible to deploy a task sequence to a user collection, so my question: is it possiable to build a computer collation based on primary users from a specific user security group?
    Thursday, August 23, 2012 4:43 PM

Answers

  • The query I wanted was:

    The computer's primary user whose AD department attribute was "Department Name". I translated and confirmed the statement to work with User Groups. Also keep in mind that searching for the primary user, the rules need to be in place and time schedule set for that functionality.

    Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client, SMS_R_User.UniqueUserName FROM SMS_R_System JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName Where SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "Domain\\GroupName")

    Here is the breakdown.

    Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client, SMS_R_User.UniqueUserName

    This select statement is pretty standard when it comes to device collections. With the exception of "SMS_R_User.UniqueUserName". This is added and needed in the Select statement so that the Where statement can look for it.

    FROM SMS_R_System
    JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName
    JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName

    Since the User tables and System tables are far from each other, we need a middle man essentially to connect the two. We take the unique System Name and link it to the same unique MachineResourceName in UserMachineRelationship. Then from the User table, we link the UniqueUserName field in the User table to the UserMachineRelatioship table. Luckily the field names are the same. Thus connecting computers to users.

    Where SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "Domain\\GroupName")

    Now for the fun part.

    We are using the Where statement to search the UniqueUserName from the results of another query. Which is pulling the users that are a part of the "Domain\Group" user group. It is important to note that SQL needs the double slash ("\\") in the statement to work. This is how we are really pulling the user data to be used as the source for the computer query.

    This SQL statement structure can be translated and morphed into other statements where you would want to search for computers based on user data. To change it, the JOIN statement and the nested SELECT statement needs to be from the same table, or else the nested SELECT statement would need to to have another JOIN in it that contains the field from the parent WHERE statement so the data would be same. Having them the same makes it less confusing. The 'UserGroupName = "Domain\\GroupName"' can be changed to a lot of things. This is really the root of the query that you are looking for.

    Hope this helps.


    • Edited by Mr. Tyler Tuesday, August 28, 2012 6:37 PM edit
    • Proposed as answer by Mr. Tyler Tuesday, August 28, 2012 6:37 PM
    • Marked as answer by concomet Sunday, September 2, 2012 5:03 PM
    Tuesday, August 28, 2012 6:37 PM

All replies

  • Here is a query rule that will create a collection of computers based on the security group the computer is in change the Domain/Group obviously:

    select SMS_R_System.ResourceId, SMS_R_System.ResourceType, SMS_R_System.Name, SMS_R_System.SMSUniqueIdentifier, SMS_R_System.ResourceDomainORWorkgroup, SMS_R_System.Client from  SMS_R_System where SMS_R_System.SystemGroupName = "SCCM\\Domain Computers"


    Justin | http://patchmypc.net | Please remember to mark as helpful/answer if this helped you

    Thursday, August 23, 2012 5:03 PM
    Moderator
  • Thanks for the reply, but I dont have any computers in my security group, only users. So I need a query, which looks up the members(users) and assign the primary device/computer in my collection.

    background: I would like to deploy a user driven installation task sequnce to specific users/computers, in AD we have only organized user accounts - all computer objects are in the same OU - so I would like to have a dynamic query based on the users/primary device.


    • Edited by concomet Thursday, August 23, 2012 5:17 PM
    Thursday, August 23, 2012 5:15 PM
  • I think justin is on the right path. There needs to be some kind of join in the SQL statement. That would essentially link some unique record that is the same for a System table and a User table. that would get you in a closer direction to your goal. i'm on a similar path, so i'll post my findings when i get there.
    • Proposed as answer by Mr. Tyler Tuesday, August 28, 2012 6:08 PM
    • Unproposed as answer by Mr. Tyler Tuesday, August 28, 2012 6:08 PM
    Tuesday, August 28, 2012 4:55 PM
  • The query I wanted was:

    The computer's primary user whose AD department attribute was "Department Name". I translated and confirmed the statement to work with User Groups. Also keep in mind that searching for the primary user, the rules need to be in place and time schedule set for that functionality.

    Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client, SMS_R_User.UniqueUserName FROM SMS_R_System JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName Where SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "Domain\\GroupName")

    Here is the breakdown.

    Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client, SMS_R_User.UniqueUserName

    This select statement is pretty standard when it comes to device collections. With the exception of "SMS_R_User.UniqueUserName". This is added and needed in the Select statement so that the Where statement can look for it.

    FROM SMS_R_System
    JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName
    JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName

    Since the User tables and System tables are far from each other, we need a middle man essentially to connect the two. We take the unique System Name and link it to the same unique MachineResourceName in UserMachineRelationship. Then from the User table, we link the UniqueUserName field in the User table to the UserMachineRelatioship table. Luckily the field names are the same. Thus connecting computers to users.

    Where SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "Domain\\GroupName")

    Now for the fun part.

    We are using the Where statement to search the UniqueUserName from the results of another query. Which is pulling the users that are a part of the "Domain\Group" user group. It is important to note that SQL needs the double slash ("\\") in the statement to work. This is how we are really pulling the user data to be used as the source for the computer query.

    This SQL statement structure can be translated and morphed into other statements where you would want to search for computers based on user data. To change it, the JOIN statement and the nested SELECT statement needs to be from the same table, or else the nested SELECT statement would need to to have another JOIN in it that contains the field from the parent WHERE statement so the data would be same. Having them the same makes it less confusing. The 'UserGroupName = "Domain\\GroupName"' can be changed to a lot of things. This is really the root of the query that you are looking for.

    Hope this helps.


    • Edited by Mr. Tyler Tuesday, August 28, 2012 6:37 PM edit
    • Proposed as answer by Mr. Tyler Tuesday, August 28, 2012 6:37 PM
    • Marked as answer by concomet Sunday, September 2, 2012 5:03 PM
    Tuesday, August 28, 2012 6:37 PM
  • It works, thanks a lot!



    • Edited by concomet Sunday, September 2, 2012 5:03 PM
    Sunday, September 2, 2012 5:02 PM
  • SMS_UserMachineRelationship and SMS_R_User

    …these class exists of course, but I’m not able to use them in JOIN or sub-SELECT statements.

    As of right now the corresponding table/view for the classes SMS_UserMachineRelationship and SMS_R_User do not have any records.  When I try to save the query in the console, it errors-out (see below).

    The strange part is I can connect to the site via WMI (e.g. root\sms\site_<siteCode>) and execute the query Select * from SMS_UserMachineRelationship (as expected the query returns 0 results because there are no records in the corresponding table).

    As a note – also in WMI/wbemtest-, I’m able to successfully execute the following query, which is ultimately the query I’d like to use for the collection (formatted below as it is for readability purposes):

    Select

         SMS_R_SYSTEM.ResourceID,

         SMS_R_SYSTEM.ResourceType,

         SMS_R_SYSTEM.Name,

         SMS_R_SYSTEM.SMSUniqueIdentifier,

         SMS_R_SYSTEM.ResourceDomainORWorkgroup,

         SMS_R_SYSTEM.Client,

         SMS_R_User.UniqueUserName

    FROM

         SMS_R_System

    JOIN

         SMS_UserMachineRelationship

    ON

         SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName

    JOIN

         SMS_R_User

    ON

         SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName

    Where

         SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "DOMAIN\\GROUPNAME")

    …again, the problem is that I’m not able to use the classes SMS_UserMachineRelationship or SMS_R_User in the JOIN and sub-SELECT statements of the query below.

    Anyone have any ideas?  I think I’m missing something, but not sure what it is…

    Wednesday, September 12, 2012 8:52 PM
  • I can't get this to work either, well i can but only as an actual query not a device collection query. 

    In 2007 i was using this query which would query top console users by organization department. This doesnt work in 2012 either it tells me it's an invalid query.

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
    from SMS_G_system_SYSTEM_CONSOLE_USAGE
    INNER JOIN SMS_R_System ON SMS_G_system_SYSTEM_CONSOLE_USAGE.ResourceID = SMS_R_System.ResourceID
    INNER JOIN SMS_R_User ON SMS_G_system_SYSTEM_CONSOLE_USAGE.TopConsoleUser = SMS_R_User.UniqueUserName
    where SMS_R_User.department = "Legal"

    When i attempt to use freakisna's query i get another error telling me that "the specified resource class does not match the current collection type". I can however create a query in the queries section under monitoring.

    Anyone have any ideas as to why this is the case? my SQL and WQL is super bad.


    Thursday, September 20, 2012 5:33 AM
  • Well as soon as i typed that i decided to try something else and got it to work. Imported the query statement from the other query and it all works.

    please note i am a fair noob. huhu

    So for the benefit of other noobs as it has never been obvious to me that this is what you can do for these confusing queries.

    1. Create the query using the above in Monitoring > Queries

    2. Create a device collection and import the query statement from the query created in step 1.

    3. ???

    4. Profit.

    • Edited by HughMc Thursday, September 20, 2012 6:11 AM added more interesting information
    Thursday, September 20, 2012 5:43 AM
  • I AM EXPERIENCING THE SAME PROBLEM.

    PLEASE ANYONE CAN HELP?

    SMS_UserMachineRelationship and SMS_R_User

    …these class exists of course, but I’m not able to use them in JOIN or sub-SELECT statements.

    As of right now the corresponding table/view for the classes SMS_UserMachineRelationship and SMS_R_User do not have any records.  When I try to save the query in the console, it errors-out (see below).

    The strange part is I can connect to the site via WMI (e.g. root\sms\site_<siteCode>) and execute the query Select * from SMS_UserMachineRelationship (as expected the query returns 0 results because there are no records in the corresponding table).

    As a note – also in WMI/wbemtest-, I’m able to successfully execute the following query, which is ultimately the query I’d like to use for the collection (formatted below as it is for readability purposes):

    Select

         SMS_R_SYSTEM.ResourceID,

         SMS_R_SYSTEM.ResourceType,

         SMS_R_SYSTEM.Name,

         SMS_R_SYSTEM.SMSUniqueIdentifier,

         SMS_R_SYSTEM.ResourceDomainORWorkgroup,

         SMS_R_SYSTEM.Client,

         SMS_R_User.UniqueUserName

    FROM

         SMS_R_System

    JOIN

         SMS_UserMachineRelationship

    ON

         SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName

    JOIN

         SMS_R_User

    ON

         SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName

    Where

         SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "DOMAIN\\GROUPNAME")

    …again, the problem is that I’m not able to use the classes SMS_UserMachineRelationship or SMS_R_User in the JOIN and sub-SELECT statements of the query below.

    Anyone have any ideas?  I think I’m missing something, but not sure what it is…


    • Proposed as answer by aersik Friday, September 12, 2014 10:23 AM
    • Unproposed as answer by aersik Friday, September 12, 2014 10:23 AM
    Tuesday, September 25, 2012 1:00 PM
  • Hi I am experiencing same issue here. Any update on this matter.

    Regards,

    Cedric

    Wednesday, October 3, 2012 2:18 PM
  • Freakisna, you are a legend!

    So like many others here, I simply cut and paste Freakisnas query, editting this part of course

    "Domain\\GroupName"

    In my grand wisdom, I decided to drop one \ making it "domain\groupname", this gave me same result as everyone else with the query syntax error. So I added the extra \ and it works perfectly!

    Tuesday, October 9, 2012 12:46 AM
  • One refinement to this query to enable only the selection of the user's Primary Device(s) - add "AND SMS_UserMachineRelationship.isActive <> 0" to the JOIN of SMS_UserMachineRelationship.

    My query looks like the following, and doesn't return devices for which the user no longer has a primary relationship:

    Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client, SMS_R_User.UniqueUserName
    FROM SMS_R_System
    JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.MachineResourceName AND SMS_UserMachineRelationship.isActive <> 0
    JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
    Where SMS_R_User.UniqueUserName in (select UniqueUserName from SMS_R_User where UserGroupName = "{Domain}\\{GroupName}")
    Oh, and Hugh McIntyre's suggestion for how to get this query into the Membership Rules worked well for me (I wasn't able to paste the query directly in without getting the errors he mentioned).
    Tuesday, December 4, 2012 7:46 PM
  • In my testing, the query cepullm provided will return computers that the member of the AD group has logged on to.  For example, if I am the primary user on two computers, but have logged on to a third computer in the past, all three computers are returned by the query.

    A query that appears to return only computers for which the member of the AD group is the primary user can be found at: http://powersheller.wordpress.com/tag/collection-query/

    Select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client 
    FROM SMS_R_System 
    JOIN SMS_UserMachineRelationship ON SMS_R_System.Name=SMS_UserMachineRelationship.ResourceName 
    JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName 
    WHERE SMS_UserMachineRelationship.Types=1 AND SMS_R_User.UserGroupName="{Domain}\\{GroupName}"


    • Edited by ardamis Thursday, January 17, 2013 5:31 PM
    • Proposed as answer by DmitryK Friday, February 8, 2019 8:22 AM
    Thursday, January 17, 2013 5:28 PM
  • So i  tested ardamis query and System Resource QUERY and COLL is very funy ..... working but .... wql query builder does not accept this syntax ... but query is working THX 


    Thursday, June 27, 2013 10:07 PM
  • Is it really so, that Primary Users cannot be queried directly from build-in ConfMgr classes? We need a list of all client names + primary user names.
    Wednesday, August 21, 2013 12:13 PM
  • This query worked great but gave me a little too many computers. I had to create collections for users that are in support roles and login to many computers. I modified this query slightly to fit my requirements. The query returns just the primary machines. Maybe will be helpful to some of you:

    selectSMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client, SMS_G_System_COMPUTER_SYSTEM.UserName

    from SMS_R_System

    inner join SMS_G_System_COMPUTER_SYSTEM on SMS_G_System_COMPUTER_SYSTEM.ResourceID = SMS_R_System.ResourceId inner join SMS_R_User ON

    SMS_G_System_COMPUTER_SYSTEM.UserName=SMS_R_User.UniqueUserNamewhere

    SMS_R_User.UserGroupName="DOMAIN\\AD Group Name"

    • Proposed as answer by J_hotsauce Monday, March 21, 2016 4:33 PM
    Wednesday, October 7, 2015 6:24 PM
  • Thanks,

    I used this to build a query that would populate a Device Collection based on members of a User Collection (SCCM 2012 R2):

    select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System where SMS_R_System.ResourceId in (select resourceid from SMS_UserMachineRelationship JOIN SMS_FullCollectionMembership ON LOWER(SMS_UserMachineRelationship.UniqueUserName) = LOWER(SMS_FullCollectionMembership.SMSID)  where MS_UserMachineRelationship.UniqueUserName is not null AND SMS_UserMachineRelationship.Types=1 AND SMS_FullCollectionMembership.CollectionID='SMS00002')


    • Edited by Joseph604 Thursday, October 8, 2015 7:35 PM
    • Proposed as answer by Matthew Laye Tuesday, October 17, 2017 10:13 PM
    Thursday, October 8, 2015 7:34 PM
  • You saved me a headache!
    Saturday, January 14, 2017 4:00 PM
  • Well as soon as i typed that i decided to try something else and got it to work. Imported the query statement from the other query and it all works.

    please note i am a fair noob. huhu

    So for the benefit of other noobs as it has never been obvious to me that this is what you can do for these confusing queries.

    1. Create the query using the above in Monitoring > Queries

    2. Create a device collection and import the query statement from the query created in step 1.

    3. ???

    4. Profit.

    This does the trick for me

    Tried to copy and paste the above query in the device collection creation window and it wasn't accepted. But paste it to the monitoring query works, then simply import the query to the device collection creation window. Thanks!

    Tuesday, September 18, 2018 6:33 AM