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 }