Querying and Reporting on Report Execution Log Data
To view report server log information, you must run a DTS package that Reporting Services provides to extract the data from the execution log and put it into a table structure that you can query. The internal table in the report server database does not present the data in a format that is accessible to users. The DTS package resolves this problem by collecting all of the data you need and putting it into a table structure that you can understand.
Before you start, you must create a database. Other files that you need are located in the \80\Tools\Reporting Services\ExecutionLog folder. These files include cleanup.sql, createtables.sql, rsexecutionlog_update.dts, and rsexecutionlog_update.ini. If the files are located in a different path, you must perform step 2 in the next section, "Extracting Execution Log Data".
If you use non-default values, you must also edit an .ini file. You only need to perform these steps once.
- In Enterprise Manager, create a new database that the DTS package can use as the destination data source. Use the name RSExecutionLog if you want to use the default name. Note that you must use this name if you want to run the DTS package from within DTS Designer.
- In Query Analyzer, open createtables.sql and then click Execute on the Query menu to add tables to the database. Be sure to select the database you created in step 2 before you run the script.
- Using Notepad, edit rsexecutionlog_update.ini to specify the report server database (target) and the execution log database (destination). If you are using default database names (reportserver and RSExecutionLog, respectively), you do not need to modify this file.
Extracting Execution Log Data
Follow these steps to extract execution log data.
- In Enterprise Manager, right-click Data Transformation Packages, click Open Package, navigate to the folder that contains the files, and RSExecutionLog_Update, and then click OK.
- (Optional.). If the path to the files is different from the default path, edit the DTS package global variable sConfigINI.
- On the Package menu, click Properties.
- Click Global Variables.
- In sConfigINI, type the full path and file name of the .ini file (for example, "c:\logfolder\rsexecutionlog_update.ini"), and then click OK.
- On the Package menu, click Execute to run the DTS package.
Viewing Execution Log Data
Reporting Services includes several reports that you can use to view execution log data. The report definition and project files are located in the \Extras\Execution Log Sample Reports folder on the product CD-ROM. To publish these reports to a report server, do the following:
- Navigate to the \Extras\Execution Log Sample Reports folder on the product CD-ROM, and then double-click executionlog.sln to open the solution in Visual Studio.
- Right-click the ExecutionLog project, and then click Properties.
- In TargetServerURL, specify the URL to the report server that will host the reports, and then click OK. If you are publishing to a local report server instance, you can use the default value http://localhost/reportserver.
- Right-click the ExecutionLog project, and then click Deploy to publish the reports.
- Open Report Manager. For instructions on how to do this, see Report Manager.
- Open the ExecutionLog folder. If Report Manager was open before you published, you may need to refresh the browser window to view the folder.
These reports use a shared data source named RSExecutionLog that defines a connection to the RSExecutionLog database on a local SQL Server. If you used a remote SQL Server instance or a different database name, you must edit the data source to use the correct values.
To learn more about building reports, follow a simple tutorial to learn the basic steps. For more information, see Walkthrough - Creating a Basic Report.
Refreshing Execution Log Data
You can run the DTS package periodically to get updated information from the execution log. New log entries are appended to the existing entries. The DTS package does not remove old entries or historical data. Examples of historical data might include users who no longer run reports on a report server, computer names that are no longer in service, or reports that no longer exist.
If you do not want historical data, you can run cleanup.sql to clear out the execution log database.
The DTS package follows these steps to ensure that entries are not duplicated:
- Determine the end date of the last entry added to the execution log database.
- Open the execution log tables in the report server database, and then find all entries added after the end date.
- Get the new entries, and get related data from other report server database tables.
- Copy all the data to the execution log database.