Logo
banner

Blogs

Migrate Audit records from Dynamics 365 CRM to Azure SQL Server

, May 12, 2022 2826 Views

Storage space comes at a premium in Dynamics 365 world. So, we need to manage it carefully and aggressively. When audit is enabled, the space it occupies creeps up steadily and silently and we only notice it when we get a message from Microsoft telling us we have run out of storage space.

So, a good option is to move the audit related data somewhere else before we can delete it in Dynamics 365.

We encountered this very same problem for one of our clients and this is what we did.

We created a .NET program that will run and will migrate the audit records from the CRM to Azure SQL Server. In the code, we performed a fetch on the “audit” entity of the CRM and used some of the built-in classes of C# to get the old and new values of the column being updated.

The issue we faced while developing the program was when we fetched the audit records from the CRM, we got the comma-separated column number that was updated instead of the column names themselves. So, we had to separate the column number and from the separated number we had to get the column name from the Attribute Metadata class.

Apart from the above issue, the next challenge we faced was we directly don’t get the old and new values of the column that got updated. For that, we, again used the internal C# class to achieve this.

To show the migrated audits on the CRM, we used Power BI report to show the audits based on specific records. We used Html web resource to bind Power BI report on entity specific forms.

In the Power BI report, we displayed Audit records the same way as we see under the OOB Audit History tab.

The issue while creating the Power BI was that for the “createdon” field, we needed to convert the UTC date time to User Local date time on the Power BI side so that the users can see the date according to their time zone. For this we used an internal CRM entity. Apart from these we also had to keep in mind the day light savings and to manage that we copied the day light saving table from Google to our Power BI report.

Hope this helps!

 


ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://www.inkeysolutions.com/microsoft-power-platform/power-app/atm-inspection

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator

mm

Inkey

INKEY is your solution partner.
Our focus is to deliver you in-time intelligent innovative solutions ("key") for the problems in hand. Maintaining a quality standard right from the inception of a project is our top most priority.

Our team of talented professionals will execute your projects with dedication and excellence. We take ownership and accountability for the effort that goes into meeting our client’s needs.

Years of experience and proven success of delivering innovative custom solutions.

More posts by

4 responses to “Migrate Audit records from Dynamics 365 CRM to Azure SQL Server”

  1. Kat says:

    What is the use of this article for readers who are looking for a solution? All you have done is just described your issue. You haven’t provided a bit of solution or reusable code for download.

    • Admin says:

      Hello Kat,

      The reason for this article is to tell the community is that this is the solution that we came up with and that this is a possible solution to this problem. We are planning on releasing this as solution at a later stage.

  2. Paris Wells says:

    If the user has a E5 License you can purchase a Add on to keep all users retention logs for 10 years , this also gives you a portal to query these logs which means you can use this to store long term log retention automatically. Given you wont be able to see these in Dynamics

    https://answers.microsoft.com/en-us/msoffice/forum/all/10-year-audit-log-retention-add-on-required-even/b0ca2f59-4dbf-4629-9038-65987062cb64

    • Admin says:

      Hello Paris,

      Thank you for your reply. However, it looks like the link you shared is not related to Dynamics 365. Are you saying that the addon can also work for Dynamics 365 because the data is stored within Dynamics 365 database storage?

Leave a Reply

Your email address will not be published. Required fields are marked *

The maximum upload file size: 2 MB. You can upload: image, audio, video, document, spreadsheet, interactive, text, archive, code, other. Drop file here

Would you like to digitize your business and put it on the cloud?
Do you need clear, concise reports for your organization?