Retrieve a list of users who have never accessed Dynamics CRM 365 and a list of users with their last login time

Introduction

Determining the active users in Dynamics CRM 365 on-premise is crucial for managing licensing costs effectively. Many users may never have logged in, while others may have ceased using the system long ago for various reasons. By identifying these inactive users, organizations can eliminate unnecessary licenses, leading to substantial cost savings. This assessment not only optimizes resource allocation but also ensures that the organization is making the most of its CRM investment. Ultimately, focusing on active users enhances overall efficiency and helps maintain a streamlined operation.

Solution

We can run the following SQL Query (in case of on-premise):

1. get the list of users who have never accessed the application

   --List of users who have never accessed the application
   SELECT 	su.fullname,
   		su.domainname,
   		su.userlicensetype,
   		su.accessmode,
   		su.isdisabled
   FROM systemuser AS su
   WHERE su.isdisabled =0 and su.systemuserid IN 
   	(SELECT systemuserid
   		FROM systemuser
   		EXCEPT
   		SELECT DISTINCT objectid
   		FROM audit
   		WHERE operation = 4);
   

2. List of last logon details for the users

   --List of last logon details for the users
   SELECT  su.fullname,
   		su.domainname,
   		su.userlicensetype,
   		su.accessmode,
   		su.isdisabled,
   		max(a.createdon) AS LastLoginDate
   FROM audit AS a INNER JOIN systemuser AS su
   	ON su.systemuserid = a.objectid
   WHERE a.operation = 4 and su.isdisabled=0
   GROUP BY su.fullname, su.domainname, su.userlicensetype, su.accessmode, su.isdisabled
   order by LastLoginDate asc;
   

Point of Interest

To enable auditing of user access and obtain the above information, navigate to the System Settings and select the Auditing tab. Make sure to check the box for "Audit user access." In the System Settings under the Audit User section, confirm that the User Access box is checked.