Início / dba / Perguntas / 309901
Accepted
Siva Dasari
Asked: 2022-03-18 18:14:00 +0800 CST

Eventos estendidos capturam todas as chamadas por login/usuário e texto sql contendo um valor

  • 772

Estou usando abaixo a sessão de eventos estendidos para capturar os dados que contém um valor 9999por login xEventsTest. Mas não vejo nenhum dado capturado com os filtros adicionados, mas pude ver essa chamada quando nenhum filtro foi adicionado aos eventos.

Não tenho certeza do que estou perdendo aqui,

    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

Proc de teste que estou usando:

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

Executando proc fazendo login como xEventsTestusuário:

exec getID @ID=9999
sql-server sql-server-2019

1 respostas

  1. Best Answer

    Capaz de descobrir o problema, faltando o caractere curinga %para o operador 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
    • 4

relate perguntas