Cleaning COMPUTERS not in Active Directory

[robinb]

Here's a little VBSCRIPT that verify each Computer in LanSweeper DB against its presence in Active Directory. If the computer is not in AD, it is remove from Lansweeper DB.
This Script must be run under an account with the correct rights on the DB. There is no error trapping nor validation in the script.
Here's the code:

Dim objCNSQL
Dim strSQLDELETE
Dim strSQLQUERY
Dim objRSSQL
Dim SQLName
Const ADS_SCOPE_SUBTREE = 2
strSQLQUERY="select * from tblcomputers" 

' **************** AD Connection ****************************
'
Set objCNAD = CreateObject("ADODB.Connection")
Set objCommand =   CreateObject("ADODB.Command")
objCNAD.Provider = "ADsDSOObject"
objCNAD.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objCNAD
objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE 

' **************** LAnsweeper connection ****************************
' * Replace SERVERNAME by your SQL Server Instance
'
Set objCNSQL=CreateObject("ADODB.Connection")
objCNSQL.Open "Provider=SQLOLEDB.1;Persist Security Info=False;Integrated Security=SSPI;Server=SERVERNAME\sqlexpress;Database=Lansweeper32"


' **************** REading Lansweeper ****************************
'
set objRSSQL=objCNSQL.execute(strSQLQUERY)


' ******************************************************************
' *     For each record in tblcomputers
' *       If computername exist in AD, do noting
' *       Else, delete the record in the DB
' *
' *  REplace COMPANY, COM by your DOMAIN
'
objRSSQL.MoveFirst
Do Until objRSSQL.EOF
SQLName=objRSSQL("Computername") 
    
    objCommand.CommandText = "SELECT Name FROM 'LDAP://dc=COMPANY,dc=COM' WHERE objectCategory='computer' AND Name='" & SQLName & "'"
    Set objRSAD = objCommand.Execute
    On Error Resume Next
     objRSAD.MoveFirst
    if(err.number<>0) then
      Wscript.Echo "DELETING: " & SQLName
      strSQLDELETE="Delete from tblcomputers where ComputerName='" & SQLName & "'"
      objCNSQL.execute(strSQLDELETE)
    else
      Wscript.Echo "Keep: " & SQLName
    end if

    objRSSQL.MoveNext
Loop

objCNSQL.Close

[Hemoco]

DGoodwin wrote:
I just looked in a couple of my tables where Computername = what we removed via the tool, and I do not see any reference to the removed in the following tables (tbl_software, tbl_OperatingSystem, tbl_ComputerSystem, tbl_Users). It look as if it does remove all data. Maybe LS can clarify if removing a machine from tbl_computers will strip out that machine from the rest of the tables?

Yes, when you delete a computer from tblcomputers it is cascaded to the other tables.

[pdumbleton]

Nice script thank you. But will this not leave orphaned entries in all the other tables.

[robinb]

No matter, you must just use an account with R/W access to the DB

[lhawk]

Thanks for the script. We have our LS database on a seperate server. Should I run this script from the application server or from the DB server? Or does it even matter?

Thanks again.

[robinb]

I mean Execute the script from a command line like this one:

CSCRIPT cleanlansweeper.vbs

[Cobra7]

I have very little experence with VBS programming myself, I'm just someone who understands scripting and can understand and modify most code I come across.

So what do you mean by "call the script"? As in just rename it?

[robinb]

Call the script using CSCRIPT.EXE in a command line to avoid popup messages. You can aslo pipe the result in a text file using ">" to keep a trace.

[taeratrin]

If you don't mind, I could make this an executable and provide the error-checking. I could also probably reoslve Cobra7's problem in the process. Let me know if you want me to do this.

[Hemoco]

I've made this thread a sticky

[Cobra7]

Thanks a ton, this helped a lot.

Only 1 request: I had to hit OK about 1800 times, any chance we could get an option at the start for an auto confirm and then just get a count at the end with how many were deleted? If you don't have the time I understand 🙂

This is exactly what I was looking for, so thanks.