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,535  
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 Drive Encryption statuses
by  JacobH   Go to last post Go to first unread
Last post: Today at 5:41:10 PM(UTC)
Report Center Local admin group report based on domain role
by  JacobH  
Go to last post Go to first unread
Last post: Today at 5:24:13 PM(UTC)
Lansweeper Number of scanned IPs
by  wayneRex   Go to last post Go to first unread
Last post: Today at 10:21:01 AM(UTC)
Lansweeper Ticket Summary by Agent?
by  susan.starr  
Go to last post Go to first unread
Last post: Yesterday at 4:41:26 PM(UTC)
Report Center Virtual machines and their host
by  klaus   Go to last post Go to first unread
Last post: Yesterday at 8:44:57 AM(UTC)
Lansweeper Patch Tuesday report, last 3 months
by  Esben.D  
Go to last post Go to first unread
Last post: 9/16/2019 1:38:33 PM(UTC)
Lansweeper Query on 'Assets: Installed software by computer' Report
by  RC62N   Go to last post Go to first unread
Last post: 9/12/2019 5:12:40 PM(UTC)
Lansweeper Adding Comments
by  AZHockeyNut  
Go to last post Go to first unread
Last post: 9/10/2019 7:39:40 PM(UTC)