June 21, 2020 at 2:41 am
I am trying to learn Extended Events & XML.
I created and E.E. that captured my running SP_WHO2. But how do I query the E.E. file to find it ?
I ran this, just to find my record, but what's the "Correct" way to query my file to search for Stored Procedure calls, or table activity INSERT, UPDATE, DELETE etc... ?
With trace files, I could query the textdata column, but this EE format has me confused.
SELECT cast(event_data as XML) as event_data
FROM sys.fn_xe_file_target_read_file('D:\SQL Server 2016 SP2\TraceFiles\SQL_Activity_EE_0_132371803292440000.xel', null, null, null)
where event_data like '%who2%'
<event name="sql_batch_completed" package="sqlserver" timestamp="2020-06-21T02:32:47.608Z">
<data name="cpu_time">
<value>47000</value>
</data>
<data name="duration">
<value>170991</value>
</data>
<data name="physical_reads">
<value>2</value>
</data>
<data name="logical_reads">
<value>400</value>
</data>
<data name="writes">
<value>11</value>
</data>
<data name="spills">
<value>0</value>
</data>
<data name="row_count">
<value>174</value>
</data>
<data name="result">
<value>0</value>
<text>OK</text>
</data>
<data name="batch_text">
<value>
execute sp_who2</value>
</data>
<action name="username" package="sqlserver">
<value>MyDomain\MyLogin</value>
</action>
<action name="sql_text" package="sqlserver">
<value>
execute sp_who2</value>
</action>
<action name="session_nt_username" package="sqlserver">
<value>MyDomain\MyLogin</value>
</action>
<action name="nt_username" package="sqlserver">
<value>MyDomain\MyLogin</value>
</action>
<action name="database_name" package="sqlserver">
<value>master</value>
</action>
<action name="client_hostname" package="sqlserver">
<value>MyServerName</value>
</action>
<action name="client_app_name" package="sqlserver">
<value>Microsoft SQL Server Management Studio - Query</value>
</action>
<action name="collect_system_time" package="package0">
<value>2020-06-21T02:32:47.619Z</value>
</action>
<action name="callstack" package="package0">
<value>sqldk.dll+0x0000000000114B71
sqllang.dll+0x0000000000733F4C
sqllang.dll+0x0000000000734027
sqllang.dll+0x0000000000733B39
sqllang.dll+0x0000000000734130
sqllang.dll+0x0000000000734224
sqllang.dll+0x0000000000735131
sqllang.dll+0x0000000000734559
sqllang.dll+0x0000000000734F8F
sqllang.dll+0x0000000000734529
sqllang.dll+0x0000000000734321
sqllang.dll+0x000000000070DD3C
sqllang.dll+0x000000000043BD2D
sqllang.dll+0x0000000000027DAF
sqllang.dll+0x0000000000019DEA
sqllang.dll+0x000000000001FF52
sqllang.dll+0x0000000000020063
sqldk.dll+0x0000000000005B4D
sqldk.dll+0x0000000000005935
sqldk.dll+0x000000000000558D
sqldk.dll+0x000000000002BEB8
sqldk.dll+0x000000000002BF50
sqldk.dll+0x000000000002BB07
sqldk.dll+0x000000000002C1D8</value>
</action>
</event>
June 21, 2020 at 2:43 am
Tried this, but 0 results, although I know there are records.
select top 1 * FROM [EE_Table_20200617]
WHERE Event_data.value('(/event/data/value)[1]', 'nvarchar(max)') LIKE '%EXECUTE%'
Thanks in Advance.
June 22, 2020 at 3:54 am
Also, how do I get the EE date/time to show as local server time, not UTC ?
I want to query within a date range. The beginning of my XML has UTC time. I want to select a 3 hour range, like 2020-06-17 08:00:00 - 2020-06-17 11:00:00
<event name="sql_batch_completed" package="sqlserver" timestamp="2020-06-21T02:32:47.608Z">
June 24, 2020 at 1:11 am
In case anyone else can benefit, I was given this as 1 possible solution.
;with src
as
(
select N.value('value[1]', 'varchar(max)') as SQLstatement
from Extend_Events_Table as T
cross apply T.Event_data.nodes('/event/data') as I(N)
)
select *
From src
Where SQLstatement LIKE '%SearchString%'
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy