cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Filo
Engaged Sweeper II
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!
1 ACCEPTED SOLUTION
Karel_DS
Champion Sweeper III
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'.

View solution in original post

6 REPLIES 6
vladygin
Engaged Sweeper
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
Engaged Sweeper II
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
Engaged Sweeper II
"tsysAssetRelationTypes, 1 being 'owned by'."

Is "Used by" 11?
Susan_A
Lansweeper Alumni
FYI for everyone: to be safe, we recommend backing up your database prior to running any scripts.
Filo
Engaged Sweeper II
Thank you 🙂
Exactly that worked for our case. Excellent!
Karel_DS
Champion Sweeper III
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'.

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now