Can I configure SQL Server Auditing feature to monitor just modifications\deletion and ignore addition of data?

Email It to Me Print this Page
Question Can I configure SQL Server content changes auditing feature to monitor just modifications\deletion and ignore addition of data?
Answer There is a way to configure SQL content audit to monitor certain change types (INSERT, UPDATE, DELTE) within a target DB:
  1. Enable Database content audit (Audit data changes in Netwrix Auditor 9.0+)
  2. Specify target databases/tables with INCLUDE filter
  3. Run data collection
  4. After it finishes, open SQL Management Studio, go to Database_name->tables->table_name->triggers->right click on “netwrix_audit_trg_...” -> Script trigger as -> Create to -> new query editor, find lineAFTER INSERT, UPDATE, DELETE” and modify it in that way “AFTER UPDATE, DELETE”
  5. Go to Database_name->Programmability->Stored procedures->right-click sp_netwrix_writeevent->script procedure as->create to->new query editor
  6. Go back to SQL content audit settings in Netwrix Auditor, change the filter for target database/tables to EXCLUDE type
  7. Run data collection
  8. After it finishes, go back to SQL Management Studio, click on EXECUTE button when CREATE PROCEDURE query is opened.
  9. Open next query CREATE TRIGGER, click EXECUTE
                Modify this query for each target table here: SET @table_name = 'dbo.target_table_name'  and execute it.
The idea is to modify content audit triggers manually and add target db to exclude list so Netwrix Auditor could not modify triggers (restore INSERT). Despite the fact that the DB will be shown as excluded in the product, audit data will be logged and collected.
Was this information helpful?