Query XML Extended Events Data?

SQL Server Administration
1

I want to search Data from Extended Events. I am new to XML & Extended Events
I loaded it into a SQL Table, and trying to search for a character string in the "Statement" tag

Something like this, but I have something wrong.
SELECT top 10 * FROM [EE_Table_20200617]
WHERE Event_data.value('(/event/data/value)[1]', 'nvarchar(max)') LIKE '%sEntcode%'

Also, how do I search for results within an EST Time zone date range, not UTC ?

<event name="sp_statement_completed" package="sqlserver" timestamp="2020-06-17T14:11:03.024Z">
  <data name="source_database_id">
    <value>5</value>
  </data>
  <data name="object_id">
    <value>1056279814</value>
  </data>
  <data name="object_type">
    <value>20038</value>
    <text>FUNCTION</text>
  </data>
  <data name="object_name">
    <value />
  </data>
  <data name="statement">
    <value>SET @nPos = charindex( N'#', @sEntcode )
	</value>
  </data>
</event>
2 
;with src
as
(
select N.value('value[1]', 'varchar(max)') as zoro
  from EE_Table_20200617 as T
 cross apply T.Event_data.nodes('/event/data') as I(N)
)
select * 
  From src 
 Where zoro LIKE '%sEntcode%'
1 Like
3

yosiasz, Thanks !

Do you also know how I can incorporate searching a date range, and include the date-time in the result ?
The first line shows timestamp as

timestamp="2020-06-17T14:11:03.024Z"

I want to select records between 2020-06-17 10:00:00 and 2020-06-17 11:30:00 in my server time zone, which is 5 hours earlier than UTC.

4 
;with src
as
(
select N.value('@timestamp[1]', 'datetime') as zoro
  from #xml as T
 cross apply T.Event_data.nodes('/event') as I(N)
)
select * 
From src 
where zoro between yadi and yada
1 Like
5

Thanks again !

6

How does this correlate to the field with the SQL statement in it ?
I'm trying to figure out how to include other "fields" in the select, if I had LoginName, or database name etc...

N.value('value[1]', 'varchar(max)')
7

Please post a new question with the sample xml data that has the extra fields?

8

Using the same XML as in my first post, and using your SQL reply, how would I also select "object_id" and "object_type" in the same query as result fields, and also used in the WHERE clause ?.
I am confused how 'value[1]' is linked to the SQL statement

9

Something like

;with src
as
(
select N.value('value[1]', 'varchar(max)') as zoro
, N.value('value[2]', 'varchar(max)') as User
, N.value('value[3]', 'varchar(max)') as Database
from EE_Table_20200617 as T
cross apply T.Event_data.nodes('/event/data') as I(N)
)
select *
From src
Where zoro LIKE '%sEntcode%'
AND Database = 'MyDatabase'
AND USER = 'JOE'

10

Does my last question make sense ? Selecting multiple "Fields", with multiple WHERE clause, using the XML in the first post.

11

I dont see those fields in that xml.
You can't query using a WHERE clause when you dont know the column that is after the WHERE clause unless you use some dynamic sql. Are you saying there could be some unknown xml nodes or properties?

12

The "value" syntax is both an xml function to extract data from an xml node but it also happens to be the name of one of you xml node's name

13

Sorry, I used made up names as an example. I am confused about how to specify the nodes to represent the fields. Thanks for your help

Using your example here, if I also want to select additional fields to display and use in the WHERE clause.
Add "source_database_id" and "object_id" to SELECT.
And WHERE "source_database_id" IN ( 5, 6, 7) AND "object_id" In ( 1056279814 , 1056261843 )

;with src
as
(
select N.value('value[1]', 'varchar(max)') as zoro
  from EE_Table_20200617 as T
 cross apply T.Event_data.nodes('/event/data') as I(N)
)
select * 
  From src 
 Where zoro LIKE '%sEntcode%'
14

I will let you do your homework homebrew :slight_smile:

look at this and see what you could do for those other properties

;with src
as
(
select N.value('@timestamp[1]', 'datetime') as zoro
  from #xml as T
 cross apply T.Event_data.nodes('/event') as I(N)
)
select * 
From src

look at what we are doing here for timestamp and then apply the same principle for these other xml properties.

15

I did this the other day to get "Timestamp" and "Statement" together, but they are different in that "Timestamp" is /event while "Statement" is /event/data.

What I am confused about is how to include another field that is also /event/data such as "Object Id" and how the code tells which is which.

;with src
as
(select N.value('value[1]', 'varchar(max)') as 'Statement'
,DATEADD(hh, -4,  M.value('@timestamp[1]', 'datetime')) as 'Date_Time'  -- 4 hours earlier from UTC
  from EE_Execute_20200623 as T
 cross apply T.Event_data.nodes('/event/data') as I(N) 
 cross apply T.Event_data.nodes('/event') as I2(M)
 )
select *
  From src 
 Where Statement LIKE '%exec%'
	AND Date_Time between '2020-06-23 15:00:00.000' AND '2020-06-23 16:30:00.000' 
	ORDER BY DATE_Time desc
16

you had really answered your own question in the above statement

N.value('@name[1]', 'nvarchar(50)') as shaboom
17

I tried adding the "shaboom" line, but don't understand the result. I am trying to add another field value from "Object Name", not the column heading. Sorry for the "Dummy XML" questions. I have not used XML before. I guess my biggest confusion is not understanding how this

"N.value('value[1]', 'varchar(max)') as 'Statement'"

results in the actual SQL statement

Statement ………...…...……....….Date_Time ………………….......shaboom
exec sp_unprepare 34350.......2020-06-23 16:04:41.363.........statement
exec sp_unprepare 34349.......2020-06-23 16:04:41.360.........statement
sp_prepexec………..………......2020-06-23 16:04:41.360......….object_name
sp_prepexec...…...…...……......2020-06-23 16:04:41.357.......object_name



;with src
as
(select N.value('value[1]', 'varchar(max)') as 'Statement'
,DATEADD(hh, -4,  M.value('@timestamp[1]', 'datetime')) as 'Date_Time'  -- 4 hours earlier from UTC
,N.value('@name[1]', 'nvarchar(50)') as shaboom
  from EE_Execute_20200623 as T
 cross apply T.Event_data.nodes('/event/data') as I(N) 
 cross apply T.Event_data.nodes('/event') as I2(M)
 )
select top 4 *
  From src 
 Where Statement LIKE '%exec%'
AND Date_Time between '2020-06-23 15:00:00.000' AND '2020-06-23 16:30:00.000' 
	AND LEN(statement) < 25
	ORDER BY DATE_Time desc
18

You will need to do some pivoting afterwards

;with src
as
(select N.value('value[1]', 'varchar(max)') as 'Statement',
        DATEADD(hh, -4,  M.value('@timestamp[1]', 'datetime')) as 'Date_Time',  -- 4 hours earlier from UTC
		N.value('@name[1]', 'nvarchar(50)') as _name
   from EE_Table_20200617 as T
  cross apply T.Event_data.nodes('/event/data') as I(N) 
  cross apply T.Event_data.nodes('/event') as I2(M)
 )
select *
  From src 
PIVOT(
    MAX(Statement) 
    FOR _name IN (
        [source_database_id],
		[object_id],
		[object_type],
		[object_name]
		)
) AS pivot_table
where source_database_id = 5
19

Now I'm more confused !

20

I thought I could add another CROSS APPLY to display another value, for source_database_id, like this. But my result is not showing database ID

;with src
as
(select N.value('value[1]', 'varchar(max)') as 'Statement'
,DATEADD(hh, -4,  M.value('@timestamp[1]', 'datetime')) as 'Date_Time'  -- 4 hours earlier from UTC
,P.value('value[1]', 'varchar(max)') as 'source_database_id'	---- **NEW VALUE**
  from EE_Execute_20200623 as T
 cross apply T.Event_data.nodes('/event/data') as I(N) 
 cross apply T.Event_data.nodes('/event') as I2(M)
 cross apply T.Event_data.nodes('/event/data') as I3(P)		---- **NEW VALUE**
 )
select *
  From src 
 Where Statement LIKE '%exec%'
AND Date_Time between '2020-06-23 15:00:00.000' AND '2020-06-23 16:30:00.000' 
	AND Source_Database_id IN ( 5, 6, 7)
	ORDER BY DATE_Time desc