How do I copy data from the barcode field to Custom 1 field

[jdeanley]

I have my asset number in the barcode field of a device and I would like to copy it to the custom 1 field so that I can map that custom 1 field to my solarwinds helpdesk so that when it synchronizes it brings that data over

Thanks for your help

Joel

[Bruce_B · Accepted solution]

You could copy over the value of the tblAssetCustom.Barcode field over to tblAssetCustom.Custom1 with an update query that you run under Configuration\Database Scripts. We've pasted a script below that will copy the value for tblAssetCustom.Barcode over to tblAssetCustom.Custom1 for ALL of your assets. Prior to running database scripts we always recommend backing up your database just in case. You can paste the script below under Configuration\Database Scripts and click the Execute SQL Code button afterwards.

Update tblAssetCustom set Custom1 = Barcode

[Bruce_B]

Sure thing, we can always go deeper 🙂

Keep in mind that joining tblADUsers to the script will likely reduce the amount of assets targeted by it. It should still cover all of your Windows computers that have had logons by AD users if you've fully scanned your AD via an AD User Path scanning target.

UPDATE tblassetcustom
  SET 
      contact = ad.Displayname
FROM tblassetcustom
     INNER JOIN tblassets a ON a.assetid = tblassetcustom.assetid
     INNER JOIN tbladusers ad ON ad.username = a.username
                                 AND ad.Userdomain = a.Userdomain;

[jdeanley]

That worked perfectly!!!

Thanks so much for your help

Joel

[Bruce_B · Accepted solution]

You could copy over the value of the tblAssetCustom.Barcode field over to tblAssetCustom.Custom1 with an update query that you run under Configuration\Database Scripts. We've pasted a script below that will copy the value for tblAssetCustom.Barcode over to tblAssetCustom.Custom1 for ALL of your assets. Prior to running database scripts we always recommend backing up your database just in case. You can paste the script below under Configuration\Database Scripts and click the Execute SQL Code button afterwards.

Update tblAssetCustom set Custom1 = Barcode

[mjdelacr]

Hi Bruce, I hope you can help me too.

How to auto-populate the tblAssetCustom.Contact  on what is in the Last User  using the same update script.

[Bruce_B · Accepted solution]

Hi,

that query would be quite a bit more complex as there isn't a single clean field that stores "last user" for all assets. There's the fields username and userdomain in tblAssets that get filled by Windows computer scans, Linux scans, Chromebook scans, etc. This is the one that makes the most sense to use I suppose. This doesn't contain a "friendlyname" though (first name, last name). Rather it contains the username used to log on to the machine with.

I've added a script below which fills userdomain\username in the Contact field, if username and userdomain are filled.

• Keep in mind that this script will overwrite any data that is currently already stored in tblAssetCustom.Contact, to avoid that, you'd need to adjust the where clause (only select rows with empty/NULL values in that field)
• Do be careful when running scripts, make sure you've got a backup ready, just in case. Keep in mind you can no longer run scripts via the web console, this was removed to improve the security of the application. You'll need to connect to your Lansweeper database using SQL Server Management Studio or user Program Files (x86)\Lansweeper\Tools\DatabaseMaintenance.exe

https://www.lansweeper.com/kb/94/backing-up-your-installation.html

UPDATE tblassetcustom
  SET 
      contact = a.userdomain + '\' + a.username
FROM tblassetcustom
     INNER JOIN tblassets a ON a.assetid = tblassetcustom.assetid
WHERE(username IS NOT NULL
      AND username <> '')
     AND (userdomain IS NOT NULL
          AND userdomain <> '');

[mjdelacr]

First of all, I would like to thank you for taking the effort on replying to this thread.

On your script it will add the username from tblAssets, is it possible to join it to tblADusersDisplayname so we can get the Display Name?