In SQL Server 2012, Server Audit can be created with a predicate expression (refer to
). This predicate expression is evaluated before audit events are written to the audit target. If the evaluation returns TRUE the event is written to the audit target else it's not. Hence one can filter audit records going to the audit target based on the predicate expression.
Consider a server principal ‘foo’ that already exists in SQL Server. This principal has server_principal_id of 261. Now following server audit will write all the audit events (configured in audit specification) generated by this principal (with id 261) to file target. It will not write audit events generated by other principals in SQL Server to the target.
CREATE SERVER AUDIT AuditDataAccessByPrincipal
TO FILE (FILEPATH ='C:\SQLAudit\' )
WHERE SERVER_PRINCIPAL_ID = 261
Now, in order to use action_id field as a predicate in the predicate expression, one has to provide integer value of action_id. Specifying a character code value for action_id results in following error:
CREATE SERVER AUDIT AuditDataAccessByAction_Id
TO FILE ( FILEPATH ='C:\SQLAudit\' )
WHERE ACTION_ID = 'SL'
Msg 25713, Level 16, State 23, Line 1
The value specified for event attribute or predicate source, "ACTION_ID", event, "audit_event", is invalid.
This is because internally action_id is stored as an integer value. sys.fn_get_audit_file DMV converts the integer value to a character code value for two main reasons:
1) Readability: Character code is more readable then integer value
2) Consistency with our internal metadata layer where we define such mapping between integer value and character code.
The above explanation also applies for class_type field that we have in sys.fn_get_audit_file.
Following functions will help to get around above mentioned problem with action_id and class_type fields.
1) This function converts action_id string value of varchar(4) to an integer value which can be used in the predicate expression.
create function dbo.GetInt_action_id ( @action_id varchar(4)) returns int
declare @x int
SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 1, 1))))