Here is my powershell script to get primary user data from McAfee ePo database, then use Add-CMUserAffinityToDevice to import them into SCCM, and remove any primary user begins with “adm” or “!”.
This can be an additional source to enhance your SCCM device affinity data.
# Remove & Add Device Affinity
# Rui Qiu
# 20181113#Import SCCM Module
Import-Module “C:\Program Files (x86)\Microsoft Configuration Manager\AdminConsole\bin\ConfigurationManager.psd1”
Set-Location -path “$(Get-PSDrive -PSProvider CMSite):\” -verbose# Get Data from SCCM SQL Database
$sqlInstance=”SQLNAME”
$sqlDatabase=”CM_XXX”
$qry=@”
select fcm.Name, umr.UniqueUserName
from v_FullCollectionMembership_Valid fcm
left join v_UserMachineRelationship umr on fcm.Name = umr.MachineResourceName AND umr.RelationActive = 1
where fcm.CollectionID = ‘_your collectionid’
“@# Get Devices without Primary User Assigned
$tobefilled = @(Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qry )
$tobefilled = $tobefilled | ? { $_.UniqueUserName.length -eq “1” }# Get Data from ePo Database
$sqlInstance2=”ePoServerName”
$sqlDatabase2=”DatabaseName”
$qry2=@”
select Computername, UserName from dbo.EPOComputerProperties Where (UserName <> ‘N/A’) AND (UserName <> ”)
“@$epodata = @(Invoke-Sqlcmd -ServerInstance $sqlInstance2 -Database $sqlDatabase2 -Query $qry2 )
# Assign primary user data into SCCM
foreach ($device in $tobefilled.Name ) {
if ( $device -in $epodata.Computername ) {
$index = $epodata.Computername.IndexOf($device)
$username = @(($epodata[$($index)].UserName -split “,”).trim() -replace “^”, “yourdomainName\”)
$username | foreach-object { Add-CMUserAffinityToDevice -Username $_ -DeviceName $device}
}
}#Remove Devices with Primary User begins with “adm-” or “!”
$results =@(Invoke-Sqlcmd -ServerInstance $sqlInstance -Database $sqlDatabase -Query $qry )
$results = $results | ? { $_.UniqueUserName -match “.*corp\\(adm\-.+|\!.+)” }foreach ($device in $results) {
Remove-CMDeviceAffinityFromUser -DeviceName $($device.Name) -UserName $($device.UniqueUserName) -Force
}