Querying Audit History

Audit history is a great out of box feature in model-driven apps. However, querying audit history is a bit tricky. Unfortunately commonly used querying mechanisms like Power Automate CDS connectors, LinQ, or simply FetchXml doesn’t support it. This post will discuss options we have and sample code for it.

Options

1. Using SDK messages, RetrieveRecordChangeHistoryRequest & RetrieveRecordChangeHistoryResponse, covered in this post 
2. Using Kingswaysoft’s Integration Toolkit for D365 (not covering in this post)

Scenario

I will query audit history for contact entity records and read audit details for its email address attribute. Audit details are available under these four heads:

• Changed date
• Changed field
• Old value
• New value

If auditing is enabled, this code will work for almost any entity and attributes.

How it works

We need ids (GUID) of entities and using those we will query audit history. I m using a fetchxml query to retrieve ids, but it can be mechanism of your choice depending on implementation and requirement.

       var targetEntites_query = @"<fetch {0}>
                                <entity name='contact'>                               
                                 </entity>
                                 </fetch>";     

Generally we know FetchXml can return maximum 5000 entities, but this code will handle and return even if there are more then 5000 records in the result.

public List<Entity> RetrieveAllRecords(string fetch)
        {
            var moreRecords = false;
            int page = 1;
            var cookie = string.Empty;
            List<Entity> Entities = new List<Entity>();
            do
            {
                var xml = string.Format(fetch, cookie);
                var collection = CrmClient.RetrieveMultiple(new FetchExpression(xml));

                if (collection.Entities.Count >= 0) Entities.AddRange(collection.Entities);

                moreRecords = collection.MoreRecords;
                if (moreRecords)
                {
                    page++;
                    cookie = string.Format("paging-cookie='{0}' page='{1}'", System.Security.SecurityElement.Escape(collection.PagingCookie), page);
                }
            } while (moreRecords);

            return Entities;
        }

Tip: FetchXml query must have {0} if query will return more then 5000 records. Additional columns can be added in fetch if required.

Next, I m looping through these ids and read audit history for records using this code:

 public AuditDetailCollection GetAuditHistory(string entityLogicalName, Guid recordId)
        {           
            var changeRequest = new RetrieveRecordChangeHistoryRequest();
            changeRequest.Target = new EntityReference(entityLogicalName, recordId);
            var changeResponse = (RetrieveRecordChangeHistoryResponse)this.CrmClient.Execute(changeRequest);             
            return changeResponse.AuditDetailCollection;
        }

Above function returns AuditDetailCollection which has a collection of AuditDetails. One Audit detail represents one entry in audit history. Please note audit history records are in the same order as they appear in UI (descending).

Every audit details record will have a changed date, and collection of new and old values with field names which we will need to loop through and read.

Below is code to accomplish this:

             //Collection of entities for which we are going to read audit history
            var AllTargetEnteties = this.RetrieveAllRecords(targetEntites_query);


            foreach (var targetComplaint in AllTargetEnteties)
            {
                //Now pass id(guid) of record with entity name to retrieve audit history 
                var audit_history_entries = this.GetAuditHistory(targetComplaint.LogicalName, targetComplaint.Id);


                foreach (AuditDetail auditDetail in audit_history_entries.AuditDetails)
                {

                    if ((auditDetail.GetType())?.Name == "AttributeAuditDetail")                     
                    {
                        //Below code reads Changed Date
                        var changeDate = auditDetail.AuditRecord.GetAttributeValue<DateTime>("createdon");

                        var newValueEntity = ((AttributeAuditDetail)auditDetail)?.NewValue;
                        if (newValueEntity.Attributes.Count > 0)
                        {
                            {
                                foreach (var attrNewValue in newValueEntity?.Attributes)
                                {
                                    //Here we will need to match attribute name to read new value.
                                    //In this case I m reading emailaddress1
                                    if (attrNewValue.Key == "emailaddress1")
                                    {                                        
                                        var newEmailAddress = attrNewValue.Value;
                                        //Custom Logic for New Value here

                                    }
                                }
                            }
                        }


                        var oldValueEntity = ((AttributeAuditDetail)auditDetail)?.OldValue;
                        if (oldValueEntity.Attributes.Count > 0)
                        {
                            foreach (var attrOldValue in oldValueEntity?.Attributes)
                            {
                                //Here we will need to match attribute name to read old value.
                                //In this case I m reading emailaddress1
                                if (attrOldValue.Key == "emailaddress1")
                                {
                                    var oldEmailAddress = attrOldValue.Value;
                                    //Custom logic for Old value will be here

                                }
                            }

                        }

                    }
                }
            }

Let’s Connect

CodeProject