我需要获取运行时日志以进行订阅。
我需要在一段时间内（从挂起状态到成功或失败状态）知道调用了多少次订阅以及运行了多长时间。
我试图从msdb.dbo的所有作业表中获取信息，但是运行时有所不同。
任何帮助将不胜感激。
Jocelynsun 回答：SSRS如何获取运行时日志进行订阅？
这是一个开始。.它不能满足您的所有需求。.但是一定足以使您入门
SELECT
sj.[name] AS [Job Name],rs.SubscriptionID,c.[Name] AS [Report Name],c.[Path],su.Description,su.EventType,su.LastStatus,su.LastRunTime
FROM msdb..sysjobs AS sj
INNER JOIN ReportServer..ReportSchedule AS rs
ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128))
INNER JOIN ReportServer..Subscriptions AS su
ON rs.SubscriptionID = su.SubscriptionID
INNER JOIN ReportServer..[Catalog] c
ON su.Report_OID = c.ItemID
SSRS ReportServer 帮助我们找到有关报告执行的详细信息以及其他详细信息。因此我们可以使用以下查询找出以下内容的答案：
-多少次订阅被调用？
-要花多长时间？
SELECT TMP_TBL.*,sc.LastStatus,sc.LastRunTime FROM (
select ROW_NUMBER() OVER(ORDER BY TimeEnd DESC) AS Rw_Nr,CASE(RequestType)
WHEN 0 THEN 'Interactive'
WHEN 1 THEN 'Subscription'
WHEN 2 THEN 'Refresh Cache'
ELSE 'Unknown'
END AS RequestType,DATEDIFF(second,TimeStart,TimeEnd) AS Execution_Time,Status,UserName,Format,c.ItemID
FROM ExecutionLogStorage EL WITH(NOLOCK)
LEFT JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
where RequestType=1
-- AND el.TimeEnd BETWEEN @BegTime AND @EndTime
) AS TMP_TBL LEFT JOIN Subscriptions sc ON sc.Report_OID = TMP_TBL.ItemID and TMP_TBL.Rw_Nr=1