Extended events capture all calls by login/user and sql text containing a value

All we need is an easy explanation of the problem, so here it is.

I’m using below extended events session to capture the data that contains a value 9999 by login xEventsTest. But I don’t see any data captured with the filters added, but I could see this call when no filters are added to the events.

Not sure what I’m missing here,

    CREATE EVENT SESSION [captureByLoginSqlText] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'9999') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'9999')))),
ADD EVENT sqlserver.rpc_starting(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'9999') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'9999')))),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'9999') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'9999')))),
ADD EVENT sqlserver.sp_statement_starting(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'9999') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'9999')))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([sqlserver].[username]=N'xEventstest' AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'9999'))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([sqlserver].[username]=N'xEventstest' AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'9999'))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'9999') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'9999')))),
ADD EVENT sqlserver.sql_statement_starting(SET collect_statement=(1)
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'9999') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'9999'))))
ADD TARGET package0.event_file(SET filename=N'captureByLoginSqlText.xel',max_file_size=(50),max_rollover_files=(20))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Test proc I’m using:

    create   procedure getID
(
    @ID int
)
as 
BEGIN
    SELECT @ID
END

Executing proc by logging in as xEventsTest user:

exec getID @ID=9999

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Able to figure out the problem, missing wild character % for operator like_i_sql_unicode_string.

CREATE EVENT SESSION [captureByLoginSqlText] ON SERVER 
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'%9999%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%9999%')))),
ADD EVENT sqlserver.rpc_starting(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'%9999%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%9999%')))),
ADD EVENT sqlserver.sp_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'%9999%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%9999%')))),
ADD EVENT sqlserver.sp_statement_starting(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'%9999%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%9999%')))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[username],N'xEventstest') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%9999%'))),
ADD EVENT sqlserver.sql_batch_starting(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([sqlserver].[equal_i_sql_unicode_string]([sqlserver].[username],N'xEventstest') AND [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%9999%'))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'%9999%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%9999%')))),
ADD EVENT sqlserver.sql_statement_starting(SET collect_statement=(1)
    ACTION(sqlserver.database_name,sqlserver.server_principal_name,sqlserver.sql_text)
    WHERE ([package0].[equal_i_unicode_string]([sqlserver].[username],N'xEventstest') AND ([sqlserver].[like_i_sql_unicode_string]([statement],N'%9999%') OR [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%9999%'))))
ADD TARGET package0.event_file(SET filename=N'captureByLoginSqlText.xel',max_file_size=(50),max_rollover_files=(20))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply