Powershell Script to Find Primary User from McAFee ePo and Feed Data into SCCM Device Affinity

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
}

Leave a Comment