is there a way to query future SSRS subscription schedules?

Question

In my office, many of us use SSRS to schedule recurring reports. I want to view the schedule of upcoming report runs, for the next few days or a week, so that we can see whether 20 reports are all going to try to run at the same time. How can I accomplish that? I've created t-sql reports that show subscription information, but they only show "last run" dates and times. That's not helpful for predicting tomorrow's bottlenecks. The solution should include data-driven subscriptions too.

Solution 1

SSRS stores all of its data in the ReportServer database so you'll need an account with read access to this database. This is all relevant for SSRS running in native mode. I'm also not sure if shared schedules or data-driven subscriptions will be handled by this code, but I'm pretty sure they will be. I just haven't tested them.

---

PLEASE NOTE: Microsoft does not recommend or support directly querying the ReportServer database. They could change the structure in the next version or update of SSRS and you likely won't get any warning. The Microsoft recommendation is to always use the SSRS web service when you need to interrogate information about reporting services.

---

These are the tables that are relevant for pulling out the subscription information:

• dbo.Catalog - Information about the deployed reports
• dbo.ReportSchedule - Information relating reports to schedules and schedules
• dbo.Subscriptions - Information about the subscriptions
• dbo.Schedule - Information about the schedules

The SQL below pulls out schedule interval information for all reports. It doesn't calculate the next run dates but by figuring out the interval that the schedule is supposed to run on you can write another query to generate the actual dates.

This SQL was originally written for a report that just displays a string describing the interval so the final output is probably not what you're after. It should give you a good starting point though since it does figure out all of the interval details.

--these CTEs are used to match the bitmask fields in the schedule to determine which days & months the schedule is triggered on
WITH wkdays AS (
    SELECT 'Sunday' AS label, 1 AS daybit
    UNION ALL
    SELECT 'Monday', 2
    UNION ALL
    SELECT 'Tuesday', 4
    UNION ALL
    SELECT 'Wednesday', 8
    UNION ALL
    SELECT 'Thursday', 16
    UNION ALL
    SELECT 'Friday', 32
    UNION ALL
    SELECT 'Saturday', 64
),
monthdays AS (
    SELECT CAST(number AS VARCHAR(2)) AS label,
        POWER(CAST(2 AS BIGINT),number-1) AS daybit
    FROM master.dbo.spt_values
    WHERE type='P' AND number BETWEEN 1 AND 31
),
months AS (
    SELECT DATENAME(MM,DATEADD(MM,number-1,0)) AS label,
        POWER(CAST(2 AS BIGINT),number-1) AS mnthbit
    FROM master.dbo.spt_values
    WHERE type='P' AND number BETWEEN 1 AND 12
)
SELECT cat.path,
    cat.name,
    cat.creationdate,
    cat.modifieddate,
    subs.Description,
    subs.LastStatus,
    subs.LastRunTime,
    subs.InactiveFlags,
    CASE RecurrenceType
        WHEN 1 THEN 'Once'
        WHEN 2 THEN 'Hourly'
        WHEN 3 THEN 'Daily' --by interval
        WHEN 4 THEN
            CASE
                WHEN WeeksInterval>1 THEN 'Weekly'
                ELSE 'Daily' --by day of week
            END
        WHEN 5 THEN 'Monthly' --by calendar day
        WHEN 6 THEN 'Monthly' --by day of week
    END AS sched_type,
    sched.StartDate,
    sched.MinutesInterval,
    sched.RecurrenceType,
    sched.DaysInterval,
    sched.WeeksInterval,
    sched.MonthlyWeek,
    wkdays.label AS wkday,wkdays.daybit AS wkdaybit,
    monthdays.label AS mnthday,monthdays.daybit AS mnthdaybit,
    months.label AS mnth, months.mnthbit
INTO #t
FROM dbo.Catalog AS cat
LEFT JOIN dbo.ReportSchedule AS repsched ON repsched.ReportID=cat.ItemID
LEFT JOIN dbo.Subscriptions AS subs ON subs.SubscriptionID=repsched.SubscriptionID
LEFT JOIN dbo.Schedule AS sched ON sched.ScheduleID=repsched.ScheduleID
LEFT JOIN wkdays ON wkdays.daybit & sched.DaysOfWeek > 0
LEFT JOIN monthdays ON monthdays.daybit & sched.DaysOfMonth > 0
LEFT JOIN months ON months.mnthbit & sched.[Month] > 0
WHERE cat.ParentID IS NOT NULL --all reports have a ParentID


/* THE PREVIOUS QUERY LEAVES MULTIPLE ROWS FOR SUBSCRIPTIONS THAT HAVE MULTIPLE BITMASK MATCHES      *
 * THIS QUERY WILL CONCAT ALL OF THOSE FIELDS TOGETHER AND ACCUMULATE THEM IN A TABLE FOR USE LATER. */

CREATE TABLE #c (type VARCHAR(16) COLLATE Latin1_General_CI_AS_KS_WS, name VARCHAR(255) COLLATE Latin1_General_CI_AS_KS_WS, path VARCHAR(255) COLLATE Latin1_General_CI_AS_KS_WS, concatStr VARCHAR(2000) COLLATE Latin1_General_CI_AS_KS_WS);


WITH d AS (
    SELECT DISTINCT path,
        name,
        mnthday AS lbl,
        mnthdaybit AS bm
    FROM #t
)
INSERT INTO #c (type,path,name,concatStr)
SELECT 'monthday' AS type,
    t1.path,t1.name,
    STUFF((
        SELECT ', ' + CAST(lbl AS VARCHAR(MAX))
        FROM d AS t2
        WHERE t2.path=t1.path AND t2.name=t1.name
        ORDER BY bm
        FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'),1,2,'') AS concatStr
FROM d AS t1
GROUP BY t1.path,t1.name;

WITH d AS (
    SELECT DISTINCT path,
        name,
        wkday AS lbl,
        wkdaybit AS bm
    FROM #t
)
INSERT INTO #c (type,path,name,concatStr)
SELECT 'weekday' AS type,
    t1.path,t1.name,
    STUFF((
        SELECT ', ' + CAST(lbl AS VARCHAR(MAX))
        FROM d AS t2
        WHERE t2.path=t1.path AND t2.name=t1.name
        ORDER BY bm
        FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'),1,2,'') AS concatStr
FROM d AS t1
GROUP BY t1.path,t1.name;

WITH d AS (
    SELECT DISTINCT path,
        name,
        mnth AS lbl,
        mnthbit AS bm
    FROM #t
)
INSERT INTO #c (type,path,name,concatStr)
SELECT 'month' AS type,
    t1.path,t1.name,
    STUFF((
        SELECT ', ' + CAST(lbl AS VARCHAR(MAX))
        FROM d AS t2
        WHERE t2.path=t1.path AND t2.name=t1.name
        ORDER BY bm
        FOR XML PATH(''),TYPE
    ).value('.','VARCHAR(MAX)'),1,2,'') AS concatStr
FROM d AS t1
GROUP BY t1.path,t1.name;


/* PUT EVERYTHING TOGETHER FOR THE REPORT */

SELECT a.path,a.name,a.sched_type,
    a.creationdate,a.modifieddate,
    a.description AS sched_desc,
    a.laststatus AS sched_laststatus,
    a.lastruntime AS sched_lastrun,
    a.inactiveflags AS sched_inactive,
    CASE RecurrenceType
        WHEN 1 THEN 'Run once on '
        ELSE 'Starting on '
    END + CAST(StartDate AS VARCHAR(32)) + ' ' +
    CASE RecurrenceType
        WHEN 1 THEN ''
        WHEN 2 THEN 'repeat every ' + CAST(MinutesInterval AS VARCHAR(255)) + ' minutes.'
        WHEN 3 THEN 'repeat every ' + CAST(DaysInterval AS VARCHAR(255)) + ' days.'
        WHEN 4 THEN 
            CASE
                WHEN WeeksInterval>1 THEN 'repeat every ' + CAST(WeeksInterval AS VARCHAR(255)) + ' on ' + COALESCE(wkdays.concatStr,'')
                ELSE 'repeat every ' + COALESCE(wkdays.concatStr,'')
            END
        WHEN 5 THEN 'repeat every ' + COALESCE(mnths.concatStr,'') + ' on calendar day(s) '  + COALESCE(mnthdays.concatStr,'')
        WHEN 6 THEN 'run on the ' + CASE MonthlyWeek WHEN 1 THEN '1st' WHEN 2 THEN '2nd' WHEN 3 THEN '3rd' WHEN 4 THEN '4th' WHEN 5 THEN 'Last' END + ' week of ' + COALESCE(mnths.concatStr,'') + ' on ' + COALESCE(wkdays.concatStr,'')
    END AS sched_pattern
FROM (
    SELECT DISTINCT path,name,creationdate,modifieddate,description,laststatus,lastruntime,inactiveflags,sched_type,recurrencetype,startdate,minutesinterval,daysinterval,weeksinterval,monthlyweek
    FROM #t
) AS a
LEFT JOIN #c AS mnthdays ON mnthdays.path=a.path AND mnthdays.name=a.name AND mnthdays.type='monthday'
LEFT JOIN #c AS wkdays ON wkdays.path=a.path AND wkdays.name=a.name AND wkdays.type='weekday'
LEFT JOIN #c AS mnths ON mnths.path=a.path AND mnths.name=a.name AND mnths.type='month'

DROP TABLE #t,#c;

Solution 2

Below Query can help you fetch schedule for your reports for the next day, this is built on standard metadata tables from report server database.

• dbo.Catalog - Information about the deployed reports
• dbo.ReportSchedule - Information relating reports to schedules and
  schedules
• dbo.Subscriptions - Information about the subscriptions
• dbo.Schedule - Information about the schedules

Query:

Change getDate() function to have particular day schedule.

SELECT CAT.Name
      ,CAT.[Path] AS ReportPath 
      --,SUB.LastRunTime 
      ,SCH.NextRunTime
      ,CONVERT(VARCHAR(10), CONVERT(datetime, SCH.NextRunTime,   1), 101) As RunDate
      ,right(convert(varchar(32),SCH.NextRunTime,100),8) As RunTime
      ,SUB.[Description] 
      ,SUB.EventType 
      ,SUB.LastStatus 
      ,SUB.ModifiedDate 
      ,SCH.Name AS ScheduleName     
FROM reportserver.dbo.Subscriptions AS SUB 
     INNER JOIN reportserver.dbo.Users AS USR 
         ON SUB.OwnerID = USR.UserID 
     INNER JOIN reportserver.dbo.[Catalog] AS CAT 
         ON SUB.Report_OID = CAT.ItemID 
     INNER JOIN reportserver.dbo.ReportSchedule AS RS 
         ON SUB.Report_OID = RS.ReportID 
            AND SUB.SubscriptionID = RS.SubscriptionID 
     INNER JOIN reportserver.dbo.Schedule AS SCH 
         ON RS.ScheduleID = SCH.ScheduleID 

Where CONVERT(VARCHAR(10), CONVERT(datetime, SCH.NextRunTime,   1), 101)  = CONVERT(VARCHAR(10), CONVERT(datetime, getDate()+1,   1), 101) 

ORDER BY USR.UserName 
        ,CAT.[Path];

---

Solution 3

This procedure will give list of all values related to Report subscription.

here you will find startdate. and on the basis of that you can complete your task.

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 as ReportName,
Convert(XML,a.[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RENDER_FORMAT"])[1]','nvarchar(50)') as ReportExtension,
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

Comments

[BrettFromLA]

Wow Mike! That's query looks powerful. I'm going to have to pick it apart, and probably show it to some colleagues, before I run it. Thanks!

[BrettFromLA]

I wanted the report output to forecast the days and times that reports are scheduled, for a certain period of time (the next week, the next 14 days, or whatever). I didn't see that in the report output you included in your answer...?

[SherlockSpreadsheets]

@BrettaFromLA As in how many subscriptions will be sent? And when? Did you solve it?

[BrettFromLA]

Exactly. Unfortunately I never solved that. I think I had to extrapolate those out manually.

[SherlockSpreadsheets]

Hmm... yes that would be tricky. You're looking a different intervals (Hours/ Days/ Weeks/ Events: i.e.- day 1 of the month/ etc. And then your talking about some things have an ending date and some are infinity-- so you would need establish a horizon (say 30 days?). My query helped me in that is shows how my schedules are being used on the server. But yeah... forecasting subscriptions would be a tough job. What was the business case for counting or displaying them all?

[BrettFromLA]

I wanted to make sure that on any given day and time, there weren't too many scheduled reports trying to run simultaneously. I was afraid that if several reports ran at once, it could tie up our SSRS server. If the server got too bogged down I was afraid one or more reports would fail to run. However, I left that job 6 months ago to start a small business, so I don't need an answer to the question anymore!

[SherlockSpreadsheets]

Neat, well thanks for sharing. Life has a way of taking new directions, good luck with your new goals.