One of the many great things about having a CRM system in place is that it acts as both a center of truth for your business’ data, and as a home for all of that fantastic data. It helps keep track of everything from those trusty Contacts to the 360 view of your customer’s activities.
To quote George Bluth Sr. from the hit TV show Arrested Development ,“There is always money in the banana stand”. (He was referring to the fact that he’d hidden $250,000 in cash in the walls of the stand for safe keeping.) If you think about it, the Bluth’s banana stand isn’t so different than your company’s CRM platform. They both contain large amounts of valuable resources, and they both have plenty of fluctuation in terms of inputs and outputs (be it chocolate covered bananas or customer data.)
If you’ve seen the episode, you know that despite George Sr.’s constant mentioning of the cash available in the banana stand, his family (for various reasons) does not take advantage of it. Unfortunately, this is very similar to what happens to the valuable customer data in many CRM databases.
There is a wealth of data a CRM system can accumulate if it’s being used properly, or often times, used at all. This usually isn’t a technical problem; it's usually one that involves business work processes or organizational culture.
Knowing how much and how often your employees are using your CRM is key in avoiding CRM project failure. So how can you get this data?
Reporting on User logins will let managers know (at a high level) if those trusty CRM licenses are being properly utilized and show which employees are using your CRM tools. What is interesting about this method is that you can pull User login data from the Audit entity (on both the Online & OnPremise versions of Dynamics CRM). Although you may not be able to hit the Audit entity from Advanced Find, you can certainly do it with a little elbow grease and some FetchXML. Below I’ll show you how to install a report that will provide you with this data.
Prior to installing the provided report, User level auditing needs to be turned on in the system. To enable this go to Settings -> Auditing -> Global Audit Settings or Settings -> Administration -> System Settings. Click on the auditing tab. Check the “Start Auditing” and “Audit User Access” checkboxes.
This should be the only preq prior to adding the report.
If you have previously played around with this functionality, you’ve probably noticed that you’re currently unable to capture audit data by running Advanced Finds or writing reports using the Report Wizard. So in this post, we’ll use some custom FetchXML statement to connect System Users with the Audit records created every time they log into CRM.
Taking a look at the FetchXML:
Looking at the fetch statement, you can see that it is fairly straight forward. It is primarily focused on the Audit entity, and links with the SystemUser entity on ‘systemuserid’. It is important to note that the audit records are filtered on action 64 & 65. This filters only those audit records that are either ‘User Access via Web’ or ‘User Access via Web Service’.
The next bit of a magic trick (“illusion”, if you’re a Gob fan) is performed by the SQL server report. When adding the dataset to the table, set the row group to the User’s full name (in our datasets case, “SystemUser_fullname”. )
To set the Row Group for the table, right click the row. There should be an option called ‘Row Group’.
The second piece of magic on this table is the Last Login Column. To get it functioning correctly, we need to add a function. We are going to use “First(createdon.Value)”. This returns the first value; in our case, the last login.
Here’s a link to this report, as well as an alternate version which provides login count:
Please let me know if you have any questions about how to get to these files! Don’t get caught not monitoring this valuable data point. It is easy to get to and definitely an important factor when measuring your CRM usage.
Have further questions about auditing user logins, or how to improve user adoption in general? Leave your questions or comments below. *cited by Forrester Research.