Issue | Who deleted a record and when? Unfortunately, Archer does not track who deletes a record unless the Audit Logging feature is used, but this is very verbose.
An enhancement request exists as ARCHCE-3612: Report on user deletion of records. To encourage this enhancement, contact Archer Support to open a support case and add your company to the enhancement request. I also recommend you Join an RSA Archer Working Group & See Upcoming Schedule.
A possible workaround is to use a SQL Trigger, but it is not retroactive and basically captures the Module Id, Level Id, Content Id, Date Deleted, and User Id. Below are the steps to create the new table, create the SQL Trigger, and how to back out those changes if you choose. For more information on SQL Triggers, check out this link.
You will need to run your own queries to get info from the new table. |
Resolution | - Perform a SQL Backup and test in a non-prod environment first.
- From SQL Management Studio, right-click the Archer Instance database and select New Query.
- Create a new SQL Table to store the data when a Content record is deleted by running the following:
-- Create a table similar to the tblIVContent table's schema CREATE TABLE [dbo].[ATBContentDeletedHistory]( [module_id] [int] NOT NULL, [level_id] [int] NOT NULL, [content_id] [int] NOT NULL, [update_date] [datetime] NULL, [update_login] [int] NULL)
- Create the SQL Trigger when a row is deleted from tblIVContent by running the following:
-- Delete the SQL Trigger if it exists IF OBJECT_ID('TRG_ArcherContentDeleted') IS NOT NULL DROP TRIGGER TRG_ArcherContentDeleted GO -- Create the SQL Trigger CREATE TRIGGER TRG_ArcherContentDeleted ON dbo.tblIVContent AFTER DELETE AS BEGIN INSERT INTO ATBContentDeletedHistory ([module_id],[level_id],[content_id],[update_date],[update_login]) SELECT [module_id],[level_id],[content_id],[update_date],[update_login] FROM DELETED END GO
- To get details from the ATBContentDeletedHistory table, run the following:
SELECT mt.module_name, lt.level_name, atb.content_id, atb.update_date, atb.update_login, u.user_display_name FROM ATBContentDeletedHistory atb JOIN tblUser(NOLOCK) u ON u.user_id = atb.update_login JOIN tblLevelTranslation(NOLOCK) lt ON lt.level_id = atb.level_id JOIN tblModuleTranslation(NOLOCK) mt ON mt.module_id = atb.module_id ORDER BY update_date DESC
|