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

[AdmJLovejoy]

Use caution with this script if you have more than one domain, or have workgroup mode clients in Lansweeper. This script, as written, will remove all computers from Lansweeper except for those in the target domain, hard coded in the script.

[jreimer1]

I am getting the same error as above with the script error in line 43

objCommand.CommandText = "SELECT Name FROM 'LDAP://dc=services,dc=local' WHERE objectCategory='computer' AND Name='" & SQLName & "'"
set objRSAD = objCommand.Execute

My domian is Services.local

Do i have that setup correctly?

[Hemoco]

This will come standard in version 4.0 (for users and computers)

[tp-tec_CE]

Hello.

Is it possible to do something similar for Useraccounts?

We have a lot of old Useraccounts stored in the database.
(users that are no longer working for our company)
These accounts do not exist any more in our AD.

Thank you!

Greetings,
Mike

[Cobra7]

It would be something like:

SELECT Name FROM 'LDAP://dc=Microsoft,dc=com' WHERE

or

SELECT Name FROM 'LDAP://dc=Cox,dc=net' WHERE

or

SELECT Name FROM 'LDAP://dc=PETA,dc=org' WHERE

[rmorgan]

Nope, I can't seam to get this to work.

I am getting the following Script Error:

Script: C:\LSShare\CleanLansweeper.vbs
Line: 43
Char: 5
Error: Table does not exist
Code: 80040E37
Source: Provider

I think my problems are the following:

' ******************************************************************
' * 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

I am not sure what is ment by "Replace COMPANY, COM by your DOMAIN"

Would it be:
"SELECT Name FROM 'LDAP://dc=MYCompanyName,dc=MYDomainName' WHERE

Help!!!

[Anonymous]

I'm getting an error while trying to download the script, can someone repost it?

"Error: Resource has been moved or is unavailable. Please contact the forum admin."

[Geno]

Hi,

I tested this out, and indeed it works.
But only on the domain where you are running it.
It deletes all the computers that are on a different domain.
I'm sure a line could be changed to make sure it doesn't delete the other computers, but I have yet had the time to look at it.
If/when I find it, I will post it here. Unless someone changes it before me.

Regards,

[DGoodwin]

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?