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

So while you could filter the server audit by the actions, that seems heavy-handed. Why not set up the database audit specification to only capture the operations you want:

CREATE DATABASE AUDIT SPECIFICATION AuditSpec FOR SERVER AUDIT YourServerAudit
   ADD (INSERT, UPDATE, DELETE ON dbo.Orders BY public)
WITH (STATE = ON)

Answer 2

Did you try this?

-- after generating a SELECT and an EXECUTE event, find their integers: 
SELECT DISTINCT action_id, CAST(action_id AS INT) AS action_id_int FROM sys.fn_get_audit_file('C:\YourAuditPath\*.sqlaudit', NULL, NULL) WHERE action_id IN ('SL ', 'EX '); 
-- then: 
ALTER SERVER AUDIT [YourServerAuditName] WITH (STATE = OFF); 
ALTER SERVER AUDIT [YourServerAuditName] WHERE (action_id <> <SELECT_int> AND action_id <> <EXECUTE_int>); 
ALTER SERVER AUDIT [YourServerAuditName] WITH (STATE = ON);

Answer 3

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)