The following code snippet can be used to get the access history of a MS Dynamics CRM 4.0 Report.
SELECT
C.Name 'Report_GUID'
,CASE WHEN ((PATINDEX('%CRM_FormatDate=%', E.Parameters)-
(PATINDEX('%CRM_FullName=%', E.Parameters) + 14 )) > 0)
THEN SUBSTRING(E.Parameters,(PATINDEX('%CRM_FullName=%', E.Parameters)+13),
(PATINDEX('%CRM_FormatDate=%', E.Parameters)-
(PATINDEX('%CRM_FullName=%', E.Parameters) + 14 )))
END 'UserName'
,E.TimeStart
,E.TimeEnd
,E.Status
FROM
ReportServer.dbo.Catalog C with(nolock)
LEFT OUTER JOIN ReportServer.dbo.ExecutionLog E with(nolock) ON C.ItemID = E.ReportID
WHERE
C.type=2
For those who want to deep dive on this Execution Log Reporting domain, please refer:
Using the Reporting Services Execution Log with CRM 4.0
Reporting Services Log Files
Auditing Report Execution using the ReportServer Database
Reading the Report Execution Log for CRM 4.0
Read Full Post »