SQL Query to find the Report subscription details for a CRM on premise

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