SQL Query : User Device Affinity Report

The first query writes to a temporary dataset then the second query is used to create a pivot table.  The only limitation is you have to specify the number of ‘user’ columns you want to display per machine.  The following example displays 5 users per device.  The Sample Output is obscured for client confidentiality. SELECT UMS.MachineResourceName, UMS.UniqueUserName INTO #TempTable FROM v_UserMachineRelationship UMS join v_UserMachineRelation UMR on UMS.RelationshipResourceID = UMR.RelationshipResourceID join v_R_System VRS on UMR.MachineResourceID = VRS.ResourceID WHERE VRS.Operating_System_Name_and0 like ‘%Workstation%’ SELECT * FROM (SELECT ‘User’+CONVERT(VARCHAR,ROW_NUMBER() OVER (PARTITION BY [MachineResourceName] ORDER…

Read More

Useful Links : User Device Affinity

User Device Affinity in Microsoft System Center 2012 Configuration Manager is a method of associating a user with one or more specified devices. User device affinity can eliminate the need to know the names of a user’s devices in order to deploy an application to that user. Instead of deploying the application to all of the user’s devices, you deploy the application to the user. Then, user device affinity automatically ensures that the application install on all devices that are associated with that user. Useful Links http://gosc.nl/blog/technology/sccm/deepdive-user-define-affinity-and-predeploy-applications-using-configmgr-2012-r2/ https://technet.microsoft.com/en-us/gg699365.aspx https://technet.microsoft.com/en-us/hh846243 http://blogs.technet.com/b/server-cloud/archive/2011/10/19/user-centric-app-deployment-part-2-user-device-affinity.aspx

Read More

SQL Query : User Machine Relationship with O/S (not server)

Really useful to report on User Device Affinity SELECT UMR.MachineResourceName ,UMR.MachineResourceID ,UMR.RelationActive ,UMR.CreationTime ,UMR.UniqueUserName ,VRS.operatingSystem0 FROM v_UserMachineRelationship UMR join v_R_System VRS on UMR.MachineResourceID = VRS.ResourceID WHERE VRS.operatingSystem0 not like ‘%server%’ order by UMR.MachineResourceName Sample OUTPUT

Read More