SCCM Duplicate Machine Cleanup

I got tired of duplicate systems appearing in SCCM caused by computers being reimaged while using the same computer name. To rid myself of this issue, I wrote the script below.

It queries the SCCM SQL database for a list of machines where the SCCM client installation was attempted with a return code of 120. This error code indicates the system is already present and active in SCCM, thereby indicating this system is the old one.

The script was designed to work with Orchestrator, SMA, Azure Automation, a scheduled task, or manual execution. Exit 1 is there if it is used with Orchestrator. Commented out line 68 is there for you to use and test this script. I highly recommend uncommenting that line and then commenting out line 69 to verify first before the script actually deletes systems from SCCM. 

You can download this script from my GitHub Repository.

 <#  
      .SYNOPSIS  
           SCCM Duplicate Cleanup  
        
      .DESCRIPTION  
           This script will query for a list of machines with error 120 when trying to install the SCCM client. This error indicates the system is a duplicate.   
        
      .PARAMETER SCCMModule  
           UNC path including file name of the configuration manager module  
        
      .PARAMETER SCCMServer  
           FQDN of SCCM Server  
        
      .PARAMETER SCCMSiteDescription  
           Description of the SCCM Server  
        
      .PARAMETER SiteCode  
           Three letter SCCM Site Code  
        
      .PARAMETER Collection  
           Name of the collection to query  
        
      .PARAMETER SQLServer  
           Name of the SQL server  
        
      .PARAMETER SQLDatabase  
           A description of the SQLDatabase parameter  
        
      .PARAMETER SQLInstance  
           Name of the SQL Database  
        
      .PARAMETER SCCMFQDN  
           Fully Qualified Domain Name of the SCCM server  
        
      .NOTES  
           ===========================================================================  
           Created with:      SAPIEN Technologies, Inc., PowerShell Studio 2017 v5.4.142  
           Created on:       10/3/2019 12:04 PM  
           Created by:       Mick Pletcher  
           Filename:          SCCMDuplicateCleanup.ps1  
           ===========================================================================  
 #>  
 [CmdletBinding()]  
 param  
 (  
      [ValidateNotNullOrEmpty()]  
      [string]$SCCMModule,  
      [ValidateNotNullOrEmpty()]  
      [string]$SCCMServer,  
      [ValidateNotNullOrEmpty()]  
      [string]$SCCMSiteDescription,  
      [ValidateNotNullOrEmpty()]  
      [string]$SiteCode,  
      [ValidateNotNullOrEmpty()]  
      [string]$Collection,  
      [ValidateNotNullOrEmpty()]  
      [string]$SQLServer,  
      [ValidateNotNullOrEmpty()]  
      [string]$SQLDatabase  
 )  
   
 $List = Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query ('SELECT Name, MachineID, CP_LastInstallationError FROM' + [char]32 + 'dbo.' + ((Invoke-Sqlcmd -ServerInstance $SQLServer -Database $SQLDatabase -Query ('Select ResultTableName FROM dbo.Collections WHERE CollectionName =' + [char]32 + [char]39 + $Collection + [char]39)).ResultTableName) + [char]32 + 'WHERE ClientVersion IS NULL AND CP_LastInstallationError = 120 Order By MachineID')  
 If ($List -ne '') {  
      Import-Module -Name $SCCMModule -Force  
      New-PSDrive -Name $SiteCode -PSProvider 'AdminUI.PS.Provider\CMSite' -Root $SCCMServer -Description $SCCMSiteDescription | Out-Null  
      Set-Location -Path ($SiteCode + ':')  
      #Test with output to screen before enabling the other line that also deletes each item  
      $List | ForEach-Object { (Get-CMDevice -ResourceId $_.MachineID -Fast).Name }  
      #$List | ForEach-Object { Get-CMDevice -ResourceId $_.MachineID -Fast | Remove-CMDevice -Confirm:$false -Force }  
      Remove-PSDrive -Name $SiteCode -Force  
      Write-Output ($List.Name | Sort-Object)  
 } else {  
      Exit 1  
 }