主页 / dba / 问题 / 309901
Accepted
Siva Dasari
Asked: 2022-03-18 18:14:00 +0800 CST

扩展事件通过登录/用户和包含值的 sql 文本捕获所有调用

  • 772

我正在使用下面的扩展事件会话来捕获包含9999login值的数据xEventsTest。但是我没有看到添加了过滤器的任何数据，但是当没有过滤器添加到事件时我可以看到这个调用。

不知道我在这里缺少什么，

    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

我正在使用的测试过程：

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

以xEventsTest用户身份登录执行 proc：

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

1 个回答

  1. Best Answer

    能够找出问题，缺少%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
    • 4

相关问题