Is it possible to cleanup the LS database by querying the contents of AD?

[pcrockett]

Is it possible to Cleanup LS by querying the contents of AD?

I need my Lansweeper database to contain only what is actually still present on the network. We are constantly replacing old computers and we remove them from Active Directory when we retire them, so is there a way or perhaps even a cleanup script option that I could use to compare the database with AD and remove any non-existent computers present?

Does the Pro version do this? Is it possible to write external scripts to achieve this by accessing the database in SQL and AD?

Thanks in advance.

[wim_dh]

We have implemented a service on the Lansweeper server that synchronizes a few times a day with AD and deletes the computers in Lansweeper that don't exist in AD anymore. I can give you the code but I leave it up to you on how to implement it as a service or get it running on your site. The code is written in VB.NET 2005. Good luck!

Imports System.Timers
Imports System.Data.Odbc
Imports System.Data.SqlClient
Imports System.ServiceProcess
Imports System
Imports System.DirectoryServices
Imports System.Text.StringBuilder
Imports System.IO.StreamWriter



Public Class LansweeperSynchronizeAD
    Inherits System.ServiceProcess.ServiceBase
    Private log As Diagnostics.EventLog
    Private oTimer As System.Threading.Timer


    Protected Overrides Sub OnStart(ByVal args() As String)
        Try
            Dim oCallBack As New System.Threading.TimerCallback(AddressOf Synchronize)
            oTimer = New System.Threading.Timer(oCallBack, Nothing, 15000, 28800000)
        Catch obug As Exception
            LogEvent(obug.Message, EventLogEntryType.Error)
            Throw obug
        End Try
    End Sub

    Protected Overrides Sub OnStop()
        Try
            log.WriteEntry("Service Stopping", EventLogEntryType.Information)
            oTimer.Dispose()
        Catch obug As Exception
            LogEvent(obug.Message, EventLogEntryType.Error)
        End Try
    End Sub

    Public Shared Sub LogEvent(ByVal sMessage As String, ByVal entryType As EventLogEntryType)
        Try
            Dim oEventLog As Diagnostics.EventLog = New Diagnostics.EventLog("Application")
            If Not Diagnostics.EventLog.SourceExists("LansweeperSynchronizeAD") Then
                Diagnostics.EventLog.CreateEventSource("LansweeperSynchronizeAD", "Application")
            End If
            Diagnostics.EventLog.WriteEntry("LansweeperSynchronizeAD", sMessage, entryType)
        Catch e As Exception
        End Try
    End Sub


    Private Sub Synchronize(ByVal state As Object)
        LogEvent("Start Synchronization Lansweeper with Active Directory", EventLogEntryType.Information)
        Dim strComputer As String = ""
        Dim sPath As String = ""
        Dim m_ADSProvider As String = "LDAP"
        Dim objDirEnt As New DirectoryEntry(m_ADSProvider & "://" & "SERVER.COMPANY.COM/cn=Computers,dc=COMPANY,dc=COM")
        Dim objSearcher As New System.DirectoryServices.DirectorySearcher(objDirEnt)
        Dim objSearchRes As System.DirectoryServices.SearchResult

        Dim strCommandLan1 As String = ""
        Dim strCommandDelete As String = ""
        Dim strResultLanDeviceName As String = ""

        Dim myCommandLan1 As SqlCommand
        Dim myCommandDelete As SqlCommand

        Dim objDataReaderLan1 As SqlDataReader

        Dim sb As Text.StringBuilder = New Text.StringBuilder

        Dim strTables(65) As String

        strTables(0) = "tblComputers"
        strTables(1) = "tblBaseBoard"
        strTables(2) = "tblBattery"
        strTables(3) = "tblBIOS"
        strTables(4) = "tblBootConfiguration"
        strTables(5) = "tblBus"
        strTables(6) = "tblCDROMDrive"
        strTables(7) = "tblCodecFile"
        strTables(8) = "tblCOMApplication"
        strTables(9) = "tblComponentCategory"
        strTables(10) = "tblAutorun"
        strTables(11) = "tblComputersystem"
        strTables(12) = "tblComputerSystemProduct"
        strTables(13) = "tblCPlogoninfo"
        strTables(14) = "tblDCOMApplication"
        strTables(15) = "tblDesktop"
        strTables(16) = "tblDesktopMonitor"
        strTables(17) = "tblDiskdrives"
        strTables(18) = "tblDiskPartition"
        strTables(19) = "tblDisplayConfiguration"
        strTables(20) = "tblDisplayControllerConfiguration"
        strTables(21) = "tblEnvironment"
        strTables(22) = "tblerrors"
        strTables(23) = "tblFileVersions"
        strTables(24) = "tblFloppy"
        strTables(25) = "tblGroups"
        strTables(26) = "tblIDEController"
        strTables(27) = "tblIEActiveX"
        strTables(28) = "tblIEBars"
        strTables(29) = "tblIEBHO"
        strTables(30) = "tblIEExtensions"
        strTables(31) = "tblInfraredDevice"
        strTables(32) = "tblKeyboard"
        strTables(33) = "tblLicenses"
        strTables(34) = "tblNetwork"
        strTables(35) = "tblNetworkClient"
        strTables(36) = "tblOnBoardDevice"
        strTables(37) = "tblOperatingsystem"
        strTables(38) = "tblOSRecoveryConfiguration"
        strTables(39) = "tblPageFile"
        strTables(40) = "tblParallelPort"
        strTables(41) = "tblPCMCIAController"
        strTables(42) = "tblPointingDevice"
        strTables(43) = "tblPortableBattery"
        strTables(44) = "tblPortConnector"
        strTables(45) = "tblPOTSModem"
        strTables(46) = "tblPrinters"
        strTables(47) = "tblProcesses"
        strTables(48) = "tblPROCESSOR"
        strTables(49) = "tblProxy"
        strTables(50) = "tblQuickFixEngineering"
        strTables(51) = "tblRegistry"
        strTables(52) = "tblScsicontroller"
        strTables(53) = "tblSerialPort"
        strTables(54) = "tblServices"
        strTables(55) = "tblShares"
        strTables(56) = "tblSoftware"
        strTables(57) = "tblSoundDevice"
        strTables(58) = "tblTapeDrive"
        strTables(59) = "tblUSBController"
        strTables(60) = "tblUsers"
        strTables(61) = "tblUsersInGroup"
        strTables(62) = "tblVideoController"
        strTables(63) = "TsysDonotscan"
        strTables(64) = "TsysLastscan"
        strTables(65) = "TsysScanSchedule"

        Dim oLansweeperConnection1 As SqlConnection = New SqlConnection
        oLansweeperConnection1.ConnectionString = "Data Source=SERVER;Initial Catalog=Lansweeper;User ID=lssqluser;Password=**********"

        Dim oLansweeperConnection2 As SqlConnection = New SqlConnection
        oLansweeperConnection2.ConnectionString = "Data Source=SERVER;Initial Catalog=Lansweeper;User ID=lssqluser;Password=*********"

        Try
            oLansweeperConnection1.Open()

            Try
                strCommandLan1 = "Select DISTINCT Computername From tblComputers"
                myCommandLan1 = New SqlCommand(strCommandLan1, oLansweeperConnection1)
                objDataReaderLan1 = myCommandLan1.ExecuteReader()

                While objDataReaderLan1.Read()
                    Try
                        strResultLanDeviceName = objDataReaderLan1.GetString(0)
                        objDirEnt = New DirectoryEntry(m_ADSProvider & "://" & "SERVER.COMPANY.COM/cn=Computers,dc=COMPANY,dc=COM")
                        objSearcher = New System.DirectoryServices.DirectorySearcher(objDirEnt)
                        objSearcher.Filter = ("(objectClass=computer)")
                        objSearcher.Filter = "(CN=" + strResultLanDeviceName + ")"
                        objSearchRes = objSearcher.FindOne

                        If objSearchRes Is Nothing Then
                            sb.Append(strResultLanDeviceName)
                            sb.Append(Environment.NewLine)
                            For Each strEnumTable As String In strTables
                                Try
                                    oLansweeperConnection2.Open()
                                    strCommandDelete = "DELETE FROM " & strEnumTable & " WHERE Computername = '" & strResultLanDeviceName & "'"
                                    myCommandDelete = New SqlCommand(strCommandDelete, oLansweeperConnection2)
                                    myCommandDelete.ExecuteNonQuery()
                                Catch sql As SqlException
                                Finally
                                    oLansweeperConnection2.Close()
                                End Try
                            Next
                        End If
                    Catch ad As Exception
                        LogEvent("Cannot get information from Active Directory", EventLogEntryType.Error)
                    End Try

                End While
                objDataReaderLan1.Close()


            Catch sql As SqlException
                LogEvent("Cannot query Lansweeper database", EventLogEntryType.Error)
            Finally
                oLansweeperConnection1.Close()
            End Try

        Catch sql As SqlException
            LogEvent("No connection to Lansweeper database", EventLogEntryType.Error)
        End Try


        Dim lansweeperLog As IO.StreamWriter

        Try
            lansweeperLog = New IO.StreamWriter("D:\Lansweeper\logs\Lansweeperlog.txt", True)
            lansweeperLog.WriteLine(Now())
            lansweeperLog.WriteLine("PC's NOT in Active Directory and deleted in Lansweeper")
            lansweeperLog.Write(sb.ToString)
            lansweeperLog.Write(Environment.NewLine)
            lansweeperLog.Write(Environment.NewLine)
            lansweeperLog.Close()
        Catch ex As Exception
            MsgBox(ex.Message.ToString)
        End Try

        LogEvent("Synchronization Lansweeper with Active Directory ended", EventLogEntryType.Information)
    End Sub

End Class

[pcrockett]

There are a lot of inventorying softwares out there that trim the databases based on last seen, but in my business, we have computers in the field that are not on the network as well as spare computers in storage. We keep ADUC organized and pruned manually, and so it's an accurate inventory that I would like to synchronize the Lansweeper database to. With over 300 computers in inventory, a tool like this that maintains an accurate inventory could be extremely beneficial.

The possibility of synchronizing LS with AD is what is making LS look so attractive. I know it's possible, but don't know where to start.

Does anyone have any ideas or recommendations on writing that script? Any help would be much appreciated.

[Hemoco]

The premium version show active directory computer details but does not automatically removes computers when not present in active directory. (most people like to keep all historical data because you have a "lastseen" field)

You can write a script to query active directory and remove all computers not found.