Home > Software engineering >  SQL Server Query to get schedules of all power bi refreshes and SSRS subscriptions
SQL Server Query to get schedules of all power bi refreshes and SSRS subscriptions

Time:05-03

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
  • Related