Below query has to be run against the ReportServer Database.Which will give you the report name, to address, CC & BCC and last report ran time details. i Found this query very handy when there are lots of report subscriptions going from your CRM.. :)
USE ReportServer
GO
DECLARE @Subscriptions
TABLE (
Report_OID UNIQUEIDENTIFIER,
ToList VARCHAR(8000),
CCList VARCHAR(8000),
BCCList VARCHAR(8000),
SubjectLine
VARCHAR(8000))DECLARE @ExtensionSettings XML
DECLARE @Report_OID
UNIQUEIDENTIFIER
DECLARE @idoc INT
DECLARE SubscriptionList
CURSOR FOR
SELECT Report_OID,ExtensionSettings
FROM Subscriptions SubscriptionList
Open SubscriptionList
FETCH NEXT FROM SubscriptionList
INTO @Report_OID
,@ExtensionSettings
WHILE (@@FETCH_STATUS=0)
BEGIN
EXEC sp_xml_preparedocument @idoc OUTPUT, @ExtensionSettings
INSERT INTO @Subscriptions
SELECT @Report_OID,[TO],[CC],[BCC],[Subject]
FROM
(
SELECT *
FROM
OPENXML (@idoc, '/ParameterValues/ParameterValue')
WITH
(Name NVARCHAR(1500) 'Name',
Value NVARCHAR(1500) 'Value')
) AS SourceTable
pivot
(
MAX(value) FOR [Name] IN ([TO],[BCC],[CC],[Subject])
) AS pivottable
EXEC sp_xml_removedocument @idoc
FETCH NEXT FROM SubscriptionList
INTO @Report_OID
,@ExtensionSettings
END
CLOSE
SubscriptionList
DEALLOCATE
SubscriptionList
SELECT c.CreationDate,c.ExecutionTime,c.path,c.name,s.Tolist,s.cclist,s.bcclist,s.subjectline FROM
Catalog c INNER JOIN @Subscriptions
s ON c.ItemID = s.Report_OID ORDER BY [path], Name
select C.Description,C.ExecutionTime,C.ExecutionFlag,C.Type,C.CreationDate,S.LastRunTime,S.EventType,S.DeliveryExtension,S.InactiveFlags from Catalog C inner join Subscriptions S on
C.ItemID=S.Report_OID
