Dynamics CRM - Audit Entity

AuditBase table or Audit entity are captured previous data of records.

CRM will create one line record in the audit entity for each action (new, update or delete records).

For example, First Name  Last Name
New Record -  FNC             LNC  => entity record
Old Record -  FN~LN             => audit stored, for example audit id 2, here first name new value is current entity record value  "FNC" and old value is current audit record "FN".
Old Record -  F~L             => audit stored, for example audit id 1, here first name new value is next audit record value  "FN" and old value is current audit record "F"



Attributes
ChangeData - it contains previous entity data not current entity record, data are differentiated by "~" symbol, for example FN~LN~10/12/2015.
If record data contains "~", CRM convert to "^s" and "^", CRM convert to "^e".
in this case if records changes in first name is  "FN~" and last name is "LN^", CRM stores in the audit table like "FN^s~LN^e"

AttributeMask - attribute id, data are differentiated by "," symbol, for example ,57,126,10045,131,30
SELECT Value, AttributeValue, AttributeName from StringMap  where objecttypecode = 2

Action - 1- new, 2- modify, 3- delete,  4- active, 5- inactive, 14 - share, ....

ObjectId - entity record id

Operation - 1- new, 2- modify, 3- delete

ObjectTypeCode - entity id, for example in crm 1 is account , 2 is contact

Get all the column name for attribute mask
@AuditColumnResult er
inner join MetadataSchema.Entity me on me.ObjectTypeCode= er.ObjectTypeCode
        inner join MetadataSchema.Attribute ma on ma.EntityId = me.EntityId and ma.ColumnNumber = er.ColumnKey

Get all the picklist values
SELECT Value, AttributeValue, AttributeName from StringMap  where objecttypecode = 2

Delete audit record

delete from [AuditBase] where Auditid ='FADER411-MAK1-DNS2-SES3-000000000AD4'