The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

Using Workflow to Maintain an Audit Log

Using Workflow to Maintain an Audit Log

  • Comments 11

Today we welcome back guest blogger Guy Riddle, a long time CRM MVP who shares his insight with us.

Out of the box, Microsoft CRM provides some basic record audit information – who & when created a record and who & when it was last updated by.

Occasionally I get asked for more detailed audit capabilities (typically from clients in the Financial Services industry). I also find that Sales Managers are interested in tracking changes made to the Opportunities – in particular the requirement is around key information that changes that has a direct impact on the Sales Pipeline E.g. Estimated Revenue; Estimated Close Date; Sales Stage/Probability; Rating; etc.

In a CRM 3.0 deployment we would typically have addressed this with writing a custom Callout. However, utilizing two of the new (and great) features of the CRM 4.0 Workflow engine we can do this with very little effort – and without the need to write any code.

Using this approach, you can take the concepts I am about to step you through and apply it to any other Entity that you want to track changes against. The only thing to consider here is that you do not have available to you in the Workflow tool is the value of the attribute before it was changed – so if you do not require an audit of the ‘before and after values’ then this works a treat! Of course if you did want the before and after values recorded together in the same audit record you would need to develop a custom Plugin to do that.

Step 1: Create the CRM Entity to hold the audit records.

In our example we will want to create a record of Opportunity changes so we will create an Entity.

Attributes have been added to this Entity to reflect the information we want to capture about changes a user makes.

A ‘Parental’ relationship has been established between this Entity and the Opportunity.

The Form has been modified to make the necessary fields visible and also to ‘lock’ the fields so that they and not editable.

Also, the Associated View has been updated to include appropriate attributes so it is easy to see what has changed.

guy1

Step 2: Create the Workflow Rule to automatically create a new audit record

Create an Organizational workflow rule, based on the ‘Opportunity’.

Guy2

Select which ‘attributes’ will cause the workflow rule to execute when a user changes any one of them.

guy3

Add a single statement (action) in the workflow rule to ‘Create a Record’ in the Opportunity History entity.

guy4

Click on the ‘Set Properties’ button to set the substitution variables that will be used to create the record.

guy5

Step 3: Publish the workflow rule and test your work

Looking down the list of entries you can easily see what has changed over a period of time.

guy6

The above is a very simple (but effective) example of the new workflow capabilities.

Be adventurous, try building on this simple example and try some of your own ideas out.

Guy Riddle

Dynamics CRM MVP

  • PingBack from http://www.travel-hilarity.com/airline_travel/?p=2585

  • To get around the previous values issue, you could create another workflow on the creation of the record to save the initial values. With the initial values and all the changes, you can record the history of the attribute values throughout the life of the record.

  • It should be noted that this is not intended to be a showcase method to deploy a SOX (or similar) compliant audit trail/log.  For those organisations that require audit that meets appropriate compliance levels then an appropriate solution needs to be implemented.  One such solution is available from c360 at http://www.c360.com/Audit.aspx

  • Elegant solution for audit trail. Attempting to use this I was unable to audit trail picklist or bit fields.  Am I making a mistake or will the workflow solution not allow these field types to be included.

  • I was having issues with picklists also.  You have to set up those fields as text (not picklist) to assign the values.

  • on the line "Add a single statement (action) in the workflow rule to ‘Create a Record’ in the Opportunity History entity."

    I am unable to locate the "Opportunity History" on the Dropdwons....

    Please advice !

  • Re: audit and modified by, is it correct that the owner of the workflow is recorded as the 'Modified By' on a record, rather than the user who has updated the source record (in my case Contacts)?

  • I cant get the audit record to link to the parent record, in my case, a Case record. The view is available in Case on the left hand side but there is no Audit record showing in there.

    Please advise on more info on relationships.

    Dom

  • if I switch to Japanese, Polish, etc. ,

  • Dear Guy,

    I have created an opportunity history entity to track changes in opportunities (as explained in this page.)

    The worflow’s related to the opportunity are succeeding every time I am changing an attribute but no opportunity history are created as related entities…

    can you help to identify what could be checked of success ?

    thanks,

    alex

  • Hi the above information was very useful.

    Can anyone of you please tell me if there is anyway that we can know the details of the users who have logged into the crm application.

Page 1 of 1 (11 items)
Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post