none
How to create a User and device affinity based single collection RRS feed

  • 问题

  • Experts,

    I'm looking forward to create a user collection, this collection needs to be based on User and Device Affinity. Please suggest how we can create a collection based on query membership rule.

    There is a suggestion to use the below query,


    selectdistinct
    SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,
    SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,
    SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client
    fromSMS_R_System JOINSMS_UserMachineRelationship ON
    SMS_R_System.ResourceID=SMS_UserMachineRelationship.ResourceID
    JOINSMS_R_User ON
    SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
    WHERESMS_UserMachineRelationship.Types=1 AND
    SMS_UserMachineRelationship.IsActive=1 AND
    SMS_R_User.UserGroupName="MYDOMAIN\\My-User-Security-Group

    But this query is not letting me to create the query membership rule in both device & user collection.


    Regards, Shanker Kumar


    • 已编辑 Shanker Kumar 2020年7月10日 14:19 Missed few details
    2020年7月10日 14:11

答案

  • Hi,

    Yes, but first the group must be enabled in Active Directory Group Discovery located within Administrator\Hierarchy Configuration\Discovery Methods, and discovered in the Assets and Compliance\User.
    Then use the query from the first reply, remember to replace the UserGroupName with your group name:
    select distinct
    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.ResourceID=SMS_UserMachineRelationship.ResourceID
    JOIN SMS_R_User ON
    SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
    WHERE SMS_UserMachineRelationship.Types=1 AND
    SMS_UserMachineRelationship.IsActive=1 AND
    SMS_R_User.UserGroupName="MYDOMAIN\\My-User-Security-Group"

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    2020年7月15日 6:40

全部回复

  • Hi,

    The query you provided is for creating a device collection based on a user group, you get nothing could be due to you do not replace the UserGroupName with your group name, and you missed a blank after the keyword: select, from, join, where.
    The query should be:
    select distinct
    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.ResourceID=SMS_UserMachineRelationship.ResourceID
    JOIN SMS_R_User ON
    SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
    WHERE SMS_UserMachineRelationship.Types=1 AND
    SMS_UserMachineRelationship.IsActive=1 AND
    SMS_R_User.UserGroupName="YOUR USER GROUP"
    And what is your specific requirements for creating a user collection? What is your purpose for creating this collection?

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    2020年7月13日 3:17
  • Thanks for your response Allen. I'm looking forward to create a new device collection based on the User and Device Affinity settings. 

    What I'm confused now is whether this can be achieved by Device Collection or User Collection as I'm unsure of this part.

    Basically by creating this collection , we are trying to get the primary device assigned to an user using the UDA settings. So please suggest which would be the best practice to achieve this, would it be Device Collection or User Collection?


    Regards, Shanker Kumar

    2020年7月13日 9:59
  • Hi,

    If you just want to get a collection with the devices that assigned to any user as a primary device, you could create a device collection with the query:

    select distinct
    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.ResourceID=SMS_UserMachineRelationship.ResourceID
    WHERE SMS_UserMachineRelationship.Types=1 AND
    SMS_UserMachineRelationship.IsActive=1 

    If you want to get a report to see all the device with the primary users, you could check the report "User device affinity association per collection" under Monitoring > Reporting > Reports > User-Device Affinity.

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    2020年7月14日 6:02
  • Thanks for your reply Allen, I was able to retrieve the list of primary device assigned to any user using this query but is there a way to add an AD group to this query and get only the list of machines assigned to the users of that specific group.

    Regards, Shanker Kumar

    2020年7月15日 4:27
  • Hi,

    Yes, but first the group must be enabled in Active Directory Group Discovery located within Administrator\Hierarchy Configuration\Discovery Methods, and discovered in the Assets and Compliance\User.
    Then use the query from the first reply, remember to replace the UserGroupName with your group name:
    select distinct
    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.ResourceID=SMS_UserMachineRelationship.ResourceID
    JOIN SMS_R_User ON
    SMS_UserMachineRelationship.UniqueUserName=SMS_R_User.UniqueUserName
    WHERE SMS_UserMachineRelationship.Types=1 AND
    SMS_UserMachineRelationship.IsActive=1 AND
    SMS_R_User.UserGroupName="MYDOMAIN\\My-User-Security-Group"

    Regards,
    Allen

    Please remember to mark the replies as answers if they help.

    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    2020年7月15日 6:40