Bulk-configure scanning servers with a SQL script

If you run many scanning servers, configuring each one through the web console is slow. This script applies the same core configuration as Auto-configure a scanning server with a SQL script, but loops through every server you list in one run.

You provide one row per scanning server in a staging table. The script loops through the rows, applies the configuration to each server, and records a result code so you can see which servers succeeded. The table stays in the database after the run so you can check those codes.

Prerequisites

• Every scanning server you configure is installed and linked to the same database, and its service has started at least once. The script configures each server's existing database record, so those records must already exist.
• You have access to the SQL Server instance that hosts the lansweeperdb database, and SQL Server Management Studio to run queries.
• You know each server's name, IP range, domain details, and preferred domain controller.

List your scanning servers

Each row in the variableSets table describes one scanning server, in this column order:

The scan schedule times, the LsAgent failover server, and the "ignore Windows during IP range scan" setting are the same for every server. Set them once inside the loop, in the SET block near the top of the WHILE statement.

Run the script

1. Stop the Lansweeper Server service on each scanning server. The service caches its configuration, so it needs to restart to pick up direct database changes.
2. Open SQL Server Management Studio and connect to the SQL Server instance that hosts the lansweeperdb database.
3. Open a New Query window and paste the script below.
4. Replace the example rows in the INSERT INTO variableSets statement with one row per scanning server. Leave NULL as the last value in each row; it holds the result code.
5. Run the script.
6. Start the Lansweeper Server service again on each server.

USE [lansweeperdb]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('dbo.variableSets','U') IS NOT NULL
    DROP TABLE [dbo].[variableSets]

CREATE TABLE variableSets (
    scannerName NVARCHAR(100),
    scannerStartIP NVARCHAR(50),
    scannerEndIP NVARCHAR(50),
    scanServerIP NVARCHAR(50),
    domainNETBiosName NVARCHAR(380),
    domainDNSSuffix NVARCHAR(10),
    domainControllerName NVARCHAR(100),
    scannerDescription NVARCHAR(100),
    queryReturnCode INT
);

-- Add one row per scanning server. Keep NULL as the last value.
INSERT INTO variableSets VALUES
('MYSCANSERVER01', '10.0.0.0', '10.0.0.255', '10.0.0.254', 'MYDOMAIN', 'local', 'MYDOMAINCONTROLLER01', 'MY-IP-LOCATION-EMEA-BELGIUM-01', NULL),
('MYSCANSERVER02', '10.1.0.0', '10.1.0.255', '10.1.0.254', 'MYDOMAIN', 'local', 'MYDOMAINCONTROLLER01', 'MY-IP-LOCATION-EMEA-BELGIUM-02', NULL)

DECLARE @scannerName NVARCHAR(100)
DECLARE @scannerStartIP NVARCHAR(50)
DECLARE @scannerEndIP NVARCHAR(50)
DECLARE @scanServerIP NVARCHAR(50)
DECLARE @domainNETBiosName NVARCHAR(380)
DECLARE @domainDNSSuffix NVARCHAR(10)
DECLARE @domainControllerName NVARCHAR(100)
DECLARE @scannerDescription NVARCHAR(100)
DECLARE @iprangeStartTime NVARCHAR(30)
DECLARE @domainUserPathStartTime NVARCHAR(30)
DECLARE @scannerNameAsSecondaryLsAgentScanServer NVARCHAR(30)
DECLARE @ignoreWindowsDuringIPRangeScan INT
DECLARE @scannerStartIPNumeric NVARCHAR(30)
DECLARE @scannerEndIPNumeric NVARCHAR(30)
DECLARE @domainDNSName NVARCHAR(100)
DECLARE @ipLocation NVARCHAR(50)
DECLARE @domainControllerFQDN NVARCHAR(100)
DECLARE @LsAgentGroupUID uniqueidentifier
DECLARE @lsAgentGroup NVARCHAR(100)
DECLARE @queryReturnCode INT

DECLARE variableSetCursor CURSOR FOR
SELECT scannerName, scannerStartIP, scannerEndIP, scanServerIP, domainNETBiosName, domainDNSSuffix, domainControllerName, scannerDescription
FROM variableSets

OPEN variableSetCursor
FETCH NEXT FROM variableSetCursor INTO @scannerName, @scannerStartIP, @scannerEndIP, @scanServerIP, @domainNETBiosName, @domainDNSSuffix, @domainControllerName, @scannerDescription

WHILE @@FETCH_STATUS = 0
BEGIN

    -- Settings that are the same for every server
    SET @iprangeStartTime = N'14:00:00.000'
    SET @domainUserPathStartTime = N'11:00:00.000'
    SET @scannerNameAsSecondaryLsAgentScanServer = N'MYSCANSERVER02'
    SET @ignoreWindowsDuringIPRangeScan = 1

    -- Derived values (no need to edit)
    SET @scannerStartIPNumeric = PARSENAME(@scannerStartIP,4) +
    REPLICATE('0',3-LEN(PARSENAME(@scannerStartIP,3))) + PARSENAME(@scannerStartIP,3) +
    REPLICATE('0',3-LEN(PARSENAME(@scannerStartIP,2))) + PARSENAME(@scannerStartIP,2) +
    REPLICATE('0',3-LEN(PARSENAME(@scannerStartIP,1))) + PARSENAME(@scannerStartIP,1)
    SET @scannerEndIPNumeric = PARSENAME(@scannerEndIP,4) +
    REPLICATE('0',3-LEN(PARSENAME(@scannerEndIP,3))) + PARSENAME(@scannerEndIP,3) +
    REPLICATE('0',3-LEN(PARSENAME(@scannerEndIP,2))) + PARSENAME(@scannerEndIP,2) +
    REPLICATE('0',3-LEN(PARSENAME(@scannerEndIP,1))) + PARSENAME(@scannerEndIP,1)
    SET @domainDNSName = @domainNETBiosName + '.' + @domainDNSSuffix
    SET @domainControllerFQDN = @domainControllerName + '.' + @domainNETBiosName + '.' + @domainDNSSuffix
    SET @ipLocation = @scannerDescription
    SET @lsAgentGroup = @scannerDescription

    BEGIN TRY

        -- Configure scan server options and mark the server active
        UPDATE [dbo].[tsysASServers] SET [IsAssetRadarCompatible] = 1, [IsAssetRadarEnabled] = 2, [ScanLastLogon] = 1, [SADDCOMP] = 0, [SADDUSER] = 0, [REFRADCOMP] = 1, [REFRADUSERS] = 1, [MAKEACTIVE] = 1, [NACOMP] = 1, [NACOMPDAYS] = 60, [DELCOMP] = 1, [DELCOMPDAYS] = 180, [NonActiveAssetRadarComp] = 1, [NonActiveAssetRadarCompDays] = 30, [DelAssetRadarCompUnknownOnly] = 1, [DelAssetRadarCompUnknownOnlyDays] = 30, [NADICOMP] = 1, [renamedComputerDetection] = 1, [RenamedComputerDetectionWithoutMac] = 1, [DoFallbackScanning] = 0 WHERE [Servername] = @scannerName

        -- Add the IP range scanning target
        INSERT [dbo].[tsysIPScanRanges] ([Servername], [Ipstart], [Ipend], [Enabled], [PingTimeout], [IPIgnoreWindows], [DontPing], [Day1], [Day2], [Day3], [Day4], [Day5], [Day6], [Day7], [Day1time], [Day2time], [Day3time], [Day4time], [Day5time], [Day6time], [Day7time], [LastIPscan], [NoSSH], [Recurring], [Minutes], [Waittime], [SSHport], [SavePingedIP], [IPIgnoreKnownWindows], [Description], [SIPport], [ScanNow], [CloudId]) VALUES
        (@scannerName, @scannerStartIP, @scannerEndIP, 1, 2, @ignoreWindowsDuringIPRangeScan, 0, 1, 1, 1, 1, 1, 1, 1, CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), NULL, 0, 0, 0, 1, 22, 1, 0, NULL, 5060, 0, NULL)

        -- Add the scan server's own IP address as a target
        INSERT [dbo].[tsysIPScanRanges] ([Servername], [Ipstart], [Ipend], [Enabled], [PingTimeout], [IPIgnoreWindows], [DontPing], [Day1], [Day2], [Day3], [Day4], [Day5], [Day6], [Day7], [Day1time], [Day2time], [Day3time], [Day4time], [Day5time], [Day6time], [Day7time], [LastIPscan], [NoSSH], [Recurring], [Minutes], [Waittime], [SSHport], [SavePingedIP], [IPIgnoreKnownWindows], [Description], [SIPport], [ScanNow], [CloudId]) VALUES
        (@scannerName, @scanServerIP, @scanServerIP, 1, 2, 0, 0, 1, 1, 1, 1, 1, 1, 1, CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), CAST(N'1900-01-01T'+@iprangeStartTime AS DateTime), NULL, 0, 0, 0, 1, 22, 1, 0, NULL, 5060, 0, NULL)

        -- Add the Active Directory domain
        INSERT INTO [dbo].[tsysASDomains] ([Servername],[Domainname],[Netbiosname],[Description],[ScanNow],[LastScanned],[Enabled],[ErrorText],[CloudId],[IntervalInSeconds],[MinRescanTimeInSeconds],[MaxRescanTimeInSeconds]) VALUES
        (@scannerName, @domainDNSName, @domainNETBiosName, null, 0, null, 1, '', null, 900, 72000, 172800)

        -- Add the Active Directory user path
        INSERT INTO [dbo].[tsysUserSchedule] ([Servername],[Scantarget],[Netbiosdomain],[Enabled],[ScanNow],[Day1],[Day2],[Day3],[Day4],[Day5],[Day6],[Day7],[Day1time],[Day2time],[Day3time],[Day4time],[Day5time],[Day6time],[Day7time],[LastScanned],[Description],[ErrorText],[CloudId]) VALUES
        (@scannerName, N'dc='+@domainNETBiosName+',dc='+@domainDNSSuffix, @domainNETBiosName, 1, 0, 1, 1, 1, 1, 1, 1, 1, CAST(N'1900-01-01T'+@domainUserPathStartTime AS DateTime), CAST(N'1900-01-01T'+@domainUserPathStartTime AS DateTime), CAST(N'1900-01-01T'+@domainUserPathStartTime AS DateTime), CAST(N'1900-01-01T'+@domainUserPathStartTime AS DateTime), CAST(N'1900-01-01T'+@domainUserPathStartTime AS DateTime), CAST(N'1900-01-01T'+@domainUserPathStartTime AS DateTime), CAST(N'1900-01-01T'+@domainUserPathStartTime AS DateTime), NULL, NULL, NULL, NULL)

        -- Add the preferred domain controller
        INSERT INTO [dbo].[tsysDomainControllers] ([Servername],[DomainNetbios],[DomainControllerDns]) VALUES
        (@scannerName, @domainNETBiosName, @domainControllerFQDN)

        -- Enable Asset Radar on the scan server's network interface
        UPDATE tsysInitialNetworkInterfaces SET [EnabledForAssetRadar] = 1 WHERE [Scanserver] = @scannerName AND [IPMachine] = @scanServerIP
        UPDATE tsysInitialNetworkInterfaces SET [EnabledForAssetRadar] = 0 WHERE [Scanserver] = @scannerName AND [IPMachine] <> @scanServerIP

        -- Create the LsAgent group with the scan server and a failover server
        SET @LsAgentGroupUID = NEWID()
        INSERT [tblLsAgentGroup] ([LsAgentGroupID],[Name],[Default],[Created],[LastChanged],[ScheduleID],[Status],[AutoUpdate])
        VALUES (@LsAgentGroupUID,@lsAgentGroup,0,GETDATE(),GETDATE(),-2,1,1)
        INSERT INTO [dbo].[tblLsAgentGroupUrl] ([SortOrder], [LsAgentGroupID], [ServerName]) VALUES (1, @LsAgentGroupUID, @scannerName)
        INSERT INTO [dbo].[tblLsAgentGroupUrl] ([SortOrder], [LsAgentGroupID], [ServerName]) VALUES (2, @LsAgentGroupUID, @scannerNameAsSecondaryLsAgentScanServer)

        -- Configure the IP location
        INSERT INTO [dbo].[tsysIPLocations] ([StartIP],[EndIP],[IPLocation],[Realstart],[Realend],[PackageShare],[ShareUsername],[SharePassword],[ShareKeyHash],[CloudId])
        VALUES (@scannerStartIPNumeric,@scannerEndIPNumeric,@ipLocation,@scannerStartIP,@scannerEndIP,'','','',NULL, NULL)

        -- Record success for this server
        SET @queryReturnCode = 0
    END TRY
    BEGIN CATCH
        -- Record the SQL error number for this server
        SET @queryReturnCode = ERROR_NUMBER()
    END CATCH

    PRINT @scannerName
    PRINT @queryReturnCode
    UPDATE variableSets SET queryReturnCode = @queryReturnCode WHERE scannerName = @scannerName

    FETCH NEXT FROM variableSetCursor INTO @scannerName, @scannerStartIP, @scannerEndIP, @scanServerIP, @domainNETBiosName, @domainDNSSuffix, @domainControllerName, @scannerDescription
END

CLOSE variableSetCursor
DEALLOCATE variableSetCursor

Check the results

The script records a result code for each server in the variableSets table. Query it to see which servers were configured:

SELECT scannerName, queryReturnCode FROM variableSets

A queryReturnCode of 0 means the server was configured. Any other value is the SQL error number returned for that server, so you can investigate and rerun it individually.

Verify the configuration

After the services restart, open the web console and confirm the settings for a sample of servers under Configuration > Server options and Scanning > Scanning targets.