As the individual responsible for managing the usage, content, permissions and overall health of my company's enterprise reporting (using SSRS), I found it difficult to proactively stay on top of every facet of my job. Often I would get questions regarding a report that produced an error, how often an item was being used (and by whom), what subscriptions were running and who had permissions to various objects. Each of these questions had an answer, but it would usually take anywhere between a couple minutes to a few hours to solidly respond. After one such incident I decided to delve into the ReportServer database and create my own diagnostic reports to assist me in the future. I've found that the time spend was well worth the investment. This article is aimed at providing you the reports I created as well as giving a demonstration of their features. Enjoy!!!
This SSRS Diagnostics Report Pack was developed in SSRS 2005 (I am not sure of the schema of the 2008 ReportServer is different). It is 10 reports (.rdl files) in total and only requires an SSRS data source called "ds_ReportServer". The "master" report is simple report with a handful of sub-reports and a document map for easy navigation. The available items from the document map are as follows:
Report Inventory: This provides a list of every report on the Report Server. The item's path, data source, creation and modified date as well as the last access date are shown. In addition, a column notes whether each particular report has a subscription (this field can be clicked to view the "Subscriptions" report for details). The background color of the "Last Used" column will be highlighted should the report have produced an error on its last execution.
Subscriptions: This details every subscription on the Report Server and provides key information such as the status, last execution time, render format, write mode. Depending upon the type of subscription the file share path or the mail-to information will be shown. As with the inventory, the background of a cell (status) will change if the most recent run of the subscription produced an error.
Users: This gives a definitive list of all the users or active directory groups who have explicit access to a report somewhere on the server. The list of users are in alphabetical order and can be selected to show a narrowed down view of the Permissions report (see below) for just that individual.
Permissions: This provides a list of every folder and report on the Report Server represented in a similar navigational flow as one would find within Report Manager. Rows are color coded (gray for folder and white for report) and child items are stepped-in from their parent for easy identification. Each row can be expanded to view the user permissions to provide a quick and easy understanding of the security level for any object.
Usage Statistics: This details every report and its important metrics such as average run time, average rows returned, success percentage, last failure and total views. Each report can also be selected to view an execution summary displaying information on every run of the report (i.e.: who viewed the report and when, what the status was, what parameters (if any) were submitted and what format was rendered). Also available on this report are six month windows tracking the number of users and hits to the overall Report Server. Lastly charts identifying the top 10 reports and users, as well as reports receiving no hits (which could be considered for deprecation) are shown.
Command Text: This displays every report and the queries used within it. If the query is a stored procedure, the report will note which proc is used. Should the command text be a SQL statement, the entire query will be shown. This report can be accessed directly from the document map or by clicking on the "Average Run Time" value from the Statistics report. When accessing via the Statistics report, only the query for the selected item will be displayed. Note - some queries may not be shown if the number of characters is excessively long.
Server Configuration: The final report is a simple item noting the configuration settings of the Report Server. In environments with multiple servers, this can be used to identify differences and produce a standard configuration.
These reports have saved many hours diagnosing failures, determining data sources and user permission settings. They have also eased maintenance as they provide key insight into the usage of individual reports. Attached is a compressed file containing the necessary rdl files. These can be imported into a Visual Studio (or Business Intelligence Development Studio) report server project and deployed from that environment. They could also be uploaded directly into Report Manager. Either installation route will require the creation of an SSRS data source called ds_ReportServer (pointing to the server's ReportServer database). Depending upon your installation route, you may have to manually configure or refresh the SSRS data source location. When deployed within Report Manager, this would be done by selecting the report properties and then noting the location of the data source.
Cheers...
