Retrieving Audit History Records via API

Although the Audit History entity is somewhat degenerated, records for this entity can be extracted via multiple API mechanisms in both Online and on-premise scenarios:

  1. Organization Service query  (QueryExpression, QueryByAttribute, LINQ, FetchXML)
  2. OrganizationData Service query

The Audit History entity lacks a FilteredView element and therefore can not be retrieved from the Database in a supported manner. Also, version 2016 new Web API does not expose the Audit entity and can not be used for that purpose.

In this post I will demonstrate using some of the API mechanisms to extract Audit History records. Note that no API mechanism retrieves all of the Audit History record attributes (as viewed in MSCRM UI), though some retrieve more attributes than others.

Using the OrganizationData Service:

The OrganizationData Service is soon to be depreciated, but it is still available in version 2016. Compared to other APIs, the OrganizationData service query retrieves the least details for an Audit History record.

The following query retrieves Audit History records created on or after 8.1.2016 08:00 AM:

https://thekrustykrab.api.crm.dynamics.com//XRMServices/2011/OrganizationData.svc/AuditSet?$filter=CreatedOn gt datetime’2016-08-01T08:00:00Z’

Following is the result describing one Audit history record regarding an Update action. Note the UserId, AuditId, the audited entity logical name, the audited entity record id attributes. The Audit new and old value are missing.

      <m:properties>
        <d:CreatedOn m:type="Edm.DateTime">2016-01-08T10:03:05Z</d:CreatedOn>
        <d:AttributeMask>8</d:AttributeMask>
        <d:AuditId m:type="Edm.Guid">a9811402-efb5-e511-80e4-6c3be5be6de0</d:AuditId>
        <d:TransactionId m:type="Edm.Guid">d4e30d02-efb5-e511-80da-d89d67645050</d:TransactionId>
        <d:UserId m:type="Microsoft.Crm.Sdk.Data.Services.EntityReference">
          <d:Id m:type="Edm.Guid">2b6b8853-b45a-4d06-b0ad-6621b22bcbde</d:Id>
          <d:LogicalName>systemuser</d:LogicalName>
          <d:Name>Yaniv Arditi</d:Name>
          <d:RowVersion m:null="true" />
        </d:UserId>
        <d:Operation m:type="Microsoft.Crm.Sdk.Data.Services.OptionSetValue">
          <d:Value m:type="Edm.Int32">2</d:Value>
        </d:Operation>
        <d:RegardingObjectId m:type="Microsoft.Crm.Sdk.Data.Services.EntityReference">
          <d:Id m:type="Edm.Guid" m:null="true" />
          <d:LogicalName m:null="true" />
          <d:Name m:null="true" />
          <d:RowVersion m:null="true" />
        </d:RegardingObjectId>
        <d:ObjectId m:type="Microsoft.Crm.Sdk.Data.Services.EntityReference">
          <d:Id m:type="Edm.Guid">eac785da-d3a5-e511-80d8-2c59e542ba68</d:Id>
          <d:LogicalName>opportunity</d:LogicalName>
          <d:Name>6 orders of Product SKU JJ202 changed</d:Name>
          <d:RowVersion m:null="true" />
        </d:ObjectId>
        <d:CallingUserId m:type="Microsoft.Crm.Sdk.Data.Services.EntityReference">
          <d:Id m:type="Edm.Guid" m:null="true" />
          <d:LogicalName m:null="true" />
          <d:Name m:null="true" />
          <d:RowVersion m:null="true" />
        </d:CallingUserId>
        <d:Action m:type="Microsoft.Crm.Sdk.Data.Services.OptionSetValue">
          <d:Value m:type="Edm.Int32">2</d:Value>
        </d:Action>
      </m:properties>

Using the Organization Service: 

The OrganizationService can be accessed from both server and client side code.

While the OrganizationService has the RetrieveMultiple method, which returns Audit History records collection with general Audit attributes, it also supports the RetrieveAuditDetailsRequest message which returns full details for a specific Audit record. So you can use any type of querying method (QueryExpression, QueryByAttribute, LINQ, FetchXML) to retrieve a matching History records collection and complete the data with the RetrieveAuditDetailsRequest.

The following FetchXML query retrieves Audit History records created on or after 8.1.2016 08:00 AM:

<fetch count=”25″>
    <entity name=”audit”>
         <filter type=”and”>
              <condition attribute=”createdon” operator=”gt” value=”2016-08-01T08:00:00Z” />
         </filter>
    </entity>
</fetch>

Following is the result describing one Audit history record. Note that the FetchXML result contains the Operation & Action type (Update) as well as AuditId, the audited entity logical name and the audited entity record id attributes.
Note that the Audit old and new values are still missing.

[
  {
    "formattedValues": {
      "operation": "Update",
      "createdon": "1/8/2016 12:03 PM",
      "action": "Update",
      "objecttypecode": "Opportunity"
    },
    "objectid": {
      "Id": "eac785da-d3a5-e511-80d8-2c59e542ba68",
      "LogicalName": "opportunity",
      "Name": "6 orders of Product SKU JJ202 changed"
    },
    "userid": {
      "Id": "2b6b8853-b45a-4d06-b0ad-6621b22bcbde",
      "LogicalName": "systemuser",
      "Name": "Yaniv Arditi"
    },
    "operation": {
      "Value": 2
    },
    "createdon": {},
    "auditid": "a9811402-efb5-e511-80e4-6c3be5be6de0",
    "attributemask": "8",
    "action": {
      "Value": 2
    },
    "objecttypecode": "opportunity",
    "transactionid": "d4e30d02-efb5-e511-80da-d89d67645050"
  }
]

Using the RetrieveAuditDetailsRequest coupled with the FetchXML result above, the Audit old & new values can be retrieved:

var audit DetailsRequest = new RetrieveAuditDetailsRequest
{
    AuditId = new Guid(“41674669-e2b5-e511-80e4-6c3be5be6de0”)
};

var auditDetailsResponse = (RetrieveAuditDetailsResponse)organizationService.Execute(auditDetailsRequest);

Following is the RetrieveAuditDetailsResponse object viewed in QuickWatch:

Quickwatch: Audit new and old value

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s