In these days of endless audits , SOX (Sarbanes-Oxley), and now PCI (Payment Card Industry), it is becoming essential to know when someone within an organization accesses data. It is also helpful to know this information from an organizational and planning perspective. As more and more reports get published, how often reports get looked at and by whom could be essential information. What Is the Execution Log?To address these issues, SSRS keeps an execution log of reports that it has run. Because the database is what stores the data, the log is still good, even in a scale-out environment. The log has a myriad of useful information, such as what reports are run, who has run them, and how long they took to process. Some of the other information it has includes the following:
How to Report Off the Execution LogThe downside of the execution log is that it is not in a human-readable format. To remedy this, Microsoft has distributed a SQL Server Integration Services Package that can be used to port the data from the Report Server's internal execution log table to another database to be used for querying and reporting against the log. There are even some sample reports against the resulting execution log table. If you are still using SQL Server 2000, an equivalent DTS package does the same thing. Three files are central to the extraction and reporting of the execution log. All three files should be located in the <Program Files\Microsoft Sql Server\90\Samples\Reporting Services\Report Samples\Server Management Sample Reports\Execution Log Sample Reports> directory. The first file is Createtables.sql , which is the script used to create the tables for the RSExecutionLog database. The second two files, RSExecutionLog_Update.dtsConfig and RSExecutionLog_Update.dtsx , form the integration package that pushes the data from the Report Server catalog into the RSExecutionLog database. Creating the RSExecutionLog DatabaseYou can create the RSExecutionLog database by completing the following steps (see Figure 20.2):
Figure 20.2. RSExecutionLog SSIS package.To keep data in the RSExecutionLog database current, periodically run the integration package. The package is designed to import new data, without overwriting or removing existing data. To remove old data in the RSExecutionLog database, run the Cleanup.sql script. Overview of the Sample ReportsThree reports come included with the sample reports packages. The first report ( Execution Status Codes.rdl ) includes a summary of reports run by the status they received. This shows the failure rate of reports on the server as well as why the processes failed. The second report ( Execution Summary.rdl ) gives an overview of report executions. It includes some key metrics, such as the number of report processed per day, the top ten most requested reports, and the longest-running reports. This report is shown in Figure 20.3. Figure 20.3. Report Execution Summary report.The last report ( Report Summary.rdl ) is similar to the execution summary, but gives the execution overview of a specific report. The sample reports can actually be published to the Report Server and accessed like any other report (they get logged like any other report as well). The only caveat with these reports is having to set the end date to one day ahead of the current date to include the current day's execution. The reason for this is that the date parameters have no way to accept time, and, hence, time default to 12:00 a.m. (start of the day). This might come up as an issue when you develop your own reports as well. |