Report Execution History


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:

  • Name of the physical machine that ran the report (Report Servernot database server)

  • Unique ID of the report

  • Unique ID of the user running the report

  • Whether the request came from a user or system process

  • What rendering format was used

  • Values of the report parameters

  • When the report process started and when it finished

  • Amount of time the server took to process the report in milliseconds

  • Type of data used for execution (live, cached, snapshot, history)

  • Final status code of the report processing (success or first error code)

  • Final size of the rendered report in bytes

  • Number of rows returned in the data sets of the rendered reports

How to Report Off the Execution Log

The 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 Database

You can create the RSExecutionLog database by completing the following steps (see Figure 20.2):

1.
Open SQL Server Management Studio, connect to the database engine, and select Master as the default database.

2.
Run the following query:

 create database RSExecutionLog      go      use RSExecutionLog      go 

3.
Open the createtable.sql file and execute it in the RSExecutionLog database. The results of the script should be as follows :

 Dropping tables...      Creating ReportTypes...      Creating Reports...        Creating Users...      Creating Machines...        Creating RequestTypes...      Creating SourceTypes...        Creating FormatTypes...      Creating StatusCodes...       Creating ExecutionLogs...       Creating ExecutionParameters...       Creating RunLogs...       Script completed. 

4.
Double-click on the dtsx file and click Execute to execute the package.

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 Reports

Three 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.



Microsoft SQL Server 2005 Reporting Services
Microsoft SQL Server 2005 Reporting Services
ISBN: 0672327996
EAN: 2147483647
Year: 2004
Pages: 254

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net