Creating Permanent SharePoint Designer Workflow Audit Trails

Overview: Workflow Audit Trails

Just about any important workflow solution requires an audit trail. We want to know who approved a particular document and when. We want to know when a task was assigned to someone and when they completed it. We leave debugging style messages in our workflow so that we can go back, after the fact, and understand who did what and when they did it so that we can improve the process. There are many good reasons to leave behind audit information.

SharePoint Designer makes this easy with the “log to history” action. This little action allows us to save just about any message we want to the workflow history.

There is a terrible flaw in this, however. Out of the box, workflow history is not permanent.

SharePoint runs a process every day that looks for and purges data from all workflow history lists. This process removes any item which is more than 60 days old! This can be a terrible shock, particularly when you’ve built a business solution that requires a long term audit trail, such as approvals on financial transactions or security requests for systems access. Even if you don’t have a strong requirement, legal or otherwise for an audit trail, it’s still nice to have that audit trail.

Why does SharePoint do this? There’s a good technical reason and it relates to the famous “2000 item limit.” Microsoft recommends that a given list or document library should normally only show 2000 items at a time. After that, SharePoint’s overall performance can degrade and hurt the end user experience. At the same time, SharePoint workflow history is actually a list, just like any other list. It’s a hidden list, so you can’t see from “view all content,” but it’s a list. As a result, it needs to conform to the same 2,000 item guideline. Microsoft solves this by purging the data from the list by arbitrarily deleting older entries.

So, Microsoft solves the technical problem for us, but we’re level with a business problem. How do we solve this? There are at least five approaches.

Solution One: Live With It
The most obvious solution is to simply live with it. This is a no-cost option and if you don’t need a permanent audit trail, you’re better off allowing SharePoint to purge it anyway. Sixty days is a pretty good default window.

Solution Two: Re-configure the SharePoint Job
Like so many options in SharePoint, the 60 day window is itself configurable. If 60 days is too short, increase it to 90 days or 365 days or whatever is required. We do this using stsadm. This technet article describes this in a little more detail: http://technet.microsoft.com/en-us/library/cc424956.aspx
There is a problem with this approach. If you disable this process altogether, or set the window to something very long (10 years, for example), your workflow history list will grow and grow and … grow. And this causes problems by itself. If it grows into 1000’s and 10’s of thousands of entries (or even larger), performance will probably degrade. It could even become uselessly slow. I don’t have personal experience with it, but keep this problem in mind.

Solution Three: Use a Custom List
Last week, I wrote about using a custom list to create flexible workflow solutions. Instead of hard coding data in a list (such an approver’s email address), instead pull those values out of the workflow and save them in a custom list. At run-time, the workflow looks up the value in that custom list. That way, if someone changes jobs, all you need to do is update the custom list. You don’t need to use SharePoint designer for that purpose.
If we can a custom list to extract data, we can also use a custom list to save an audit history. Instead of using “log to history” we can use “create list item” actions to create our own audit trail with our own audit information. This gives us full control over when and how this audit information is purged (if ever).
We still have a 2,000 item guideline problem. But now we have the power. With that power comes responsibility and all that, but at least it’s in our hands.
I plan to write up a fully functioning example of this in the next week or two.

Solution Four: Embed audit history in the data
This is a bit of a special case. I had the opporuntity to work with a client that used an InfoPath form in conjunction with a workflow process to manage on-boarding new hires. The InfoPath form used an “administrator view” to capture and provide a single view of audit data. As the workflow progressed through its steps, it would record the who/when bits and save them directly onto the form.
The advantage here is that the form becomes both a place to enter some data, but also its own audit trail. The audit trail survives as long as the form itself survives.
A similar approach could be taken with a custom list or document library, although you need to get a little clever if there are complex auditing requirements.
The downside to this is that the data could be “adjusted” by someone with the right privileges and that can defeat the purpose of an audit trail in the first place. Normally, we want our audit trails to be write-once and read-forever. This is more difficult to accomplish when the form itself stores the audit data.

Solution Five: Use Built-in SharePoint Auditing
Lastly, use SharePoint’s built it auditing capability.
SharePoint provides a rich auditing capability. See here for all the gory details: http://technet.microsoft.com/en-us/library/cc424956.aspx
There’s a lot of material in there, but the key takeaways are:

1. MOSS provides a better all-around solution over WSS.
2. It’s not a particularly end user friendly solution, especially on the WSS side.
3. It’s permanent.
4. It’s transportable. If you upgrade hardware or carry out a wide variety of backend administrative tasks, your audit history will travel with you.
5. It’s extensible by way of programming. It’s not extensible in an end user friendly way, but if you partner up with your developer colleagues, you have a lot of good options.

Conclusion
One way of looking at SharePoint Designer workflow and auditing is that, frankly, it sets us up to fail. Many people don’t realize that potentially precious audit messages are purged after 60 days. SharePoint Designer doesn’t warn us that this is going to happen. SharePoint itself doesn’t give us any clue. There ought to be warning messages all over the place because it’s perfectly natural to assume that workflow history is going to be permanent.

In this case, SharePoint gives us auditing with one hand, takes it away with another, but thankfully, gives it back to us with one or more of the solutions described in this article.

If you’ve been blithely assuming that your workflow audits are safe, they are not. If you need those audit trails, plan on designing a solution sooner rather than later, because the clock is ticking.
Postscript

You can see my MindJet Mindmap for this article here: Auditing Workflows.pdf


REF: http://www.endusersharepoint.com/2009/01/27/creating-permanent-sharepoint-designer-workflow-audit-trails/