SQL Query: Find user’s primary device by deparment AD attribute.

We are getting ready to rollout a Windows 7 desktop deployment in order to “clean up” the software license reports.  Part of this project entails creating static collections for the various departments within the company which will have an OSD task sequence deployed to.  In order to take advantage of User Device Affinity in SCCM 2012, I requested an SAP report of all users within the affected departments.  The report I received contained fields within Active Directory such as the “Department” attribute.  I then created the following SQL query to retrieve all users within a deparment and display their primary device.

Use [CM_BSG]
select upm.UserResourceID, upm.MachineID, vru.Name0 as [User Name],
vrs.Name0 as [Machine Name], vru.department0 as [Department]
from v_UsersPrimaryMachines  upm left join v_R_User AS vru
on upm.UserResourceID = vru.ResourceID left join v_R_System vrs
on upm.MachineID = vrs.ResourceID where vru.department0 like '[VALUE OF AD DEPARTMENT FIELD HERE]'

If you are looking for User Device Affinity for a specific username, you can easily modify the query as the following:

Use [CM_BSG]
select upm.UserResourceID, upm.MachineID, vru.Name0 as [User Name],
vrs.Name0 as [Machine Name], vru.department0 as [Department]
from v_UsersPrimaryMachines  upm left join v_R_User AS vru
on upm.UserResourceID = vru.ResourceID left join v_R_System vrs
on upm.MachineID = vrs.ResourceID where vru.name0 like '%[USERNAME HERE]%'

Or all devices for all users in the database:

Use [CM_BSG]
select upm.UserResourceID, upm.MachineID, vru.Name0 as [User Name], 
vrs.Name0 as [Machine Name], vru.department0 as [Department] 
from v_UsersPrimaryMachines upm left join v_R_User AS vru 
on upm.UserResourceID = vru.ResourceID left join v_R_System vrs 
on upm.MachineID = vrs.ResourceID where vru.name0 IS NOT NULL


5 Comments

  • Mike

    I’m looking for something exactly like this, however I can’t seem to get this work with in CM 2012, I always get a syntax error when trying input this query and save. Any suggestions???

    • Hi Mike, I apologize for getting back to you just now. I’ve been away on business and then right after vacation. 🙂 You might be getting a syntax error because the quote marks near the end of the query sometimes does not transfer well when copy/ paste. Try to type in the quote marks manually after pasting the code into your query. Also, remember to replace the “Use [CM_BSG]” with your database name such as “Use [MyDB]”.

      • Ray

        Hi, I am not Mike, but I have same syntax error. I replace “Use [CM_BSG]” with the correct database name in our envrionment, but still got same error ? thanks!

  • The query works, but there seems to be a many to one and one to many relationship between computers and users. This violates a strict interpretation of the term ‘primary’. A user’s ‘primary’ system should be the one system he spends the most time using (I get that he may be the ‘primary’ user for many machines, but there is only one that he uses the most. Conversely, a computer should have only one ‘primary’ user.

    When I use the SCCM console, select Devices, then select a specific device, in the details pane there is a section called ‘Related Objects’ which contains a link that states ‘Primary User’. I have NEVER seen more than one user listed when I click on this link. I would like to know what query is executed when I click on that link, because THAT’s the information I want to retrieve for ALL my devices…the bonafide “primary user”.

    Anyone know what that query might be?

  • Danny B

    Hey All, I need some help. I am try to run a report for our upcoming Windows 10 upgrade. I am using the SCCM Built-In Report “Operating System requirement status for computers in a specific collection” under the Upgrade Assesment folder. This will show me what computers are eligable to upgrade to Windows 10.

    They have asked me to add 2 columns to the report that will show the User Name of the person using the computer and what department they fall under. How can I add this to the report results? I am not that good at SQL. Thank you

Leave a Reply to rcheing Cancel reply