Auditing Report Execution using the ReportServer Database

We welcome our guest blogger David Jennaway who is the technical director at Excitation and a CRM MVP.

Reporting Services is usually considered at most as just the engine for executing and rendering reports. However, it also has its own SQL database that contains information that can be useful. In this article I’ll look at how you can use information derived from the ExecutionLog and Catalog tables to find information about who ran which report, and how long it took.

Accessing data in the ReportServer database

The recommended approach for querying the execution log is to periodically extract the data from the ReportServer database into a separate, denormalised database, then query this database. This approach is described in the SQL Server Books Online here (http://msdn.microsoft.com/en-us/library/ms155836(SQL.90).aspx ), and there are associated samples which include an SSIS package to perform the extract, and some sample reports on the extracted data (http://msdn.microsoft.com/en-us/library/ms161561(SQL.90).aspx ).

There are several advantages to this approach:

• The denormalisation process in the SSIS package parses the parameters used in the report execution, which will be useful for us later
• Using a separate database allows you to have more control over any additional SQL objects you create. As I’ll discuss later, it is useful to create a SQL function to get the friendly name of the report in CRM 4.0
• Using a separate database allows you more flexibility when managing the SQL security. By default most users will not have the rights to query the ReportServer database directly
• If the ReportServer and CRM database(s) are on a separate server, you can place the denormalised database on the same SQL Server as the CRM database(s). This will simplify the process of getting the report name from the CRM database

However, it is also possible to query the ReportServer database directly. This will give you data that is always up to date, but you will have to ensure users have sufficient permission on the tables, and you will have to parse the report parameters yourself.

The rest of this article is based on the denormalised database created from the SQL Server Samples.

Database Structure

The scripts described above create several tables; those that are relevant to this article are:

• Reports. This contains a record for each report in Reporting Services
• Users. An entry for each user on Reporting Services
• ExecutionLogs. This table has the core data we’re using here. It has a record for each execution of a report, including statistics on the time taken, and the parameters used
• ExecutionParameters. One record for each parameter used for each report execution

The following query illustrates a simple SQL query on these tables:

select r.Name as ReportName, u.UserName
, l.TimeStart, l.TimeDataRetrieval + l.TImeProcessing + l.TimeRendering as TotalTime

from executionlogs l
join reports r on l.reportkey = r.reportkey
join users u on l.userkey = u.userkey

This produces output like the following:

The first record is from a separate report on a database that has nothing to do with Dynamics CRM, while the second record is from executing the Activity report on a Dynamics CRM 4.0 Server, in a deployment where the Dynamics CRM Data Connector for Reporting Services has been installed.

From this you can probably see that, while the above query gives immediately helpful information when running non-CRM reports, it is not so useful for the CRM report. There are 2 things we have to do; get the actual user name or the user running the report, and get a usable report name.

Getting the UserName from the ExecutionParameters

The UserName information from the above query would normally identify the user that ran the report. However, if you use the Dynamics CRM Data Connector for Reporting Services, then this field will not give you this information.

However, it is possible to get the user information from the parameters passed to the report. Dynamics CRM passes several pieces of information to a report in the form of parameters, and the CRM_FullName parameter holds the name of the user executing the report.

So, we can change the above query to the following:

select r.Name as ReportName
, (select p.Value from executionparameters p where ExecutionLogID = l.ExecutionLogID and Name = 'CRM_FullName') as UserName
, l.TimeStart, l.TimeDataRetrieval + l.TImeProcessing + l.TimeRendering as TotalTime
from executionlogs l
join reports r on l.reportkey = r.reportkey

This now gives us:

Rather than using a join to get the ExecutionParameter, I used a subquery. This is mostly a matter of preference, but it makes it easier to include several parameter values on the select list.

Getting the Report Name from the MSCRM Database

Now we need the report name. If we were using CRM 3.0, this would show us the name of the report as we see it in the Dynamics CRM user interface, but things work differently in CRM 4.0. In CRM 4.0, the reports are created with a Guid for a name, and the usable name is stored in the organisation’s MSCRM database.

To get the report name in our query we will need data from another database. This can be done in a join, but I prefer to use a SQL function to do the work:

create function fExcitationGetReportName(@RSName nvarchar(425))
returns nvarchar(425)
as
begin
declare @ret nvarchar(425)
select @ret = r.name from AdventureWorksCycle_MSCRM..FilteredReport r
where @RSName = '{' + cast(r.reportid as nvarchar(100)) + '}'
return @ret
end

To avoid supportability concerns, I create this function in my RSExecutionLog database, and include the specific organisation database name in the function definition. You’ll need to replace AdventureWorksCycle_MSCRM with your CRM database name. If you have multiple organisations you could either create a function per organisation, or pass the organisation name as a parameter into the function, and use dynamically generated SQL.

Another point to make about the function is that we need to cast the reportid in CRM from a Guid to a string, and add the curly braces to match the name that is stored in Reporting Services

Once we’ve create the function, we can use it as follows:

select dbo.fExcitationGetReportName(r.Name) as ReportName
, (select p.Value from executionparameters p where ExecutionLogID = l.ExecutionLogID and Name = 'CRM_FullName') as UserName
, l.TimeStart, l.TimeDataRetrieval + l.TImeProcessing + l.TimeRendering as TotalTime
from executionlogs l
join reports r on l.reportkey = r.reportkey

Which gives the output we want:

Further Thoughts

So far I’ve concentrated on the SQL aspects of getting the data you want from the underlying tables. Once you have this, you can present this information in your own reports. The samples that create the RSExecutionLog database include some sample reports that you can use, and the SQL within these reports can be easily modified using the techniques described above to get the report and user names.

I don’t have the space in this article to go into detail about extra things you can do with the data from ReportServer, but here are some additional ideas which may make it into a subsequent article:

• Some other ExecutionParameters may be useful. CRM_FilterText gives a text representation of the pre-filter used on a report
• If you have multiple CRM organisations, I find the easiest way to identify the organisation is to use the first part of the Path column in the Reports table. This gives the database name, which is derived from the organisation’s unique name

Links

Report Execution Log at SQL Books Online - http://msdn.microsoft.com/en-us/library/ms159110(SQL.90).aspx

Code used in this article, and sample reports on the MSDN Code Gallery - http://code.msdn.microsoft.com/RSExecutionLogCRM40.

Cheers,

David Jennaway