Migrate Audit records from Dynamics 365 CRM to Azure SQL Server
WebSiteAdmin, May 12, 20223538 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
