Handy code snippits for exploring the ssrs database

/*
    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