Share via

Query for user affinity

  • Article

I had a customer ask for a query that would show which users were primary on clients based on user device affinity data.  There are a few views that are related to UDA data (https://technet.microsoft.com/en-us/library/dn581963.aspx) and you can join these on a few different columns, depending on the view.

The v_UserMachineRelationship view shows users and their primary devices and  the v_UserMachineIntelligence view shows more detailed information about the affinity, like number of logons, time spend for each session, etc.  So, the following query is a result of that work.  I didn't care about local account logons so I'm using the WHERE to filter those out.

SELECT  v_GS_COMPUTER_SYSTEM.Name0 AS name, v_UserMachineRelationship.UniqueUserName, v_UserMachineIntelligence.ConsoleMinutes, v_UserMachineIntelligence.LastLoginTime, v_UserMachineIntelligence.NumberOfLogins

FROM v_GS_COMPUTER_SYSTEM INNER JOIN v_UserMachineRelationship ON v_GS_COMPUTER_SYSTEM.ResourceID = v_UserMachineRelationship.MachineResourceID INNER JOIN v_UserMachineIntelligence ON v_UserMachineRelationship.MachineResourceID = v_UserMachineIntelligence.MachineResourceID

WHERE (v_UserMachineRelationship.UniqueUserName LIKE 'bennett\%')

order by name, consoleminutes desc