(last updated: 2019-11-12 @ 23:40 EST / 2019-11-13 @ 04:40 UTC )
(related post: Server Audit Mystery 2: Filtering action_id gets Error Msg 25713 )
The other day I ran into an odd problem trying to answer the following question on DBA.StackExchange:
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:
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:
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:
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:
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
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
file_name is also
VARCHAR, but it is explicitly not a filterable field). Strange indeed. Here is what we know so far:
action_idare filterable, unlike
=operator is defined, unlike what we saw with
class_typefield returned by
sys.fn_get_audit_filecontains values such as: "A", "V", "U", "FN", and "P".
- 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
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
then 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
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
master.dbo.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.
Documentation correction has been submitted via Pull Request (PR) #363.
The documentation corrections are now live 😺 .