Synchronize SMS with AD

[LMichel]

Dear all,

I've a similar issue to the one described in thread22-1351081.

I'd like to compare data from AD and SMS to remove from the SMS database the records of clients that have been removed from the active directory. I'd like to do this on a daily basis.

I can't reduce values for the "delete aged discovery data" maintenance task because some computers are sometimes offline for a very long time. These computers should stay in the SMS database.

To summarize, when the computer is removed from A.D., it should also be removed from SMS within 24 hours. How to achieve that?

Regards

 

[Jerz]

I solved this problem with a script(s) at my last company. The first script determined which machines got deleted from AD, and as it deleted them, it created a list TXT file of the names. The second script read the TXT file, and created a query you could base a collection on. We ran it weekly, so every Monday I would delete computers, and update the query on the collection, then do a 'delete special' on the collection after it was populated.

You could just write a script that read members of the 'All Computers' collection, and checked each one for existance in AD. Something like this:

dom = "ADName"
Set colQueryCollectionResults=objSWbemServices.ExecQuery _
    ("SELECT * FROM SMS_FullCollectionMembership WHERE _
CollectionID='SMS00001'" )
For Each objResult In colQueryCollectionResults
    strComputer = ucase(objResult.Name)
    set otrans=createobject("nametranslate")
    with otrans
        .init 3,""
        .set 3,dom & "\" & strComputer & "$"
    end with
    sdn=otrans.get(1)
    If err.number <> 0 
'I don't know the VBS for 'Delete Special', but it goes here - or you could dump to TXT file from here, or create the query all in 1 script and update your collection.  Maybe redefine the collection's query in the script?
    End If         
next

Format a collection query from a TXT file:

path = ".\"
strQuery = Path & "Query.txt"
txtfile = Path & "source.txt"
On Error Resume Next
Const ForReading = 1
Const ForWriting = 2
'***************************************************
'*          Query file creation and header         * 
'***************************************************
Set objFSO1 = CreateObject ("Scripting.FileSystemObject")
Set objFileRpt = objFSO1.CreateTextFile (strQuery, True)
QueryTxt = "select SMS_R_System.ResourceID,SMS_R_System.ResourceType,SMS_R_System.Name,SMS_R_System.SMSUniqueIdentifier,_
SMS_R_System.ResourceDomainORWorkgroup,SMS_R_System.Client from SMS_R_System where Name in ( "
'***************************************************
'*  Read Computer Name from each line in workfile  * 
'***************************************************
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile(txtfile, ForReading)
First = "Yes"
Do While objTextFile.AtEndOfStream <> True
    Computer = objTextFile.ReadLine
    If First = "Yes" then
      QueryTxt = QueryTxt & Chr(34) & computer & Chr(34)  
      First = "No"
    Else
       QueryTxt = QueryTxt & "," & Chr(34) & computer & Chr(34) 
    End If
Loop
QueryTxt = QueryTxt & " )"
objFileRpt.writeline Querytxt