Sample Header Ad - 728x90

Use square brackets on extended events filter

5 votes
1 answer
1207 views
I want create an extended events session and use the like_i_sql_unicode_string operator to filter the phrase [demo], with the square brackets. I've started with:
CREATE EVENT SESSION [demo] ON SERVER 
ADD EVENT sqlserver.sql_batch_completed(
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%[demo]%'))
)
ADD TARGET package0.ring_buffer
alter event session [demo] on server state=start
But this interprets [demo] as a character group on a regex-like syntax. So if I run this:
-- m
It will be captured on the extended event. The closest I've got was filtering it later, using [sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%demo%') on the filter and then:
SELECT
n.ev.value('@timestamp','datetimeoffset') as [timestamp],
n.ev.value('data[@name="batch_text"]','varchar(max)')  as [batch_text]
FROM sys.dm_xe_session_targets xet
INNER JOIN sys.dm_xe_sessions xe ON xe.[address] = xet.event_session_address
cross apply (select CONVERT(XML, target_data) as xData ) as x
cross apply x.xData.nodes(N'RingBufferTarget/event') AS n(ev)
WHERE xe.name = N'demo' AND xet.target_name = N'ring_buffer'
and n.ev.value('data[@name="batch_text"]','varchar(max)') like '%\[demo\]%' escape '\'
But this still captures more events than necessary. I've tried '%[[]demo[]]%', '%{[}demo{]}%', '%\[demo\]%', '%$[demo$]%', and none of those worked.
Asked by Jorge Bugal (218 rep)
Aug 26, 2022, 06:47 PM
Last activity: Aug 27, 2022, 07:06 AM