SQL Server Extended Events Filtering by Database Name

Problem

In my previous tip about SQL Server Extended Events, I discussed the trade-offs involved with choosing to store the database name instead of looking up the name via database_id later. At the time, we decided to use the latter approach since we are generally not concerned about the edge case where DDL is performed against a database that is then dropped before collection occurs.

Something else that has since come up: we want to filter out activity against a set of databases matching a specific name pattern since the DDL activity there is system-generated and does not need to be audited. We quickly learned that you have to be careful with how you use the database name in an Extended Events predicate.

Solution

Before we look at how to filter the activity, let’s cover what our Extended Events session looks like that we are using to collect the data.

Our Extended Events session is pretty simple:

CREATE EVENT SESSION TheDDLSession 
ON SERVER 
ADD EVENT sqlserver.object_altered
(
    ACTION(sqlserver.server_principal_name, sqlserver.sql_text, sqlserver.database_name, ...)
    WHERE ([database_id] <> (2))
)
ADD TARGET ...;

This is filtering out any activity against tempdb.

But, we want to also filter out any activity against a database name with the pattern &apos;XYZ[_]%&apos; (refer to this tip to understand LIKE syntax). Since we can no longer rely on database_id, the intuitive thing for me was to add a predicate against sqlserver.database_name as follows:

 
    WHERE ([database_id] <> (2)) AND NOT (sqlserver.database_name LIKE N'XYZ[_]%')
 

Testing some DDL statements, the results were surprising. I expected the predicate to ignore any action against an object in either tempdb or a database named XYZ_ABC. Here I ran ALTER object commands against three different objects; one in a user database, one in tempdb, and one in a user database matching the pattern I wanted to exclude. I ran those three statements from the context of each of the three databases:

USE userdb
GO
ALTER TABLE userdb.dbo.t1 ADD from_userdb intshould be captured
GO
ALTER TABLE tempdb.dbo.t1 ADD from_userdb int
GO
ALTER TABLE XYZ_01.dbo.t1 ADD from_userdb int

What ended up happening in this case:

USE userdb
GO
ALTER TABLE userdb.dbo.t1 ADD from_userdb intwas captured; great!
GO
ALTER TABLE tempdb.dbo.t1 ADD from_userdb intwas not captured; great!
GO
ALTER TABLE XYZ_01.dbo.t1 ADD from_userdb intwas captured; wrong!

I wondered what was going wrong. I realized (and confirmed with my pal Jonathan Kehayias) that my predicate should use the action’s database_name, not sqlserver.database_name from the event. So, I changed the session again, to:

ADD EVENT sqlserver.object_altered
(
    ACTION(sqlserver.server_principal_name, sqlserver.sql_text, sqlserver.database_name, ...)
    WHERE ([database_id] <> (2)) AND NOT (database_name LIKE N'XYZ[_]%')
)

Then, I ran the above commands (but am focusing here on what happens when the target object is in database XYZ_01):

USE userdb
ALTER TABLE XYZ_01.dbo.t1 ADD from_userdb intwas captured; wrong!
GO
USE tempdb
ALTER TABLE XYZ_01.dbo.t1 ADD from_tempdb intwas captured; wrong!
GO
USE XYZ_01
ALTER TABLE XYZ_01.dbo.t1 ADD from_xyz_01 intwas captured; wrong!

I went back and realized why this happened: database_name the action is an empty string when you haven’t explicitly collected the database name. An empty string is definitely NOT LIKE N&apos;XYZ[_]%&apos;, so as a result it wasn’t filtered out. I changed the session again:

ADD EVENT sqlserver.object_altered
(
    SET collect_database_name = (1)
    ACTION(sqlserver.server_principal_name, sqlserver.sql_text, sqlserver.database_name, ...)
    WHERE ([database_id] <> (2)) AND NOT (database_name LIKE N'XYZ[_]%')
)

And voila:

USE userdb
ALTER TABLE XYZ_01.dbo.t1 ADD from_userdb intwas not captured; great!
GO
USE tempdb
ALTER TABLE XYZ_01.dbo.t1 ADD from_tempdb intwas not captured; great!
GO 
USE XYZ_01
ALTER TABLE XYZ_01.dbo.t1 ADD from_xyz_01 intwas not captured; great!

I rounded out the testing with a session that collected the database name but still used sqlserver.database_name</code>; it behaved incorrectly, as expected.</p> <p>The total testing matrix looks like this:</p> <div class="imageborder">
<img alt="Testing combinations of events" src="/wp-content/images-tips/6697_sql-server-extended-events-database-name-predicate.001.png" />
</div> <p>You can see that tempdb was always filtered out correctly. It relies on <code>database_id, which doesn’t reflect the context database of the session. You can also see that the context database led to missing events in some cases and collecting too many events in others.

Next Steps

The moral of the story is, if you want to filter on a database name or pattern, you need both SET collect_database_name = (1) and use database_name in your predicate (not sqlserver.database_name). If you don’t do both, you won’t be collecting the right events in all scenarios.

See these related tips and other resources related to Extended Events:

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *