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: 20th
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%'
--
Comment out Line 28 If you don't wish to limit on a Collection or
Distinguished_Name0
ORDER BY CollectionName
