Use square brackets on extended events filter

Akhter Khan 1 Reputation point
2022-08-27T07:34:07.803+00:00

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)
)
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 on the filter and then:

SELECT
n.ev.value('@timestamp','datetimeoffset') as [timestamp],
n.ev.value('data@DeezNutz ="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@DeezNutz ="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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,675 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 112.1K Reputation points
    2022-08-27T07:38:36.52+00:00

    It seems that you did not try like '%[[]demo]%' yet.

    Also check this: like '%\[demo\]%' escape '\'.

  2. YufeiShao-msft 7,056 Reputation points
    2022-08-29T05:59:57.417+00:00

    Hi @Akhter Khan ，

    On the SQL Server, square brackets have a special meaning when used in a query filter, in order to match a square bracket notation must be used with a left bracket in the character set, as in "[[]", and the right square bracket does not need any special handling

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.