I have a report server with power bi dashboards that have scheduled refreshes, and SSRS reports that have scheduled subscriptions. Is there a sql server query I can implement that gives me the following information:
| Report/Dashboard | Format | Frequency | Time | Day |
|---|---|---|---|---|
| blah | Power BI | Daily | 2AM | Daily |
| blah2 | SSRS | Weekly | 8PM | Monday |
I am essentially looking for the schedules of the refreshes and the subscriptions. I have access to the report server database on sql server.
Thank you Deirdre for the response, I solved my initial question using this query that I compiled from other queries online.
WITH
EnhancedSchedule AS
(
SELECT s.*
,CASE WHEN DaysOfWeek & 1 <> 0 THEN 'Sun, ' ELSE '' END
+ CASE WHEN DaysOfWeek & 2 <> 0 THEN 'Mon, ' ELSE '' END
+ CASE WHEN DaysOfWeek & 4 <> 0 THEN 'Tue, ' ELSE '' END
+ CASE WHEN DaysOfWeek & 8 <> 0 THEN 'Wed, ' ELSE '' END
+ CASE WHEN DaysOfWeek & 16 <> 0 THEN 'Thu, ' ELSE '' END
+ CASE WHEN DaysOfWeek & 32 <> 0 THEN 'Fri, ' ELSE '' END
+ CASE WHEN DaysOfWeek & 64 <> 0 THEN 'Sat, ' ELSE '' END AS DaysOfWeekString
,CASE WHEN DaysOfMonth & 1 <> 0 THEN '1st ' ELSE '' END
+ CASE WHEN DaysOfMonth & 2 <> 0 THEN '2nd, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 4 <> 0 THEN '3rd, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 8 <> 0 THEN '4th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 16 <> 0 THEN '5th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 32 <> 0 THEN '6th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 64 <> 0 THEN '7th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 128 <> 0 THEN '8th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 256 <> 0 THEN '9th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 512 <> 0 THEN '10th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 1024 <> 0 THEN '11th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 2048 <> 0 THEN '12th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 4096 <> 0 THEN '13th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 8192 <> 0 THEN '14th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 16384 <> 0 THEN '15th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 32768 <> 0 THEN '16th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 65536 <> 0 THEN '17th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 131072 <> 0 THEN '18th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 262144 <> 0 THEN '19th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 524288 <> 0 THEN '20th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 1048576 <> 0 THEN '21st, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 2097152 <> 0 THEN '22nd, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 4194304 <> 0 THEN '23rd, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 8388608 <> 0 THEN '24th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 16777216 <> 0 THEN '25th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 33554432 <> 0 THEN '26th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 67108864 <> 0 THEN '27th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 134217728 <> 0 THEN '28th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 268435456 <> 0 THEN '29th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 536870912 <> 0 THEN '30th, ' ELSE '' END
+ CASE WHEN DaysOfMonth & 1073741824 <> 0 THEN '31st, ' ELSE '' END AS DaysOfMonthString
,CASE WHEN Month = 4095 THEN 'every month'
ELSE CASE WHEN Month & 1 <> 0 THEN 'Jan, ' ELSE '' END
+ CASE WHEN Month & 2 <> 0 THEN 'Feb, ' ELSE '' END
+ CASE WHEN Month & 4 <> 0 THEN 'Mar, ' ELSE '' END
+ CASE WHEN Month & 8 <> 0 THEN 'Apr, ' ELSE '' END
+ CASE WHEN Month & 16 <> 0 THEN 'May, ' ELSE '' END
+ CASE WHEN Month & 32 <> 0 THEN 'Jun, ' ELSE '' END
+ CASE WHEN Month & 64 <> 0 THEN 'Jul, ' ELSE '' END
+ CASE WHEN Month & 128 <> 0 THEN 'Aug, ' ELSE '' END
+ CASE WHEN Month & 256 <> 0 THEN 'Sep, ' ELSE '' END
+ CASE WHEN Month & 512 <> 0 THEN 'Oct, ' ELSE '' END
+ CASE WHEN Month & 1024 <> 0 THEN 'Nov, ' ELSE '' END
+ CASE WHEN Month & 2048 <> 0 THEN 'Dec, ' ELSE '' END
END AS MonthString
,CASE MonthlyWeek
WHEN 1 THEN 'first'
WHEN 2 THEN 'second'
WHEN 3 THEN 'third'
WHEN 4 THEN 'fourth'
WHEN 5 THEN 'last'
END AS MonthlyWeekString
,' starting ' + CONVERT(VARCHAR, StartDate, 101)
+ CASE WHEN EndDate IS NOT NULL THEN ' and ending '
+ CONVERT(VARCHAR, EndDate, 101) ELSE ''
END AS StartEndString
,CASE CONVERT(VARCHAR, DATEPART(HOUR, StartDate) % 12) WHEN 0 THEN '12'
ELSE CONVERT(VARCHAR, DATEPART(HOUR, StartDate) % 12)
END + ':'
+ CASE WHEN DATEPART(MINUTE, StartDate) < 10 THEN '0'
+ CONVERT(VARCHAR(2), DATEPART(MINUTE, StartDate))
ELSE CONVERT(VARCHAR(2), DATEPART(MINUTE, StartDate)) END
+ CASE WHEN DATEPART(HOUR, StartDate) >= 12 THEN ' PM' ELSE ' AM'
END AS StartTime
FROM ReportServer.dbo.Schedule s
),
SuperEnhancedSchedule AS
(
SELECT EnhancedSchedule.*
,CASE WHEN RecurrenceType = 1
THEN 'At ' + StartTime + ' on '
+ CONVERT(VARCHAR, StartDate, 101)
WHEN RecurrenceType = 2
THEN 'Every ' + CONVERT(VARCHAR, ( MinutesInterval / 60 ))
+ ' hour(s) and '
+ CONVERT(VARCHAR, ( MinutesInterval % 60 ))
+ ' minute(s), ' + 'starting '
+ CONVERT (VARCHAR, StartDate, 101) + ' at '
+ SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0, 6)
+ ' ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 109),
25, 2)
+ CASE WHEN EndDate IS NOT NULL
THEN ' and ending '
+ CONVERT (VARCHAR, EndDate, 101)
ELSE ''
END
WHEN RecurrenceType = 3
THEN 'At ' + StartTime + ' every '
+ CASE DaysInterval
WHEN 1 THEN 'day, '
ELSE CONVERT(VARCHAR, DaysInterval) + ' days, '
END + StartEndString
WHEN RecurrenceType = 4
THEN 'At ' + StartTime + ' every '
+ CASE WHEN LEN(DaysOfWeekString) > 1
THEN LEFT(DaysOfWeekString,
LEN(DaysOfWeekString) - 1)
ELSE ''
END + ' of every '
+ CASE WHEN WeeksInterval = 1 THEN ' week,'
ELSE CONVERT(VARCHAR, WeeksInterval)
+ ' weeks,'
END + StartEndString
WHEN RecurrenceType = 5
THEN 'At ' + StartTime + ' on day(s) '
+ CASE WHEN LEN(DaysOfMonthString) > 1
THEN LEFT(DaysOfMonthString,
LEN(DaysOfMonthString) - 1)
ELSE ''
END + ' of ' + MonthString + StartEndString
WHEN RecurrenceType = 6
THEN 'At ' + StartTime + ' on the ' + MonthlyWeekString
+ ' '
+ CASE WHEN LEN(DaysOfWeekString) > 1
THEN LEFT(DaysOfWeekString,
LEN(DaysOfWeekString) - 1)
ELSE ''
END + ' of ' + MonthString + StartEndString
ELSE 'At ' + SUBSTRING(CONVERT(VARCHAR, StartDate, 8), 0,
6) + ' '
+ SUBSTRING(CONVERT(VARCHAR, StartDate, 109), 25, 2)
+ StartEndString
END AS ScheduleText
FROM EnhancedSchedule
),
SubscriptionXML AS
(
SELECT s.*
,CONVERT(XML, ExtensionSettings) AS ExtensionSettingsXML
FROM ReportServer.dbo.Subscriptions s
),
Receipients AS
(
SELECT SubscriptionXML.*
,ISNULL(Settings.value('(./*:Name/text())[1]', 'nvarchar(1024)'), 'Value') AS SettingName
,Settings.value('(./*:Value/text())[1]', 'nvarchar(max)') AS SettingValue
FROM
SubscriptionXML
CROSS APPLY SubscriptionXML.ExtensionSettingsXML.nodes('//*:ParameterValue') Queries ( Settings )
)
SELECT c.Path
,c.Name AS Report
,CASE
WHEN c.Type = 1 THEN 'Folder'
WHEN c.Type = 2 THEN 'SSRS Paginated Report'
WHEN c.Type = 3 THEN 'File'
WHEN c.Type = 4 THEN 'Linked Report'
WHEN c.Type = 5 THEN 'Datasource'
WHEN c.Type = 6 THEN 'Model'
WHEN c.Type = 8 THEN 'Shared Dataset'
WHEN c.Type = 9 THEN 'Report Part'
WHEN c.Type = 11 THEN 'KPI'
WHEN c.Type = 12 THEN 'SSRS Mobile Report'
WHEN c.Type = 13 THEN 'Power BI Report'
ELSE 'Unknown'
END AS Format
,CASE
WHEN ses.RecurrenceType = 1 THEN 'Once'
WHEN ses.RecurrenceType = 2 THEN 'Hourly'
WHEN ses.RecurrenceType = 3 THEN 'Daily'
WHEN ses.RecurrenceType = 4 THEN 'Weekly'
WHEN ses.RecurrenceType = 5 THEN 'Monthly'
WHEN ses.RecurrenceType = 6 THEN 'Week of Month'
ELSE 'Unknown'
END AS Frequency
,ses.ScheduleText AS TimeDayFreq
,r.SettingName AS ReceipientSettings
,r.SettingValue AS Receipients
FROM ReportServer.dbo.ReportSchedule rs
LEFT JOIN ReportServer.dbo.Subscriptions s
ON rs.SubscriptionID = s.SubscriptionID
LEFT JOIN ReportServer.dbo.Catalog c
ON s.Report_OID = c.ItemID
LEFT JOIN SuperEnhancedSchedule ses
ON rs.ScheduleID = ses.ScheduleID
LEFT JOIN Receipients r
ON rs.SubscriptionID = r.SubscriptionID
WHERE c.Type IN (2,13)
AND s.InactiveFlags = 0
ORDER BY c.Nam