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

The question:

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

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

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


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 Comment