cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
kschrantz
Engaged Sweeper II
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?
15 REPLIES 15
Kboyer
Engaged Sweeper III
prighi61
Engaged Sweeper III
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
Engaged Sweeper
I'm also waiting for it.
wkorrubel
Engaged Sweeper II
+1
Frédéric
Engaged Sweeper
+1
JDL66
Engaged Sweeper
+1
sgritmacker
Engaged Sweeper
We need this function desperately as well
gwb
Engaged Sweeper
With the ever increasing amount of mobile devices, the ability to add a user relation to the import process would be incredibly helpful.
zblack
Engaged Sweeper III
+1