Filter SQL Server Audit on action_id / class_type predicate

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)

...