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)

...

Comments

  • Anonymous
    October 14, 2012
    Cool. Looks like this blog is finally come alive now :)
  • Anonymous
    March 08, 2013
    really informative article
  • Anonymous
    September 09, 2013
    Cool. Looks like this blog is finally come alive now :)
  • Anonymous
    April 11, 2016
    I have an AD group with a lot of users and don't want to filter user by user. Also users comes and go so I'll need to change the audit filter every time a new left or arrive in the team.So, easy way for me is to filter by AD group. I tried to use IS_MEMBER('Domain\ADGroupName)=1 but SQL Server doesn't accept that in the CREATE SERVER AUDIT command. This is really impossible or there's a workaround?Thank you,Vitor
  • Anonymous
    June 05, 2018
    Interesting. Thanks for sharing this info and the two functions. Somehow I missed this post when I was looking for this info months ago, and so I tried figuring it out on my own.I found that getting the "class_type" values was fairly easy: they are listed in the master.dbo.spt_values tables (and yes, they have been updated across each version of SQL Server). I posted my investigation into "class_type" here: Server Audit Mystery 1: Filtering class_type gets Error Msg 25713 ( https://sqlquantumleap.com/2018/01/22/server-audit-mystery-filtering-class_type-gets-error-msg-25713/ ).Unfortunately, "action_id" was not so simple since those values are not listed in spt_values or any other table I checked (including the extended events meta-data). But, I was at least able to come up with a means of quickly figuring them out, albeit one at a time. I posted that investigation here: Server Audit Mystery 2: Filtering action_id gets Error Msg 25713 ( https://sqlquantumleap.com/2018/01/30/server-audit-mystery-filtering-action_id-gets-error-msg-25713/ ). And, now that I found your post, I updated mine to include a refactored version of your "dbo.GetInt_action_id" function (mostly a single SELECT statement) and used it calculate all possible values :-).At the end of each of those posts is a link to a page containing the full list of values, showing which ones are available for each version of SQL Server, starting with 2012.