How do filter Action column on SQL Database Audit Specification?

Question

Hi Expert,

How to exclude Column [Action ID] with record 'SELECT','EXECUTE' & 'VIEW' action?

I've filtered [Action ID] using conversion to INT type to getting ID of the action(Select,Execute,View)

but it still not worked ..

any suggestion?

great thanks for all respond

warm regards,

UrBel

Answer 1

In SQL Server Audit, the action_id column in the audit log is a varchar(4), not an int. To filter out specific actions such as SELECT, EXECUTE, and VIEW in a database audit specification, use the audit action names and their corresponding action_id values from sys.dm_audit_actions, and filter on the character action_id (not by converting it to int).

General approach:

1. Find the action_id values for the actions to exclude:

      SELECT action_id, name
      FROM sys.dm_audit_actions
      WHERE name IN ('SELECT', 'EXECUTE', 'VIEW DEFINITION');
   

2. Use those action_id values in the audit predicate or in queries against the audit log, for example:

      SELECT *
      FROM sys.fn_get_audit_file('your_audit_path', NULL, NULL)
      WHERE action_id NOT IN ('SLCT', 'EX', 'VWDF');  -- sample IDs; use actual values from step 1
   

Key points:

• action_id in the audit log and in sys.dm_audit_actions is varchar(4) and should be compared as text.
• Use sys.dm_audit_actions to map between the human-readable action name (SELECT, EXECUTE, VIEW DEFINITION, etc.) and the action_id used in audit records.
• Do not convert action_id to int for filtering; that breaks the match and causes the filter to fail.

---

References:

• SQL Server Audit Records
• sys.dm_audit_actions (Transact-SQL)
• sys.database_audit_specification_details (Transact-SQL)