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!
In order to do this exercise, you will need
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,,,,,



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


@param1 INT = 0,
@param2 INT = 0
SELECT @param1, @param2
Now you’re ready to write your own logic in the stored procedure.
CREATE PROCEDURE <Procedure_Name, sysname, ProcedureName>
CREATE PROCEDURE [dbo].[ProTip70_CSVtoCustomFields]

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;

EXEC dbo.ProTip70_CSVtoCustomFields;



And now, just some of the possibilities, for those that managed to make it this far!
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.
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.
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 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 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.
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.
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.
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.
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
You can learn how to automate/schedule the stored procedure to run here: https://learn.microsoft.com/en-us/ssms/agent/create-a-job
Explore Lansweeper for free.
No credit card required.