Sonoma Partners Microsoft CRM and Salesforce Blog

Plotting CRM Audit Data

Today's blog post was written by Angel Shishkov, Principal Developer at Sonoma Partners.

CRM field audit data is notoriously hard to retrieve. It is not available through Advanced Find and is not exportable through the Audit view on the CRM record. Going the custom route, it is possible to retrieve audit records through FetchXML queries, but getting to the concrete values that changed becomes harder. The recommended and supported way of getting to the field audit logs then is to use the RetrieveAttributeChangeHistoryRequest message.

I will demonstrate how to use the RetrieveAttributeChangeHistoryRequest message in CRM by setting up a simple feature that requires us to read the Audit logs.

Requirement

We have a custom currency field on the Account entity called new_revenue. We have an integration or some other process that updates this field periodically with the total revenue on this Account. We would like to plot the value of revenue on the Account over time, so we can visualize the trend.

Design

We will make use of CRM’s native field auditing to track the historical values of the revenue field on Account. We need to enable auditing on the CRM org, on the Account entity, and on the custom new_revenue field. We will use a custom console application to retrieve the field audit data for new_revenue through the RetrieveAttributeChangeHistoryRequest request of the CRM SDK. The data retrieved will be dumped into a CSV file, which can be opened in Excel and the data can be analyzed and plotted on a graph.

Implementation

The relevant C# code for the console app is below.

This is what it does:

  • Creates a RetrieveAttributeChangeHistoryRequest and sets the Account record as the Target and the new_revenue field as the AttributeLogicalName. This request will return all the audit history for this field.
  • Executes the request against the CRM OrgService and receives the response.
  • Opens a stream to write to a file called output.csv. This file will contain the audit history output in a comma-separated format that is readable by Excel.
  • The response returns a collection of AttributeAuditDetails, each of which represent a single change in the value of the new_revenue field.
  • We loop through all AttributeAuditDetails, and for each one we extract the following:
    • createdon: This is the date and time of the change. We retrieve it from FormattedValues, so that we get the date and time converted in our local time zone, instead of UTC.
    • new_revenue old value: This is the current value of new_revenue at the time the change occurred.
    • new_revenue new value: This is the new value that was assigned to new_revenue.
  • We format these values into a comma-separated line, and write the line to the CSV file.

When we run this code, it produces a file with contents that look like this:

We can open this file in Excel and see the three columns of data we extracted, the date and time of the change, the old value, and the new value of revenue. Now that it is in Excel, we can set up a simple chart to visualize the data. Select columns A and C (the date and the new value), open the Insert tab in Excel, and select a chart, for example a 2D Line. Excel automatically sorts out the axes, plotting the revenue dollar value on the vertical and the date and time values on the horizontal.

Here is what it looks like:

Angel image 1

Conclusion

Mining your audit data has lots of uses, and this is just one of them. If you are looking to expand on this, or build something more complex and you need some help, give us a call. Thanks for reading!

Ease into the cloud with Microsoft Dynamics Lifecycle Services

Topics: Microsoft Dynamics 365