Querying Audit History





0/5 (0 vote)
Tips to query audit history
Audit history is a great out of the 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
don’t support it. This post will discuss options we have and sample code for it.
Options
- Using SDK messages,
RetrieveRecordChangeHistoryRequest
andRetrieveRecordChangeHistoryResponse
, covered in this post - 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 a 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 a maximum of 5000 entities, but this code will handle and return even if there are more than 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 than 5000 records. Additional columns can be added infetch
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;
}
The 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 the 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
}
}
}
}
}
}
I hope it was helpful.