Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Would like to create a report to show when all the reports on our Report server are scheduled to refresh. If this is possible where is the information stored.
Also would like to report on what AD group the user belongs to is that stored anywhere on the Report server?
Solved! Go to Solution.
This would help you out.
SELECT e.name AS ReportName ,e.path AS ReportPath ,d.description as SubscriptionName , a.SubscriptionID , d.laststatus , d.eventtype , d.LastRunTime ,j.date_created AS ScheduleCreatedDate ,j.date_modified AS ScheduleModifiedDate ,CASE j.[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS Job_Enabled ,CASE sch.[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS Schedule_enabled, CASE sch.freq_type WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly relative' WHEN 64 THEN 'When SQLServer Agent starts' END AS Frequency, CASE sch.active_start_date WHEN 0 THEN null ELSE substring(convert(varchar(15),sch.active_start_date),1,4) + '-' + substring(convert(varchar(15),sch.active_start_date),5,2) + '-' + substring(convert(varchar(15),sch.active_start_date),7,2) END AS ScheduleStartDate, CASE len(sch.active_start_time) WHEN 3 THEN cast('00:0' + Left(right(sch.active_start_time,3),1) +':' + right(sch.active_start_time,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(sch.active_start_time,4),2) +':' + right(sch.active_start_time,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(sch.active_start_time,5),1) +':' + Left(right(sch.active_start_time,4),2) +':' + right(sch.active_start_time,2) as char (8)) WHEN 6 THEN cast(Left(right(sch.active_start_time,6),2) +':' + Left(right(sch.active_start_time,4),2) +':' + right(sch.active_start_time,2) as char (8)) END AS ScheduleStartTime ,CASE jsch.next_run_date WHEN 0 THEN null ELSE substring(convert(varchar(15),jsch.next_run_date),1,4) + '-' + substring(convert(varchar(15),jsch.next_run_date),5,2) + '-' + substring(convert(varchar(15),jsch.next_run_date),7,2) END AS nextRunDate, CASE len(jsch.next_run_time) WHEN 3 THEN cast('00:0' + Left(right(jsch.next_run_time,3),1) +':' + right(jsch.next_run_time,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(jsch.next_run_time,4),2) +':' + right(jsch.next_run_time,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(jsch.next_run_time,5),1) +':' + Left(right(jsch.next_run_time,4),2) +':' + right(jsch.next_run_time,2) as char (8)) WHEN 6 THEN cast(Left(right(jsch.next_run_time,6),2) +':' + Left(right(jsch.next_run_time,4),2) +':' + right(jsch.next_run_time,2) as char (8)) END AS NextRunTime FROM ReportServer.dbo.ReportSchedule a LEFT OUTER JOIN ReportServer.dbo.ReportSchedule c ON a.ScheduleID = c.ScheduleID LEFT OUTER JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID LEFT OUTER JOIN ReportServer.dbo.[Catalog] e ON d.report_oid = e.itemid LEFT OUTER JOIN msdb.dbo.sysjobs j ON CAST(a.ScheduleID AS VARCHAR(100)) = j.name LEFT OUTER JOIN msdb.dbo.sysjobschedules jsch ON j.job_id = jsch.job_id LEFT OUTER JOIN msdb.dbo.sysschedules sch ON sch.schedule_id = jsch.schedule_id
-- to find specific report last status -- WHERE e.name = 'Usage Stats'
-- to find failed status --WHERE -- LastStatus <> 'Completed Data Refresh'
-- or for a specific SQL Agent Job --WHERE
--b.name = '9B7BED26-A81C-479C-A23B-016C9E05E760' -- NOTE you have to look in subscription history for any error messages/details and decode the JSON response (good luck with that!)
Check the below blog as well.
This would help you out.
SELECT e.name AS ReportName ,e.path AS ReportPath ,d.description as SubscriptionName , a.SubscriptionID , d.laststatus , d.eventtype , d.LastRunTime ,j.date_created AS ScheduleCreatedDate ,j.date_modified AS ScheduleModifiedDate ,CASE j.[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS Job_Enabled ,CASE sch.[enabled] WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' END AS Schedule_enabled, CASE sch.freq_type WHEN 1 THEN 'Once' WHEN 4 THEN 'Daily' WHEN 8 THEN 'Weekly' WHEN 16 THEN 'Monthly' WHEN 32 THEN 'Monthly relative' WHEN 64 THEN 'When SQLServer Agent starts' END AS Frequency, CASE sch.active_start_date WHEN 0 THEN null ELSE substring(convert(varchar(15),sch.active_start_date),1,4) + '-' + substring(convert(varchar(15),sch.active_start_date),5,2) + '-' + substring(convert(varchar(15),sch.active_start_date),7,2) END AS ScheduleStartDate, CASE len(sch.active_start_time) WHEN 3 THEN cast('00:0' + Left(right(sch.active_start_time,3),1) +':' + right(sch.active_start_time,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(sch.active_start_time,4),2) +':' + right(sch.active_start_time,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(sch.active_start_time,5),1) +':' + Left(right(sch.active_start_time,4),2) +':' + right(sch.active_start_time,2) as char (8)) WHEN 6 THEN cast(Left(right(sch.active_start_time,6),2) +':' + Left(right(sch.active_start_time,4),2) +':' + right(sch.active_start_time,2) as char (8)) END AS ScheduleStartTime ,CASE jsch.next_run_date WHEN 0 THEN null ELSE substring(convert(varchar(15),jsch.next_run_date),1,4) + '-' + substring(convert(varchar(15),jsch.next_run_date),5,2) + '-' + substring(convert(varchar(15),jsch.next_run_date),7,2) END AS nextRunDate, CASE len(jsch.next_run_time) WHEN 3 THEN cast('00:0' + Left(right(jsch.next_run_time,3),1) +':' + right(jsch.next_run_time,2) as char (8)) WHEN 4 THEN cast('00:' + Left(right(jsch.next_run_time,4),2) +':' + right(jsch.next_run_time,2) as char (8)) WHEN 5 THEN cast('0' + Left(right(jsch.next_run_time,5),1) +':' + Left(right(jsch.next_run_time,4),2) +':' + right(jsch.next_run_time,2) as char (8)) WHEN 6 THEN cast(Left(right(jsch.next_run_time,6),2) +':' + Left(right(jsch.next_run_time,4),2) +':' + right(jsch.next_run_time,2) as char (8)) END AS NextRunTime FROM ReportServer.dbo.ReportSchedule a LEFT OUTER JOIN ReportServer.dbo.ReportSchedule c ON a.ScheduleID = c.ScheduleID LEFT OUTER JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID LEFT OUTER JOIN ReportServer.dbo.[Catalog] e ON d.report_oid = e.itemid LEFT OUTER JOIN msdb.dbo.sysjobs j ON CAST(a.ScheduleID AS VARCHAR(100)) = j.name LEFT OUTER JOIN msdb.dbo.sysjobschedules jsch ON j.job_id = jsch.job_id LEFT OUTER JOIN msdb.dbo.sysschedules sch ON sch.schedule_id = jsch.schedule_id
-- to find specific report last status -- WHERE e.name = 'Usage Stats'
-- to find failed status --WHERE -- LastStatus <> 'Completed Data Refresh'
-- or for a specific SQL Agent Job --WHERE
--b.name = '9B7BED26-A81C-479C-A23B-016C9E05E760' -- NOTE you have to look in subscription history for any error messages/details and decode the JSON response (good luck with that!)
Check the below blog as well.
Thank you so much! now I just have to figure out the security part. Really all I want is what AD group they are assigned to or used to login in.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
6 | |
4 | |
3 | |
3 |
User | Count |
---|---|
15 | |
9 | |
6 | |
3 | |
3 |