/*
Ran instances oof reports with the parameters
*/
SELECT x.TimeStart
,c.Name
,x.USERNAME
,x.Parameters
,c.Type
,x.TimeDataRetrieval
,x.TimeProcessing
,x.TimeRendering
,x.ByteCount
,x.[RowCount]
,c.Path
,'XX' as 'Catelog_Fields_Start_Here'
,c.*
,'XX' as 'ExecutionLog_Fields_Start_Here'
,x.*
FROM Catalog c
INNER JOIN
ExecutionLog x
ON c.ItemID = x.ReportID
WHERE Type = 2 AND x.USERNAME like '%gerard%'
order by x.TimeStart DESC
/*
code to get xml of report from content field
*/
--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
SELECT
ItemID,Name,[Type]
,CASE Type
WHEN 2 THEN 'Report'
WHEN 5 THEN 'Data Source'
WHEN 7 THEN 'Report Part'
WHEN 8 THEN 'Shared Dataset'
ELSE 'Other'
END AS TypeDescription
,CONVERT(varbinary(max),Content) AS Content
FROM ReportServer.dbo.Catalog
WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CASE
WHEN LEFT(Content,3) = 0xEFBBBF
THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
ELSE
Content
END AS Content
FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
SELECT
ItemID,Name,[Type],TypeDescription
,CONVERT(xml,Content) AS ContentXML
FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
ItemID,Name,[Type],TypeDescription,ContentXML
,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
-- dsMain
SELECT
ReportID,
Name AS ReportName,
InstanceName,
COALESCE(C.Path, 'Unknown') AS ItemPath,
UserName,
ExecutionId,
CASE(RequestType)
WHEN 0 THEN 'Interactive'
WHEN 1 THEN 'Subscription'
WHEN 2 THEN 'Refresh Cache'
ELSE 'Unknown'
END AS RequestType, -- SubscriptionId,
Format, Parameters,
CASE(ReportAction) WHEN 1 THEN 'Render'
WHEN 2 THEN 'BookmarkNavigation'
WHEN 3 THEN 'DocumentMapNavigation'
WHEN 4 THEN 'DrillThrough'
WHEN 5 THEN 'FindString'
WHEN 6 THEN 'GetDocumentMap'
WHEN 7 THEN 'Toggle'
WHEN 8 THEN 'Sort'
WHEN 9 THEN 'Execute'
ELSE 'Unknown'
END AS ItemAction,
TimeStart,
TimeEnd,
TimeDataRetrieval,
TimeProcessing,
TimeRendering,
CASE(Source)
WHEN 1 THEN 'Live'
WHEN 2 THEN 'Cache'
WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History'
WHEN 5 THEN 'AdHoc'
WHEN 6 THEN 'Session'
WHEN 7 THEN 'Rdce'
ELSE 'Unknown'
END AS Source,
CASE (Status)
WHEN 'rsHttpRuntimeClientDisconnectionError' THEN 'HttpRuntimeClientDisconnectionError'
WHEN 'rsReportServerDatabaseError' THEN 'ReportServerDatabaseError'
WHEN 'rsProcessingAborted' THEN 'Processing Aborted'
WHEN 'rsSuccess' THEN 'Success'
WHEN 'rrRenderingError' THEN 'Rendering Error'
ELSE 'Unknown'
END AS Status,
ByteCount,
[RowCount],
AdditionalInfo
FROM ExecutionLogStorage EL WITH(NOLOCK)
LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
WHERE ReportID = @ReportList
;
--dsSSRSReportList
Select ItemID
,[Name]
From [Catalog] c
Where [Type] = 2
AND [Hidden] <> 1
AND SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1)) = @FolderList
ORDER BY c.[Name]
--dsSSRSFolderList
--Returns a list of the folder structure on SSRS Server
SELECT DISTINCT SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1)) As [Path] From [Catalog] c
create VIEW [dbo].[vw_REPORT_JWS_SSRS_ReportUsage]
AS
SELECT ReportName,[Path]
,Runs
, DATEDIFF(dd, (select Min(timestart) from ExecutionLogStorage),GETDATE())+1 AS DaysHistoryPresent
,CASE
WHEN DATEDIFF(dd, (select Min(timestart) from ExecutionLogStorage),GETDATE())+1 = 0 OR Runs = 0 THEN 0
ELSE Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)
END AS ApproxRunsPerDay
, Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)*30 AS ApproxRunsPerMonth
FROM
(
SELECT
c.Name AS ReportName
,c.Path
,Count(1) AS Runs
,MIN(el.TimeStart) AS FirstDay
,MAX(el.TimeStart) AS LastDay
FROM ExecutionLogStorage EL WITH(NOLOCK)
LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
WHERE el.Source <> 6
AND [Name] NOT IN ('Global', 'Default','dsRptFmt','dsRptFmt_Internal','Dummy')
GROUP BY c.Name,c.Path
) AS a
ORDER BY
CASE
WHEN DATEDIFF(dd, FirstDay,LastDay)+1 = 0.000 OR Runs = 0 THEN 0.000
ELSE (Runs/ DATEDIFF(dd, (select Min(timestart) from ExecutionLogStorage),GETDATE())+1)
END DESC
GO
