locked
Automatic unwanted client cleanup from sms database RRS feed

  • Question

  • Hi,

    We are using sms2003sp2 in our environment and we have a lot of clients in the collections that those of the clients does not really exixts in the network(but old records)

    is there any way to cleanup the clients that should be done immediately after the object has been removed from Active directory.

    otherwise could anyone have some scripts to do this

     


    with Thanks, Lokesh
    • Moved by TorstenMMVP Monday, October 18, 2010 12:22 PM moved to SMS 2003 subforum (From:Configuration Manager Setup/Deployment)
    Monday, October 18, 2010 12:18 PM

Answers

  • I agree with Eshwar, those Maintenance Tasks are what is needed in SMS.

    Also needed, is DNS Scavenging, and a way to clean up AD accounts.

    This is a great article I’ve seen re enabling DNS Scavenging… it will take some time but it’s the best way!
    http://blogs.technet.com/b/networking/archive/2008/03/19/don-t-be-afraid-of-dns-scavenging-just-be-patient.aspx

    And to help clean up AD… use something like OldCmp.
    http://www.joeware.net/freetools/tools/oldcmp/index.htm

    When both above used, and appropriate “Delete Aged” Tasks are enabled in SMS… you’ll “remove” the junk clients from SMS.

    And this tool can show you a comparision between AD and SMS...
    http://www.osdeployment.net/mssms/CompCount.zip
    (change SMSSERVERNAME in the HTA)

    Saturday, October 23, 2010 5:09 AM
  • Hi,

    I found a script that deletes the computer if it is not pingable,

    strSMSServer = " SERVER_NAME"
    strSMSSiteCode = "SMS_SITE_CODE"

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    If not objFSO.FileExists("C:\computer_lists\sms_deletion.txt") Then
     WScript.Quit(99)
    End If

    Set ObjStream = objFSO.OpenTextFile("C:\computer_lists\sms_deletion.txt",1)

    Set objLoc =  CreateObject("WbemScripting.SWbemLocator")
    Set objSMS= objLoc.ConnectServer(strSMSServer, "root\sms")
    Set Results = objSMS.ExecQuery _
        ("SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true")
    For each Loc in Results
        If Loc.ProviderForLocalSite = True Then
            Set objSMS = objLoc.ConnectServer(Loc.Machine, "root\sms\site_" & _
                Loc.SiteCode)
            strSMSSiteCode = Loc.SiteCode
        end If
    Next


    Do While Not ObjStream.AtEndOfStream
     strComputer = ObjStream.ReadLine
     
     ' strComputer shouldn't be blank, if it is, there is something wrong with the input file.
     If strComputer = "" Then
      WScript.Quit
     End If
      
     'get the resource ID of the computer
     bIsInSMS = False
     intResourceID = GetResourceID(strComputer,bIsInSMS)

     ' we check to see if the computer is "pingable".
     '  If it is, then perhaps we shouldn't be deleting it.
     If Not Reachable(strComputer) Then
      If bIsInSMS Then
       On Error Resume Next
       Set objResource = GetObject( "WinMgmts:\\" & strSMSServer & _
           "\root\SMS\site_" & strSMSSiteCode & _
           ":SMS_R_System.ResourceID=" & cstr(intResourceID))
       
       ' don't try and delete the computer if we couldn't get a handle to it.
          If Err.Number <> 0 Then
           wscript.echo "An SMS error occurred: " + Err.Description + " (" + cstr(Err.Number) + ") "& strComputer
          else
        objResource.Delete_
        wscript.echo "Deleted " & strComputer & "(" & intResourceID & ")"
        WScript.sleep 5000
       End If
       On Error GoTo 0
      Else
       wscript.echo "Not Found in SMS: " & strComputer
      End If
       Else
        wscript.echo "This machine is alive: " & strComputer
      End If

     On Error GoTo 0
    Loop

    WScript.Quit

    Function GetResourceID(strComputerName,bFoundInSms)
       
        bFoundInSms = False
        Set colResourceIDs = objSMS.ExecQuery _
            ("select ResourceID from SMS_R_System where Name = '" & _
                 strComputer & "'")
        for Each objResID in colResourceIDs
            GetResourceID = objResID.ResourceID
            bFoundInSms = True
        Next
    End Function
    Function Reachable(strComputer)
     '     On Error Resume Next
     
      Dim wmiQuery, objWMIService, objPing, objStatus
     
      wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"
     
      Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
      Set objPing = objWMIService.ExecQuery(wmiQuery)
     
      For Each objStatus In objPing
          If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
              Reachable = False 'if computer is unreacable, return false
          Else
              Reachable = True 'if computer is reachable, return true
          End If
      Next
     End Function

     

    * Tested in Lab Environment only *


    with Thanks, Lokesh
    • Marked as answer by lokeshbabu Wednesday, February 2, 2011 9:15 AM
    Monday, October 18, 2010 4:22 PM
  • However, these tasks will only delete if the system is not discovered again by another discovery method such as AD System Discovery or Network Discovery. So you really do need to do both DNS and AD scavenging, as mentioned by Shane below.
    Wally Mead
    • Marked as answer by lokeshbabu Wednesday, February 2, 2011 9:16 AM
    Saturday, October 30, 2010 11:33 AM

All replies

  • Hi,

    I found a script that deletes the computer if it is not pingable,

    strSMSServer = " SERVER_NAME"
    strSMSSiteCode = "SMS_SITE_CODE"

    Set objFSO = CreateObject("Scripting.FileSystemObject")

    If not objFSO.FileExists("C:\computer_lists\sms_deletion.txt") Then
     WScript.Quit(99)
    End If

    Set ObjStream = objFSO.OpenTextFile("C:\computer_lists\sms_deletion.txt",1)

    Set objLoc =  CreateObject("WbemScripting.SWbemLocator")
    Set objSMS= objLoc.ConnectServer(strSMSServer, "root\sms")
    Set Results = objSMS.ExecQuery _
        ("SELECT * From SMS_ProviderLocation WHERE ProviderForLocalSite = true")
    For each Loc in Results
        If Loc.ProviderForLocalSite = True Then
            Set objSMS = objLoc.ConnectServer(Loc.Machine, "root\sms\site_" & _
                Loc.SiteCode)
            strSMSSiteCode = Loc.SiteCode
        end If
    Next


    Do While Not ObjStream.AtEndOfStream
     strComputer = ObjStream.ReadLine
     
     ' strComputer shouldn't be blank, if it is, there is something wrong with the input file.
     If strComputer = "" Then
      WScript.Quit
     End If
      
     'get the resource ID of the computer
     bIsInSMS = False
     intResourceID = GetResourceID(strComputer,bIsInSMS)

     ' we check to see if the computer is "pingable".
     '  If it is, then perhaps we shouldn't be deleting it.
     If Not Reachable(strComputer) Then
      If bIsInSMS Then
       On Error Resume Next
       Set objResource = GetObject( "WinMgmts:\\" & strSMSServer & _
           "\root\SMS\site_" & strSMSSiteCode & _
           ":SMS_R_System.ResourceID=" & cstr(intResourceID))
       
       ' don't try and delete the computer if we couldn't get a handle to it.
          If Err.Number <> 0 Then
           wscript.echo "An SMS error occurred: " + Err.Description + " (" + cstr(Err.Number) + ") "& strComputer
          else
        objResource.Delete_
        wscript.echo "Deleted " & strComputer & "(" & intResourceID & ")"
        WScript.sleep 5000
       End If
       On Error GoTo 0
      Else
       wscript.echo "Not Found in SMS: " & strComputer
      End If
       Else
        wscript.echo "This machine is alive: " & strComputer
      End If

     On Error GoTo 0
    Loop

    WScript.Quit

    Function GetResourceID(strComputerName,bFoundInSms)
       
        bFoundInSms = False
        Set colResourceIDs = objSMS.ExecQuery _
            ("select ResourceID from SMS_R_System where Name = '" & _
                 strComputer & "'")
        for Each objResID in colResourceIDs
            GetResourceID = objResID.ResourceID
            bFoundInSms = True
        Next
    End Function
    Function Reachable(strComputer)
     '     On Error Resume Next
     
      Dim wmiQuery, objWMIService, objPing, objStatus
     
      wmiQuery = "Select * From Win32_PingStatus Where Address = '" & strComputer & "'"
     
      Set objWMIService = GetObject("winmgmts:\\.\root\cimv2")
      Set objPing = objWMIService.ExecQuery(wmiQuery)
     
      For Each objStatus In objPing
          If IsNull(objStatus.StatusCode) Or objStatus.Statuscode<>0 Then
              Reachable = False 'if computer is unreacable, return false
          Else
              Reachable = True 'if computer is reachable, return true
          End If
      Next
     End Function

     

    * Tested in Lab Environment only *


    with Thanks, Lokesh
    • Marked as answer by lokeshbabu Wednesday, February 2, 2011 9:15 AM
    Monday, October 18, 2010 4:22 PM
  • To delete the old records ,you can use the built-in maintenance task for this. Under site settings, you will see Site Maintenance Tasks and you will see "Delete Inactive Client Discovery Data" and "Delete Aged Discovery Data".

     I think these are specificly what you are trying to do. These tasks are not enabled by default and are set to 90 days when you first enable them. You will have to set this to 30 days or more.

    When enabling this task, you should configure the schedule to run at an interval greater than the Heartbeat Discovery schedule. This allows active clients to send a Heartbeat Discovery record to mark their client record as active so that this task does not delete them.


    //Eswar Koneti @ http://eskonr.wordpress.com/

    Thursday, October 21, 2010 8:07 AM
  • I agree with Eshwar, those Maintenance Tasks are what is needed in SMS.

    Also needed, is DNS Scavenging, and a way to clean up AD accounts.

    This is a great article I’ve seen re enabling DNS Scavenging… it will take some time but it’s the best way!
    http://blogs.technet.com/b/networking/archive/2008/03/19/don-t-be-afraid-of-dns-scavenging-just-be-patient.aspx

    And to help clean up AD… use something like OldCmp.
    http://www.joeware.net/freetools/tools/oldcmp/index.htm

    When both above used, and appropriate “Delete Aged” Tasks are enabled in SMS… you’ll “remove” the junk clients from SMS.

    And this tool can show you a comparision between AD and SMS...
    http://www.osdeployment.net/mssms/CompCount.zip
    (change SMSSERVERNAME in the HTA)

    Saturday, October 23, 2010 5:09 AM
  • Thanks shane
    with Thanks, Lokesh
    Monday, October 25, 2010 11:45 AM
  • However, these tasks will only delete if the system is not discovered again by another discovery method such as AD System Discovery or Network Discovery. So you really do need to do both DNS and AD scavenging, as mentioned by Shane below.
    Wally Mead
    • Marked as answer by lokeshbabu Wednesday, February 2, 2011 9:16 AM
    Saturday, October 30, 2010 11:33 AM