Add option to exclude schemas

Nov 6, 2013 at 8:49 PM
I propose to add some setting to exclude some schemas from auditing. For example I often user schema InterfaceIN and InterfaceOUT where I keep interface tables to exchange bulk data with external systems.
Coordinator
Nov 13, 2013 at 1:27 PM
Edited Nov 13, 2013 at 1:27 PM
Hi rosacek,

I will add this feature in the next update. For now though, here is what you can do to implement this functionality...

Currenty in the pAutoAuditAll sp we have the following code in the cursor declaration:
DECLARE cTables CURSOR FAST_FORWARD READ_ONLY
  FOR  SELECT s.name, t.name 
              from sys.tables t
                join sys.schemas s
                  on t.schema_id = s.schema_id
             where t.name not in 
                ('AuditHeader','AuditDetail',
                'SchemaAudit','Audit',
                'AuditHeaderArchive','AuditDetailArchive',
                'LegacyAudit_Migrated','RolePermissions','sysdiagrams')
             and t.name not like 'aspnet_%'
        EXCEPT
        SELECT  SchemaName, TableName
        FROM    AuditAllExclusions
add this code right before the EXCEPT keyword:
        AND s.name not in
            (SELECT SchemaName
            FROM    AuditAllExclusions where TableName = '<All>')
you will end up with something like this...
DECLARE cTables CURSOR FAST_FORWARD READ_ONLY
  FOR  SELECT s.name, t.name 
              from sys.tables t
                join sys.schemas s
                  on t.schema_id = s.schema_id
             where t.name not in 
                ('AuditHeader','AuditDetail',
                'SchemaAudit','Audit',
                'AuditHeaderArchive','AuditDetailArchive',
                'LegacyAudit_Migrated','RolePermissions','sysdiagrams')
             and t.name not like 'aspnet_%'
        AND s.name not in
            (SELECT SchemaName
            FROM    AuditAllExclusions where TableName = '<All>')
        EXCEPT
        SELECT  SchemaName, TableName
        FROM    AuditAllExclusions
Now, in the AuditAllExclusions table add a rows with these values:
SchemaName = InterfaceIN (or any schema you want to exclude)
TableName = <All>

...and all tables in that schema will be excluded.


John
Coordinator
Nov 13, 2013 at 1:31 PM
This discussion has been copied to a work item. Click here to go to the work item and continue the discussion.
Coordinator
Nov 19, 2013 at 6:31 PM
AutoAudit 3.20h had been released and addresses this issue.
Marked as answer by JohnSigouin on 11/19/2013 at 10:31 AM