DataAudit for Microsoft Dynamics CRM 4.0

As you all know CRM doesn’t provide inbuilt DataAudit which will track changes made by user.
Below is the complete solution which will track EntityName,attribute name, Old Value and New Value of field, Modified By and Modified On..Please follow these steps

1)      Create two custom entity lets say

DataAudit(new_dataaudit) – Tracked record on this entity

DataAudit Configuration (new_dataauditconfiguration) – used for data audit configuration

2)      Create these field on DataAudit entity.
Attribute Name(new_attributename)- nvarchar

Previous Value(new_previousvalue)- nvarchar

New Value(new_newvalue)-nvarchar

Name(new_name)—already exist

3)      Create a 1-M relationship from opportunity(in my case) to DataAudit and publish the all customization done till now.

4)      Create some field on DataAudit Configuration entity

Name(new_name) – already exist and make it read only.

IsActive(new_isactive)-bit- radio button with default value  “yes”

Relationshipattributename(new_relationshipattributename)-nvarchar

Entity Name(new_entityname)- Picklist with value(Lead,Opportunity,Account,Contact,….)

Copy and paste below code on OnChange event of Entity Name field.

// Entity Name OnChange Event

var entityName=crmForm.all.new_entityname.SelectedText;

switch(entityName)

{

case “Opportunity” :

crmForm.all.new_name.DataValue=”opportunity”;

break;

case “Lead” :

crmForm.all.new_name.DataValue=”lead”;

break;

case “Contact” :

crmForm.all.new_name.DataValue=”contact”;

break;

case “Account” :

crmForm.all.new_name.DataValue=”account”

break;;

}

5)      Create one record for each entity in DataAudit Configuration entity.

Lets say for opportunity

6)      Build dll for DataAudit using following code.

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.Crm.Sdk;

using Microsoft.Crm.SdkTypeProxy;

using System.Collections.Specialized;

using System.Web.Services.Protocols;

using Microsoft.Crm.Sdk.Metadata;

using Microsoft.Crm.SdkTypeProxy.Metadata;

using Microsoft.Crm.Sdk.Query;

namespace CRM.Customization.DataAudit

{

public class DataAudit:IPlugin

{

DynamicEntity preImageEntity = null;

DynamicEntity postImageEntity = null;

Guid recordGuid;

string relationshipSchemaName = string.Empty;

string label = string.Empty;

#region IPlugin Members

public void Execute(IPluginExecutionContext context)

{

ICrmService service = context.CreateCrmService(true);

IMetadataService mservice = context.CreateMetadataService(true);

// Check DataAudit Configration and check if entity is auditable.

bool isDataAuditConfigExist = RetrieveAuditConfiguration(service, context);

if(isDataAuditConfigExist)

{

if (context.MessageName == “Update” || context.MessageName == “Assign”)

{

preImageEntity = (DynamicEntity)context.PreEntityImages.Properties[“PostImage”];

postImageEntity = (DynamicEntity)context.PostEntityImages.Properties[“PostImage”];

// Retrieve  id of entity

Key rGuid = (Key)postImageEntity.Properties[context.PrimaryEntityName + “id”];

recordGuid = rGuid.Value;

// Adding pre and post value of attribute to collection.

NameValueCollection prop = new NameValueCollection();

AddPropertiesToCollection(prop, preImageEntity, context.PrimaryEntityName, false);

AddPropertiesToCollection(prop, postImageEntity, context.PrimaryEntityName, true);

foreach (string key in prop)

{

// using visual  basic split method in order to split on a string, //instead of a character

string[] arr = Microsoft.VisualBasic.Strings.Split(prop[key], “|,|”, -1, Microsoft.VisualBasic.CompareMethod.Text);

if (arr.Length > 1)

{

if (arr[0] != arr[1])

{

// calling getLabel method to retrieve the label of Field

label = GetLabel(mservice,key, context.PrimaryEntityName);

//if the value do not match, then insert a record into our DataAudit //table

InsertAuditRecord(service, context.PrimaryEntityName,label, arr[0], arr[1]);

}

}

else

{

//if the array length is 1, then we have a case where a text value //was changed to or from blank value.

// need to split on just the resulting string

string[] arrBlankText = prop[key].Split(‘|’);

if (arrBlankText.Length > 1)

{

// calling getLabel method to retrieve the label of Field

label = GetLabel(mservice,key, context.PrimaryEntityName);

//if the value do not match, then insert a record into our DataAudit //table

InsertAuditRecord(service, context.PrimaryEntityName,label, arrBlankText[0], arrBlankText[1]);

}

}

}

}

}

}

private void InsertAuditRecord(ICrmService service, string entityName,string AttributeName, string PreviousValue, string NewValue)

{

try

{

// Create instance of Dynamic Entity

DynamicEntity dynamicEntity = new DynamicEntity();

dynamicEntity.Name = “new_dataaudit”;

// Instantiate a string property.

StringProperty attribute = new StringProperty();

attribute.Name = “new_attribute”;

attribute.Value = AttributeName;

StringProperty oldValue = new StringProperty();

oldValue.Name = “new_previousvalue”;

oldValue.Value = PreviousValue;

StringProperty newValue = new StringProperty();

newValue.Name = “new_newvalue”;

newValue.Value = NewValue;

StringProperty entity = new StringProperty();

entity.Name = “new_name”;

if (entityName.StartsWith(“new_”))

{

entity.Value = entityName.Substring(4);

}

else

{

entity.Value = entityName;

}

LookupProperty entityLookup = new LookupProperty();

entityLookup.Name = relationshipSchemaName;

entityLookup.Value = new Lookup();

entityLookup.Value.Value = recordGuid;

entityLookup.Value.type = entityName;

// Set the properties for entity.

//dynamicEntity.Properties = new Property[] { entity, attribute,    //oldValue, newValue, entityLookup };

dynamicEntity.Properties.Add(entity);

dynamicEntity.Properties.Add(attribute);

dynamicEntity.Properties.Add(oldValue);

dynamicEntity.Properties.Add(newValue);

dynamicEntity.Properties.Add(entityLookup);

TargetCreateDynamic myTarget = new TargetCreateDynamic();

myTarget.Entity = dynamicEntity;

CreateRequest create = new CreateRequest();

create.Target = myTarget;

CreateResponse myResponse = (CreateResponse)service.Execute(create);

}

catch (SoapException ex)

{

throw ex;

}

catch (Exception ex)

{

throw ex;

}

}

private void AddPropertiesToCollection(NameValueCollection prop, Microsoft.Crm.Sdk.DynamicEntity entityData, string entityName, bool postData)

{

string attributeName;

string attributeValue;

string attributeModValue;

PropertyCollection dyProperty = entityData.Properties;

//Loop through the field

foreach (Microsoft.Crm.Sdk.Property property in dyProperty)

{

attributeValue = string.Empty;

attributeModValue = string.Empty;

// Get the attributes name

attributeName = property.Name;

attributeValue = GetValueFromProperty(entityData, entityName,  attributeName, property);

//Add a pipe character after the value if we are looping through the pre data

//or before the value if we are looping through the post data. attributeModValue = (postData) ? “|” + attributeValue : attributeValue + “|”;

//skip these values , since they changes on every update

if ((attributeName != “modifiedon”) && (attributeName != “modifiedby”))

{

prop.Add(attributeName, attributeModValue);

}

}

}

private string GetValueFromProperty(Microsoft.Crm.Sdk.DynamicEntity prePostImageEntity, string entityName, string attributeName, Microsoft.Crm.Sdk.Property inputProperty)

{

Type propType = inputProperty.GetType();

string propValue = string.Empty;

// string propValue1 = string.Empty;

if (propType == typeof(Microsoft.Crm.Sdk.StringProperty))

// if field is of type string.

{

propValue = ((Microsoft.Crm.Sdk.StringProperty)inputProperty).Value.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.CustomerProperty))

// if field is of Customer type.

{

propValue = ((Microsoft.Crm.Sdk.CustomerProperty)inputProperty).Value.name.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.CrmBooleanProperty))

// if field is of CrmBoolean type.

{

propValue = ((Microsoft.Crm.Sdk.CrmBooleanProperty)inputProperty).Value.Value.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.CrmMoneyProperty))

// if field is of CrmMoney type.

{

propValue = ((Microsoft.Crm.Sdk.CrmMoneyProperty)inputProperty).Value.Value.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.CrmDateTimeProperty))

// if field is of CrmDateTime type.

{

string myValue;

myValue = ((Microsoft.Crm.Sdk.CrmDateTimeProperty)inputProperty).Value.Value.ToString();

propValue = Convert.ToDateTime(myValue).GetDateTimeFormats()[3];

}

else if (propType == typeof(Microsoft.Crm.Sdk.CrmDecimalProperty))

// if field is of CrmDecimal type.

{

propValue = ((Microsoft.Crm.Sdk.CrmDecimalProperty)inputProperty).Value.Value.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.CrmFloatProperty))

// if field is of CrmFloat Type.

{

propValue = ((Microsoft.Crm.Sdk.CrmFloatProperty)inputProperty).Value.Value.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.CrmNumberProperty))

//if field is of CrmNumber Type.

{

propValue = ((Microsoft.Crm.Sdk.CrmNumberProperty)inputProperty).Value.Value.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.LookupProperty))

//if field is of Lookup Type.

{

if (inputProperty.Name.ToString() != “owningbusinessunit”)

propValue = ((Microsoft.Crm.Sdk.LookupProperty)inputProperty).Value.name.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.OwnerProperty))       //if field is of owner type.

{

propValue = ((Microsoft.Crm.Sdk.OwnerProperty)inputProperty).Value.name.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.StatusProperty))

// if field is of type Status

{

propValue = ((Microsoft.Crm.Sdk.StatusProperty)inputProperty).Value.ToString();

}

else if (propType == typeof(Microsoft.Crm.Sdk.PicklistProperty))

//if field is of type Picklist

{

propValue = ((Microsoft.Crm.Sdk.PicklistProperty)inputProperty).Value.name.ToString();

}

return propValue;

}

private string GetLabel(IMetadataService service, string fieldName, string entityName)

{

string display = string.Empty;

//call instance of MetaDataService to call a web service             RetrieveAttributeRequest request = new RetrieveAttributeRequest();

request.EntityLogicalName = entityName;

request.LogicalName = fieldName;

RetrieveAttributeResponse response = (RetrieveAttributeResponse)service.Execute(request);

// Get Type of Field

Type attributeType = response.AttributeMetadata.GetType();

// Access the retrieved attribute

if (attributeType == typeof(DecimalAttributeMetadata))

{

DecimalAttributeMetadata retrievedAttributeMetadata = (DecimalAttributeMetadata)response.AttributeMetadata;              LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

else if (attributeType == typeof(FloatAttributeMetadata))

{

FloatAttributeMetadata retrievedAttributeMetadata = (FloatAttributeMetadata)response.AttributeMetadata;

LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

else if (attributeType == typeof(IntegerAttributeMetadata))

{

IntegerAttributeMetadata retrievedAttributeMetadata = (IntegerAttributeMetadata)response.AttributeMetadata;

LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

else if (attributeType == typeof(MoneyAttributeMetadata))

{

MoneyAttributeMetadata retrievedAttributeMetadata = (MoneyAttributeMetadata)response.AttributeMetadata;

LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

else if (attributeType == typeof(LookupAttributeMetadata))

{

LookupAttributeMetadata retrievedAttributeMetadata = (LookupAttributeMetadata)response.AttributeMetadata;

LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

else if (attributeType == typeof(PicklistAttributeMetadata))

{

PicklistAttributeMetadata retrievedAttributeMetadata = (PicklistAttributeMetadata)response.AttributeMetadata;

LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

else if (attributeType == typeof(StateAttributeMetadata))

{

StateAttributeMetadata retrievedAttributeMetadata = (StateAttributeMetadata)response.AttributeMetadata;

LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

else if (attributeType == typeof(StatusAttributeMetadata))

{

StatusAttributeMetadata retrievedAttributeMetadata = (StatusAttributeMetadata)response.AttributeMetadata;

LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

else if (attributeType == typeof(StringAttributeMetadata))

{

StringAttributeMetadata retrievedAttributeMetadata = (StringAttributeMetadata)response.AttributeMetadata;

LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

else if (attributeType == typeof(DateTimeAttributeMetadata))

{

DateTimeAttributeMetadata retrievedAttributeMetadata = (DateTimeAttributeMetadata)response.AttributeMetadata;

LocLabel localLabel = retrievedAttributeMetadata.DisplayName.UserLocLabel;

display = localLabel.Label;

}

return display;

}

private bool RetrieveAuditConfiguration(ICrmService service,IPluginExecutionContext context)

{

try

{

string entityName = string.Empty;

bool isActive = false;

QueryByAttribute query = new QueryByAttribute();

query.EntityName = “new_dataauditconfiguration”;

ColumnSet cols = new ColumnSet();

cols.AddColumn(“new_entityname”);

cols.AddColumn(“new_relationshipattributename”);

cols.AddColumn(“new_isactive”);

query.ColumnSet = cols;

query.Attributes = new string[] { “new_name” };

query.Values = new object[] { context.PrimaryEntityName };

RetrieveMultipleRequest request = new RetrieveMultipleRequest();

request.Query = query;

request.ReturnDynamicEntities = true;

RetrieveMultipleResponse response = (RetrieveMultipleResponse)service.Execute(request);

DynamicEntity dynamicsEntity =  (DynamicEntity)response.BusinessEntityCollection.BusinessEntities[0];

if (dynamicsEntity.Properties.Count > 0)

{

// Extract the fullname from the dynamic entity string fullname;

if(dynamicsEntity.Properties.Contains(“new_relationshipattributename”))

{

String relationshipName = (String)dynamicsEntity.Properties[“new_relationshipattributename”];

relationshipSchemaName = relationshipName.ToString();

}

if (dynamicsEntity.Properties.Contains(“new_entityname”))

{

Picklist ename = (Picklist)dynamicsEntity.Properties[“new_entityname”];

entityName = ename.name.ToString();

}

if (dynamicsEntity.Properties.Contains(“new_isactive”))

{

CrmBoolean active = (CrmBoolean)dynamicsEntity.Properties[“new_isactive”];

isActive = active.Value;

}

}

if ((isActive) && (entityName != string.Empty) && (relationshipSchemaName !=string.Empty))

{

return true;

}

else

{

return false;

}

}

catch (SoapException ex)

{

throw ex;

}

}

#endregion

}

}

7)  Finally register the plugin on Post-Update and Post-Assign as shown in screen shot

Register Pre and Post Image as

I have register it on PostUpdate and PostAssign in Asynchronos mode as per my requirement.You can also register it on PostCreate.

if you are unable to view screen shot please find below url for word document.

https://arvindcsit.files.wordpress.com/2010/07/dataaudit-for-microsoft-dynamics-crm-4.docx

Enjoy J

Comments
  1. Denys says:

    Thanks a lot for the code!

    Superb solution…Had to implement order trailing on this one project at the company im working for and this really helped

    Keep up the good work guys!

  2. cyberdem says:

    Thank you so much. You rock. I appreciate your help and time for sharing

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