In SQL Server 2012, Server Audit can be created with a predicate expression (refer to MSDN ). 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.
Predicate can refer to any of the audit fields described in sys.fn_get_audit_file (Transact-SQL) except file_name and audit_file_offset.
For example:
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
GO
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'
GO
Error:
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
begin
declare @x int
SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 1, 1))))
if LEN(@action_id)>=2
SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 2, 1)))) * power(2,8) + @x
else
SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,8) + @x
if LEN(@action_id)>=3
SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 3, 1)))) * power(2,16) + @x
else
SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,16) + @x
if LEN(@action_id)>=4
SET @x = convert(int, convert(varbinary(1), upper(substring(@action_id, 4, 1)))) * power(2,24) + @x
else
SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,24) + @x
return @x
end
Select dbo.GetInt_action_id ('SL') as Int_Action_Id
Int_Action_Id
------------------
538987603
Following command will now succeed.
CREATE SERVER AUDIT AuditDataAccessByAction_Id
TO FILE ( FILEPATH ='C:\SQLAudit\' )
WHERE ACTION_ID = 538987603
GO
2) This function converts class_type string value of varchar(2) to an integer value which can be used in the predicate expression.
create function dbo.GetInt_class_type ( @class_type varchar(2)) returns int
begin
declare @x int
SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 1, 1))))
if LEN(@class_type)>=2
SET @x = convert(int, convert(varbinary(1), upper(substring(@class_type, 2, 1)))) * power(2,8) + @x
else
SET @x = convert(int, convert(varbinary(1), ' ')) * power(2,8) + @x
return @x
end
go
Select dbo.GetInt_class_type ('A') as Int_class_type
Int_class_type
-------------
8257
Following command will now succeed.
CREATE SERVER AUDIT ClasstypeAuditDataAccess
TO FILE ( FILEPATH ='C:\SQLAudit\' )
WHERE CLASS_TYPE = 8257
GO
Following audit record will be generated for Server Audit (‘A’) class type.
ALTER SERVER AUDIT ClasstypeAuditDataAccess
WITH (STATE = ON)
...