0

Is there a query that I can run on a Microsoft Dynamics CRM database (SQL Server) that will show me all the cases where a particular field was modified in all records of a particular entity in the CRM instance? I would like to see the "Old Value" and "New Values" values of the modified field. I have seen this done through the CRM Audit Log Summary page (i.e. https://social.microsoft.com/Forums/en-US/01b4318b-ec73-4eb2-9245-479f643cc58a/ms-dynamics-crm-audit-history-shows-icons-instead-of-values), but want to run a SQL query instead of go through the CRM UI to capture more info.

For example, I have an custom entity type called the "Car" entity type. There is a field in the Car entity called "Driver". How can I query the audits (auditing is enabled on the Car entity type in Dynamics CRM, btw) for all Car records so that I can display all Cars whose Driver has changed in a format with "Old Value" and "New Value" in the results?

I have queries the default CRM table 'AuditBase', but the only remotely relevant column in that table is 'ChangeData', which I cannot find any useful info in.

Can anyone help?

Thanks in advance.

Arun Vinoth-Precog Tech - MVP
  • 22,364
  • 14
  • 59
  • 168
Fuzzy Analysis
  • 3,168
  • 2
  • 42
  • 66
  • 1
    As you already know, Audit history and it's related data structure is complex and you will not directly be able to retrieve old and new values with query. I have never used sql to query database, but I have used c# code lot of time to query audit table and was sucessfully able to retreive old and nw values. In your case you have only 1 field whose data you wish to retrieve why not go with c# console app and loop through each record which are changed and retrieve audit details. Link for Ref:https://coderscollection.wordpress.com/2017/12/27/how-to-get-audit-details-from-ms-crm-c-net/ – AnkUser Sep 06 '19 at 11:12
  • 1
    Possible duplicate of [Getting Audit Record Details from Dynamics 365 to Power BI](https://stackoverflow.com/questions/57564142/getting-audit-record-details-from-dynamics-365-to-power-bi) – Arun Vinoth-Precog Tech - MVP Sep 06 '19 at 14:30

0 Answers0