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.
CodePudding user response：
This MSSQLTips will give you information on how to query SSRS information from the ReportServer database. The equivalent information for Power BI scheduled refreshes can be queried using the ExecutionLog3 view. Once you have created the queries you need to pull the information for SSRS & Power BI, you can use UNION ALL to create the table that you want.
CodePudding user response：
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