CODEWITHSUNDEEP
powershellsccmwql
DarkLite1
DarkLite1

Reputation: 14735

Get primary user of device through SCCM query in PowerShell

We're scanning the active directory to find all computer objects. Then for each machine we're trying to find the user that used that machine the most, i.e. the primary user.

To find this data we found 2 working queries towards SCCM:

$Query = "
SELECT
    SMS_R_User.FullUserName, SMS_R_User.UniqueUserName, SMS_R_System.Name
FROM
    SMS_R_System
INNER JOIN
    SMS_G_System_SYSTEM_CONSOLE_USAGE
    ON
    SMS_G_System_SYSTEM_CONSOLE_USAGE.ResourceId = SMS_R_System.ResourceId
INNER JOIN
    SMS_R_User
    ON
    SMS_G_System_SYSTEM_CONSOLE_USAGE.TopConsoleUser = SMS_R_User.UniqueUserName
"

$Query = "
    SELECT
        SMS_R_System.name, SMS_R_User.UniqueUserName
    FROM
        SMS_R_System
    INNER JOIN
        SMS_UserMachineRelationship
        ON
        SMS_UserMachineRelationship.ResourceId = SMS_R_System.ResourceId
    JOIN
        SMS_R_User
        ON
        SMS_UserMachineRelationship.UniqueUserName = SMS_R_User.UniqueUserName
      WHERE
       SMS_UserMachineRelationship.Types = 1
    "

We execute these with:

    $WmiParams = @{
        Namespace    = "root\SMS\site_EU1"
        ComputerName = $SCCMServer
        Query        = $Query
    }

    $Result = Get-WmiObject @WmiParams

We're not much of SCCM experts but both queries give different results and duplicate computer objects $Result.SMS_R_System.Name | Group-Object | where Count -ge 2.

What is the correct query to have one machine name connected to one primary user with SamAccountName and FullUserName?

Thank you for your help.

Upvotes: 0

Views: 18737

Answers (2)

Skatterbrainz
Skatterbrainz

Reputation: 1087

Another option could be to use the PowerShell module "dbatools"...

Invoke-DbaQuery -SqlInstance "myserver.contoso.local" -Database "CM_P01" -Query "select UniqueUserName,MachineResource Name from v_UserMachineRelationship"

Upvotes: 0

Mudit Bahedia
Mudit Bahedia

Reputation: 246

SMS_UserMachineRelationship should contain Primary device for users. It's possible that 1 device can be Primary device of many users, and also 1 user can have more than 1 Primary device. It depends on the criteria configured in Client Settings to identify Primary device. By default if some users logs into a device for 48 hours in a month then it's considered as Primary device of that user. This criteria can be changed. You may read more about User Device Affinity at https://learn.microsoft.com/en-us/sccm/apps/deploy-use/link-users-and-devices-with-user-device-affinity

Upvotes: 1

Related Questions