Asset import and relationship with "owner"

[kschrantz]

Hey folks. there was a post a few years ago about adding a relationship to an asset during the import process and I believe it said it was on the list of customer requests (https://www.lansweeper.com/forum/yaf_postst8267_Import-Assets---Relationships.aspx#post34241)
did anything come of this?
reason I ask is... I am about to import 1500 ipads and 2000 iphones into the system and already have the "owner" of said devices in the csv file (pulled from another system). I don't see any fields in the template to look like they would correspond to a relationship
Alternatively, does someone have the magic SQL that would let me create the relationship based off said csv, in bulk?

[Kboyer]

[prighi61]

Hi Everybody,

as suggested by Bruce.B you can use Custom Fields, moreover you can then insert the asset relationships with the following script.

Assuming that in:
- Custom1 you set the relation type id (as in table tsysAssetRelationTypes)
RelationTypeID Name
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

- Custom2 you set the Name of the Parent Asset

you can execute the following script:

Set IDENTITY_INSERT [lansweeperdb].[dbo].[tblAssetRelations] ON
GO

DECLARE @offset integer;
set @offset = (Select Max(RelationID) from [lansweeperdb].[dbo].[tblAssetRelations]);

insert into [lansweeperdb].[dbo].[tblAssetRelations] (
[RelationID],[ParentAssetID],[ChildAssetID],[Type],[Comments],[Lastchanged],[posX],[posY],[StartDate],[EndDate],[CloudId])
select ROW_NUMBER() OVER(PARTITION BY NULL ORDER BY a.AssetID) + @offset as RelationID,
(select ta.AssetID from tblAssets ta JOIN tblAssetCustom tc on ta.AssetID = tc.AssetID where ta.AssetName = c.[Custom2] ) as ParentAssetID,
a.[AssetID] as ChildAssetID,
c.[Custom1] as Type, -- UsedBy see tsysAssetRelationTypes table
null as Comments,
GETDATE() as Lastchanged,
null as posX,
null as posY,
null as StartDate,
null as EndDate,
null as CloudId
FROM [lansweeperdb].[dbo].[tblAssets] a JOIN [lansweeperdb].[dbo].[tblAssetCustom] c on a.AssetID = c.AssetID
Where c.[Custom1] > '' and c.[Custom2] > ''

Set IDENTITY_INSERT [lansweeperdb].[dbo].[tblAssetRelations] OFF
GO

Or the following more simple version:

insert into [lansweeperdb].[dbo].[tblAssetRelations] (
[ParentAssetID],[ChildAssetID],[Type],[Comments],[Lastchanged],[posX],[posY],[StartDate],[EndDate],[CloudId])
select
(select ta.AssetID from tblAssets ta JOIN tblAssetCustom tc on ta.AssetID = tc.AssetID where ta.AssetName = c.[Custom2] ) as ParentAssetID,
a.[AssetID] as ChildAssetID,
c.[Custom1] as Type, -- UsedBy see tsysAssetRelationTypes table
null as Comments,
GETDATE() as Lastchanged,
null as posX,
null as posY,
null as StartDate,
null as EndDate,
null as CloudId
FROM [lansweeperdb].[dbo].[tblAssets] a JOIN [lansweeperdb].[dbo].[tblAssetCustom] c on a.AssetID = c.AssetID
Where c.[Custom1] > '' and c.[Custom2] > ''

Once executed you should clear the Custom1 and Custom2 fields.

Bye.

Paolo Righi.

[Cristiano]

I'm also waiting for it.

[wkorrubel]

+1

[Frédéric]

+1

[JDL66]

+1

[sgritmacker]

We need this function desperately as well

[gwb]

With the ever increasing amount of mobile devices, the ability to add a user relation to the import process would be incredibly helpful.

[zblack]

+1