Notification

Icon
Error

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)
Filo

Filo

Member Original PosterPosts: 10
0
Like
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,
Martin!
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'.
Filo
#2Filo Member Original PosterPosts: 10  
posted: 5/18/2016 12:13:50 PM(UTC)
Thank you :)
Exactly that worked for our case. Excellent!
Susan.A
#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.
wilkinsonian
#4wilkinsonian Member Posts: 5  
posted: 2/13/2017 10:21:13 PM(UTC)
"tsysAssetRelationTypes, 1 being 'owned by'."

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

I ran a custom report with:

Quote:
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!
Alham
#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

Lansweeper Changes in the licensing model
by  mcbeathb   Go to last post Go to first unread
Last post: Today at 6:47:51 AM(UTC)
Lansweeper About Memory Changes
by  fjca  
Go to last post Go to first unread
Last post: Yesterday at 5:51:17 PM(UTC)
Lansweeper Wildcard in file scanning to get all files in a directory?
by  RKCar   Go to last post Go to first unread
Last post: Yesterday at 4:03:43 PM(UTC)
Lansweeper Wishlist is dead, no responses from LS staff.
by  RobertB  
Go to last post Go to first unread
Last post: Yesterday at 3:08:12 PM(UTC)
Lansweeper Deploy installer packages not working any more
by  richv   Go to last post Go to first unread
Last post: Yesterday at 3:06:19 PM(UTC)
Lansweeper File Scanning
by  RKCar  
Go to last post Go to first unread
Last post: Yesterday at 2:22:54 PM(UTC)
Lansweeper HP Warranty scan - broken for some products
by  zaty   Go to last post Go to first unread
Last post: Yesterday at 1:52:38 PM(UTC)
Lansweeper How to stop deployments ?
by  Leandro Martins  
Go to last post Go to first unread
Last post: Yesterday at 1:47:22 PM(UTC)