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

2 Replies

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

2 thoughts on “SQL Query: Find user’s primary device by deparment AD attribute.”

  1. 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???

    Reply ↓
    1. rcheing Post author

      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]”.

      Reply ↓

Leave a reply to Mike Cancel reply