Forum Topic

Thanks for your participation and contribution.


Back to Azure

Extended Events - Import xel files to SQL Server

Post a reply
1705 views
i90runner1
i90runner1

https://gist.githubusercontent.com/brazilnut2000/f9e9859f095219b1a17d151292efc278/raw/b7801829cc88ff0b2af6fbc7e1d3f9cb2b303d15/SQL:%2520convert%2520extended%2520events%2520file%2520into%2520table.sql

-- convert all .xel files in a given folder to a single-column table
-- (alternatively specify an individual file explicitly)
select event_data = convert(xml, event_data)
    into #eeTable
from sys.fn_xe_file_target_read_file(N'd:\killme\extended events\*.xel', null, null, null);

-- select from the table
select * from #eeTable
-- and click on a hyperlink value to see the structure of the xml

-- create multi-column table from single-column table, explicitly adding needed columns from xml
SELECT 
  ts    = event_data.value(N'(event/@timestamp)[1]', N'datetime'),
  [sql] = event_data.value(N'(event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)'),
  cpu   = event_data.value(N'(event/data[@name="cpu_time"]/value)[1]', N'nvarchar(max)'),
  duration   = event_data.value(N'(event/data[@name="duration"]/value)[1]', N'nvarchar(max)'),
  result  = event_data.value(N'(event/action[@name="result"]/value)[1]', N'int'),
  row_count = event_data.value(N'(event/data[@name="row_count"]/value)[1]', N'nvarchar(max)'),
  spid  = event_data.value(N'(event/action[@name="session_id"]/value)[1]', N'int'),
  physical_reads  = event_data.value(N'(event/action[@name="physical_reads"]/value)[1]', N'int'),
  logical_reads  = event_data.value(N'(event/action[@name="logical_reads"]/value)[1]', N'int'),
  writes  = event_data.value(N'(event/action[@name="writes"]/value)[1]', N'int'),
  user_nm  = event_data.value(N'(event/action[@name="username"]/value)[1]', N'nvarchar(max)')
  into PexTrace_20170526
FROM #eeTable

-- add id
ALTER TABLE PexTrace_20170526
ADD eventId INT IDENTITY;

-- set id as PK
ALTER TABLE PexTrace_20170526
ADD CONSTRAINT PK_PexTrace_20170526 PRIMARY KEY(eventId);

-- now query all you like
SELECT *
FROM PexTrace_20170526
where sql like '%springer%'
order by ts
i90runner1
i90runner1

Parsing Azure SQL Extended Events

-- convert all .xel files in a given folder to a single-column table
-- (alternatively specify an individual file explicitly)
select event_data = convert(xml, event_data)
    INTO StagingTable
from sys.fn_xe_file_target_read_file(N'\\extendeventshare\*.xel', null, null, null);

-- select from the table
select TOP 100 * from StagingTable
-- and click on a hyperlink value to see the structure of the xml

-- create multi-column table from single-column table, explicitly adding needed columns from xml
SELECT TOP 100 
  ts = event_data.value(N'(event/data[@name="event_time"]/value)[1]', N'datetime')
  -- INTO MaineLoadTestRunParsed
FROM StagingTable

DROP TABLE StagingTable
-- create multi-column table from single-column table, explicitly adding needed columns from xml
SELECT 
  event_time    = event_data.value(N'(event/data[@name="event_time"]/value)[1]', N'datetime') ,
  [statement] = event_data.value(N'(event/data[@name="statement"]/value)[1]', N'nvarchar(max)'),
  duration_milliseconds   = event_data.value(N'(event/data[@name="duration_milliseconds"]/value)[1]', N'int'),
  server_principal_name   = event_data.value(N'(event/data[@name="server_principal_name"]/value)[1]', N'nvarchar(255)'),
  succeeded  = event_data.value(N'(event/data[@name="succeeded"]/value)[1]', N'nvarchar(255)'),
  row_count = event_data.value(N'(event/data[@name="affected_rows"]/value)[1]', N'nvarchar(max)'),
  client_ip  = event_data.value(N'(event/data[@name="client_ip"]/value)[1]', N'nvarchar(255)'),
  server_instance_name  = event_data.value(N'(event/data[@name="server_instance_name"]/value)[1]', N'nvarchar(255)'),
  [database_name]  = event_data.value(N'(event/data[@name="database_name"]/value)[1]', N'nvarchar(255)'),
  host_name  = event_data.value(N'(event/data[@name="host_name"]/value)[1]', N'nvarchar(255)'),
  application_name  = event_data.value(N'(event/data[@name="application_name"]/value)[1]', N'nvarchar(255)')
INTO StagingTableParsed
FROM StagingTable
WHERE event_data.value(N'(event/data[@name="event_time"]/value)[1]', N'datetime') >'2019-02-22'
AND  event_data.value(N'(event/data[@name="event_time"]/value)[1]', N'datetime') <'2019-02-23'

1-2 of 2

Reply to this discussion

You cannot edit posts or make replies: You should be logged in before you can post.