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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply