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
Improve this question
2
  • Have you tried `[nt_user] IN (Select lname from @Tbl)?
    – Lennart - Slava Ukraini
    Commented Jul 23, 2020 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
    Commented Jul 23, 2020 at 20:47

1 Answer 1

Reset to default
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.

Improve this answer
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
    Commented Jul 23, 2020 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
    Commented Jul 23, 2020 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
    Commented Jul 23, 2020 at 20:57
  • Collect them all and filter them later. Logon is not a high-velocity event.
    – David Browne - Microsoft
    Commented Jul 23, 2020 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
    Commented Jul 23, 2020 at 21:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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