CODEWITHSUNDEEP
sql-server
ohad mesika
ohad mesika

Reputation: 79

SQL Server Extended Events - find particular string in the syntax

How can I check with SQL Server Extended Events if some string appears in the SQL query statement?

For example: I want to find all the queries that contain the string ord_id=4 from 12:00 to 15:00 every day.

How can I trace this with extended events?

Thanks, Ohad

Upvotes: 1

Views: 1372

Answers (2)

Hakan Hifzioglu
Hakan Hifzioglu

Reputation: 35

You can use this Query to read Extended Events

SELECT CONVERT(XML, event_data) XMLEventData FROM sys.fn_xe_file_target_read_file(N'PathForTheFile\*.xel', NULL, NULL, NULL)

if you write * this means read all files, if you know where to look you can write your file name instead of *

After this to make it easy to the eye use the Query below, you need to modify the query for your needs.

SELECT            

     xexml.value('(./action[@name="username"]/value)[1]', 'varchar(400)') as UserName                 

    ,xexml.value('(./action[@name="client_hostname"]/value)[1]', 'varchar(400)') as Client_Hostname 

    ,xexml.value('(./action[@name="collect_system_time"]/value)[1]', 'datetime') as ProcessTime                 

    ,xexml.value('(./data[@name="statement"]/value)[1]', 'nvarchar(4000)') as SQLStatement                

 FROM                 
(                
 SELECT CONVERT(XML, event_data) XMLEventData FROM sys.fn_xe_file_target_read_file(N'PathOfYourFiles\*.xel', NULL, NULL, NULL) f
) AS EventTable                 

CROSS APPLY XMLEventData.nodes('/event') n (xexml)                
WHERE cast(xexml.value('(./action[@name="collect_system_time"]/value)[1]', 'datetime')as time) between '12:00:00' and '15:00:00'
AND  xexml.value('(./data[@name="statement"]/value)[1]', 'nvarchar(4000)') like '%ord_id=4%'

Upvotes: 1

TheGameiswar
TheGameiswar

Reputation: 28930

In the session wizard,select sql statement starting and in global fields ,select sql text like below

enter image description here

and in final screen, you can filter as shown in below screenshot

enter image description here

Upvotes: 1

Related Questions