000032991 - How to track who deleted a record and when in RSA Archer using a SQL Trigger

Document created by RSA Customer Support Employee on Jun 14, 2016•Last modified by RSA Customer Support Employee on Apr 21, 2017
Version 4Show Document
  • Comment • 0
  • View in full screen mode

Article Content

Article Number000032991
Applies ToRSA Product Set: Archer
RSA Product Version: 5.5.2 and higher
IssueWho 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
  1. Perform a SQL Backup and test in a non-prod environment first.
  2. From SQL Management Studio, right-click the Archer Instance database and select New Query.
  3. 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)

  1. 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

  1. 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

User-added image

 
NotesTo stop using this functionality, delete the SQL Trigger and SQL Table by running the following:
-- Delete the SQL Trigger if it exists
IF OBJECT_ID('TRG_ArcherContentDeleted') IS NOT NULL 
DROP TRIGGER TRG_ArcherContentDeleted 
GO
 
-- Delete the table if it exists
IF OBJECT_ID('ATBContentDeletedHistory') IS NOT NULL 
DROP TABLE ATBContentDeletedHistory 
GO

Attachments

    Outcomes