How to get all SSRS subscription data using stored procedure?

Question

I am new to ssrs.

I want to get all the possible data for a signed ssrs report that is available in the ResportServer database.

I found several queries, but they do not have the correct data. It works for only one report.

I need a list of unique data subscriptions. If a possible stored procedure is preferred.

My request:

SELECT b.name AS JobName , e.name , e.path , d.description , a.SubscriptionID , laststatus , eventtype , LastRunTime , date_created , date_modified FROM ReportServer.dbo.ReportSchedule a JOIN msdb.dbo.sysjobs b ON a.ScheduleID = b.name JOIN ReportServer.dbo.ReportSchedule c ON b.name = c.ScheduleID JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID JOIN ReportServer.dbo.Catalog e ON d.report_oid = e.itemid WHERE e.name = 'Sales_Report' 

Thanks in advance.

Answer 1

I have the same requirement as you have now ...

See the stored procedure below.

 CREATE PROCEDURE [dbo].[GetSubscriptionData] AS BEGIN SET NOCOUNT ON; WITH [Sub_Parameters] AS ( SELECT [SubscriptionID], [Parameters] = CONVERT(XML,a.[Parameters]) FROM [Subscriptions] a ), [MySubscriptions] AS ( SELECT DISTINCT [SubscriptionID], [ParameterName] = QUOTENAME(p.value('(Name)[1]', 'nvarchar(max)')), [ParameterValue] = p.value('(Value)[1]', 'nvarchar(max)') FROM [Sub_Parameters] a CROSS APPLY [Parameters].nodes('/ParameterValues/ParameterValue') t(p) ), [SubscriptionsAnalysis] AS ( SELECT a.[SubscriptionID], a.[ParameterName], [ParameterValue] = ( SELECT STUFF((SELECT [ParameterValue] + ', ' as [text()] FROM [MySubscriptions] WHERE [SubscriptionID] = a.[SubscriptionID] AND [ParameterName] = a.[ParameterName] FOR XML PATH('') ),1, 0, '') +'' ) FROM [MySubscriptions] a GROUP BY a.[SubscriptionID],a.[ParameterName] ) SELECT DISTINCT (a.[SubscriptionID]), c.[UserName] AS Owner, b.Name, b.Path, a.[Locale], a.[InactiveFlags], d.[UserName] AS Modified_by, a.[ModifiedDate], a.[Description], a.[LastStatus], a.[EventType], a.[LastRunTime], a.[DeliveryExtension], a.[Version], sch.StartDate, --e.[ParameterName], --LEFT(e.[ParameterValue],LEN(e.[ParameterValue])-1) as [ParameterValue], SUBSTRING(b.PATH,2,LEN(b.PATH)-(CHARINDEX('/',REVERSE(b.PATH))+1)) AS ProjectName FROM [Subscriptions] a INNER JOIN [Catalog] AS b ON a.[Report_OID] = b.[ItemID] Inner Join ReportSchedule as RS on rs.SubscriptionID = a.SubscriptionID INNER JOIN Schedule AS Sch ON Sch.ScheduleID = rs.ScheduleID LEFT OUTER JOIN [Users] AS c ON a.[OwnerID] = c.[UserID] LEFT OUTER JOIN [Users] AS d ON a.MODIFIEDBYID = d.Userid LEFT OUTER JOIN [SubscriptionsAnalysis] AS e ON a.SubscriptionID = e.SubscriptionID; END 

This is a simplified request to get all SSRS subscribers.

 SELECT USR.UserName AS SubscriptionOwner ,SUB.ModifiedDate ,SUB.[Description] ,SUB.EventType ,SUB.DeliveryExtension ,SUB.LastStatus ,SUB.LastRunTime ,SCH.NextRunTime ,SCH.Name AS ScheduleName ,CAT.[Path] AS ReportPath ,CAT.[Description] AS ReportDescription FROM dbo.Subscriptions AS SUB INNER JOIN dbo.Users AS USR ON SUB.OwnerID = USR.UserID INNER JOIN dbo.[Catalog] AS CAT ON SUB.Report_OID = CAT.ItemID INNER JOIN dbo.ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID AND SUB.SubscriptionID = RS.SubscriptionID INNER JOIN dbo.Schedule AS SCH ON RS.ScheduleID = SCH.ScheduleID ORDER BY USR.UserName, CAT.[Path]; 

if you still have any request, comment on it.