locked
SCCM Collection displaying list of PC and thair Primary users RRS feed

  • Question

  • I'm trying to make a collection that would display All of the PC and Primary users of these PCs

    I don't mean Top Console user, I mean primary user that get set by User Device Affinity.

    Any help would be appreciated ?

    On the side note, is there a good guide on writing these and report quires ? or maybe a wizard like UI to build queries

    Friday, September 11, 2015 4:53 PM

Answers

  • To add-on there is a canned report, named User device affinity associations per collection, that provides that information. If you really want to have a view in the console than you could use the ConsoleBuilder and do something like this: http://gosc.nl/blog/technology/sccm/configmgr-2012-customization-creating-user-device-affinity-overview/

    My Blog: http://www.petervanderwoude.nl/
    Follow me on twitter: pvanderwoude

    Friday, September 11, 2015 5:57 PM

All replies

  • If you want a collection that list Primary users of the device this is not possible. You can't have a collection that as device and users in it.

    What you are looking for it's either a Query, SQL report or even a powershell to list all the primary users for your device.


    Friday, September 11, 2015 5:02 PM
  • What i want is a list of PC that have a filed in it to indicate who is using it the most (the primary user assigned by the User Device Affinity.

    and i want to be able to search/group that list.

    For example i want to see if User X uses multiple PCs, how many and what PC names are they.

    or group the list so it shows that User X have 4 PC's user Y have 3 and user Z have 6

    I guess Query under monitoring would work too. but what is the syntax for it ?

    Friday, September 11, 2015 5:10 PM
  • Go here: http://www.mssccmfaq.de/2012/07/24/uda-per-sql-abfragen/

    This is a SQL query that you can use. After with the report you can always export it in excel and play with it.

    https://social.technet.microsoft.com/Forums/en-US/4e49152e-0973-495b-b4fd-2401c935a9d0/query-listing-primary-user-and-the-primary-device?forum=configmanagergeneral
    Friday, September 11, 2015 5:26 PM
  • To add-on there is a canned report, named User device affinity associations per collection, that provides that information. If you really want to have a view in the console than you could use the ConsoleBuilder and do something like this: http://gosc.nl/blog/technology/sccm/configmgr-2012-customization-creating-user-device-affinity-overview/

    My Blog: http://www.petervanderwoude.nl/
    Follow me on twitter: pvanderwoude

    Friday, September 11, 2015 5:57 PM
  • In SCCM goto Monitoring > Queries right mouse click on Queries and click new query. Give a name to your query for example "Assinged Primary User on Deivce"
    Set the collection liniting or prompt for a specific collection.

    Click on Edit Query Statement > Click on Show Query Language and paste this query:

    SELECT SMS_R_System.name, SMS_R_User.UniqueUserName
      FROM SMS_R_System
      INNER JOIN SMS_UserMachineRelationship ON SMS_UserMachineRelationship.ResourceId = SMS_R_System.ResourceId
      JOIN SMS_R_User ON SMS_UserMachineRelationship.UniqueUserName = SMS_R_User.UniqueUserName
      WHERE
       SMS_UserMachineRelationship.Types = 1

    • Proposed as answer by ScriptWork Monday, September 24, 2018 7:38 PM
    Thursday, August 3, 2017 9:00 AM
  • Nice Query! Works perfectly!
    Thursday, August 9, 2018 2:36 PM
  • Hello, I have this query for obtain all machines that have primary users. My problem is in sql because appears one row for every primary users that machines have. How can obtain all the primary users in the same column separate for commas as appears in SCCM console. Thanks.

    SELECT distinct TOP (100) PERCENT SYS.Name0 AS [Computer Name], SYS.User_Domain0 AS [Computer Domain], SYS.AD_Site_Name0 AS [Active Directory Site] , dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 AS [Chassis Types],
    dbo.v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 AS [BIOS Asset Tag], dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial] , dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS [Manufacturer],
    dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Model], dbo.v_GS_PHYSICAL_MEMORY.Capacity0 [Memory in MB], dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [OS Version],
    dbo.v_GS_OPERATING_SYSTEM.OSArchitecture0 AS [OS Architecture], dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 AS [OS Build], dbo.v_GS_COMPUTER_SYSTEM.UserName0 AS [Last Logged User Domain],
    USR.Full_User_Name0 AS [User CN], USR.Mail0 AS [User Mail], USR.SID0, USR.Distinguished_Name0 AS [User DN], USR.Unique_User_Name0 AS [ Primary User Name], SYS.Last_Logon_Timestamp0 AS [Last Logon Time]
    FROM dbo.v_GS_OPERATING_SYSTEM INNER JOIN
    dbo.v_R_System AS SYS ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = SYS.ResourceID INNER JOIN
    dbo.v_GS_SYSTEM_ENCLOSURE ON SYS.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID INNER JOIN
    dbo.v_GS_COMPUTER_SYSTEM ON SYS.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
    dbo.v_GS_PHYSICAL_MEMORY ON SYS.ResourceID = dbo.v_GS_PHYSICAL_MEMORY.ResourceID LEFT OUTER JOIN
    dbo.v_UsersPrimaryMachines AS upm LEFT OUTER JOIN
    dbo.v_R_User AS USR ON upm.UserResourceID = USR.ResourceID ON SYS.ResourceID = upm.MachineID

    WHERE dbo.v_GS_OPERATING_SYSTEM.Caption0 NOT LIKE ‘%Server%’ and dbo.v_GS_PHYSICAL_MEMORY.Capacity0 > 4

    ORDER BY USR.Full_User_Name0


    Monday, October 28, 2019 6:08 PM
  • Hello, I have this query for obtain all machines that have primary users. My problem is in sql because appears one row for every primary users that machines has. How can obtain all the primary users in the same column separate for commas as appears in SCCM console. Thanks.

    SELECT distinct TOP (100) PERCENT SYS.Name0 AS [Computer Name], SYS.User_Domain0 AS [Computer Domain], SYS.AD_Site_Name0 AS [Active Directory Site] , dbo.v_GS_SYSTEM_ENCLOSURE.ChassisTypes0 AS [Chassis Types],
    dbo.v_GS_SYSTEM_ENCLOSURE.SMBIOSAssetTag0 AS [BIOS Asset Tag], dbo.v_GS_SYSTEM_ENCLOSURE.SerialNumber0 AS [Serial] , dbo.v_GS_SYSTEM_ENCLOSURE.Manufacturer0 AS [Manufacturer],
    dbo.v_GS_COMPUTER_SYSTEM.Model0 AS [Model], dbo.v_GS_PHYSICAL_MEMORY.Capacity0 [Memory in MB], dbo.v_GS_OPERATING_SYSTEM.Caption0 AS [OS Version],
    dbo.v_GS_OPERATING_SYSTEM.OSArchitecture0 AS [OS Architecture], dbo.v_GS_OPERATING_SYSTEM.BuildNumber0 AS [OS Build], dbo.v_GS_COMPUTER_SYSTEM.UserName0 AS [Last Logged User Domain],
    USR.Full_User_Name0 AS [User CN], USR.Mail0 AS [User Mail], USR.SID0, USR.Distinguished_Name0 AS [User DN], USR.Unique_User_Name0 AS [ Primary User Name], SYS.Last_Logon_Timestamp0 AS [Last Logon Time]
    FROM dbo.v_GS_OPERATING_SYSTEM INNER JOIN
    dbo.v_R_System AS SYS ON dbo.v_GS_OPERATING_SYSTEM.ResourceID = SYS.ResourceID INNER JOIN
    dbo.v_GS_SYSTEM_ENCLOSURE ON SYS.ResourceID = dbo.v_GS_SYSTEM_ENCLOSURE.ResourceID INNER JOIN
    dbo.v_GS_COMPUTER_SYSTEM ON SYS.ResourceID = dbo.v_GS_COMPUTER_SYSTEM.ResourceID INNER JOIN
    dbo.v_GS_PHYSICAL_MEMORY ON SYS.ResourceID = dbo.v_GS_PHYSICAL_MEMORY.ResourceID LEFT OUTER JOIN
    dbo.v_UsersPrimaryMachines AS upm LEFT OUTER JOIN
    dbo.v_R_User AS USR ON upm.UserResourceID = USR.ResourceID ON SYS.ResourceID = upm.MachineID

    WHERE dbo.v_GS_OPERATING_SYSTEM.Caption0 NOT LIKE ‘%Server%’ and dbo.v_GS_PHYSICAL_MEMORY.Capacity0 > 4

    ORDER BY USR.Full_User_Name0


    Monday, October 28, 2019 7:25 PM