SharePoint's Automatic Workflow Cleanup and a Loss of Approvals

The Problem

I haven't posted in a while because I have been really busy at work. But, I think it appropriate to take a little bit of time to detail a very serious problem that cropped up. Specifically it applies to approval workflows and libraries / lists being audited. And, I want to take some time to also explain what I did to work around this issue.

For this scenario I will use the Foo library and the Bar workflow in any given example.

So last week an issue come in that the approvals in the Foo library had suddenly disappeared. Auditors had gone in to review the library but any approval that had been showing in the Foo Approvals column, courtesy of our Bar approval workflow, had suddenly vanished if the approval itself was older than 60 days.

I immediately went into the Foo Approval Tasks list to find that the approval tasks themselves had also disappeared. Not to be found in the Site or the Site Collection Recycle bins either.

My next diagnostic step was to look at the Activity Duration Report to determine if these approvals had ever even occurred in the first place.

Sure enough, all of the data was there supporting that the approvals had occurred. After much more painstaking research and eyebrow raising I came to the conclusion that this wasn't the doing of some human. I had begun to think that someone had come in and deleted the Bar Workflow just to recreate another Bar Workflow with the same name. This too, wipes out prior Approvals but doesn't explain the every 60 days component.

Then I found it. The Automatic Workflow Cleanup job.

Microsoft actually designed this job in SharePoint to delete workflow instances and related task entries that still existed 60 days after the workflow completed or was canceled. WHAT?!? So now there is no evidence that my valuable and precious List items were approved?

Here is what they say about this:

By default, Microsoft SharePoint Server 2010 runs a daily Workflow Auto Cleanup job to permanently delete workflow instances and related task entries that still exist 60 days after a workflow is completed or canceled. Workflow history items themselves are not deleted, but the entry point to view them on the status page for a particular instance of a workflow will no longer be available. You can disable the Workflow Auto Cleanup job if you want to keep workflow data available longer. However, as with any SharePoint list, as the workflow history and task lists grow in size, site performance may be compromised. If you are concerned about the size of these lists, keep the Workflow Auto Cleanup job enabled. Whether or not you keep the Workflow Auto Cleanup job enabled, you can create a separate history and task list for each workflow association to distribute items across lists.

Workflow history is not intended to be used to audit workflow events and is not necessarily secure because a user who has edit permissions on the site can update items in the history list, by default.

And there you have it. In a nutshell we are not supposed to be using workflows for auditing purposes. *sigh*

The Workaround

The most appropriate work around is to use the auditing capabilities already found within SharePoint. I would even suggest doing this in conjunction with a Records Center and coupled with a solid retention policy. No small task when given the scale of that project. IT Admins, corporate Compliance Officers, Legal, Finance, Business Analysts and many other folks are required for that undertaking.

To solve this issue quickly I did the following:

1. Opened the Workflow History list. This can be accessed by visiting http://[domainname]/[sitename]/lists/Workflow%20History or another similar path depending on where you are pointing the workflow's history.
2. Created a new view called "Audit View" using the "All History" view as a basis. This will create a special URL http://[domainname]/[sitename]/lists/Workflow%20History/Audit%20View.aspx. During creation of the view I would recommend a few slight changes to make this easier to read for your auditors / compliance officers.
• Only display the Description, Outcome, Date Occurred, User ID, and Data columns.
• Sort by Date Occurred.
• Filter where List ID is equal to the GUID (for example {066c342b-0715-4583-8a20-9a1f725e5359} or another number) to the type of item you are auditing. This is particularly useful if you are grouping all your histories into one history list.
• Group by Primary Item ID and then by Workflow History Parent Instance.
3. Back on your actual document library create a new Calculated column called "Audit Link". The formula will be similar to the following (change URL as is appropriate).

   =CONCATENATE("http://[domainname]/[sitename]/lists/Workflow%20History/Audit%20View.aspx?FilterField1=Item&FilterValue1","=",ID)

4. You should now have a column that contains a URL to your Workflow History. Another annoyance I found is that apparently you can't put proper URLs in a calculated field. So users will need to copy / paste this into the browser. Or, I would recommend using a bit of JavaScript to handle the links for you.
5. The last step involves going back to your Workflow History list. I would recommend modifying the permissions on this list as you deem is appropriate. Microsoft is correct in stating that the workflow history "...is not necessarily secure because a user who has edit permissions on the site can update items in the history list, by default.". I would recommend Restricted Read or Read here.

That's it! This is really only a short term work around for this problem. As I outlined above a more proper solution is to discuss File Retention, Compliance, and Record Centers with the appropriate teams.