Dynamics Community Forum Thread Details

Report on Audit history

Users keep updating a field called " Number of Services Done" on the account form. So I need to fetch those accounts which have 'This' field modified in the last month. The audit is enabled, I can run the 'MSCRM tool Kit' to get the audit history of a particular field. The challenge is, "I need to have few fields of Account and the old and new values of this field "Number of Services Done" . How do I achieve this? Pleaseee helpp.

Unfortunately this isn't as simple as you might like. If you have CRM OnPremise you can get this via SQL and build a report, though it is quite involved. This link gives some information on the audit table structure, and this gives an example of a specific report on audit data.

Some important things to note:

The AttributeMask field is a comma-separated list of the columns affected, and the ChangeData field has the old values, separated by ~. If you have SQL 2016 or higher you can use the STRING_SPLIT SQL function to split the data, otherwise you'll need to find an alternate function (there are several on the internet). Note that, if you have a lot of data, a poorly performing split function could have a big impact.
If you're only concerned about one field on one entity, you can perform a simple query where AttributeMask matches the column number, e.g. if the column number is 123, then AttributeMask like '%,123,%'. The column number will not change within an organisation once the column has been created, but note it may differ in different organisations
The audit data only stores the old values. To get the value it was changed to, you'll need to get this either from the next audit record that has this column, or the current data
The audit table is not a filtered view, and by default CRM users do not have SQL permission to query it

Quick Links

Leaderboard