0

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
2
  • Have you tried `[nt_user] IN (Select lname from @Tbl)? – Lennart Jul 23 '20 at 20:09
  • I did and unfortunately it doesn't like IN as part of the syntax, even if I tried to do only two hardcoded options. – datadawg2000 Jul 23 '20 at 20:47
4

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.

7
  • appreciate it, any thoughts then on filtering from an NT_Group instead of an NT_User? My main goal is to track when a member of an NT_group logs into to a database. I was trying to use a table populated by xp_logininfo data to do this, because I don't see an nt_group option. – datadawg2000 Jul 23 '20 at 20:32
  • If the user has access through a group the logon event still shows the user's individual identity, not the group. – David Browne - Microsoft Jul 23 '20 at 20:51
  • That is ok for my purposes, but I am hoping to collect only the logins from that group. I only need to filter behind the scenes. For example, to filter from an AD group called 'Domain\DB_Group', even if the events only show that individual user login when recorded. This way, only login events relevant to me will show up in my event_file – datadawg2000 Jul 23 '20 at 20:57
  • Collect them all and filter them later. Logon is not a high-velocity event. – David Browne - Microsoft Jul 23 '20 at 20:58
  • Wouldn't the filtering be the same though, where on the event_file, I can only filter where nt_username = 'specificname', rather than filtering members of an AD group, because that field isn't being captured? It is confusing because if I can filter logins to a specific database, it should be the same impact as filtering members of an AD group – datadawg2000 Jul 23 '20 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.