Handy query to determine if an email subscription failed to send a report

 

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description

FROM Subscriptions AS S

LEFT OUTER JOIN [Catalog] AS C

ON C.ItemID = S.Report_OID

WHERE LEFT (S.LastStatus, 12) != 'Mail sent to'

AND LEFT (S.LastStatus, 12) != 'New Subscrip'