The other day I ran into an odd problem trying to answer the following question on DBA.StackExchange:

How to filter out Scalar Valued Function usage from SQL Server Audit Data?

According to the documentation for CREATE SERVER AUDIT, I should be able to add a WHERE clause (starting in SQL Server 2012) to do simple filtering. The documentation states that the list of fields that can be filtered on is found in the documentation for the [sys.fn_get_audit_file] system function. Selecting from that function showed that the class_type field contains the “object type” (“FN” = Function, “P” = Stored Procedure, etc.) value. That’s just what I needed. So I’m just about done, right? Not so fast!

To see what I mean, execute the following to set up the Server Audit (you don’t need to define the Database Audit Specification in order to see the problem). You will need to either have an existing C:\TEMP\ folder that the Service Account running SQL Server can write to (a non-issue if you are using SQL Server Express LocalDB, since that runs as your Windows Login), OR you will need to (create and) specify a folder that can be written to by SQL Server. If you use a folder other than C:\TEMP\, you will need to change the FILEPATH value below.

USE [master];
CREATE SERVER AUDIT [TestAuditFilter]
TO FILE 
(
    FILEPATH = N'C:\TEMP\',
    MAXSIZE = 0 MB,
    MAX_ROLLOVER_FILES = 1,
    RESERVE_DISK_SPACE = OFF
)
WITH
(
    QUEUE_DELAY = 1000,
    ON_FAILURE = CONTINUE
);
GO

That should complete successfully. Now let’s add the filter:

ALTER SERVER AUDIT [TestAuditFilter]
WHERE ([class_type] = 'FN'); -- VARCHAR(2)?

For some odd reason, that get’s the following error:

Msg 25713, Level 16, State 23, Line XXXXX

The value specified for event attribute or predicate source, “class_type”, event, “audit_event”, is invalid.

Making the value lower-case doesn’t help, nor does prefixing the string with an upper-case N. I tried a few other fields and found that executing the following also receives the same 25713 error:

ALTER SERVER AUDIT [TestAuditFilter]
WHERE ([action_id] = 'EX'); -- VARCHAR(4)?

Now, the documentation (currently) states that only two fields cannot be used for filtering: file_name and audit_file_offset. We can test those two fields by executing the following:

ALTER SERVER AUDIT [TestAuditFilter]
WHERE ([file_name] = 'what')
ALTER SERVER AUDIT [TestAuditFilter]
WHERE ([audit_file_offset] = 9)

which returns the following two errors:

Msg 25706, Level 16, State 8, Line XXXXX

The event attribute or predicate source, “file_name”, could not be found.

Msg 25706, Level 16, State 8, Line XXXXX

The event attribute or predicate source, “audit_file_offset”, could not be found.

Those two errors are expected / consistent with what the documentation states. However, executing the following statement:

ALTER SERVER AUDIT [TestAuditFilter]
WHERE ([event_time] = 5)

receives the following error:

Msg 25715, Level 16, State 2, Line XXXXX

The predicate on event “audit_event” is invalid. Operator ‘=’ is not defined for type “filetime”, event attribute or predicate source: “event_time”.

This error is slightly different. It indicates that the field event_time is found, unlike the previous two, but it doesn’t work due to the datatype — DATETIME2 — not being filterable. It appears that someone forgot to either define the operator(s) that should work with this datatype (no other field has this datatype), or to put this field in the exclude list so that it would not be found (hence getting the same error as file_name and audit_file_offset). The end-result is the same, though: this field cannot be used for filtering.

Returning our attention to class_type, I find it interesting that it and the other field that gets the same error, action_id, are the only two filterable fields that are of type VARCHAR (yes, file_name is also VARCHAR, but it is explicitly not a filterable field). Strange indeed. Here is what we know so far:

1. class_type and action_id are filterable, unlike file_name and audit_file_offset.
2. The = operator is defined, unlike what we saw with event_time.
3. The class_type field returned by sys.fn_get_audit_file contains values such as: “A”, “V”, “U”, “FN”, and “P”.
4. String values are not valid, for whatever reason, in Server Audit predicates.

Given the above information I wondered if class_type was an enum (i.e. enumeration / lookup list) and so tried the following:

ALTER SERVER AUDIT [TestAuditFilter]
WHERE ([class_type] = 1);

And that worked!! So, class_type accepts a number instead of a string (and yes, the same turns out to be true for action_id). Now the only question is: what values correspond to which object types? The documentation won’t be of any help given that it doesn’t even indicate that these two fields can only be filtered on numbers even though they return strings from sys.fn_get_audit_file.

I remembered that there is a list of values in SQL Server: master.dbo.spt_values. It does not seem to be used much, but I do recall looking through them a while back, so maybe it’s time to take a closer look:

SELECT spt.[type], spt.[name], spt.[number]
FROM [master].[dbo].[spt_values] spt
ORDER BY spt.[type], spt.[name];

Scrolling through the results, there were two “types” (i.e. groups / categories) that looked promising: EOB and EOD. Between those two, EOD seemed like a better choice to try first since there are a bunch of duplicates in EOB. The “number” for the FUNCTION SCALAR SQL “name” in “type” EOD is: 20038.

Using that value, I ran the following:

ALTER SERVER AUDIT [TestAuditFilter]
WHERE ([class_type] = 20038); -- FUNCTION SCALAR SQL

re-enabled the Server Audit, and executed both a Stored Procedure and a Scalar UDF. This time, and for the first time, only the Scalar UDF was captured in the audit. Woo hoo!!

And, just to be extra-special sure that I had found the correct set of values, I disabled the Server Audit and executed:

ALTER SERVER AUDIT [TestAuditFilter]
WHERE ([class_type] = 8272); -- STORED PROCEDURE

I then re-enabled the Server Audit, and executed both a Stored Procedure and a Scalar UDF again. This time only the Stored Procedure was captured in the audit. Again, woo hoo!!

At this point it should be safe enough to conclude that we can use the following query to find the possible values for class_type:

SELECT spt.[name], spt.[number]
FROM   [master].[dbo].[spt_values] spt
WHERE  spt.[type] = N'EOD'
ORDER BY spt.[name];

TO SEE THE COMPLETE LIST OF FILTERABLE VALUES, please visit: Server Audit Filter Values For [class_type]

I couldn’t find a category in spt_values that corresponds to action_id, so a future post will show the investigation into those values.

I will submit a correction on GitHub for CREATE SERVER AUDIT.

UPDATE 2018-01-23

Documentation correction has been submitted via Pull Request (PR) #363.

UPDATE 2018-01-25

The documentation corrections are now live .