Importing CSV Data via SQL Stored Procedure

Pro Tips #70

Lansweeper On-Premise, Custom Fields, and SQL Stored Procedures

For this Pro-Tip, I’ll share yet another way that I like to automate the population of custom fields.

This time, we’ll take a trip back to our On-Premise/Hybrid offering, and take advantage of MSSQL Stored Procedures to process CSV files – something that comes in really handy when you want to integrate with an application that has no easy way to integrate with, but it can automatically save CSV files to disk. Plus, it’s simple enough that even I can do it!

Prerequisites

In order to do this exercise, you will need

• An ‘Advanced’ installation of On-Premise – specifically, one with a full SQL installation as the back-end
• SQL Server Management Studio (SSMS) and access permissions to create a stored procedure.
• Create ‘Field1, Field2, Field3 in custom fields 18, 19, and 20 (adjust accordingly if those fields are used already, just don’t forget to change the column numbers in the SQL Query!)
• *Knowledge of MSSQL – as this is NOT supported*- i.e. if you mess up the query, you could potentially mess up your Lansweeper database – (If you aren’t familiar with the concepts, make sure you back up your database – or, just don’t do it!)

Step-By-Step Guide

Step 1: Prepare the CSV

• Open Notepad++ and paste the following content – note: make sure you update the test data with information that matches your assets (Serial Numbers, IP Addresses, Asset Names, MAC Addresses) – as the below is from my home lab:

FIELD1,FIELD2,FIELD3,SerialNumber,IPAddress,AssetName,MACAddress
1001,tagA,groupX,VMware-42 3a 50...,10.0.1.27,DB01,00:50:56:BA:D3:B7 
1002,tagB,groupY,,,DC1,00:0C:29:9B:BA:53 
1003,tagC,groupZ,,,,SQL01 
1004,tagD,groupW,,,,,10.0.1.25 
1005,tagE,groupNoMatch,,,,, 

• Go to File > Save As:
  • Name: example.csv
  • Location: C:\test\ on the SQL Server (For testing purposes)
• In Notepad++, confirm the Encoding is set to UTF-8 (check lower right) If not, go to Encoding > Convert to UTF-8, then save again.

Step 2: Connect to a SQL Server and View Databases in SSMS

• Connect to Your SQL Server Instance
• Launch SQL Server Management Studio from your Start Menu or desktop shortcut.
• Fill out the fields:
  • Server type: Database Engine
  • Server name: (enter your server name — e.g., localhost, SQL01, or an IP address)
  • Authentication: Choose either:
    • Windows Authentication (default)
    • Or SQL Server Authentication (if you need to enter a username and password)
    • Click ‘Connect’

• Expand Databases in Object Explorer
  • After connecting, you’ll see your SQL Server listed in the Object Explorer panel on the left.
  • Click the + next to Databases to expand the list.
  • You will now see a list of databases on that server, such as lansweeperdb, master, model, etc.
  • (The red arrow in the image points to lansweeperdb… that’s our database – expand it.)

Step 3: Create a New Stored Procedure

• In Object Explorer, expand your target database (e.g., lansweeperdb).
• Expand the Programmability folder
• Right-click on Stored Procedures
• Select New > Stored Procedure…

This opens a new query window with a template for creating a stored procedure.

Step 4: Clean Up the Template

• In the opened script window, you’ll see a lot of template text like the above image
• Delete the default parameters

@param1 INT = 0,
@param2 INT = 0

• Delete the dummy SELECT block:
• Remove the example line:

SELECT @param1, @param2 

Now you’re ready to write your own logic in the stored procedure.

Step 5: Name the Stored Procedure & Paste Your Query

• Replace the default line:

CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>

• with your desired name, for example:

CREATE PROCEDURE [dbo].[ProTip70_CSVtoCustomFields]

• Paste the below query inside the BEGIN … END block of the procedure.
  • This Script Includes:
    • Creating the temp table
    • Bulk inserting the CSV
    • Matching by priority: SerialNumber > MAC > Name > IP
    • Updating Custom18–20 (Custom Fields)
  • Make sure the entire logic is within the BEGIN … END block of the procedure definition.

    SET NOCOUNT ON;

    -- Step 1: Create temp table
    CREATE TABLE #tmp_csv_import (
        FIELD1 INT NOT NULL,
        FIELD2 NVARCHAR(100) COLLATE Latin1_General_CI_AI NULL,
        FIELD3 NVARCHAR(100) COLLATE Latin1_General_CI_AI NULL,
        SerialNumber NVARCHAR(100) COLLATE Latin1_General_CI_AI NULL,
        IPAddress NVARCHAR(50) COLLATE Latin1_General_CI_AI NULL,
        AssetName NVARCHAR(100) COLLATE Latin1_General_CI_AI NULL,
        MACAddress NVARCHAR(50) COLLATE Latin1_General_CI_AI NULL
    );

    -- Step 2: Import from CSV
    BULK INSERT #tmp_csv_import
    FROM 'C:\test\example.csv'
    WITH (
        DATAFILETYPE = 'char',
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n',
        CODEPAGE = '65001',
        FIRSTROW = 2
    );

    -- Step 3: Match imported rows with assets (SerialNumber now from tblAssetCustom)
    ;WITH ScoredMatches AS (
        SELECT
            t.*,
            a.AssetID,
            MatchConfidence = CASE
                WHEN t.SerialNumber IS NOT NULL AND t.SerialNumber = ac.SerialNumber THEN 4
                WHEN t.MACAddress IS NOT NULL AND t.MACAddress = a.MAC THEN 3
                WHEN t.AssetName IS NOT NULL AND t.AssetName = a.AssetName THEN 2
                WHEN t.IPAddress IS NOT NULL AND t.IPAddress = a.IPAddress THEN 1
                ELSE 0
            END
        FROM #tmp_csv_import t
        LEFT JOIN lansweeperdb.dbo.tblAssets a ON 1 = 1
        LEFT JOIN lansweeperdb.dbo.tblAssetCustom ac ON a.AssetID = ac.AssetID
        WHERE
            (t.SerialNumber IS NOT NULL AND t.SerialNumber = ac.SerialNumber)
            OR (t.MACAddress IS NOT NULL AND t.MACAddress = a.MAC)
            OR (t.AssetName IS NOT NULL AND t.AssetName = a.AssetName)
            OR (t.IPAddress IS NOT NULL AND t.IPAddress = a.IPAddress)
    )
    , BestMatches AS (
        SELECT *
        FROM (
            SELECT *,
                ROW_NUMBER() OVER (PARTITION BY FIELD1 ORDER BY MatchConfidence DESC) AS rn
            FROM ScoredMatches
            WHERE MatchConfidence > 0
        ) Ranked
        WHERE rn = 1
    )

    -- Step 4: Update tblAssetCustom
    UPDATE AC
    SET 
        AC.Custom18 = CAST(B.FIELD1 AS NVARCHAR),
        AC.Custom19 = B.FIELD2,
        AC.Custom20 = B.FIELD3
    FROM BestMatches B
    INNER JOIN lansweeperdb.dbo.tblAssetCustom AC ON B.AssetID = AC.AssetID;

    -- Optional: clean up
    -- DROP TABLE #tmp_csv_import;

Step 6: Execute the Stored Procedure Script

• Click the Execute button (or press F5) to run the script.

• Look at the Messages pane at the bottom. You should see ‘Command(s) completed successfully). This confirms that the stored procedure has been created.
• Optional: You can call your procedure at any time using EXEC dbo.ProTip70_CSVtoCustomFields;

Step 7: Verify the Results in Lansweeper

• Open the Lansweeper web console.
• Go to Assets > Search and open one of the assets used in your CSV (e.g., DB01, DC1).
• Look in the Custom Fields section on the right.
• You should see:
  • Custom18 = FIELD1 (e.g., 1001)
  • Custom19 = FIELD2 (e.g., tagA)
  • Custom20 = FIELD3 (e.g., groupX)
• This confirms the CSV import and update were successful.

Example Use Cases

And now, just some of the possibilities, for those that managed to make it this far!

Veeam

You can pull backup job data from Veeam to track when each device was last backed up. If a Lansweeper asset isn’t in the list, it likely isn’t protected by backups — a major compliance risk.

SCOM

By connecting monitoring alerts from SCOM, you can see each asset’s last alert, severity, and health state. If something in Lansweeper isn’t monitored, it may be flying under the radar entirely.

SCCM (MECM)

Using SCCM data, you can track which devices are patched, what ring they’re in, and their last check-in. If a device doesn’t appear in this list, it’s probably unmanaged from a patching standpoint.

Zabbix

Zabbix performance alerts can show you what’s being watched and what’s not. Devices missing from the feed may not be monitored for CPU usage, uptime, or other critical metrics.

Tripwire

Tripwire can tell you if a device has gone out of compliance or been changed unexpectedly. If a Lansweeper asset isn’t in the results, it’s not being monitored for integrity or security compliance.

PRTG

You can sync sensor alerts and probe info from PRTG. Assets not included might be completely unmonitored for performance, availability, or outages — leaving you blind to potential issues.

ServiceDesk Plus

Ticket and incident history from your helpdesk can be tied to each asset. If an asset isn’t linked to any tickets, you may have no support visibility into it — or it may not be in your ITSM at all.

Cherwell (on-prem)

You can bring in data like CSAT scores and incident counts from Cherwell. If an asset isn’t showing up, it might be missing from the CMDB or have no support history recorded.

Conclusion

So, there you have it – who knew that a little stored procedure, along with the power and flexibility of Lansweeper, could do so much!

We did, didn’t we!!?

Happy Sweeping!

Jacob

Additional Reference

You can learn how to automate/schedule the stored procedure to run here: https://learn.microsoft.com/en-us/ssms/agent/create-a-job