
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-12-2016 12:06 PM
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,
Martin!
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,
Martin!
Solved! Go to Solution.
Labels:
- Labels:
-
General Discussion
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-18-2016 11:58 AM
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'.
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'.
6 REPLIES 6

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-01-2019 03:12 PM
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?
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?

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-14-2017 08:42 AM
Hi 🙂
I ran a custom report with:
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
Greetings,
Martin!
I ran a custom report with:
Select Top 1000000 tsysAssetRelationTypes.RelationTypeID,
tsysAssetRelationTypes.Name
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
Greetings,
Martin!

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎02-13-2017 10:21 PM
"tsysAssetRelationTypes, 1 being 'owned by'."
Is "Used by" 11?
Is "Used by" 11?
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-19-2016 02:46 PM
FYI for everyone: to be safe, we recommend backing up your database prior to running any scripts.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-18-2016 01:13 PM
Thank you 🙂
Exactly that worked for our case. Excellent!
Exactly that worked for our case. Excellent!

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-18-2016 11:58 AM
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'.
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'.
