T-SQL Query SCCM Users and their Primary Device (Including Multiple Devices)

Summary
In your environment you may have Users that have multiple machines E.G. some Users with Both a Laptop and a Desktop.

This SQL Query will display Users and their Primary computers (1 Row per User, which is achieved using the SQL Function Stuff).

The 3 Defined Columns are:
UserName CollectionName ComputerNames
However CollectionName is only useful if a Collection is being defined in your Where Clause.
Check Lines 17 & 28.

/*     .NOTES

===========================================================================

        Created on:         20^th October 2017

        Created by:         Matthew Swanston

        Contact             http://thebeardysccmguy.blogspot.com

        Filename:           DetectionMethods

===========================================================================

       .DESCRIPTION

              Provides a User and Machine information including Multiple Primary Devices

*/

if object_id('tempdb..#consoleuser') is not null

       drop table #consoleuser;

WITH CTE_ConsoleUser as (

select TopConsoleUser00

,Name0

from SYSTEM_CONSOLE_USAGE_DATA

join v_R_System on v_R_System.ResourceID = SYSTEM_CONSOLE_USAGE_DATA.MachineID)

 SELECT T1.TopConsoleUser00,     

 STUFF((SELECT Distinct ', ' + T2.Name0

 FROM   CTE_ConsoleUser as T2             

 WHERE  T1.TopConsoleUser00 = T2.TopConsoleUser00           

 FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,'') AS ComputerNames

 INTO #consoleuser

 FROM CTE_ConsoleUser as T1

 where TopConsoleUser00 like 'PROD\%'

 --Comment out Line 17 if you only have 1 Domain, or you don't need to filter the Domains e.g. DEV, Prod etc.

 GROUP BY TopConsoleUser00

select SMSID AS UserName

,CollectionName

,ComputerNames

from CollectionMembers

JOIN Collections on Collections.SiteID = CollectionMembers.SiteID

JOIN v_R_User on v_R_User.Unique_User_Name0 = CollectionMembers.SMSID

LEFT JOIN #consoleuser on #consoleuser.TopConsoleUser00 = SMSID

--WHERE (CollectionMembers.SiteID = 'LAB001D7' and Distinguished_Name0 like '%OU=Europe Middle East Africa%' and computernames like '%,%')

-- Comment out Line 28 If you don't wish to limit on a Collection or Distinguished_Name0 or Multiple Computers Only (Filtered using a Comma)

ORDER BY CollectionName