Notification

Icon
Error

Sync information - Information between 2 assets

Posted: Thursday, September 16, 2021 9:20:11 PM(UTC)
Jay-IT

Jay-IT

Member Original PosterPosts: 1
0
Like
On a computer asset, I entered information in the building and department section. Is it possible that this information is dynamically updated with the connected assets (relations) like monitors?

I need to create a report every year on all equipements in each building. It will be very usefull to track equipements that can be move without IT intervention.

Thanks!
prighi61
#1prighi61 Member Posts: 21  
posted: 9/24/2021 11:14:07 AM(UTC)
I think you should create a database trigger that traps tblAssets changes and tsysAssetRelationTypes additions so to keep child assets updated. Be aware that a child asset could have more than one parent, maybe you should filter on the Relation Type field.
prighi61
#2prighi61 Member Posts: 21  
posted: 9/24/2021 1:08:51 PM(UTC)
The first trigger could be:

use [lansweeperdb]
go

-- drop trigger dbo.UpdateChildAsset
-- go

CREATE TRIGGER dbo.UpdateChildAsset
ON tblAssetCustom
AFTER UPDATE As

BEGIN
SET NOCOUNT ON;

DECLARE @Building nvarchar(max);
DECLARE @ParentAssetID int;

SELECT @Building = Building FROM inserted;
SELECT @ParentAssetID = AssetID FROM inserted;

UPDATE tblAssetCustom SET Building = @Building where tblAssetCustom.AssetID in
(Select tblAssetRelations.ChildAssetID from tblAssetRelations
WHERE tblAssetRelations.ParentAssetID = @ParentAssetID
)
END
prighi61
#3prighi61 Member Posts: 21  
posted: 9/24/2021 2:34:06 PM(UTC)
use [lansweeperdb]
go

drop trigger dbo.UpdateChildAsset
go

CREATE TRIGGER dbo.UpdateChildAsset
ON tblAssetCustom
AFTER UPDATE As

BEGIN
SET NOCOUNT ON;

DECLARE @Building nvarchar(max),
@Department nvarchar(max),
@ParentAssetID int;

SELECT @Building = Building, @Department = Department FROM inserted;
SELECT @ParentAssetID = AssetID FROM inserted;

UPDATE tblAssetCustom SET Building = @Building, Department = @Department where tblAssetCustom.AssetID in
(Select tblAssetRelations.ChildAssetID from tblAssetRelations
WHERE tblAssetRelations.ParentAssetID = @ParentAssetID
)
END

drop trigger dbo.UpdateAssetFromParent
go

CREATE TRIGGER dbo.UpdateAssetFromParent
ON tblAssetRelations
AFTER INSERT As

BEGIN
SET NOCOUNT ON;

DECLARE @Building nvarchar(max),
@Department nvarchar(max);
DECLARE @ChildAssetID int;

SELECT Top 1 @Building = Building, @Department = Department FROM inserted, tblAssetCustom where tblAssetCustom.AssetID = inserted.ParentAssetID;
SELECT @ChildAssetID = ChildAssetID FROM inserted;

UPDATE tblAssetCustom SET Building = @Building, Department = @Department where tblAssetCustom.AssetID = @ChildAssetID;

END

Active Discussions

Lansweeper Chrome 95 Audit
by  Esben.D  
Go to last post Go to first unread
Last post: Yesterday at 11:14:31 AM(UTC)
Lansweeper Detecting and Reporting on Programs in Apps & Features
by  LANGuy  
Go to last post Go to first unread
Last post: 10/18/2021 6:09:11 PM(UTC)
Lansweeper OS: Not latest Build of Windows 10 not working
by  jmm25   Go to last post Go to first unread
Last post: 10/18/2021 12:48:41 PM(UTC)
Lansweeper AD DistinguishedName of Computer object
by  elKastr0nom   Go to last post Go to first unread
Last post: 10/14/2021 6:52:50 PM(UTC)
Lansweeper Report if Credential Guard is active
by  ericatbrandmauer  
Go to last post Go to first unread
Last post: 10/14/2021 10:28:15 AM(UTC)