Another example of importing extended event data

SET QUOTED_IDENTIFIER ON

IF (OBJECT_ID(‘tempdb..#staging’)) IS NOT NULL DROP TABLE #staging

SELECT q.statement_text,q.client_hostname,q.client_app_name,timestamp
INTO #staging
FROM (
SELECT statement_text=e.event_data_XML.value(‘(//data[@name=”statement”]/value)[1]’,’varchar(max)’)
, client_hostname=e.event_data_XML.value(‘(//action[@name=”client_hostname”]/value)[1]’,’varchar(max)’)
, client_app_name=e.event_data_XML.value(‘(//action[@name=”client_app_name”]/value)[1]’,’varchar(max)’)
, TIMESTAMP=e.event_data_XML.value(‘(//@timestamp)[1]’,’datetime2(7)’)
, name=e.event_data_XML.value(‘(//@name)[1]’,’varchar(max)’)
, *
FROM (
SELECT CAST(event_data AS XML) AS event_data_XML
FROM sys.fn_xe_file_target_read_file(‘Z:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log\trace*.xel’, NULL, NULL, NULL)

)e
)q

IF OBJECT_ID(‘database_name.dbo.readonly_trace’) IS NULL

SELECT TOP 0 *
INTO database_name.dbo.readonly_trace
FROM #staging

INSERT INTO database_name.dbo.readonly_trace
SELECT b.* FROM database_name.dbo.readonly_trace a
RIGHT JOIN #staging b ON a.timestamp = b.TIMESTAMP
WHERE a.timestamp IS NULL

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: