Tracking Changes in On-Premise CRM 2011 Audit View using SQL
Have you ever found yourself needing to track the changes you make to records in CRM, but feeling uncertain how to do so? If you are using the On-Premise version of CRM 2011, you can leverage the Audit view in SQL Management Studio to easily track your changes.
For example, let’s say you needed to track the changes for customers in CRM 2011 On-Premise to show one instance where the data had changed.
CRM 2011 on premise Audit view contains all the attributes of a transaction in a string. Changes to a transaction are separated by “,” and the previous values of a transaction are separated by “~”. Unfortunately, using the string alone we can’t query individual attributes to track changes. In order to more easily accomplish this, we would split the values to be able to join to other views or tables.
In the following example, we will be tracking the last updates on all the Account names to view the current and previous names in the database.
Please note: If your Audit table is large, we recommend filtering from the beginning. Additionally, you can use “SELECT INTO” to help filter your data to limit SQL resource consumption.
It is also recommended that you make a backup of your database before beginning.
Below is the Audit view string from a select.
First, we will create a temporary table:
Next, we will split the values for Audit view into our new temporary table.
Although there are many different approaches to accomplish this task, the approach we used in this case was to make a copy of the Audit view on a temporary table with a row count to loop into.
Here is the select result of the temporary table:
In this example, ChangeData contains the old data for the AttributeMask. If it’s a new record and it was updated, the resulting value will be blank.
Next, we can join the data to get the Operation, Entity, Attribute and Action.
This query will return the following result:
In this example, the ObjectID is the GUID for the record on the Entity table.
If you want to track the last changes on the account name, you would need to join to Account view on tmpAudit.ObjectId = Account.AccountId.
Here is the result of this query:
These results reflect the current value for name, which is stored on the entity account, and the previous value for name, which is stored on the audit view.
Congratulations! You now understand the concept of splitting the On-Premise CRM 2011 Audit view for SQL querying to other tables or views. We are providing only a few examples, but there are many possibilities available if you modify these queries to suit your specific needs.
If you would like assistance with this or any other CRM/SQL concepts, please feel free to contact us using the form below.