It's not entirely straight forward or obvious how you query this. There are a number of tables in the ReportServer DB that need to be linked together but you also have to hop onto MSDB to get the actual schedule details. The actual refresh jobs get built as SQL agent jobs with GUIDs for names. These GUIDS track back to the schedules you create in SSRS in the ReportServer DB.
This query gives you a good chunk of the kind of data you might need. Other stuff is available in the various tables but this gives you a decent starting point for how it all hooks together to generate somehting useful
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!)
