/*dstMain*/
SELECT C.[ItemID]
,C.[Name]
,C.[Path]
,C.[Type]
,TypeName = case C.[Type]
when 1 then 'Folder'
when 2 then 'Report'
when 3 then 'Resources'
when 4 then 'Linked Report'
when 5 then 'Data Source'
when 6 then 'Report Model'
when 7 then 'Report Part (SQL 2008 R2, unverified)'
when 8 then 'Shared Dataset (SQL 2008 R2)'
end
,ExecutionCount = COUNT(C.Name)
,SubscriptionCount = COUNT(distinct SUB.[Description])
,LastRan = MAX(el.TimeStart)
--Total Time
,TimeDataRetrievalTotal = SUM(el.TimeDataRetrieval)
,TimeProcessingTotal = SUM(el.TimeProcessing)
,TimeRenderingTotal = SUM(el.TimeRendering)
,TimeTotal = SUM(el.TimeDataRetrieval)+SUM(el.TimeProcessing)+SUM(el.TimeRendering)
--Average Time
,TimeDataRetrievalAverage = AVG(el.TimeDataRetrieval)
,TimeProcessingAverage = AVG(el.TimeProcessing)
,TimeRenderingAverage = AVG(el.TimeRendering)
,TimeTotalAverage = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
--Max Time
,TimeDataRetrievalMax = MAX(el.TimeDataRetrieval)
,TimeProcessingMax = MAX(el.TimeProcessing)
,TimeRenderingMax = MAX(el.TimeRendering)
,TimeTotalMax = MAX(el.TimeDataRetrieval)+MAX(el.TimeProcessing)+MAX(el.TimeRendering)
--Min Time
,TimeDataRetrievalMin = MIN(el.TimeDataRetrieval)
,TimeProcessingMin = MIN(el.TimeProcessing)
,TimeRenderingMin = MIN(el.TimeRendering)
,TimeTotalMin = MIN(el.TimeDataRetrieval)+MIN(el.TimeProcessing)+MIN(el.TimeRendering)
,ByteCountSum = SUM(el.ByteCount)
,RowCountSum = SUM(el.[RowCount])
,CreatedBy = U.UserName
,CreationDate = C.CreationDate
,ModifiedBy = UM.UserName
,ModifiedDate = C.ModifiedDate
FROM dbo.Catalog C
JOIN dbo.Users U
ON C.CreatedByID = U.UserID
JOIN dbo.Users UM
ON c.ModifiedByID = UM.UserID
LEFT JOIN ExecutionLog el
ON C.ItemID = el.ReportID
LEFT JOIN dbo.Subscriptions SUB
ON SUB.Report_OID = c.ItemID
WHERE C.[Type] = 2 --Reports
AND c.ItemID = @prmReport
AND el.TimeStart >= @prmDate
--AND Name like @ReportName
GROUP BY
C.[ItemID]
,C.[Name]
,C.[Path]
,C.[Type]
,U.UserName
,C.CreationDate
,UM.UserName
,C.ModifiedDate
/*dstReportSubscriptions*/
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, SUB.SubscriptionID, SUB.MatchData,
ExecuteText = 'exec dbo.AddEvent @EventType=''SharedSchedule'', @EventData=' + cast(SUB.MatchData as nvarchar(max)) + ''''
FROM Subscriptions AS SUB INNER JOIN
Users AS USR ON SUB.OwnerID = USR.UserID INNER JOIN
Catalog AS CAT ON SUB.Report_OID = CAT.ItemID INNER JOIN
ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID AND SUB.SubscriptionID = RS.SubscriptionID INNER JOIN
Schedule AS SCH ON RS.ScheduleID = SCH.ScheduleID
WHERE (CAT.Type = 2) AND (CAT.ItemID = @prmReport)
ORDER BY SubscriptionOwner, ReportPath
/*dstDataSource*/
DECLARE @SQL nvarchar(max)
Declare @Namespace NVARCHAR(500)
SELECT @Namespace= SUBSTRING(
x.CatContent
,x.CIndex
,CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex
)
FROM
(
SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
,CIndex = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
FROM Reportserver.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) X
SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace +''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )
SELECT ReportName = name
,DataSourceName = x.value(''(@Name)[1]'', ''VARCHAR(250)'')
,DataProvider = x.value(''(ConnectionProperties/DataProvider)[1]'',''VARCHAR(250)'')
,ConnectionString = x.value(''(ConnectionProperties/ConnectString)[1]'',''VARCHAR(250)'')
FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
AND C.ItemID = ''' + @prmReport + '''
) a
CROSS APPLY reportXML.nodes(''/Report/DataSources/DataSource'') r ( x )
ORDER BY name ;'
EXEC(@SQL)
/*dstDataSets*/
DECLARE @SQL nvarchar(max)
Declare @Namespace NVARCHAR(500)
SELECT @Namespace= SUBSTRING(
x.CatContent
,x.CIndex
,CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex
)
FROM
(
SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
,CIndex = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
FROM Reportserver.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
) X
SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + ''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )
SELECT distinct
ReportName = name
,DataSetName = x.value(''(@Name)[1]'', ''VARCHAR(250)'')
,DataSourceName = x.value(''(Query/DataSourceName)[1]'',''VARCHAR(250)'')
,CommandText = x.value(''(Query/CommandText)[1]'',''VARCHAR(max)'')
--,Fields = df.value(''(@Name)[1]'',''VARCHAR(250)'')
--,DataField = df.value(''(DataField)[1]'',''VARCHAR(250)'')
--,DataType = df.value(''(rd:TypeName)[1]'',''VARCHAR(250)'')
FROM ( SELECT C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
FROM dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
AND C.ItemID = ''' + @prmReport + '''
) a
CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet'') r ( x )
--CROSS APPLY x.nodes(''Fields/Field'') f(df)
ORDER BY name '
EXEC(@SQL)
/*dstTimings*/
DECLARE @prmReport nvarchar(max) = '6D0597A6-5A64-4AF5-8AB8-AE5E29B78363'
DECLARE @prmDate datetime = '2017-01-01'
SELECT [Typeorder]=1
,[Type] = 'Max'
,TimeDataRetrievalMax = MAX(el.TimeDataRetrieval)
,TimeProcessingMax = MAX(el.TimeProcessing)
,TimeRenderingMax = MAX(el.TimeRendering)
,TimeTotalMax = MAX(el.TimeDataRetrieval)+MAX(el.TimeProcessing)+MAX(el.TimeRendering)
FROM dbo.Catalog C
LEFT JOIN ExecutionLog el
ON C.ItemID = el.ReportID
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
UNION ALL
SELECT [Typeorder]=2
,[Type] = 'Min'
,TimeDataRetrievalMax = MIN(el.TimeDataRetrieval)
,TimeProcessingMax = MIN(el.TimeProcessing)
,TimeRenderingMax = MIN(el.TimeRendering)
,TimeTotalMax = MIN(el.TimeDataRetrieval)+MIN(el.TimeProcessing)+MIN(el.TimeRendering)
FROM dbo.Catalog C
LEFT JOIN ExecutionLog el
ON C.ItemID = el.ReportID
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
UNION ALL
SELECT [Typeorder]=3
,[Type] = 'Average'
,TimeDataRetrievalMax = AVG(el.TimeDataRetrieval)
,TimeProcessingMax = AVG(el.TimeProcessing)
,TimeRenderingMax = AVG(el.TimeRendering)
,TimeTotalMax = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
FROM dbo.Catalog C
LEFT JOIN ExecutionLog el
ON C.ItemID = el.ReportID
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
UNION ALL
SELECT [Typeorder]=4
,[Type] = 'Cached'
,TimeDataRetrievalMax = AVG(el.TimeDataRetrieval)
,TimeProcessingMax = AVG(el.TimeProcessing)
,TimeRenderingMax = AVG(el.TimeRendering)
,TimeTotalMax = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
FROM dbo.Catalog C
LEFT JOIN [dbo].[ExecutionLog3] el
ON C.[Path] = el.ItemPath
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
AND [Source] = 'Cache'
