If you’ve ever tried to create an audit report for Dynamics CRM, you may have noticed that you can’t create one with the Report Wizard. The Audit Summary Report in the Settings area gives you some information, provided you enable auditing for the correct entities. However, if you need detailed information on your report, or you wish to share this report to others in CRM, you quickly realize that your options are limited.
Fortunately, there is another option that you can take to overcome these challenges. This option involves writing a custom SQL Server Reporting Services (SSRS) report that can be uploaded into CRM and shared with others to provide auditing information. I will describe how I created a report to provide detailed login information for CRM users. This article assumes the reader understands how to create a custom SSRS report for Dynamics CRM.
The first challenge I encountered was deciding how to write the query for my custom SSRS report. I have 2 choices: a SQL query or a FetchXML query. Writing a SQL query will work if the report is grabbing data from an on-premises version of CRM, but what would I do if I wanted to write this report for CRM Online? Since I can’t write a SQL query for CRM Online, I am forced to use FetchXML for the report.
My first thought was “Can I access the audit tables via FetchXML like I can with SQL?”. After a bit of searching, I found a GitHub project that had a couple of SSRS reports that accessed the audit tables via FetchXML (credit to BrettRojas: https://github.com/BrettRojas/CRM2011UserAuditReport). Once I examined the query, I realized that with a bit of SSRS report manipulation I could use this to provide a detailed login report in CRM Online.
The query for this report is listed here:
<fetch distinct="false" no-lock="false" mapping="logical"> <entity name="audit"> <attribute name="createdon" /> <attribute name="action" /> <attribute name="userid" /> <attribute name="objectid" /> <attribute name="objecttypecode" /> <order attribute="createdon" descending="true" /> <filter type='and'> <condition attribute='action' operator='eq' value='64'/> </filter> <link-entity name='systemuser' from='systemuserid' to='objectid' link-type='outer' alias='SystemUser'> <attribute name="fullname" /> <attribute name="windowsliveid" /> <attribute name="businessunitid" /> <attribute name="isdisabled" /> </link-entity> </entity> </fetch>
There are a few things to note about this query:
- Enabling Auditing – Auditing in CRM must be enabled for this report to function properly. Since we are creating a login report, we need to enable auditing for the SystemUser entity in CRM.
- Query Entity – The entity used in the query is titled Audit, which is an internal entity in Dynamics CRM. This means it is not displayed in the Advanced Find dialog, nor the query generator for the Report Wizard. You still have access to this entity, but you need to have System Administrator privileges to do so.
- Action Attribute – This attribute in the Audit entity describes the type of record being recorded. We are only interested in entries with the integer value of 64, which corresponds to a successful access via a web page. Since only CRM users would access the system via a web page, we can safely assume that these are real CRM users logging in to the system. As a side note, the original FetchXML query included entries with the value of 65, but this corresponds to a successful access to CRM via a webservices call (such as a plugin) and we do not need these records for our report.
- Linked Entity – The query also performs an outer link to the SystemUser entity, in order to get the details we need for our report. Since the Audit entity only has the GUID for the main entity record being tracked (SystemUser in our case), we need to perform this link so we can see information such as the name of the user, their business unit, Windows Live ID, and their status (active/disabled).
- Ordering of Results – The query performs an ordering of the results, and uses the CreatedOn field from the Audit entity. Since we are tracking SystemUser records, this field corresponds to a successful login by a CRM user, so ordering the query results in descending order assures us that the first record return for each CRM user (if we group by CRM users) will be the latest login.
Creating and Publishing the Report
You can now use the above query to create your SSRS report (and publish to CRM) using the normal techniques and processes to do so. Here is an example of the layout of the report that I created – you can display information about specific users, such as Last Login or Number of Logins over the last 30 days. Please note that the data displayed on this report will start when you enable auditing (as mentioned above).
I hope this is helpful – Happy reporting!