Query D365/Dataverse Audit logs through API or Organization Service

Today, I'd like to show some manners about how to query D365/Dataverse Audit logs through API or Organization Service.

What's the Audit log?

The Auditing feature is designed to meet the external and internal auditing, compliance, security, and governance policies that are common to many enterprises. Dataverse auditing logs changes made to customer records in an environment with a Dataverse database. Dataverse auditing also logs user access through an app or the SDK in an environment.

Dataverse auditing is supported on all custom and most customizable tables and columns. Audit logs are stored in Dataverse and consume log storage capacity. Audit logs can be viewed in the Audit History tab for a single record and in the Audit Summary view for all audited operations in a single environment. Audit logs can also be retrieved using the Web API or the Organization Service.

About how to configure and enable it in Dataverse you can Google it and there are many articles and documents about that. Because the Auditing feature is a very common function in D365,

You can view and search the Audit logs within the D365 CRM in different manners. For example, view the Audit history for a single record or view all Audits logs from the Audit Summary view.

Audit Logs for a single record

Audit Summary view for all Audit logs

Why do we need to query Audit details?

As mentioned above, It's very easy to view the audit logs within the D365 environment, however, sometimes we need to flow the Audit logs to the downstream systems or do some reporting insights based on the whole auditing logs.

For this scenario, it's necessary to query the audits log through API or C# Retrieve request.

Audit and Audit details Tables 

• Audit table will have the basic information of change. But to check Audit details like old value, new value we need to get from Audit details. 
• Audit and Audit Details are 2 separate tables, can't get all the details through one query, the relationship is 1 to Many (1:N).
• Data for auditing events are in the Auditing table. The audit table is read-only.
• Calling user must have the prvReadAuditSummary privilege to retrieve data from this table.
• Audit data is not available using the Dataverse TDS(SQL) endpoint, as a workaround you can retrieve them through SQL4CDS tool in the XrmToolbox.

Important notice:

• Large column values included in AttributeDetail Oldvalue or NewValue properties such as Email.Description or Annotation are limited(capped) to 5KB or 5000 characters in length.
• A Capped column value can be recognized by three dots at the end of the text. 
• Because the data is truncated, you cannot use the audit data to restore changes for these columns' values.

Get Audit Details through API

Testing Environment

• D365 CRM online
• Auditing is enabled and data changes are made to those tables and columns being audited.
• One Record(97e71d5d-b2b1-ed11-83fd-000d3a370dc4) with 4 Audit logs. 
• Please refer to the screenshot below.

Get Audit 

Request URL: 

https://{orgname}.crm.dynamics.com/api/data/v9.2/audits?$filter=objecttypecode eq 'new_test' and _objectid_value eq '97e71d5d-b2b1-ed11-83fd-000d3a370dc4'&$orderby=createdon desc

Response:

• 3 records will be retrieved because the Audit Enabled Event won't be responded.
• Auditid is the primary key of the Audit records.

Get Audit Details

There are 3 ways to get Audit Details.

You can check more from https://docs.microsoft.com/en-us/power-apps/developer/data-platform/auditing/retrieve-audit-data?tabs=webapi#retrieveauditdetails-message

1, RetrieveAuditDetails Message

Using this message to retrieve the audit details for a single record.

Request URL

                  GET [Organization URL] /api/data/v9.2/audits(caf7856c-b2b1-ed11-ba77-281878e731c0)/Microsoft.Dynamics.CRM.RetrieveAuditDetails

Response

C# 

/// <summary>
/// Returns audit details for the specified audit record
/// </summary>
/// <param name="svc">The IOrganizationService instance to use.</param>
/// <param name="auditid">The auditid for the audit record.</param>
static void ShowAuditDetail(
    IOrganizationService svc,
    Guid auditid)
{
    RetrieveAuditDetailsRequest req =
                new RetrieveAuditDetailsRequest
                {
                    AuditId = auditid
                };
    RetrieveAuditDetailsResponse resp =
        (RetrieveAuditDetailsResponse)svc.Execute(req);
    DisplayAuditDetail(resp.AuditDetail);
}

2, RetrieveAttributeChangeHistory Message

Use this message to retrieve a list of changes for a specific table column.

GET [Organization URI]/api/data/v9.2/RetrieveAttributeChangeHistory(Target=@target,AttributeLogicalName=@attributeLogicalName,PagingInfo=@paginginfo)?

3, RetrieveRecordChangeHistory Message

This message shows the history of data changes for a given record indicated by the Target parameter.

GET [Organization URI]/api/data/v9.2/RetrieveRecordChangeHistory(Target=@target,PagingInfo=@paginginfo)?@target={ '@odata.id':'accounts(611e7713-68d7-4622-b552-85060af450bc)'}&@paginginfo={ "PageNumber": 1,"Count": 2,"ReturnTotalRecordCount": true}

The End

Hope it is helpful.

Regards.

Query D365/Dataverse Audit logs through API or Organization Service