November 11, 2009 at 10:08 pm
Part of this depends on whether they are looking for subscriptions having run or failures.
I tend to look only at exceptions, and use MOM (or SCOM) to monitior the windows event log for failures.
RS has event logs that can also be monitored.
And if you query the tables within your RS db, you can look for sucesses and failures, along with begin and end times, and even rendering times.
As mentioned previously, these are not really meant to be queried.
But there are a few options, depending on what the real business need is.
I focus more on exceptions, as this is where I see more value.
For example, we run our nightly ETL and cube build on the same box. If it runs late, reports can run slow. So the times may not be as important as the failures.
Greg E
March 24, 2010 at 8:31 am
My users have the same requirements to
1) Document who has what report subscription
2) Show a list of reports that were scheduled to run each day and the status of the runs.
As Microsoft will not document these tables
This is what I have found.
1) the report table is called catalog. The key ItemId in the catalog table is the foreign key to all of the other tables ReportId
2) The subscription table lists last time the subscription was executed, but no status on a successful last run.
3) The executionLog table does not easily join to the report or subscription. It looks like the subscription puts the report into a queue, but actual execution times do not map back to the execution log.
4) The subscription id joins to jobname in the MSDB.sysjobs table. This should provide a listing with a join to the jobs history table.....working on that now.
I will post the (yes, I know MS might change the tables in the future) SQL for the reports, but if anyone else has anything to add...let me know.
Eric
March 24, 2010 at 9:21 am
ok, I figured it out....join is to sysjobs name to convert(sysname, schedule_id)
Note: it will not list out all subscriptions, but only those that have been scheduled and are active.
I am not done testing it, so use at your own risk..........and needs to be run in your reportServer database.
Enjoy
EP
SELECT
r.name as 'Report Name',
sub.[description] as 'Description',
'Last Run Stat'= isnull(Case when Q2.run_status = 1 then 'Succeeded'
when Q2.run_status = 0 then 'Failed'
when Q2.run_status = 2 then 'Retry'
when Q2.run_status = 3 then 'Canceled'
when Q2.run_status = 4 then 'Running'
End,'NA'),
'Last Run Date' = isnull (
CASE q2.run_date
WHEN 0 THEN 'N/A'
ELSE substring(convert(varchar(15),q2.run_date),1,4) + '/' +
substring(convert(varchar(15),q2.run_date),5,2) + '/' +
substring(convert(varchar(15),q2.run_date),7,2)
end, 'N/A'),
'Last Run Time' = isnull(
CASE len(q2.run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(q2.run_time,3),1)
+':' + right(q2.run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(q2.run_time,5),1)
+':' + Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(q2.run_time,6),2)
+':' + Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
END, 'NA'),
'Job Enab' = CASE J.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Sched Enab' = CASE S.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Sched Freq' = CASE s.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,
'Occurs'= s.freq_subday_interval,
'Sub Freq Interval'= Case s.freq_subday_type
when 4 then 'Minutes'
When 8 then 'Hours'
End,
'Start Date' = CASE active_start_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),active_start_date),1,4) + '/' +
substring(convert(varchar(15),active_start_date),5,2) + '/' +
substring(convert(varchar(15),active_start_date),7,2)
END,
'Start Time' = CASE len(active_start_time)
WHEN 3 THEN cast('00:0'
+ Left(right(active_start_time,3),1)
+':' + right(active_start_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(active_start_time,5),1)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
WHEN 6 THEN cast(Left(right(active_start_time,6),2)
+':' + Left(right(active_start_time,4),2)
+':' + right(active_start_time,2) as char (8))
END,
isnull(CASE len(Q1.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(Q1.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q1.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q1.run_duration,3),1)
+':' + right(Q1.run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q1.run_duration,4),2)
+':' + right(Q1.run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q1.run_duration,5),1)
+':' + Left(right(Q1.run_duration,4),2)
+':' + right(Q1.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(Q1.run_duration,6),2)
+':' + Left(right(Q1.run_duration,4),2)
+':' + right(Q1.run_duration,2) as char (8))
END,'NA') as 'Avg Duration',
isnull(CASE len(Q2.[Last Duration])
WHEN 1 THEN cast('00:00:0'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q2.[Last Duration],3),1)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q2.[Last Duration],5),1)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
END,'NA') as 'Last Duration'
FROM MSDB.dbo.sysjobs J
join ReportSchedule RS
on j.name = convert(sysname, rs.scheduleId)
join Subscriptions Sub
on RS.subscriptionId = sub.subscriptionId
join catalog R
on RS.ReportID = r.itemId
LEFT OUTER JOIN MSDB.dbo.sysjobschedules JS
ON J.job_id = JS.job_id
Left outer join msdb.dbo.sysschedules s
on JS.schedule_id = s.schedule_id
LEFT OUTER JOIN (SELECT job_id, avg(run_duration) AS run_duration
FROM MSDB.dbo.sysjobhistory
GROUP BY job_id) Q1
ON J.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status,T0.run_date, T0.run_time,'Last Duration'=T0.run_duration from MSDB.dbo.sysjobhistory T0
inner join
(select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON j.job_id = Q2.job_id
WHERE Next_run_time = 0
--and path like '/Genesys%'
UNION
SELECT r.name as 'Report Name',
sub.[description] as 'Description',
'Last Run Stat' = isnull(Case when Q2.run_status = 1 then 'Succeeded'
when Q2.run_status = 0 then 'Failed'
when Q2.run_status = 2 then 'Retry'
when Q2.run_status = 3 then 'Canceled'
when Q2.run_status = 4 then 'Running'
End,'NA'),
'Last Run Date' = isnull (
CASE q2.run_date
WHEN 0 THEN 'N/A'
ELSE substring(convert(varchar(15),q2.run_date),1,4) + '/' +
substring(convert(varchar(15),q2.run_date),5,2) + '/' +
substring(convert(varchar(15),q2.run_date),7,2)
end, 'N/A'),
'Last Run Time' = isnull(
CASE len(q2.run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(q2.run_time,3),1)
+':' + right(q2.run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(q2.run_time,5),1)
+':' + Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(q2.run_time,6),2)
+':' + Left(right(q2.run_time,4),2)
+':' + right(q2.run_time,2) as char (8))
END, 'NA'),
'Job Enab' = CASE j.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Sched Enabled' = CASE s.Enabled
WHEN 1 THEN 'Yes'
WHEN 0 THEN 'No'
END,
'Sched Freq' = CASE 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,
'Occurs'=freq_subday_interval,
'Sub Freq Interval'= Case freq_subday_type
when 4 then 'Minutes'
When 8 then 'Hours'
End,
'Start Date' = CASE next_run_date
WHEN 0 THEN null
ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
'Start Time' = isnull(CASE len(next_run_time)
WHEN 3 THEN cast('00:0'
+ Left(right(next_run_time,3),1)
+':' + right(next_run_time,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
WHEN 6 THEN cast(Left(right(next_run_time,6),2)
+':' + Left(right(next_run_time,4),2)
+':' + right(next_run_time,2) as char (8))
END,'NA'),
isnull(CASE len(Q1.run_duration)
WHEN 1 THEN cast('00:00:0'
+ cast(Q1.run_duration as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q1.run_duration as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q1.run_duration,3),1)
+':' + right(Q1.run_duration,2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q1.run_duration,4),2)
+':' + right(Q1.run_duration,2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q1.run_duration,5),1)
+':' + Left(right(Q1.run_duration,4),2)
+':' + right(Q1.run_duration,2) as char (8))
WHEN 6 THEN cast(Left(right(Q1.run_duration,6),2)
+':' + Left(right(Q1.run_duration,4),2)
+':' + right(Q1.run_duration,2) as char (8))
END,'NA') as 'Avg Duration',
isnull(CASE len(Q2.[Last Duration])
WHEN 1 THEN cast('00:00:0'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 2 THEN cast('00:00:'
+ cast(Q2.[Last Duration] as char) as char (8))
WHEN 3 THEN cast('00:0'
+ Left(right(Q2.[Last Duration],3),1)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 4 THEN cast('00:'
+ Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 5 THEN cast('0'
+ Left(right(Q2.[Last Duration],5),1)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
WHEN 6 THEN cast(Left(right(Q2.[Last Duration],6),2)
+':' + Left(right(Q2.[Last Duration],4),2)
+':' + right(Q2.[Last Duration],2) as char (8))
END,'NA') as 'Last Duration'
FROM MSDB.dbo.sysjobs J
join ReportSchedule RS
on j.name = convert(sysname, rs.scheduleId)
join Subscriptions Sub
on RS.subscriptionId = sub.subscriptionId
join catalog R
on RS.ReportID = r.itemId
LEFT OUTER JOIN MSDB.dbo.sysjobschedules JS
ON j.job_id = JS.job_id
Left outer join msdb.dbo.sysschedules s
on JS.schedule_id = s.schedule_id
LEFT OUTER JOIN (SELECT job_id, avg(run_duration) AS run_duration
FROM MSDB.dbo.sysjobhistory
GROUP BY job_id) Q1
ON j.job_id = Q1.job_id
Left outer join (select T0.job_id,T0.run_status, T0.run_date, T0.run_time,'Last Duration'=T0.run_duration from MSDB.DBO.sysjobhistory T0
inner join
(select job_id,'Instance_id'=max(instance_id) FROM MSDB.dbo.sysjobhistory where step_id=1 GROUP BY job_id ) T1
On
T0.job_id=T1.job_id and
T0.instance_id=T1.instance_id) Q2
ON j.job_id = Q2.job_id
WHERE Next_run_time <> 0
-- and path like '/Genesys%'
ORDER BY 1,4,5, 2
March 25, 2010 at 6:43 am
We use Data Driven Subscriptions, and here's how I take a quick look at recent failures.
Since we run Enterprise Edition, not sure how this might compare with other editions, or user generated subscriptions.
No 'Successful' in Last Status, but enough for me to use it. I only care to see failures, or a Pending report when I run this. Bulk of our use is Sunday night, so I just run this Monday morning to see where we might have had problems.
Greg E
USE your RS Database
select
SubscriptionID,
Report_OID,
[Path],
[Name],
a.[Description],
LastStatus,
LastRunTime,
Parameters
from
dbo.Subscriptions a
INNER JOIN Catalog b on Report_OID = ItemID
where LastRunTime >= GetDate() -1
and LastStatus NOT LIKE 'Mail Sent%'
and (LastStatus LIKE '%Error:%'
or RIGHT(LastStatus,10) <> ' 0 errors.')
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply