SQL Server Extended event filters: can you use an IN predicate with a subquery?

Posted on

Question :

I have an extended event that tracks and filters login events from specific users. The filter is something like this:

WHERE (([sqlserver].[nt_user]=N'fredJohnson') OR ([sqlserver].[nt_user]=N'sallySmith')))

What I would really like to do is use an IN, and have it filter off a much larger list of users. I have these usernames stored in a table. I want to go the subquery route because this EE will be pushed to many different databases with many different users. So for example, I would want it to do something like this, where the lname column is a list of a few hundred users from a table:

   WHERE (([sqlserver].[nt_user]=N'Select lname from @Tbl')))

But it doesn’t works. Any ideas how to pull this off?

The full EE i’m trying to modify:

CREATE EVENT SESSION [TrackLogins] ON SERVER 
ADD EVENT sqlserver.login(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.session_nt_username,sqlserver.username)
    WHERE (([sqlserver].[nt_user]=N'Select lname from @Tbl')))

WITH (STARTUP_STATE=ON)
GO

Answer :

No. Extended Event filters can’t run queries. Extended event filters need to be very cheap, so you should favor over-collecting events to over-filtering them.

Leave a Reply

Your email address will not be published.