Thursday, August 18, 2011

Of duplicate computer accounts in SCCM

Recently wrote a little script that deletes duplicate computer accounts in SCCM. This is because I have included the addition of computers from AD





#Region Description
#
# Name       : remove-DuplicateComputers.ps1
#
# Version    : 1.0
# Web        :
# Date       : 21-12-2010  
# Description: Find duplicate computer objects to SCCM and delete
#             
#EndRegion
#Region Functions
#EndRegion
[array]$ArrayComps=$null
$ConnStr="Provider=MSDASQL;DSN=SMS_TB1;"
$sSQL="select T.Name0, T.CountName
from (select dbo.v_R_System.Name0, count(*) as CountName
from dbo.v_R_System
Group By dbo.v_R_System.Name0 ) as T
where T.CountName > 1
ORDER BY T.Name0"
$adoSQL= New-Object -ComObject  ADODB.Connection
$rsSheet= New-Object -ComObject ADODB.Recordset
$adoSQL.ConnectionString=$ConnStr
$adoSQL.Open()
$rsSheet.Open($sSQL,$adoSQL)
if(!$rsSheet.EOF)
{
 $meter=0
 while (!$rsSheet.EOF){
  $ArrayComps+=@{Name=$rsSheet.Fields.Item("Name0").Value;Count=$rsSheet.Fields.Item("CountName").Value; BadID="";LastDate=$null;}
  $rsSheet.MoveNext()
 }
}
if (!$adoSQL)
{
 $adoSQL.Close()
}
$adoSQL=$null
$meter=0
$meter2=0
foreach ($Comp in $ArrayComps)
{
 Get-WmiObject -Namespace "Root\SMS\site_TB1" -Query  ("select * from SMS_R_System where Name = '"+$Comp.Name+"' ") | %{
  if (!$_.Client -and !$_.Active)
  {
   $Comp.BadID=$_.ResourceId
   #$_.Delete()
   Write-Host "№"($meter++)" Computer " $Comp.Name "with ID="$_.ResourceId "Deleted!!!" -ForegroundColor Red
  }
  else
  {
   if ($Comp.LastDate)
   {
    if ($_.SMSUUIDChangeDate -ge $Comp.LastDate)
    {
     Write-Host "---№"($meter2++)" Computer " $Comp.Name "with ID="$Comp.BadID "Deleted!!!" -ForegroundColor Red
    }
    else
    {
     Write-Host "---№"($meter2++)" Computer " $Comp.Name "with ID="$_.ResourceId "Deleted!!!" -ForegroundColor Red
    }
   }
   else
   {
    $Comp.LastDate=[double](($_.SMSUUIDChangeDate).split("."))[0]
    $Comp.BadID=$_.ResourceId
   }
  }
   
 }
}
#$ArrayComps | ?{!$_.BadID}