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
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.