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 Lansweeper Reporting Old Assets as New
by  Jpatterson   Go to last post Go to first unread
Last post: Today at 8:09:00 PM(UTC)
Lansweeper Change History or Restore of Custom Reports?
by  GoHawks5  
Go to last post Go to first unread
Last post: Today at 4:20:52 PM(UTC)
Lansweeper agents can't create new tabs
by  Esben.D   Go to last post Go to first unread
Last post: Today at 3:51:07 PM(UTC)
Lansweeper Schedule/Calendar Ticket
by  enush  
Go to last post Go to first unread
Last post: Today at 3:46:21 PM(UTC)
Lansweeper Admins view all helpdesk tickets
by  Esben.D   Go to last post Go to first unread
Last post: Today at 3:44:50 PM(UTC)
Lansweeper Features Questions
by  datev   Go to last post Go to first unread
Last post: Today at 2:03:03 PM(UTC)
Lansweeper Assets disappearing
by  Ian.Prentice  
Go to last post Go to first unread
Last post: Today at 11:39:52 AM(UTC)