[MSSQL / XEvent] XEvent 파일 내용 조회

-- 1. XEvent 디렉토리 확인
EXEC XP_DIRTREE 'D:\xe파일존재하는폴더경로',1,1


-- ## DB 설정이 off로 되어있어 on상태로 변경
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

-- 2. 가장 최근에 생성된 순서대로 정렬(option : /o-d)하여 맨 마지막 파일의 파일명.확장자(option : /b) 반환
DECLARE @V_TABLE_NAME NVARCHAR(100) = N''
DECLARE @V_TABLE TABLE(
V_TABLE_NAME NVARCHAR(100)
)

INSERT INTO @V_TABLE
EXEC XP_CMDSHELL 'DIR D:\xe파일존재하는폴더경로\찾고자하는 파일명* /B /O-D' -- *는 like연산자 같은 것임

SELECT TOP 1 @V_TABLE_NAME = V_TABLE_NAME
FROM @V_TABLE

SELECT @V_TABLE_NAME


-- ## DB 설정 원복
EXEC SP_CONFIGURE 'XP_CMDSHELL', 0
GO
RECONFIGURE
GO

-- 3. 디렉토리에 존재하는 XEVENT 데이터 확인 
SELECT CAST(EVENT_DATA AS XML) AS EVENT_DATA
FROM SYS.FN_XE_FILE_TARGET_READ_FILE('D:\xe파일존재하는폴더경로\찾고자하는 파일명*.XEL', NULL, NULL, NULL) -- * -> LIKE % 와 비슷한 기능 / 모든파일

SELECT CAST(EVENT_DATA AS XML) AS EVENT_DATA
FROM SYS.FN_XE_FILE_TARGET_READ_FILE('D:xe파일존재하는폴더경로\찾고자하는 파일명.XEL', NULL, NULL, NULL) --특정파일 


-- 4. 위 파일 내용 중 필요한 데이터 파싱하여 확인
SELECT	N.value('(@name)[1]', 'VARCHAR(50)') AS EVENT_NAME
--,		N.value('(@package)[1]', 'VARCHAR(50)') AS PACKAGE_NAME
,		N.value('(@timestamp)[1]', 'DATETIME2') AS [UTC_TIMESTAMP]
,		N.value('(data[@name="duration"]/value)[1]', 'INT') AS DURATION
,		N.value('(data[@name="cpu_time"]/value)[1]', 'INT') AS CPU
,		N.value('(data[@name="physical_reads"]/value)[1]', 'INT') AS PHYSICAL_READS
,		N.value('(data[@name="logical_reads"]/value)[1]', 'INT') AS LOGICAL_READS
,		N.value('(data[@name="writes"]/value)[1]', 'INT') AS WRITES
,		N.value('(data[@name="row_count"]/value)[1]', 'INT') AS ROW_COUNT
--,		N.value('(data[@name="last_row_count"]/value)[1]', 'INT') AS LAST_ROW_COUNT
--,		N.value('(data[@name="line_number"]/value)[1]', 'INT') AS LINE_NUMBER
--,		N.value('(data[@name="offset"]/value)[1]', 'INT') AS OFFSET
--,		N.value('(data[@name="offset_end"]/value)[1]', 'INT') AS OFFSET_END
,		N.value('(data[@name="object_name"]/value)[1]', 'NVARCHAR(128)') AS OBJECT_NAME
,		N.value('(data[@name="statement"]/value)[1]', 'NVARCHAR(MAX)') AS STATEMENT
,		N.value('(data[@name="batch_text"]/value)[1]', 'NVARCHAR(MAX)') AS BATCH_TEXT
,		N.value('(data[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS SQL_TEXT
--		N.value('(action[@name="database_name"]/value)[1]', 'NVARCHAR(128)') AS DATABASE_NAME
FROM	(
			SELECT CAST(EVENT_DATA AS XML) AS EVENT_DATA
			--FROM sys.fn_xe_file_target_read_file('D:\xe파일존재하는폴더경로\찾고자하는 파일명*.xel', null, null, null)) ED
			FROM sys.fn_xe_file_target_read_file('D:\xe파일존재하는폴더경로\찾고자하는 파일명.xel', NULL, NULL, NULL)
		) AS ED
		CROSS APPLY ED.EVENT_DATA.nodes('event') AS Q(N)
--WHERE 1=1 
--AND   n.value('@timestamp', 'DATETIME2')   >= DATEADD(MINUTE,-10,GETDATE())


SELECT *
FROM sys.dm_xe_objects as o
	inner join sys.dm_xe_packages as p 
		on o.package_guid = p.guid