Search code examples
dynamics-crmdynamics-crm-2011crm

Making sense of the Audit table in CRM

I'm attempting to extract all the Merged records out of CRM and I see in the "Audit History" page of a Contact record the following:

enter image description here

If I click on it, I get the following:

enter image description here

And if I profile the code to see what SQL runs, it executes the following:

exec sp_executesql N'select 
"audit0".AuditId as "auditid"
, "audit0".AttributeMask as "attributemask"
, "audit0".ChangeData as "changedata"
, "audit0".CreatedOn as "createdon"
, "audit0".Action as "action"
, "audit0".Operation as "operation"
, "audit0".CallingUserId as "callinguserid"
, "audit0".UserId as "userid"
, "audit0".ObjectId as "objectid"
, "audit0".ObjectTypeCode as "objecttypecode"
, "audit0".CallingUserIdName as "callinguseridname"
, "audit0".UserIdName as "useridname"
, "audit0".ObjectIdName as "objectidname" 
from
 Audit as "audit0" 
where
 ("audit0".AuditId = @AuditId0)',N'@AuditId0 uniqueidentifier',
 @AuditId0='7FE1B120-87EC-E811-8BE0-005056B12EA2'

with the results shown as:

enter image description here

Does anyone know how I get the Yes record that it was actually merged? I can't make sense of the results on how they get displayed as Old Value v New Value.

Solution

  • When you click the Save button on the record in CRM there's one audit row created in SQL, which describes all the changes made by this save. Only the old values are stored in SQL.
    So how to get new values?
    You can sort audit query by createdon ascending and for every row the new value is the next's row old value. To get the final new value you should join audit table with the contact table and get the Merged field value from the contact table.