|
19.11.2008, 20:05 | #1 |
Участник
|
Microsoft Dynamics CRM Team Blog: Auditing Report Execution using the ReportServer Database
Источник: http://blogs.msdn.com/crm/archive/20...-database.aspx
============== 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/libr...6(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/libr...1(SQL.90).aspx ). There are several advantages to this approach:
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:
select r.Name as ReportName, u.UserNameThis produces output like the following: ReportName UserName TimeStart TotalTime My Report MyDomain\My User 2008-10-06 12:51:22.123 1023 {7121cc90-d2c0-dc11-8308-0003ff562152} NT AUTHORITY\NETWORK SERVICE 2008-10-06 12:51:51.817 551 ... 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 ReportNameThis now gives us: ReportName UserName TimeStart TotalTime {7121cc90-d2c0-dc11-8308-0003ff562152} CRM Admin 2008-10-06 12:51:51.817 551 ... 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))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 ReportNameWhich gives the output we want: ReportName UserName TimeStart TotalTime Activities CRM Admin 2008-10-06 12:51:51.817 551 ... 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:
Report Execution Log at SQL Books Online - http://msdn.microsoft.com/en-us/libr...0(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 Источник: http://blogs.msdn.com/crm/archive/20...-database.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|
|
Опции темы | Поиск в этой теме |
Опции просмотра | |
|