Easily Shred Event Data

It has been nearly a year since I started an extensive series about Extended Events. Previous to that start, I had already written a bunch of articles about Extended Events.

Among the batch of articles preceding the 60 day series one can find an article about shredding extended event data. Then as a part of the series (the first article in the 60 day series) one can find how to shred the action payload data attached to an extended event session.

You can read the article on shredding XEvent payload data here. Then you can follow that up by reading how to shred the Action data here. And once you are done with those and you really want to learn more about Extended Events, please read the whole series which can be found here.

All of that reading to catch up should keep you busy for a tad bit.

Upgrade

A year is a really long time to go without updating a script – according to some. Today, I have an update for both of the XML shredding scripts you just read about in the list of articles mentioned moments earlier in this post.

Before I dive into the script, let’s revisit a sample of the XML from an event session payload. Within an event session, you can see that there are nodes for both the data and the actions (highlighted in green and red). With all of this information found within the same XML for the session, it somewhat makes sense to try and parse all of the data at once.

In addition to parsing all of the XML for the data and the actions at the same time, it seems to also make sense to generate the statements that would parse the XML within a single effort. As you would have noted, my previous scripts were just that – scripts. That implies executing a separate script for each the data and the actions. Maybe it would make more sense to execute a single script.

It is that notion of a single script that constitutes this upgraded version of the script.

One Script

In the following script, I have tried to accomplish just that – a single script to create the entire XML parser for me, for you, and for anybody wishing to use it. I don’t want to have to remember the subtle nuances of how to parse each of the events each time I need to parse the session data. I want something that is quick, easy, and repeatable.

With all of that said, here is the script that I now use to parse my session data. You should notice that it has been simplified and is more extensive now.

DECLARE @EventName VARCHAR(64) = NULL --'query_post_execution_showplan'--'sp_statement_completed'
	,@ReadFlag VARCHAR(64) = 'readonly' --NULL if all columntypes are desired
	,@SessionName	VARCHAR(128) = 'AuditDeprecated' --'system_health' --NULL if all Sessions are desired --'Deadlock' --NULL
	,@UniqueEventColumns	TINYINT = 1 --1 for unique payload columns across all events within the session, NULL for all columns in session
	,@UniqueActions TINYINT = 1 --1 for unique actions, NULL for all actions in the session

/*gen tsql to parse session payload */
SELECT '/* parse the data */
IF EXISTS (SELECT OBJECT_ID(''tempdb.dbo.#xmlprocess''))
BEGIN
DROP TABLE #xmlprocess
END

SELECT CAST ([t2].[event_data] AS XML) AS event_data, t2.file_offset,t2.file_name, cte1.event_session_id--, '' AS event_predicate
	INTO #xmlprocess
	FROM ( SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),''.xel'',''*.xel'') AS targetvalue, ses.event_session_id
				FROM sys.server_event_sessions ses
					INNER JOIN sys.server_event_session_fields sesf
						ON ses.event_session_id = sesf.event_session_id
					--INNER JOIN sys.server_event_session_events sese
					--	ON ses.event_session_id = sese.event_session_id
				WHERE sesf.name = ''filename''
					AND ses.name = ''' + @SessionName +'''
					) cte1
				OUTER APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue,	NULL, NULL, NULL) t2
		;


SELECT x.event_data.value(''(event/@name)[1]'', ''varchar(50)'') AS event_name
		, x.event_data.value(''(event/@package)[1]'', ''varchar(50)'') AS package_name
		, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),
					x.event_data.value(''(event/@timestamp)[1]'',
										''datetime2'')) AS [timestamp]';

WITH presel AS (
SELECT oc.OBJECT_NAME AS EventName
		,oc.name AS column_name, oc.type_name
		,',event_data.value(''(event/data[@name="' + oc.name + '"]/value)[1]'',''' + 
			CASE 
				WHEN ISNULL(xmv.name,'') = ''
					AND oc.type_name = 'guid'
				THEN 'uniqueidentifier'
				WHEN ISNULL(xmv.name,'') = ''
					AND oc.type_name = 'boolean'
				THEN 'bit'
				WHEN ISNULL(xmv.name,'') = ''
					AND oc.type_name = 'callstack'
				THEN 'varchar(max)'
				WHEN ISNULL(xmv.name,'') = ''
					AND oc.type_name <> 'unicode_string'
					AND oc.type_name <> 'ansi_string'
					AND oc.type_name <> 'ptr'
					AND oc.type_name NOT LIKE '%int%'
				THEN oc.type_name
				WHEN ISNULL(xmv.name,'') = ''
					AND oc.type_name LIKE '%int%'
				THEN 'bigint'
				ELSE 'varchar(max)' END + ''') AS ' + oc.name + '' AS ColumnXML
		,oc.column_type AS column_type
		,oc.column_value AS column_value
		,oc.description AS column_description
		,ca.map_value AS SearchKeyword
		,sesea.SessionName
		,rownum = ROW_NUMBER() OVER (PARTITION BY sesea.SessionName, oc.name ORDER BY oc.object_name)
	FROM sys.dm_xe_object_columns oc
	-- do we have any custom data types
		OUTER APPLY (SELECT DISTINCT mv.name FROM sys.dm_xe_map_values mv
			WHERE mv.name = oc.type_name
			AND mv.object_package_guid = oc.object_package_guid) xmv
	--just get the unique events that are tied to a session on the server (stopped or started state)
		CROSS APPLY (SELECT DISTINCT sese.name,ses.name AS SessionName
						FROM sys.server_event_session_events sese
							INNER JOIN sys.server_event_sessions ses
								ON sese.event_session_id = ses.event_session_id) sesea
	--keyword search phrase tied to the event
		CROSS APPLY (SELECT TOP 1 mv.map_value
						FROM sys.dm_xe_object_columns occ
						INNER JOIN sys.dm_xe_map_values mv
							ON occ.type_name = mv.name
							AND occ.column_value = mv.map_key
						WHERE occ.name = 'KEYWORD'
							AND occ.object_name = oc.object_name) ca
	WHERE oc.column_type <> @ReadFlag
		AND sesea.name = oc.object_name
		AND oc.object_name = ISNULL(@EventName,oc.object_name)
		AND sesea.SessionName = ISNULL(@SessionName,sesea.SessionName)
)

SELECT p.EventName
	 , p.column_name
	 , p.type_name
	 , p.ColumnXML
	 , p.column_type
	 , p.column_value
	 , p.column_description
	 , p.SearchKeyword
	 , p.SessionName
	FROM presel p
	WHERE p.rownum = ISNULL(@UniqueEventColumns,p.rownum)
	ORDER BY p.SessionName,p.EventName
	;

/* auto generate the xml associated to an event session action deployed to the server */
WITH presel AS (
	SELECT p.name AS package_name
	        ,o.name AS action_name
			,',event_data.value(''(event/action[@name="' + esa.name + '"]/value)[1]'', ''' + 
			CASE 
				WHEN o.type_name = 'guid'
				THEN 'uniqueidentifier'
				WHEN o.type_name = 'boolean'
				THEN 'bit'
				WHEN o.type_name = 'binary_data'
				THEN 'varbinary(max)'
				WHEN o.type_name = 'callstack'
				THEN 'varbinary(max)'
				WHEN o.type_name = 'filetime'
				THEN 'varbinary(max)'
				WHEN o.type_name = 'cpu_cycle'
				THEN 'varbinary(max)'
				WHEN ISNULL(o.type_name,'') = ''
				THEN NULL
				WHEN o.type_name <> 'unicode_string'
					AND o.type_name <> 'ansi_string'
					AND o.type_name <> 'ptr'
					AND o.type_name NOT LIKE '%int%'
				THEN o.type_name
				WHEN o.type_name LIKE '%int%'
				THEN 'int'
				ELSE 'varchar(max)' END + ''') AS ' + esa.name +'' AS ActionXML
			,ses.name AS EventSessionName
			, ese.name AS EventName
	        ,o.description
			, rownum = ROW_NUMBER() OVER (PARTITION BY ses.name,o.name ORDER BY ese.name)
	FROM sys.dm_xe_packages AS p
		INNER JOIN sys.dm_xe_objects AS o
			ON p.guid = o.package_guid
		INNER JOIN sys.server_event_session_actions esa
			ON o.name = esa.name
		INNER JOIN sys.server_event_sessions ses
			ON esa.event_session_id = ses.event_session_id
		INNER JOIN sys.server_event_session_events ese
			ON esa.event_session_id = ese.event_session_id
			AND ese.event_id = esa.event_id
	WHERE o.object_type = 'action'
		AND (o.capabilities IS NULL OR o.capabilities & 1 = 0)
		AND (p.capabilities IS NULL OR p.capabilities & 1 = 0)
		AND ese.name = ISNULL(@EventName,ese.name)
		AND ses.name = ISNULL(@SessionName,ses.name)
)
SELECT p.package_name
	 , p.action_name
	 , p.ActionXML
	 , p.EventSessionName
	 , p.EventName
	 , p.description
	FROM presel p
	WHERE p.rownum = ISNULL(@UniqueActions,p.rownum)
	ORDER BY p.EventSessionName, p.action_name, p.EventName
	;

/* gen last piece of xml parsing sql code block*/
SELECT '	FROM #xmlprocess x
		LEFT OUTER JOIN sys.server_event_session_events sese
			ON x.event_data.value(''(event/@name)[1]'', ''varchar(50)'') = sese.name
				AND x.event_session_id = sese.event_session_id
	ORDER BY timestamp
		, event_data.value(''(event/action[@name="event_sequence"]/value)[1]'',
							''varchar(max)'');'

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

DECLARE @EventName VARCHAR(64) = NULL --'query_post_execution_showplan'--'sp_statement_completed'

,@ReadFlag VARCHAR(64) = 'readonly' --NULL if all columntypes are desired

,@SessionName VARCHAR(128) = 'AuditDeprecated' --'system_health' --NULL if all Sessions are desired --'Deadlock' --NULL

,@UniqueEventColumns TINYINT = 1 --1 for unique payload columns across all events within the session, NULL for all columns in session

,@UniqueActions TINYINT = 1 --1 for unique actions, NULL for all actions in the session

/*gen tsql to parse session payload */

SELECT '/* parse the data */

IF EXISTS (SELECT OBJECT_ID(''tempdb.dbo.#xmlprocess''))

BEGIN

DROP TABLE #xmlprocess

END

SELECT CAST ([t2].[event_data] AS XML) AS event_data, t2.file_offset,t2.file_name, cte1.event_session_id--, '' AS event_predicate

INTO #xmlprocess

FROM ( SELECT REPLACE(CONVERT(NVARCHAR(128),sesf.value),''.xel'',''*.xel'') AS targetvalue, ses.event_session_id

FROM sys.server_event_sessions ses

INNER JOIN sys.server_event_session_fields sesf

ON ses.event_session_id = sesf.event_session_id

--INNER JOIN sys.server_event_session_events sese

-- ON ses.event_session_id = sese.event_session_id

WHERE sesf.name = ''filename''

AND ses.name = ''' + @SessionName +'''

) cte1

OUTER APPLY sys.fn_xe_file_target_read_file(cte1.targetvalue, NULL, NULL, NULL) t2

;

SELECT x.event_data.value(''(event/@name)[1]'', ''varchar(50)'') AS event_name

, x.event_data.value(''(event/@package)[1]'', ''varchar(50)'') AS package_name

, DATEADD(hh, DATEDIFF(hh, GETUTCDATE(), CURRENT_TIMESTAMP),

x.event_data.value(''(event/@timestamp)[1]'',

''datetime2'')) AS [timestamp]';

WITH presel AS (

SELECT oc.OBJECT_NAME AS EventName

,oc.name AS column_name, oc.type_name

,',event_data.value(''(event/data[@name="' + oc.name + '"]/value)[1]'',''' +

CASE

WHEN ISNULL(xmv.name,'') = ''

AND oc.type_name = 'guid'

THEN 'uniqueidentifier'

WHEN ISNULL(xmv.name,'') = ''

AND oc.type_name = 'boolean'

THEN 'bit'

WHEN ISNULL(xmv.name,'') = ''

AND oc.type_name = 'callstack'

THEN 'varchar(max)'

WHEN ISNULL(xmv.name,'') = ''

AND oc.type_name <> 'unicode_string'

AND oc.type_name <> 'ansi_string'

AND oc.type_name <> 'ptr'

AND oc.type_name NOT LIKE '%int%'

THEN oc.type_name

WHEN ISNULL(xmv.name,'') = ''

AND oc.type_name LIKE '%int%'

THEN 'bigint'

ELSE 'varchar(max)' END + ''') AS ' + oc.name + '' AS ColumnXML

,oc.column_type AS column_type

,oc.column_value AS column_value

,oc.description AS column_description

,ca.map_value AS SearchKeyword

,sesea.SessionName

,rownum = ROW_NUMBER() OVER (PARTITION BY sesea.SessionName, oc.name ORDER BY oc.object_name)

FROM sys.dm_xe_object_columns oc

-- do we have any custom data types

OUTER APPLY (SELECT DISTINCT mv.name FROM sys.dm_xe_map_values mv

WHERE mv.name = oc.type_name

AND mv.object_package_guid = oc.object_package_guid) xmv

--just get the unique events that are tied to a session on the server (stopped or started state)

CROSS APPLY (SELECT DISTINCT sese.name,ses.name AS SessionName

FROM sys.server_event_session_events sese

INNER JOIN sys.server_event_sessions ses

ON sese.event_session_id = ses.event_session_id) sesea

--keyword search phrase tied to the event

CROSS APPLY (SELECT TOP 1 mv.map_value

FROM sys.dm_xe_object_columns occ

INNER JOIN sys.dm_xe_map_values mv

ON occ.type_name = mv.name

AND occ.column_value = mv.map_key

WHERE occ.name = 'KEYWORD'

AND occ.object_name = oc.object_name) ca

WHERE oc.column_type <> @ReadFlag

AND sesea.name = oc.object_name

AND oc.object_name = ISNULL(@EventName,oc.object_name)

AND sesea.SessionName = ISNULL(@SessionName,sesea.SessionName)

)

SELECT p.EventName

, p.column_name

, p.type_name

, p.ColumnXML

, p.column_type

, p.column_value

, p.column_description

, p.SearchKeyword

, p.SessionName

FROM presel p

WHERE p.rownum = ISNULL(@UniqueEventColumns,p.rownum)

ORDER BY p.SessionName,p.EventName

;

/* auto generate the xml associated to an event session action deployed to the server */

WITH presel AS (

SELECT p.name AS package_name

,o.name AS action_name

,',event_data.value(''(event/action[@name="' + esa.name + '"]/value)[1]'', ''' +

CASE

WHEN o.type_name = 'guid'

THEN 'uniqueidentifier'

WHEN o.type_name = 'boolean'

THEN 'bit'

WHEN o.type_name = 'binary_data'

THEN 'varbinary(max)'

WHEN o.type_name = 'callstack'

THEN 'varbinary(max)'

WHEN o.type_name = 'filetime'

THEN 'varbinary(max)'

WHEN o.type_name = 'cpu_cycle'

THEN 'varbinary(max)'

WHEN ISNULL(o.type_name,'') = ''

THEN NULL

WHEN o.type_name <> 'unicode_string'

AND o.type_name <> 'ansi_string'

AND o.type_name <> 'ptr'

AND o.type_name NOT LIKE '%int%'

THEN o.type_name

WHEN o.type_name LIKE '%int%'

THEN 'int'

ELSE 'varchar(max)' END + ''') AS ' + esa.name +'' AS ActionXML

,ses.name AS EventSessionName

, ese.name AS EventName

,o.description

, rownum = ROW_NUMBER() OVER (PARTITION BY ses.name,o.name ORDER BY ese.name)

FROM sys.dm_xe_packages AS p

INNER JOIN sys.dm_xe_objects AS o

ON p.guid = o.package_guid

INNER JOIN sys.server_event_session_actions esa

ON o.name = esa.name

INNER JOIN sys.server_event_sessions ses

ON esa.event_session_id = ses.event_session_id

INNER JOIN sys.server_event_session_events ese

ON esa.event_session_id = ese.event_session_id

AND ese.event_id = esa.event_id

WHERE o.object_type = 'action'

AND (o.capabilities IS NULL OR o.capabilities &