latest Post

Access "Last Access Date" of each User Directly in CRM.

Since CRM 2011  when Microsoft first offered the ability to Audit User Access, I have been waiting for them to offer a simple OOB solution to be able to view the last Access Date of a user from a field on the user form. Rolling on 7 years to Dynamics365 this is still not possible.

Those users who are experienced in CRM Auditing will know Audit Logs cannot be exported or queried using advanced find meaning if any data manipulation is needed on the logs then a 3rd party tool is required or some custom code to export the logs and be able to manipulate the data.

For years now to be able to check user adoption and monitor user licences I have been exporting the logs to excel using either the XRM toolbox solution or writing a fetchxml query and manipulating the data to get a list of users who havent logged in over a period of time. I have always wanted to get round to automating this process so I could simply view the users last access date directly on user profile and thats exactly what I have done. 

To accomplish this task I first wrote the below FetchXML query. The below query returns all Audit logs of type "User Access via Web" in the below code this is referenced by value 64. For a full list of codes for each type click here.  It then also filters out any logs which either were not created today or is not  created before a passed in date parameter. This is because I want to either run it nightly for only todays logs but also have the option to run it as a one off to search all audit logs for a time period.  Finally it filters out disabled users as I am not interested in updating those users. 

Then using SSIS and the KingswaysoftCRM source connector I downloaded the the records returned from the query and stored them in a local SQL database. I then manipulated the data with a simple SQL query to return the Last Login Date for each user.

I  then created a new custom field in CRM on the OOB user entity "Last Access Date" and added it the form. 

I then used the above SQL Query as my source component in my SSIS package to loop through each user and update the corresponding CRM user record with the last access date.  

Now putting that all together I created the below SSIS package.

SSIS Control Flows 

The below shows the SSIS control flow steps in my SSIS package which starts by truncating the local sql table with the user audit logs.

Data Flow 1

First data flow which queries CRM using the above Fetch query and outputs the data to a local SQL table.

Data Flow 2

Second data flow task which uses the SQL query above as the source and loops through each record and updates the corresponding CRM users last access date field.

Finally once the package has ran we have the last access date field populated. 

This SSIS package is then scheduled to run each night to automatically update the last access date for each user and that's how simple it is to get the last access date for each user updated automatically. 

Now I finally got round to this I bet in the next release its added as standard!! haha :-)
Recommended Posts × +


Post a Comment