SCCM - How query for Primary User?

[VanGoghJoe1]

Hi,

I realize this is really more of an SCCM question, but having trouble deciphering the SCCM docs, so hoping someone here can help.

We're using the Service Graph Connector for SCCM with a client.  Out of the box, it populates the Assigned To field on the CI from the TopConsolerUser from SCCM.

However, this client wants to pull from the SCCM "Primary User" instead and we're having trouble finding a query for this in the SCCM documentation.  Does anyone know of one?

Also, if there can be more than one Primary User per device, does anyone know of some fields in SCCM that we could use to choose which Primary User to use?   Eg, is there a field like Last Login Time for a given user for a given device?

[Itallo Brandão]

Hi VanGoghJoe1,

This is a great requirement. Moving from TopConsoleUser to Primary User (User Device Affinity) is definitely the right move for accurate asset management, as TopConsoleUser can be volatile.

To answer your questions:

1. The Query for Primary User In SCCM, the "Primary User" relationship is stored in the view v_UserMachineRelation. You need to join this with the System and User views to get readable names.

Here is the base SQL query you can use in your Service Graph Connector data source:

SQL

SELECT
    sys.Netbios_Name0 AS ComputerName,
    sys.ResourceID,
    usr.User_Name0 AS UserName,
    usr.Windows_NT_Domain0 AS Domain,
    rel.SourceID, 
    rel.CreationTime
FROM
    v_UserMachineRelation rel
    JOIN v_R_System sys ON rel.MachineResourceID = sys.ResourceID
    JOIN v_R_User usr ON rel.UniqueUserName = usr.Unique_User_Name0

2. Handling Multiple Primary Users Yes, a device can have multiple Primary Users in SCCM (e.g., one assigned by an Admin manually, and one calculated by usage statistics).

To handle the "which one to pick" scenario, you should look at the SourceID column in v_UserMachineRelation.

• SourceID = 2: Administrator defined (Manual). This usually has the highest priority.

• SourceID = 3: User defined.

• SourceID = 4: Usage-based (Automatically calculated by SCCM thresholds).

The Strategy: You likely want to prioritize a Manually Assigned user over an Automatically Detected user. Regarding "Last Login Time", querying login data (v_GS_SYSTEM_CONSOLE_USAGE) is resource-heavy and complex to join here. Instead, it is better to use ROW_NUMBER() to pick the "Best" primary user based on Source type.

Here is a robust query that picks only one Primary User per device, prioritizing Manual assignment first, then the most recently created affinity:

SQL

WITH RankedUsers AS (
    SELECT
        sys.Netbios_Name0,
        usr.User_Name0,
        rel.SourceID,
        ROW_NUMBER() OVER (
            PARTITION BY rel.MachineResourceID 
            ORDER BY 
                CASE WHEN rel.SourceID = 2 THEN 1 ELSE 2 END ASC, -- Prioritize Admin(2) over Usage(4)
                rel.CreationTime DESC -- If tie, take the newest relationship
        ) AS RowNum
    FROM
        v_UserMachineRelation rel
        JOIN v_R_System sys ON rel.MachineResourceID = sys.ResourceID
        JOIN v_R_User usr ON rel.UniqueUserName = usr.Unique_User_Name0
)
SELECT * FROM RankedUsers WHERE RowNum = 1

Use this logic in your Data Source SQL, and you will get a clean, single user per device to map to assigned_to.

Hope this helps!

If this response helps you achieve your requirement, please mark it as Accepted Solution.
This helps the community grow and assists others in finding valid answers faster.

Best regards,
Brandão.

[VanGoghJoe1]

I'll have to wait till I can test this in day or two before marking it as Accepted, but thank you very much for such a detailed and prompt reply!!

[Itallo Brandão]

Hi VanGoghJoe1,

You are very welcome!

It is absolutely the right call to test thoroughly before finalizing the configuration.

If you encounter any syntax errors or if the data doesn't look quite right during your validation, feel free to drop a comment here.

I will keep an eye on this thread. Good luck with the testing!

Best regards,
Brandão