My SharePoint Workflow History Is History!

The other day I had a customer get in touch with me who is using a SharePoint document library with a basic, out-of-the-box approval workflow. The problem that they were having was that the approval status column was not displaying anything where previously it had displayed "Approved" with a handy link to the Workflow Status page. A page that shows a reasonably formatted Workflow History that they were using as proof for auditing purposes.

I'm not going to get into the discussion regarding whether or not this is appropriate or whether SharePoint auditing is the right answer. If you are interesting in a snapshot of that discussion, it has been pretty well covered here.

The reality is that we make these tools available to our customers and they use them (if we're lucky). I needed to figure out what was happening and what our options for resolving things for them were.

The issue stems from the "Workflow Auto Cleanup job" that is scheduled to run for each SharePoint application (one job per app). Here is a description from Microsoft describing what this job is all about:
"By default, Microsoft Office SharePoint Server 2007 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 cancelled. Workflow history items themselves are not deleted, but the entry point to view them on the status page for a particular instance will no longer be available. You can disable the Workflow Auto Cleanup job if you want to keep workflow data available for a longer period. However, as with any SharePoint list, as the workflow history and task lists grow in size, site performance may be compromised."
So it's a feature. From what I can tell, nothing has changed in SharePoint 2010.

Finding out the "why" did not take too long to figure out and report back to the customer. Figuring out how to fix things is a little more complicated.

The first thing I did was to disable the Workflow Auto Cleanup job for the application associated with the target site-collection. This is easily accomplished through the Central Administration site (Operations, Timer job definitions and then find the Workflow Auto Cleanup job for the appropriate application).

Much of what follows I found here. However, the writer of the article does not go into any detail on what values need to change or how he determined what those values should be. He does offer screenshots of many of these steps where I have not. I don't care if you use my article, his or both. Whatever works best for you and gets you over the hump that brought you here in the first place is what really counts.

OK then.

Since the workflow history is not deleted, it must exist somewhere. It turns out that there is a hidden list called "Workflow History" that is created by default when you set up workflows initially. Typically you can find this list by appending "/Lists/Workflow%20History" to the URL for your site. For instance:
http://sharepoint.company.com/sites/TargetSite/Lists/Workflow%20History/
It's very raw and it probably contains every event for every item in every list in the site that has an associated workflow. We need this to be in a much more manageable form so we will create a new view with the following settings:

  • View Name: Audit View
  • Display Columns:
    • Date Occurred
    • User ID
    • Event Type
    • Outcome
    • Description
  • Sort by Date Occurred in Ascending order
  • Filter the List ID based on the GUID of the Document Library you are interested in displaying the history for. I will tell you how to get this shortly.
  • Group by the following columns showing the groups as expanded by default:
    • Primary Item ID
    • Workflow History Parent Instance

Now you need the List ID for the library with the workflow attached. The way I found it may be a bit silly, but since I was in a hurry and it worked, I didn't dig too deeply. Please, if there is a better way let me know.

What I did is go to the Settings page for the target list and pulled the ID from the URL. So it should look a little something like this:
http://sharepoint.company.com/sites/TargetSite/_layouts/listedit.aspx?List=%7B3FA113C7%2D4EAB%2D4377%2DAB95%2DD82149FEB884%7D
The ID is everything after the "List=" part of the URL. Now this is URL encoded and it needs to be decoded:

  • %7B = {
  • %7D = }
  • %2D = -

Thus the List ID for me is {3FA113C7-4EAB-4377-AB95-D82149FEB884} and that is the value that I would enter in the List ID Filter for the view we created.

Things should be looking fairly nice now. But there is still a problem. We have groupings that are related to workflows, but nothing really ties it back to the item that spawned it.

Hopefully you still have the settings page for the list still open from where we were looking for the List ID. If not, pull it up because we are going to add a column to it.

In the columns section, click the link to "Create column". This is how you want to set it up:

  • Column Name: Audit History Link
  • Column Type: Calculated
  • Formula:
    =CONCATENATE("http://sharepoint.company.com/sites/TargetSite/Lists/Workflow%20History/Audit%20View.aspx?View=<ID of the view>&FilterField1=Item&FilterValue1=",ID)

So how do you get the View ID? Again, I have another goofy method, but it's easy and it works.

Go to the Workflow History list and make sure you are using the "Audit View" that we created earlier. Now set a filter on any column. You will see that the URL changed. In fact, if you compare what you see in the URL to what we have entered as the first part of the string we are concatenating in the formula above, you can tell where that came from. It should look a little something like this:
http://sharepoint.company.com/sites/TargetSite/Lists/Workflow%20History/Audit%20View.aspx?View={3E822E38-4A21-4662-ACF3-119A2A76329C}&FilterField1=User&FilterValue1=John%20Smith
The value you want is the GUID that shows up between the "View=" and "&FilterValue1". It needs to be URL encoded when you enter it into the formula for the calculated column. Use the same strings as we did to decode previously. Thus it becomes %7B3E822E38%2D4A21%2D4662%2DACF3%2D119A2A76329C%7D.

Finally, if all has gone well, your Document Library (or other kind of list) should display a column with a long and admittedly ugly URL. Clicking that link should take you to the target Workflow History for that item and that item only.

Granted, it's nowhere near as nice in presentation as the Workflow Status page, but if you just need a way to go back and show what happened, it's a low-cost way of doing so.