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.
