Database Script to Populate Custom Fields with Registry Value Scan

[Joshua]

I spent most of my evening figuring this out. All of my Googling led to nothing but questions with no real answers. So I figured I would share to aid others who might be looking for the same thing.

Lansweeper can scan registry keys and stores the values in its database. But it doesn't allow you to intuitively put the data into the Custom Fields that show nice and pretty on an Asset's page.

To do this, you need to run a SQL query to copy the data from one table to another. You can do this from SQL Server Management Studio or you can do it from the Database Scripts website under Configuration within Lansweeper.

This is the query I used:

UPDATE dbo.tblAssetCustom
SET dbo.tblAssetCustom.Custom1 = dbo.tblRegistry.value
FROM dbo.tblAssetCustom INNER JOIN dbo.tblRegistry
ON dbo.tblAssetCustom.AssetID = dbo.tblRegistry.AssetID
WHERE tblRegistry.Regkey LIKE '%teamviewer%'

In this case, I used the field Custom1 as a storage for Teamviewer ID. I created a custom registry scan of the registry key HKLM\SOFTWARE\Wow6432Node\Teamviewer and the Value ClientID. I was lazy and used a Like on the last line but if you want to be exact you can use an = 'SOFTWARE\Wow6432Node\Teamviewer' instead.

You can change the number in Custom1 to any value between 1 and 20 as you have 20 custom fields you can work with. Just don't use the same value more than once unless you want to overwrite your data. I plan on filling many more Custom fields with other registry values.

If you screw something up you can always clear out all the data by running this query:

UPDATE dbo.tblAssetCustom
SET dbo.tblAssetCustom.Custom1 = NULL

This will set every Custom1 value on every asset to NULL (blanks out all data) so you can start over.

The last piece of the puzzle I need to figure out is how to automate these "database scripts". Obviously the registry data will change every so often so you'd want to update your custom fields regularly to make sure they show the most up to date information. For now I'm heading to bed but I'll do more research tomorrow and will report back what I find on automation 🙂

[Joshua]

Yea I meant to post an update. SQL Server 2008 Express doesn't come with the ability to schedule jobs from within the software sadly. So I plan on creating a few batch files and use Task Scheduler.

[Susan_A]

There is no built-in functionality in Lansweeper for automating scripts. You could create a scheduled job in SQL Server Management Studio, though this is not something we can provide instructions or support for unfortunately. Built-in functionality for displaying file/registry scanning results in the Summary tabs of your computers' Lansweeper webpages is on the wish list, but we do not have a release date for this feature. File/registry scanning results are listed under Config\Scanned Info instead.

Just an FYI for everyone else: Joshua's scripts will only work if your database is hosted in SQL Server. They won't work in SQL Compact. You can check which database server you're using under Configuration\Database Scripts. Always create a database backup prior to running any scripts, just to be safe. Backup instructions can be found here.