A little tweak, my log backups are all copied to the same folder so I can parse that folder and know what the last file copied was:
DROP TABLE IF EXISTS #File
GO
DROP TABLE IF EXISTS #CopiedFiles
GO
--===== Create a holding table for the file names
CREATE TABLE #File
(
FileName SYSNAME,
Depth TINYINT,
IsFile TINYINT
);
--===== Capture the names in the desired directory
-- (Change "C:\Temp" to the directory of your choice)
INSERT INTO #File
(FileName, Depth, IsFile)
EXEC xp_dirtree N'T:\Logshipping\',2,1;
SELECT SUBSTRING(FileName, 1, CHARINDEX('_', FileName)-1) as DBName,
MAX(FileName) as last_file_copied,
MAX(CONVERT (DATETIME, CONCAT(Substring(RIGHT(FileName, 18),1,4), '-', Substring(RIGHT(FileName, 18),5,2), '-', Substring(RIGHT(FileName, 18),7,2), ' ', Substring(RIGHT(FileName, 18),9,2), ':', Substring(RIGHT(FileName,
18),11,2)))) as last_copied_date
INTO #CopiedFiles
FROM #File
WHERE IsFile = 1
and FileName like '%.trn'
GROUP BY SUBSTRING(FileName, 1, CHARINDEX('_', FileName)-1)
UPDATE ms SET
last_restored_file = B.physical_device_name,
last_restored_date = B.restore_date,
last_restored_date_utc = B.restore_date_utc
,
last_copied_file = CF.last_file_copied,
last_copied_date = CF.last_copied_date,
last_copied_date_utc = CF.last_copied_date
FROM msdb.dbo.log_shipping_monitor_secondary ms JOIN (
SELECT rs.destination_database_name, RestoreBuFamily.physical_device_name, rs.restore_date, rs.restore_date as restore_date_utc
FROM msdb..restorehistory rs JOIN (
SELECT [destination_database_name], MAX(restore_history_id) AS MAX_restore_history_id
FROM msdb..restorehistory
WHERE restore_type = 'L'
GROUP BY [destination_database_name]
) AS A
ON rs.restore_history_id = A.MAX_restore_history_id
and rs.[destination_database_name] = A.[destination_database_name]
LEFT JOIN msdb.dbo.backupset RestoreSet with (nolock)
ON rs.backup_set_id = RestoreSet.backup_set_id
LEFT JOIN msdb.dbo.backupmediafamily RestoreBuFamily with (nolock)
ON RestoreBuFamily.media_set_id = RestoreSet.media_set_id
) as B
ON ms.secondary_database = B.destination_database_name
LEFT JOIN #CopiedFiles CF
ON ms.secondary_database = CF.DBName
