05-18-2017 10:26 PM
Solved! Go to Solution.
05-22-2017 05:45 PM
Update tblAssetCustom set Custom1 = Barcode
08-11-2022 12:30 PM - edited 08-11-2022 12:56 PM
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.
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 <> '');
08-12-2022 11:59 AM
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;
05-24-2017 10:35 PM
05-22-2017 05:45 PM
Update tblAssetCustom set Custom1 = Barcode
08-11-2022 11:53 AM
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.
08-11-2022 12:30 PM - edited 08-11-2022 12:56 PM
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.
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 <> '');
08-12-2022 03:19 AM
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?
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now