查找过去30天内从未成功运行的SSRS订阅?

Question

我正试图编写一个SQL选择语句来查找在过去30天内从未成功运行的SSRS订阅，因此我可以禁用这些订阅。

有人知道怎么做吗?由于每次作业运行时执行日志都会创建唯一的记录，因此我试图根据报告路径对它们进行分组，但却陷入了困境。有人能帮帮我吗?

我的想法是:

SELECT ItemPath
FROM [ReportServer].dbo.ExecutionLog3 EX
GROUP BY ItemPath, Status, TimeEnd
HAVING Ex.TimeEnd >= DATEADD(day, -30, GETDATE())
AND Status != 'rsSuccess'

Answer 1

这里有一个算法可以帮助你:

With
Subscriptions As
(
Select SubscriptionId, SubscriptionName
From dbo.TableOfSubscriptions
)
, Executions As
(
Select SubscriptionId, ExecutionStatus, Count (*) NumExecutions
From dbo.TableOfExecutions
Group By SubscriptionId, ExecutionStatus
)
, SubscriptionsExecutions As
(
Select 
SubscriptionId, SubscriptionName
, IsNull((Select Sum (NumExecutions) From Executions Where ExeuctionStatus = 'Failed'), 0) ExecutionFailed
, IsNull((Select Sum (NumExecutions) From Executions Where ExeuctionStatus = 'Success'), 0) ExecutionSuccessful
From Subscriptions
)
Select *
From SubscriptionsExecutions
Where ExecutionSuccessful = 0
and ExecutionFailed <> 0