Database Administrators Asked on December 17, 2021
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.
Answered by David Browne - Microsoft on December 17, 2021
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP