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:
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:
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("<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:{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.

River Traffic

River Traffic, originally uploaded by Thomas Gehrke.

Fishing the Ohio River from the foot of Broadway in Paducah, Kentucky. The river is still up from the Spring flooding of 2011.

This photo is a bit outside my comfort zone (not that I ever truly feel comfortable). I'm a little self conscious when I point my lens at people. I don't do portraits, but really respect those who do it well (e.g. Meshpoint).

Saint Louis In Miniature - Memorial Drive

Memorial Drive in Saint Louis as seen from the top of the Arch.

Unfortunately, as I go back and look at things I've done I realize just how much I have learned over time. And since I can't leave well enough alone, I have been going back to improve on things I've done in the past.

Personally, I like this one much better than the original.

If you are interested in a print, it's available on Imagekind.

I'm Such A Card: An and Review

A few weeks ago I got an email from, with an offer for free business cards. For those who don't know, is a site with one very simple goal; to give everyone a very quick and easy way to centralize all of those links to various sites and services that, in toto, paint a picture of who we are online. I set my page up just as soon as they started allowing us non-celebrity types play (but before they were bought by Aol).

Now why would I want such a thing when I have a personal blog, site, Facebook, Twitter, whatever? Simply because it is so very easy. Find a picture, write up a short description of yourself and set up the links to each of the services and sites where people can find out more about you.

It is, essentially, a very comprehensive business card. Which is, I suspect, why they have teamed up with to bring your digital business card back into the real world.

I went from thinking this was a neat idea, to asking myself why I would want or even need such a thing. But the price point won me over (actually $5.50 with shipping) and I pulled the trigger.

Honestly, I was expecting to get what I paid for and ended up getting more. Even taking all of the basic options and the bar at the bottom of the card (which can be removed for a price), these cards are nice. The stock is nice and heavy. The printing is well done. The colors are great (matte finish). The QR code that represents the URL to your site is the pièce de résistance.

My only real regret is that they are actually too nice to give to my small circle of friends and since I have few opportunities to hand them to new acquaintances, the fifty (50) cards I got will last forever.

Edward Jones Dome Miniature - Take 2

The Edward Jones Dome in St. Louis, Missouri as seen from the Arch.

This is a redo of what was, frankly, an embarrassing first cut.

That's not entirely true. There are aspects of the original that I like, but overall... let's just say I wanted a little more realistic take.

Script-Based SharePoint 2007/2010 Site-Collection Backup - Part 3: Notifications

This is the third and final article that describe a script-based method for creating backups at the site-collection level.
At this point, if you've gotten through parts 1 and 2, you have a fairly automated site-collection backup process in place. But you will probably want to keep an eye on things to make sure everything is running smoothly. The script also provides some useful (albeit very basic) information in terms of how resources are being utilized.

You will likely be dealing with several web applications as your SharePoint infrastructures grow. I don't manage what I consider to be a huge farm, but when you also factor in development, staging, testing, etc. farms, I was running around 20 backup jobs a night. That translated into around 20 emails in my inbox every morning. This was on top of all of the other system-generated mail that I get.

To combat the self-imposed spam I turned, once again, to SharePoint. I found that email enabled discussion lists seem ideally suited as a target for storing these notifications. Since the emails themselves are HTML encoded, the final result is not bad at all.

I then created a view that only displays topics added "today". Now anyone with access to the site, has the ability to monitor the status of jobs.

Some of you are probably thinking, "That's great, Tom. But now I have to go check the site every day. I'll never remember to do that." Quite right! But if you set up an alert for the list....

What I've done is to create an alert for myself that lets me know of everything new that has been added since the last time I was alerted. I schedule this to run around 8am every day. Now I get one email (well, two emails during our transition for SP2007 to SP2010) that shows everything. I chose 8am as the time for the alert because that is late enough to grab my attention when it comes in, but early enough to hit that window in my day where I am dealing with such things.

One nice thing about the script, IMO, is that if it hits any errors they are rendered in the message body in a big, in-your-face yellow box. While the email might be quite long now, most of the time I can do a quick scan for yellow and immediately delete the email if all I see is nice, normal blandness.

If you take a more leisurely approach, you can also keep up with how large each site collection is getting, how many users have provisioned "My Sites", how long the jobs are taking (which, if you see a jump in times, can be an indicator of other problems) and so on.

So now you have a large number of emails going to a SharePoint list on a daily basis. It won't take long for things to get out of hand. Depending on your needs, cleaning this out periodically would not be a bad idea. Once again, SharePoint to the rescue. By creating a retention policy, I have set each discussion topic to expire 30 days after it has been added.


As I said in the first part of this series, this script-based method of backups is no substitute for a more complete backup solution that requires an investment in software/hardware. But it is definitely better than doing nothing. If you are on a tight budget, you could do worse than using this series as a starting point. Feel free to modify and improve!

If you find this to be useful or if you have suggestions for improving upon any aspect, please drop me a comment or give me a shout on Twitter at @tomgehrke.