8

I am trying to create a trace in Extended Events filtering with logins, in profiler we can filter with login names but I do not see that option in XE. How do I do that?

1 Answer 1

7

In SQL 2016 there are 5 filter predicates you can use to filter logins.
For NT login/user you can use all 5 but SQL login/user you can use number 3,4 and 5 only.

  1. sqlserver.nt_username-Collect NT username
  2. sqlserver.session_nt_username-Get the current session NT user
  3. sqlserver.server_principal_name-Get the name of the Server Principal in whose context the event is being fired.
  4. sqlserver.username-Get the current username
  5. sqlserver.session_server_principal_name-Get the name of the Server Principal that originated the session in which the event is being fired.

Using tsql, you need to change my code depending on what kind of account and real value you are using.

    ALTER EVENT SESSION [test] ON SERVER 
    ADD EVENT sqlserver.sp_statement_completed(
     WHERE ((((([sqlserver].[username]=N'') 
       AND ([sqlserver].[session_server_principal_name]=N'')) 
       AND ([sqlserver].[server_principal_name]=N'')) 
       AND ([sqlserver].[session_nt_user]=N'')) 
       AND ([sqlserver].[session_server_principal_name]=N'')))
    GO

From GUI:

Once you pick select events, highlight event and click configure.

enter image description here

Under filter set your And/or condition, filed, operator, value. enter image description here

4
  • Is there a reference anywhere for what those different fields are?
    – Rory
    Apr 23, 2022 at 13:26
  • I am not aware of, other than the description within the GUI. I remember doing some testing while answering this question. What exactly you are looking for? Apr 24, 2022 at 13:27
  • Just wondering which of all these fields I need. What’s the difference? Presumably some are populated with windows vs sql auth … but then what?
    – Rory
    Apr 24, 2022 at 15:20
  • ie the subtleties between the different fields aren’t immediately obvious, to me at least. Would be great to see some examples of when they’re different from each other & what values they have in different cases to make it more obvious which to choose.
    – Rory
    Apr 24, 2022 at 15:34

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.