How to get all SSRS Subscription Data using stored procedure?

Question

I am new to ssrs.

I want to get all the possible data for ssrs subscribed report, which are Available in ResportServer database.

I have found some queries, but that does not have proper data. It only works for single report.

I need list of unique subscription with it's data. If possible stored procedure is preferable.

My query:

SELECT
        b.name AS JobName
        , e.name
        , e.path
        , d.description
        , a.SubscriptionID
        , laststatus
        , eventtype
        , LastRunTime
        , date_created
        , date_modified
FROM ReportServer.dbo.ReportSchedule a
JOIN msdb.dbo.sysjobs b
        ON a.ScheduleID = b.name
JOIN ReportServer.dbo.ReportSchedule c
        ON b.name = c.ScheduleID
JOIN ReportServer.dbo.Subscriptions d
        ON c.SubscriptionID = d.SubscriptionID
JOIN ReportServer.dbo.Catalog e
        ON d.report_oid = e.itemid
WHERE e.name = 'Sales_Report'

Thanks in advance.

Answer 1

I am using following query to get subscriptions and then to find SQL Jobs scripts

USE ReportServer 
GO


CREATE TABLE #tempReports(LogEntryId BIGINT, subscriptionid VARCHAR(1000),LastRunTime DATETIME,  _Description VARCHAR(1000), ReportID VARCHAR(1000), 
             LastStatus VARCHAR(1000), rowNum INT)
INSERT INTO #tempReports
SELECT *
FROM 
(
    SELECT DISTINCT E.LogEntryId, S.subscriptionid, LastRunTime,  S.Description, E.ReportID, S.LastStatus,
          ROW_NUMBER() OVER(PARTITION BY E.ReportID, S.Description ORDER BY S.LastRunTime DESC) as rowNum
    FROM [ExecutionLogStorage]  E
    INNER JOIN Subscriptions S
        ON S.Report_OID = E.ReportID 
    WHERE 1 = 1
    AND (
        S.LastStatus LIKE '%has been saved to the "\\<server>\c$\SSRS_Report_Export\20%'
        )
)T
WHERE rowNum = 1
ORDER BY ReportID, Description


CREATE TABLE #ExecutionStatements (ExecStatement VARCHAR(1000), job_name VARCHAR(1000), theReportOrder INT,
            LogEntryId BIGINT, subscriptionid VARCHAR(1000), LastRunTime DATETIME,  _Description VARCHAR(1000), 
            ReportID VARCHAR(1000),  LastStatus VARCHAR(1000))
INSERT INTO #ExecutionStatements
SELECT 'exec sp_start_job @job_name = ''' + cast(j.name as varchar(40)) + '''' AS ExecStatement,
        j.name AS job_name, 
        ROW_NUMBER () OVER(ORDER BY LogEntryId) AS theReportOrder,
        LogEntryId, subscriptionid,LastRunTime,  _Description, ReportID, 
        LastStatus
from msdb.dbo.sysjobs j 
join msdb.dbo.sysjobsteps js 
    on js.job_id = j.job_id        
join #tempReports s 
    on js.command like '%' + cast(s.subscriptionid as varchar(40)) + '%'
WHERE 1 = 1

---Execute required statements in MSDB
SELECT E.ExecStatement + '--'+ CONVERT(VARCHAR(10),E.theReportOrder), E.theReportOrder, *
FROM #ExecutionStatements E
--WHERE subscriptionid = '3AFDAC30-4F30-423F-9F72-7C04C86026AB'
ORDER BY E.theReportOrder

SELECT DISTINCT S.subscriptionid, S.Description, E.ReportID, S.LastStatus, MAX(LastRunTime)    -- 2021-04-25 18:35:00.840
FROM [ExecutionLogStorage]  E
INNER JOIN Subscriptions S
    ON S.Report_OID = E.ReportID 
WHERE S.subscriptionid = '3AFDAC30-4F30-423F-9F72-7C04C86026AB'
GROUP BY S.subscriptionid, S.Description, E.ReportID, S.LastStatus

Answer 2

In case you need to find the sql server agent Job use this updated code

SET NOCOUNT ON;
WITH
[Sub_Parameters] AS
(
    SELECT  [SubscriptionID], [Parameters] = CONVERT(XML,a.[Parameters])
    FROM [Subscriptions] a
),
[MySubscriptions] AS
(
    SELECT DISTINCT [SubscriptionID], [ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')),   [ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)')
    FROM [Sub_Parameters] a
        CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p)
),
[SubscriptionsAnalysis] AS
(
    SELECT  a.[SubscriptionID], a.[ParameterName],  [ParameterValue] =  
        (
            SELECT  STUFF((SELECT [ParameterValue] + ', ' as [text()] 
            FROM [MySubscriptions]  
            WHERE   [SubscriptionID] = a.[SubscriptionID]   AND [ParameterName] = a.[ParameterName] 
                FOR XML PATH('')    ),1, 0, '') +''
        )
    FROM [MySubscriptions] a
    GROUP BY a.[SubscriptionID],a.[ParameterName]
)

SELECT
    DISTINCT (a.[SubscriptionID]),
    j.name  AS SQLServerAgentJob,
    c.[UserName] AS Owner, 
    b.Name,
    b.Path,
    a.[Locale], 
    a.[InactiveFlags], 
    d.[UserName] AS Modified_by, 
    a.[ModifiedDate], 
    a.[Description], 
    a.[LastStatus], 
    a.[EventType], 
    a.[LastRunTime], 
    a.[DeliveryExtension],
    a.[Version],
    sch.StartDate,
    --e.[ParameterName],
    --LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue],
    SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName

FROM [Subscriptions] a 
INNER JOIN [Catalog] AS b ON a.[Report_OID] = b.[ItemID]
Inner Join ReportSchedule as RS on rs.SubscriptionID = a.SubscriptionID
INNER JOIN Schedule AS Sch ON Sch.ScheduleID = rs.ScheduleID
LEFT OUTER JOIN [Users] AS c ON a.[OwnerID] = c.[UserID]
LEFT OUTER JOIN [Users] AS d ON a.MODIFIEDBYID = d.Userid
LEFT OUTER JOIN [SubscriptionsAnalysis] AS e ON a.SubscriptionID = e.SubscriptionID
LEFT JOIN msdb.dbo.sysobjects so ON rs.ScheduleID= so.name
INNER JOIN msdb.dbo.sysjobs J ON CONVERT( NVARCHAR(128), RS.ScheduleID ) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

Answer 3

I have same requirement once as like you have now...

See below stored procedure..

CREATE PROCEDURE [dbo].[GetSubscriptionData]
AS
BEGIN
SET NOCOUNT ON;
WITH
[Sub_Parameters] AS
(
    SELECT  [SubscriptionID], [Parameters] = CONVERT(XML,a.[Parameters])
    FROM [Subscriptions] a
),
[MySubscriptions] AS
(
    SELECT DISTINCT [SubscriptionID], [ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')),   [ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)')
    FROM [Sub_Parameters] a
        CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p)
),
[SubscriptionsAnalysis] AS
(
    SELECT  a.[SubscriptionID], a.[ParameterName],  [ParameterValue] =  
        (
            SELECT  STUFF((SELECT [ParameterValue] + ', ' as [text()] 
            FROM [MySubscriptions]  
            WHERE   [SubscriptionID] = a.[SubscriptionID]   AND [ParameterName] = a.[ParameterName] 
                FOR XML PATH('')    ),1, 0, '') +''
        )
    FROM [MySubscriptions] a
    GROUP BY a.[SubscriptionID],a.[ParameterName]
)

SELECT
DISTINCT (a.[SubscriptionID]),
c.[UserName] AS Owner, 
b.Name,
b.Path,
a.[Locale], 
a.[InactiveFlags], 
d.[UserName] AS Modified_by, 
a.[ModifiedDate], 
a.[Description], 
a.[LastStatus], 
a.[EventType], 
a.[LastRunTime], 
a.[DeliveryExtension],
a.[Version],
sch.StartDate,
--e.[ParameterName],
--LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue],
SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName
FROM 
    [Subscriptions] a 
    INNER JOIN [Catalog] AS b ON a.[Report_OID] = b.[ItemID]
    Inner Join ReportSchedule as RS on rs.SubscriptionID = a.SubscriptionID
    INNER JOIN Schedule AS Sch ON Sch.ScheduleID = rs.ScheduleID
    LEFT OUTER JOIN [Users] AS c ON a.[OwnerID] = c.[UserID]
    LEFT OUTER JOIN [Users] AS d ON a.MODIFIEDBYID = d.Userid
    LEFT OUTER JOIN [SubscriptionsAnalysis] AS e ON a.SubscriptionID = e.SubscriptionID;
END

This is simplified query to get all SSRS Subscriptions

SELECT USR.UserName AS SubscriptionOwner 
  ,SUB.ModifiedDate 
  ,SUB.[Description] 
  ,SUB.EventType 
  ,SUB.DeliveryExtension 
  ,SUB.LastStatus 
  ,SUB.LastRunTime 
  ,SCH.NextRunTime 
  ,SCH.Name AS ScheduleName       
  ,CAT.[Path] AS ReportPath 
  ,CAT.[Description] AS ReportDescription 
FROM dbo.Subscriptions AS SUB 
 INNER JOIN dbo.Users AS USR 
     ON SUB.OwnerID = USR.UserID 
 INNER JOIN dbo.[Catalog] AS CAT 
     ON SUB.Report_OID = CAT.ItemID 
 INNER JOIN dbo.ReportSchedule AS RS 
     ON SUB.Report_OID = RS.ReportID 
        AND SUB.SubscriptionID = RS.SubscriptionID 
 INNER JOIN dbo.Schedule AS SCH 
     ON RS.ScheduleID = SCH.ScheduleID 
ORDER BY USR.UserName, CAT.[Path];

if you still have any query, comment it..