cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
jdeanley
Engaged Sweeper II
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
2 ACCEPTED SOLUTIONS
Bruce_B
Lansweeper Alumni
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

View solution in original post

Bruce_B
Lansweeper Alumni

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 <> '');

 

 

View solution in original post

6 REPLIES 6
Bruce_B
Lansweeper Alumni

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
Engaged Sweeper II
That worked perfectly!!!

Thanks so much for your help

Joel
Bruce_B
Lansweeper Alumni
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
Engaged Sweeper II

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
Lansweeper Alumni

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
Engaged Sweeper II

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?