Initial Filling of User-Relation by using last logged on user - Probably done by SQL-Script

Posted: Thursday, May 12, 2016 11:06:40 AM(UTC)


Member Original PosterPosts: 10
This issue has been solved! Click here to view the solution
Hi everyone,

I've seen a lot of approaches using last logged on user in reports used for determination of the "owner" of an asset.

What we're looking for is a possibility to autofill the user-relation of a PC / Notebook to a user based on the last logged on user which was scanned. This will be a one-shot script for initially filling a relation which might be altered afterwards. Based on the data we got while scanning in our trial-period we think this could be a good 90-percent-solution for starting with lansweeper as our new CMDB.

As mentioned I only saw SQL-scripts generating a "report". I'm aware that - in another inquiry - a support-member said that this is not 100% supported by Lansweeper-Team, but I would give it a try, having a backup of the database.

Does anyone can help providing a script for this as I've sadly got the lack of SQL-Knowledge for accomplishing this..?

Thanks in Advance,
Karel DS
#1Karel DS Member Lansweeper Developer Administration Posts: 202  
posted: 5/18/2016 10:58:19 AM(UTC)
The following query should perform the requested:

INSERT tblAssetUserRelations (Username, Userdomain, AssetID, Type, LastChanged, StartDate)
SELECT Username, Userdomain, AssetID, 1, GETDATE(), GETDATE() FROM tblAssets WHERE Username + Userdomain IN (SELECT Username + Userdomain FROM tblADusers)

Instead of using GETDATE(), you can set a custom date using this format: '2014-06-18 00:00:00.000'. The type of relation can be found in tsysAssetRelationTypes, 1 being 'owned by'.
#2Filo Member Original PosterPosts: 10  
posted: 5/18/2016 12:13:50 PM(UTC)
Thank you :)
Exactly that worked for our case. Excellent!
#3Susan.A Member Administration Posts: 1,536  
posted: 5/19/2016 1:46:21 PM(UTC)
FYI for everyone: to be safe, we recommend backing up your database prior to running any scripts.
#4wilkinsonian Member Posts: 5  
posted: 2/13/2017 10:21:13 PM(UTC)
"tsysAssetRelationTypes, 1 being 'owned by'."

Is "Used by" 11?
#5Filo Member Original PosterPosts: 10  
posted: 2/14/2017 8:42:40 AM(UTC)
Hi :)

I ran a custom report with:

Select Top 1000000 tsysAssetRelationTypes.RelationTypeID,
From tsysAssetRelationTypes

Which gave:

ID Relation-Type

1 Owned By
2 Borrowed By
3 Connected To
4 Used With
5 Inside
6 Ordered For
8 Installed By
9 Needed For
10 Backed Up To
11 Controlled By
12 Used By
13 Forbidden For
14 Is Located In

#6Alham Member Posts: 1  
posted: 8/1/2019 2:12:14 PM(UTC)
Hi there.
Could you please help me with script for another idea.
My computers have owners and now, I would like to assign them as owners for monitors, that are connected to their computers. It is possible to do that?

Active Discussions

Installer Sophos Silent Install
by  Esben.D   Go to last post Go to first unread
Last post: 2/14/2020 10:41:20 AM(UTC)
Installer Update Windows with PSWindowsUpdate
by  Patrick Kuhl  
Go to last post Go to first unread
Last post: 1/17/2020 5:32:57 PM(UTC)
Installer Windows 10 Upgrade
by  Anita   Go to last post Go to first unread
Last post: 1/16/2020 5:11:46 PM(UTC)
Installer NEC_UC_Suite-6.1_Install_No_Reboot
by  mchapman1963  
Go to last post Go to first unread
Last post: 12/24/2019 4:50:17 PM(UTC)
Installer Team Viewer Host update / install
by  BastiOn  
Go to last post Go to first unread
Last post: 11/27/2019 2:09:10 PM(UTC)
Installer Office 2016 Update
by  Cem Serdar   Go to last post Go to first unread
Last post: 11/7/2019 12:05:50 PM(UTC)
Installer Mozilla FireFox 70.0 Install
by  CyberCitizen  
Go to last post Go to first unread
Last post: 11/7/2019 12:09:14 AM(UTC)