ReportServer - failed subscriptions

ssrs_failed_subscriptions.sql

use ReportServer
go
drop table if exists #failedSubs

select	c.ItemID
		,u.UserName as SubscriptionOwner
		,s.Description as SubscriptionDescription
		,s.LastStatus
		,s.SubscriptionID
		,c.Name as ReportName
		,c.Path
		,lastExec.TimeStart
		,lastExec.TimeEnd
		,lastExec.TimeDataRetrieval / 1000 as DataRetrievalSeconds
		,lastExec.TimeProcessing as ProcessingTimeMs
		,lastExec.ByteCount
		,lastExec.[RowCount]
		,lastexec.Status
into #failedSubs
from dbo.Subscriptions as s
join dbo.Users as u
on u.UserID = s.OwnerID
join dbo.Catalog as c
on c.ItemID = s.Report_OID
outer apply (
	select top 1 *
	from dbo.ExecutionLogStorage as els
	where els.ReportID = c.ItemID
	order by els.TimeStart desc
) as lastExec
where s.LastStatus like 'Failure sending%'
or s.LastStatus like 'Error%'
order by s.LastStatus asc

select *
from #failedSubs as fs
cross apply (
	select count(*) as TotalExecutions
			,sum(case when els.Status <> N'rsSuccess' then 1 else 0 end) as Failures
	from dbo.ExecutionLogStorage as els
	where els.ReportID = fs.ItemID
	and els.TimeStart >= getutcdate() - 7
) as weekStats