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:

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


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. Required fields are marked *