Using the CRM Audit Log to Rollback Records

Argh! Someone has done something and something is ruined! Fortunately someone only changed records in CRM, and even better audit logging was enabled. You can now use the web service SDK to analyse changes that have occurred, you can then use this information to perform the opposite action and roll back changes.

I first came across this technique after finding myself in a situation where someone had accidentally bulk updated the status reason of a few hundred records. Unfortunately I couldn’t just bulk update in reverse as each record had one of numerous statuses.

Whilst looking into this problem, I found this MSDN article which led me in the right direction Retrieve and delete the history of audited data changes.

Effectively I was able to write the code below which searched for changes:

  • On a specific set of records.
  • By a specific user.
  • At a specific time.
  • On the status reason field.

I was able to find the original value, and the new (wrong) value. I then made a status change call to reverse the change.

Code below, see the inline comments for further detail.

static void Main(string[] args)
{
    //I used Log4Net to log the changes.
    XmlConfigurator.Configure();
    ILog log = LogManager.GetLogger("Log");

    //This code was run from a console app, so this creates my connection.
    OrganizationService service = new OrganizationService("Connection");

    //I knew a specific set of records had been changed. This query finds those records.
    QueryExpression query = new QueryExpression("new_myentity")
    {
        //Removed for sake of space...
    };
            
    //For each record retrieved above I then checked the audit log for that record.
    foreach(Entity e in service.RetrieveMultiple(query).Entities)
    {
        //This retrieves all the changes that have occured on the status code attribute for a single record.
        //This wasnt very fast by the way.
        var request = new RetrieveAttributeChangeHistoryRequest
        {
            Target = new EntityReference("new_myentity", e.Id),
            AttributeLogicalName = "statuscode"
        };

        var response = (RetrieveAttributeChangeHistoryResponse)service.Execute(request);

        //For logging.
        String number = e.GetAttributeValue<String>("new_name");

        //I'm building an anonymous type here to contain each valid record with its original and new value.
        //The response contains all the changes that have occured on that field for that record.
        var changes = response.AuditDetailCollection.AuditDetails
                .Where(ad => ad as AttributeAuditDetail != null) //When null we arnt interested - doesn't have values below.
                .Select(ad =>
                {
                    AttributeAuditDetail aad = ((AttributeAuditDetail)ad);

                    return new
                    {
                        Number = number,
                        RecordId = e.Id,
                        ChangeDate = ad.AuditRecord.GetAttributeValue<DateTime>("createdon"),
                        By = ad.AuditRecord.GetAttributeValue<EntityReference>("userid").Name,

                        //Get the old values, these functions help extract the information.
                        OldValue = GetValue(aad.OldValue),
                        OldValueCode = GetValueCode(aad.OldValue),

                        //And the new.
                        NewValue = GetValue(aad.NewValue),
                        NewValueCode = GetValueCode(aad.NewValue),
                    };
                })
                .Where(c => c.By == "A user" && ValidDate(c.ChangeDate)); //Only interested by changes by a specific user on a specific day.

        //We now have a list of changes, the code below loops through logging out the details, and then makes the reverse change.
        //In my case, there was only 1 change, so the loop is actually unneccessary but thats just how the code ended up.
        //If you have multiple changes your might want to give a little more thought to how they should be applied.
        foreach(var c in changes)
        {
            log.Info(c.Number + " - " + c.OldValue + "(" + c.OldValueCode + ") -> " + c.NewValue + "(" + c.NewValueCode + ")");
                    
            SetStateRequest state = new SetStateRequest();
            state.State = new OptionSetValue(0);
            state.Status = new OptionSetValue(c.OldValueCode);
            state.EntityMoniker = new EntityReference("new_myentity", e.Id);
            service.Execute(state);

            log.Info(c.Number + " - " + c.OldValue + "(" + c.OldValueCode + ") <- " + c.NewValue + "(" + c.NewValueCode + ")");
        }
    }
}

static string GetValue(Entity e)
{
    if (e.FormattedValues.Count > 0)
    {
        return e.FormattedValues["statuscode"];
    }
    return "?";
}

static int GetValueCode(Entity e)
{
    if (e.FormattedValues.Count > 0)
    {
        return e.GetAttributeValue<OptionSetValue>("statuscode").Value;
    }
    return 1;
}

static bool ValidDate(DateTime dt)
{
    DateTime start = new DateTime(2015, 07, 15, 11, 15, 0);
    DateTime end = new DateTime(2015, 07, 15, 11, 21, 0);

    return dt > start && dt < end;
}

And the output.

INFO  Log - I-123 - In Progress(6) -> Pending(1)
INFO  Log - I-123 - In Progress(6) <- Pending(1)
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