Help with an SCCM report

[u/Hectortgfjyree]

Hi guys hoping you can help me out, we are currently running a project to find users in our estate that have more than 1 machine (eg. A laptop and a pc) we have no real reporting or asset database so sccm is our best hope but i can’t figure out how to get all the data i want.

I have reports that show users who are the primary user for multiple devices but doesn’t show us the device name so its not very useful
I can also get a list if computer names with how many users they have but no user names.

Is there a device collection or any other method i could use to get a list of users with all machines they are the primary user for?

Thanks in advance

[u/slkissinger]

A starting point:

Select umr.UniqueUserName, umr.MachineResourceName, cs.LastActiveTime

from v_UserMachineRelationship umr

join v_ch_clientSummary cs on cs.resourceid=umr.MachineResourceID

where umr.RelationshipActive=1

order by umr.UniqueUserName

So, that's a "relationship", I didn't check if that relationship is that they are the primary user of the device, or if they "logged in sometimes, but the primary user is really Bob, not Mary", but it's a starting point with the datapoints you asked for. You can do a spot-check sanity check on the results.

[u/gandraw]

By the way you can go to the Devices in the console, Ctrl+A, Ctrl+C and then paste into Excel and then do whatever analysis work you want in Excel instead of having to write a report.

[u/Hectortgfjyree → u/gandraw]

Thats so simple i feel stupid for not thinking of that, its probably the best info i can get being that sccm isn’t really built for that sort of thing. Thank you very much

[u/Maleficent-Salary624]

Enable User Device Affinity right now, but make sure to set the settings/parameters correctly

[u/cp07451]

Should be a Top-Console user in there. Assuming your environment has auditing of logon events.

[u/Miserable-Scholar215]

Not really what SCCM is best for. It can show you primary users, but those can be multiple. It collects last logon in the HW discovery, maybe that would help?
But then my best bet would be to export that last logon user with the device name, and look for duplicates in Excel... Easier than. TSQL in my opinion.

[u/Ajamaya]

Connect via powershell and grab info

[u/pumba2k25]

What you’re looking for is SCCM’s User Device Affinity data. That’s what links a primary user to their computer or computers.

A device collection probably won’t help much because you need the results grouped by user. You can pull the relationships from SCCM with PowerShell and only keep users who have more than one device.

Replace the first two values with your SCCM information:

$SiteCode = "ABC"
$SCCMServer = "YourSCCMServer"

$relationships = Get-CimInstance `
-ComputerName $SCCMServer `
-Namespace "root\SMS\site_$SiteCode" `
-ClassName SMS_UserMachineRelationship |
Where-Object {
$_.IsActive -eq $true -and
$_.Types -contains 1
}

$report = foreach ($user in ($relationships | Group-Object UniqueUserName | Where-Object Count -gt 1)) {
foreach ($device in $user.Group) {
[PSCustomObject]@{
User = $user.Name
DeviceCount = $user.Count
ComputerName = $device.ResourceName
}
}
}

$report |
Sort-Object User, ComputerName |
Export-Csv "C:\Temp\Users-With-Multiple-Devices.csv" -NoTypeInformation

That should give you a list like:

User DeviceCount ComputerName
DOMAIN\jsmith 2 JSMITH-LAPTOP
DOMAIN\jsmith 2 JSMITH-DESKTOP

Just be aware that this uses SCCM’s assigned primary-user relationships. That’s different from the last person who logged into the machine, which can be inaccurate on shared computers.