Editar

Compartilhar via


Retrieve the history of audited data changes

When auditing is enabled and data in audited tables and columns is changed, you can retrieve the change history of those tables and columns.

Audit data isn't available using the Dataverse TDS (SQL) endpoint.

Audit history is not available for tables in the mobile app.

Audit table

Auditing events are stored in the Auditing (Audit) table. In the Web API, the audit EntityType is the resource for this data. The audit table is read-only.

The audit table provides the data for the View Audit Summary displayed in the Power Platform admin center.

Make sure the calling user has the prvReadAuditSummary privilege to retrieve data from the table.

The following table summarizes important columns in the audit table.

SchemaName
LogicalName
DisplayName
Type Description
Action
action
Event
Choice Represents the event that caused the change. Learn more about actions.
AttributeMask
attributemask
Changed Field
Memo When the change represents a change to record data, contains a comma-separated list of numbers that correspond to the AttributeMetadata.ColumnNumber for the columns that were changed.

Note: Don't use this data. Instead, use the messages to retrieve change history.
AuditId
auditid
Record Id
Unique identifier Identifies the primary key for the audit table.
CallingUserId
callinguserid
Calling User
Lookup Identifies the calling user when impersonation is used for the operation; otherwise, null.
CreatedOn
createdon
Changed Date
DateTime Identifies when the audit record was created, which is when the user operation took place.
ObjectId
objectid
Record
Lookup Uniquely identifies the record that was audited.
ObjectTypeCode
objecttypecode
Entity
EntityName Displays the logical name of the table referred to by the objectid column.
Operation
operation
Operation
Choice Identifies the operation that created the audit record; one of four values:
1 = Create
2 = Update
3 = Delete
4 = Access
UserId
userid
Changed By
Lookup Displays the ID of the user who changed the data.

Audit actions

Use Action Choices/Options to filter for specific events. The following tables categorize the events.

Table row events

These events capture changes to a record.

Value Label Message Description
1 Create Create A record is created.
2 Update Update A record is updated.
3 Delete Delete A record is deleted.
12 Merge Merge A record is merged with another.
13 Assign Assign The ownerid column value for a user-owned table record is changed.
41 Set State SetState The statecode column value for a record is changed.

Record sharing events

These events capture changes to record access when a record is shared.

Value Label Message Description
14 Share GrantAccess A user is granted privileges to a record.
48 Modify Share ModifyAccess The privileges granted to a user are changed.
49 Unshare RevokeAccess A user's access to a record is removed.

Many-to-many relationship events

These events capture changes for many-to-many relationships.

Value Label Message Description
33 Associate Entities Associate One or more records are associated with another.
34 Disassociate Entities Disassociate One or more records are disassociated from another.
53 Assign Role To Team Associate A security role is assigned to a team.
54 Remove Role From Team Disassociate A security role is removed from a team.
55 Assign Role To User Associate A security role is assigned to a user.
56 Remove Role From User Disassociate A security role is removed from a user.

User access events

These options capture the history of user access when user access auditing is enabled. The audit record for these events has an operation column value of 4.

Value Label Description
64 User Access via Web User is accessing Dataverse using a model-driven app.
65 User Access via Web Services User is accessing Dataverse with web services using a client other than a model-driven app.
112 User Access Audit Started User access audit began.
113 User Access Audit Stopped User access audit ended.

The Sample: Audit user access shows how to use these action options to audit user access.

Metadata change events

These events capture changes to table and column definitions as well as changes to the organization table.

Value Label Description
100 Delete Entity User deleted a table.
101 Delete Attribute User deleted a column.
102 Audit Change at Entity Level User changed a table definition to enable or disable auditing.
103 Audit Change at Attribute Level User changed a column definition to enable or disable auditing.
104 Audit Change at Org Level User changed organization settings.

Audit change events

These events capture changes to audit settings.

Value Label Description
105 Entity Audit Started Auditing was enabled for a table.
106 Attribute Audit Started Auditing was enabled for a column.
107 Audit Enabled Auditing was enabled for the organization.
108 Entity Audit Stopped Auditing was disabled for a table.
109 Attribute Audit Stopped Auditing was disabled for an attribute.
110 Audit Disabled Auditing was disabled for a column.
111 Audit Log Deletion An audit log was deleted.

Security role change events

These events capture changes to security roles.

Value Label Message Description
57 Add Privileges to Role AddPrivilegesRole Privileges were added to a role.
58 Remove Privileges From Role RemovePrivilegeRole Privileges were removed from a role.
59 Replace Privileges In Role ReplacePrivilegesRole Privileges for a role were replaced.

Other actions

The remaining action options generally refer to auditable operations that apply to specific solutions, such as Dynamics 365 Sales, Customer Service, and Marketing.

The labels for these actions should align with an SdkMessage.Name value that represents the action. The specific operation may be a combination of the action name and a table. For example, an option with a value of 10 and the label Close should correspond to the CloseIncident or CloseQuote messages.

Audit table relationships

The audit table has only two many-to-one relationships with the systemuser table:

Relationship Audit Table Lookup Description
lk_audit_userid userid Relates the user to all the audit records created because of changes they made.
lk_audit_callinguserid callinguserid Relates the user to any of the audit records they created while impersonating another user.

You can use these relationships to filter audit data records created for a specific user.

The audit entity supports only one link entity in a query. Since only two relationships exist with the systemuser table, you can include data from either the callinguserid or userid columns, but not both at the same time.

You can't build queries using QueryExpression or FetchXml that join audit data with tables other than the two formal relationships that exist with the systemuser table.

Audit EntityType definition

With the Web API, use the audit EntityType resource to read data from the audit table. The following data is the audit EntityType definition from the Web API CSDL $metadata service document without annotations.

<EntityType Name="audit" BaseType="mscrm.crmbaseentity">
  <Key>
      <PropertyRef Name="auditid" />
  </Key>
  <Property Name="operation" Type="Edm.Int32" />
  <Property Name="attributemask" Type="Edm.String" Unicode="false" />
  <Property Name="action" Type="Edm.Int32" />
  <Property Name="useradditionalinfo" Type="Edm.String" Unicode="false" />
  <Property Name="createdon" Type="Edm.DateTimeOffset" />
  <Property Name="objecttypecode" Type="Edm.String" Unicode="false" />
  <Property Name="_callinguserid_value" Type="Edm.Guid" />
  <Property Name="_regardingobjectid_value" Type="Edm.Guid" />
  <Property Name="_objectid_value" Type="Edm.Guid" />
  <Property Name="_userid_value" Type="Edm.Guid" />
  <Property Name="transactionid" Type="Edm.Guid" />
  <Property Name="auditid" Type="Edm.Guid" />
  <NavigationProperty Name="callinguserid" Type="mscrm.systemuser" 
    Nullable="false" Partner="lk_audit_callinguserid">
      <ReferentialConstraint Property="_callinguserid_value" 
        ReferencedProperty="systemuserid" />
  </NavigationProperty>
  <NavigationProperty Name="userid" Type="mscrm.systemuser" 
    Nullable="false" Partner="lk_audit_userid">
      <ReferentialConstraint Property="_userid_value" 
        ReferencedProperty="systemuserid" />
  </NavigationProperty>
</EntityType>

Note

The ChangeData Column isn't included in the Web API audit EntityType. Don't use this data. Instead, use the messages to retrieve audit change history.

Example: Find contact records deleted by a user

The following examples are queries that show the audit history for contact records deleted by a specific user.

Both of the following queries return the same response.

The following query filters on the _userid_value property of the audit record where the value matches <user id>.

Request:

GET [Organization URI]/api/data/v9.2/audits?$select=_objectid_value,objecttypecode,createdon,_userid_value&$orderby=createdon desc&$filter=operation eq 3 and objecttypecode eq 'contact' and _userid_value eq '<user id>'

Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0
If-None-Match: null
Prefer: odata.include-annotations="*" 

The following query accesses the collection of audit records for a specific user with the lk_audit_userid collection-valued navigation property from the systemuser table, where the systemuserid value matches <user id>.

Request:

GET [Organization URI]/api/data/v9.2/systemusers(<user id>)/lk_audit_userid?$select=_objectid_value,objecttypecode,createdon,_userid_value&$orderby=createdon desc&$filter=operation eq 3 and objecttypecode eq 'contact'

Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0
If-None-Match: null
Prefer: odata.include-annotations="*" 

Response:

HTTP/1.1 200 OK
Preference-Applied: odata.include-annotations="*"

{
  "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#audits(_objectid_value,objecttypecode,createdon,_userid_value)",
  "@Microsoft.Dynamics.CRM.totalrecordcount": -1,
  "@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": false,
  "value": [
    {
      "_objectid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "contact",
      "_objectid_value": "0e76dc8a-41b5-ec11-983f-0022482bf046",
      "objecttypecode@OData.Community.Display.V1.FormattedValue": "Contact",
      "objecttypecode": "contact",
      "createdon@OData.Community.Display.V1.FormattedValue": "5/12/2022 3:19 PM",
      "createdon": "2022-05-12T22:19:12Z",
      "_userid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
      "_userid_value@OData.Community.Display.V1.FormattedValue": "FirstName LastName",
      "_userid_value": "4026be43-6b69-e111-8f65-78e7d1620f5e"
    },
    < Other results truncated for brevity>
  ]
}

Retrieve audit change history

You can use any of three messages to retrieve data changes that are audited.

Web API SDK for .NET Description
RetrieveAuditDetails Function RetrieveAuditDetailsRequest Class> Retrieve the full audit details from an audit record.
RetrieveAttributeChangeHistory Function RetrieveAttributeChangeHistoryRequest Class Retrieve the change history for a single column of an audited record.
RetrieveRecordChangeHistory Function RetrieveRecordChangeHistoryRequest Class Retrieve all audited data changes for a specific record.

To use these messages, make sure you have the prvReadRecordAuditHistory and prvReadAuditSummary privileges.

AuditDetail types

These messages provide more details that depend on the type of action. The details are implemented using types that are derived from a base AuditDetail type, as shown in the following table.

Web API SDK for .NET Description
AuditDetail ComplexType AuditDetail Class Displays the base type for the derived classes. Provides access to the audit record.
AttributeAuditDetail ComplexType AttributeAuditDetail Class Provides details when data changes occur for a record. Provides access to old values and new values.
Returned by the following types of actions:
- Table row events
- Metadata change events
- Audit change events
RelationshipAuditDetail ComplexType RelationshipAuditDetail Class Provides details when records are associated or disassociated using a many-to-many relationship. Provides the name of the relationship and a list of the records the operation changed.
Returned by many-to-many relationship events.
RolePrivilegeAuditDetail ComplexType RolePrivilegeAuditDetail Class Provides details when the definitions of Security Role (Role) records change. Provides information about the old and new role privileges associated with the role.
Returned by security role change events.
ShareAuditDetail ComplexType ShareAuditDetail Class Provides details when a record is shared or unshared or when the level of access to a shared record changes.
Returned by record sharing events.
UserAccessAuditDetail ComplexType UserAccessAuditDetail Class> Provides details to track user access auditing. Provides details on the interval and access time.
Returned by user access events.

Important

  • The Web API types listed earlier that inherit from AuditDetail ComplexType don't return the AuditRecord navigation property value they should inherit from AuditDetail. The SDK for .NET classes returns this data.

  • Large column values included in AttributeAuditDetail OldValue or NewValue properties such as Email.Description or Annotation are capped at 5KB or about 5,000 characters. A capped column value can be recognized by an ellipsis (…) at the end of the text; for example, "lorem ipsum, lorem ip…" Because the data is truncated, you can't use it to restore changes to these column values.

RetrieveAuditDetails message

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

RetrieveAuditDetails is a function bound to the audit table. Include the Prefer: odata.include-annotations="*" request header to get formatted values.

The following example shows the AttributeAuditDetail ComplexType returned when the parentaccountid is set on an account record.

Request:

GET [Organization URI]/api/data/v9.2/audits(12869c65-d7d3-ec11-b656-281878f0eba9)/Microsoft.Dynamics.CRM.RetrieveAuditDetails

Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0
If-None-Match: null
Prefer: odata.include-annotations="*" 

Response:

HTTP/1.1 200 OK
OData-Version: 4.0
Preference-Applied: odata.include-annotations="*"

{
 "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#Microsoft.Dynamics.CRM.RetrieveAuditDetailsResponse",
 "AuditDetail": {
  "@odata.type": "#Microsoft.Dynamics.CRM.AttributeAuditDetail",
  "InvalidNewValueAttributes": [],
  "LocLabelLanguageCode": 0,
  "DeletedAttributes": {
   "Count": 0,
   "Keys": [],
   "Values": []
  },
  "OldValue": {
   "@odata.type": "#Microsoft.Dynamics.CRM.account"
  },
  "NewValue": {
   "@odata.type": "#Microsoft.Dynamics.CRM.account",
   "_parentaccountid_value@OData.Community.Display.V1.FormattedValue": "A. Datum Corporation",
   "_parentaccountid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "parentaccountid",
   "_parentaccountid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "account",
   "_parentaccountid_value": "d249d106-38b5-ec11-983f-002248296cd0"
  }
 }
}

Learn more about:

RetrieveAttributeChangeHistory Message

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

Use the PagingInfo parameter to control the number of records to return and move forward or backward through the pages. For subsequent requests, set the PagingInfo.PagingCookie property to the value returned by the AuditDetailCollection.PagingCookie.

Changes for this message are always AttributeAuditDetail types.

This example returns a single audited change history for the description column of an account table record.

Request:

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

Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0
If-None-Match: null

Response:

HTTP/1.1 200 OK

{
 "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#Microsoft.Dynamics.CRM.RetrieveAttributeChangeHistoryResponse",
 "AuditDetailCollection": {
  "MoreRecords": true,
  "PagingCookie": "<cookie page=\"1\"><cookieExtensions ContinuationToken=\"{&quot;pageNumber&quot;:2,&quot;continuationToken&quot;:&quot;[{\\&quot;compositeToken\\&quot;:{\\&quot;token\\&quot;:null,\\&quot;range\\&quot;:{\\&quot;min\\&quot;:\\&quot;3A800000000000000000000000000000\\&quot;,\\&quot;max\\&quot;:\\&quot;3B000000000000000000000000000000\\&quot;}},\\&quot;orderByItems\\&quot;:[{\\&quot;item\\&quot;:\\&quot;2022-05-13T22:06:46.6175613Z\\&quot;}],\\&quot;rid\\&quot;:\\&quot;CVoNAJIidnNsmz0AAADwAw==\\&quot;,\\&quot;skipCount\\&quot;:0,\\&quot;filter\\&quot;:null}]&quot;}\" /></cookie>",
  ,
  "TotalRecordCount": 3,
  "AuditDetails": [
   {
    "@odata.type": "#Microsoft.Dynamics.CRM.AttributeAuditDetail",
    "InvalidNewValueAttributes": [],
    "LocLabelLanguageCode": 0,
    "DeletedAttributes": {
     "Count": 0,
     "Keys": [],
     "Values": []
    },
    "OldValue": {
     "@odata.type": "#Microsoft.Dynamics.CRM.account",
     "description": "Old description value"
    },
    "NewValue": {
     "@odata.type": "#Microsoft.Dynamics.CRM.account",
     "description": "New description value"
    }
   }
  ]
 }
}

Learn more about:

RetrieveRecordChangeHistory Message

The RetrieveRecordChangeHistory message shows the history of data changes for the record indicated by the Target parameter.

Use the PagingInfo parameter to control the number of records to return and move forward or backward through the pages. For subsequent requests, set the PagingInfo.PagingCookie property to the value returned by the AuditDetailCollection.PagingCookie.

The results of this message are commonly seen as the AttributeAuditDetail data displayed in model-driven apps when you select Related > Audit history. It shows the old values and the new values of the records, but it also returns RelationshipAuditDetail and ShareAuditDetail types.

This message can also be used with the systemuser and role tables to return RolePrivilegeAuditDetail and UserAccessAuditDetail types.

The following example returns just the first two of four changes to an account record.

Request:

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
}
Accept: application/json  
OData-MaxVersion: 4.0  
OData-Version: 4.0
If-None-Match: null

Response:

HTTP/1.1 200 OK

{
 "@odata.context": "[Organization URI]/api/data/v9.2/$metadata#Microsoft.Dynamics.CRM.RetrieveRecordChangeHistoryResponse",
 "AuditDetailCollection": {
  "MoreRecords": true,
  "PagingCookie": "<cookie page=\"1\"><cookieExtensions ContinuationToken=\"{&quot;pageNumber&quot;:2,&quot;continuationToken&quot;:&quot;[{\\&quot;compositeToken\\&quot;:{\\&quot;token\\&quot;:null,\\&quot;range\\&quot;:{\\&quot;min\\&quot;:\\&quot;38000000000000000000000000000000\\&quot;,\\&quot;max\\&quot;:\\&quot;38800000000000000000000000000000\\&quot;}},\\&quot;orderByItems\\&quot;:[{\\&quot;item\\&quot;:\\&quot;2022-05-13T22:06:27.8029732Z\\&quot;}],\\&quot;rid\\&quot;:\\&quot;CVoNAJIidnPOnT0AAAAICA==\\&quot;,\\&quot;skipCount\\&quot;:0,\\&quot;filter\\&quot;:null}]&quot;}\" /></cookie>",
  "TotalRecordCount": 4,
  "AuditDetails": [
   {
    "@odata.type": "#Microsoft.Dynamics.CRM.AttributeAuditDetail",
    "InvalidNewValueAttributes": [],
    "LocLabelLanguageCode": 0,
    "DeletedAttributes": {
     "Count": 0,
     "Keys": [],
     "Values": []
    },
    "OldValue": {
     "@odata.type": "#Microsoft.Dynamics.CRM.account",
     "description": "Old description value"
    },
    "NewValue": {
     "@odata.type": "#Microsoft.Dynamics.CRM.account",
     "description": "New description value"
    }
   },
   {
    "@odata.type": "#Microsoft.Dynamics.CRM.AttributeAuditDetail",
    "InvalidNewValueAttributes": [],
    "LocLabelLanguageCode": 0,
    "DeletedAttributes": {
     "Count": 0,
     "Keys": [],
     "Values": []
    },
    "OldValue": {
     "@odata.type": "#Microsoft.Dynamics.CRM.account",
     "_ownerid_value@OData.Community.Display.V1.FormattedValue": "FirstName LastName",
     "_ownerid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "ownerid",
     "_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
     "_ownerid_value": "4026be43-6b69-e111-8f65-78e7d1620f5e"
    },
    "NewValue": {
     "@odata.type": "#Microsoft.Dynamics.CRM.account",
     "_ownerid_value@OData.Community.Display.V1.FormattedValue": "TeamName",
     "_ownerid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "ownerid",
     "_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "team",
     "_ownerid_value": "39e0dbe4-131b-e111-ba7e-78e7d1620f5e"
    }
   }
  ]
 }
}

Note

The AuditDetail ComplexType values returned don't include the AuditRecord property, so no data about who made the change and when it was made is available.

Learn more about:

See also

Auditing overview
Configure auditing
Delete audit data
Manage Dataverse auditing
Sample: Audit table data changes
Sample: Audit user access