Get xml_deadlock_report from system_health session file target

SystemHealthDeadlockReports.sql

--get xml_deadlock_report from system_health session file target
WITH
	  --get full path to current system_health trace file
	  CurrentSystemHealthTraceFile AS (
		SELECT CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') AS FileName
		FROM sys.dm_xe_session_targets
		WHERE
			target_name = 'event_file'
			AND CAST(target_data AS xml).value('(/EventFileTarget/File/@name)[1]', 'varchar(255)') LIKE '%\system[_]health%'
	)
	  --get trace folder name and add base name of system_health trace file with wildcard
	, BaseSystemHealthFileName AS (
		SELECT 
			REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'\', REVERSE(FileName)), 255)) + N'system_health*.xel' AS FileNamePattern
		FROM CurrentSystemHealthTraceFile
		)
	  --get xml_deadlock_report events from all system_health trace files
	, DeadLockReports AS (
		SELECT CAST(event_data AS xml) AS event_data
		FROM BaseSystemHealthFileName
		CROSS APPLY sys.fn_xe_file_target_read_file ( FileNamePattern, NULL, NULL, NULL) AS xed
		WHERE xed.object_name like 'xml_deadlock_report'
	)
--display 10 most recent deadlocks
SELECT TOP 10
	  DATEADD(hour, DATEDIFF(hour, SYSUTCDATETIME(), SYSDATETIME()), event_data.value('(/event/@timestamp)[1]', 'datetime2')) AS LocalTime
	, event_data AS DeadlockReport
FROM DeadLockReports
ORDER BY LocalTime ASC;
GO

@guzmanda CurrentSystemHealthTraceFile is a string 'D:\rdsdbdata\Log\system_health_0_132880920013830000.xel'.

What should be BaseSystemHealthFileName?

What should i run

SELECT
REVERSE(SUBSTRING(REVERSE(FileName), CHARINDEX(N'', REVERSE(FileName)), 255)) + N'system_health*.xel' AS FileNamePattern
FROM 'D:\rdsdbdata\Log\system_health_0_132880920013830000.xel'

please help

@nil96, the script in this gist should be run in it's entirety. With your current file name, the BaseSystemHealthFileName CTE will return 'D:\rdsdbdata\Log\system_health*.xel' and that wildcard path used in the DeadLockReports CTE to extract xml_deadlock_report events from all available system_health trace files. The final SELECT query will extract the 10 oldest events but you can change the order to DESC to get the most recent ones and change to TOP clause to return more or fewer as desired.