Initial Filling of User-Relation by using last logged on user

[Filo]

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!

[vladygin]

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?

[Filo]

Hi 🙂

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!

[wilkinsonian]

"tsysAssetRelationTypes, 1 being 'owned by'."

Is "Used by" 11?

[Susan_A]

FYI for everyone: to be safe, we recommend backing up your database prior to running any scripts.

[Filo]

Thank you 🙂
Exactly that worked for our case. Excellent!

[Karel_DS · Accepted solution]

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'.