Weekly Newsletter Signup

• Upcoming
  Live Online Workshops

  • Excel and SharePoint: I didn’t know you could do that!
  • Easy Tabs Interface - Simple SharePoint Navigation for Complex Information
  • Train the Trainer: Materials and Resources for in-house SharePoint Training
  • Become Your Company’s SharePoint SuperStar!
  • Hack SharePoint Web Part Displays with XSLT
  • Business Solution: Health Care Case Management
  • Killer Calendars in SharePoint
  • The Basics of Content Types
  • Business Solution: Create a Compliance Report Intake System in SharePoint
  • Business Solution: Manage Input with Dynamic Feedback Forms
  • Live Blogging at SharePoint Conference 2009

• Ongoing Series

  • Article Archives (1000+)
    All Authors
  • This Week in SharePoint with Joel
    Joel Oleson
  • jQuery for Everyone
    Paul Grenier
  • The Calculated Column
    Dessie Lunsford
  • The Data View Web Part
    Laura Rogers
  • Stump the Panel: Q&A
  • Subscribe to the Weekly Newsletter

• Authors

  • Dessie Lunsford
  • Laura Rogers
  • Lee Reed
  • Mark Miller
  • Jim Bob Howard
  • Paul Galvin
  • Paul Grenier
  • Toni Frankola
  • Woody Windischman

• SharePoint Resources

  • Bits of SharePoint
  • Codeless Solutions for SharePoint
  • Get the Point
  • Innovation Matters
  • Path to SharePoint
  • Paul Galvin
  • SharePoint 911
  • SharePoint Blank
  • SharePoint Blogs at SharePointU
  • SharePoint for Non-Developers
  • SharePoint Forums at SharePointU
  • SharePoint Screencasts
  • SharePoint Use Cases
  • Social Media Talk Tweets
  • The Sanity Point
  • The Weekly Newsletter  

SharePoint Designer workflow doesn’t give us a lot of visibility into what’s happening with our workflow solutions.  And, the visibility that we do get is hampered by a relatively poor interface and 60 day time window.  This 60 day window can be a major disappointment to new SharePoint Designer users because it’s not advertised by the tool itself.  It’s not at all uncommon for someone to fire up SharePoint Designer, create a workflow solution that leverages the “Log To History List” action as shown in Figure 1:

Figure 1: Log To History List action

The problem is that after 60 days, any messages that you create this way are deleted from the workflow history list!  After a bit of teeth gnashing and “what were they thinking?” arguments, the bottom line is this: it happens and it needs to happen.  The question is, how can we get around it?

The official answer is to rely upon SharePoint’s built-in auditing feature.  From an end user’s point of view, however, that’s very weak in WSS and not much better in MOSS.  Fortunately, we can still leverage the familiar SharePoint Designer tool to create a durable workflow history and audit trail which is an order of magnitude more useful to boot.  Here’s how.

Start by creating a custom list and adding some columns to hold onto the auditing information that you’ll want to keep.  My list is named “Process Log” and has columns that look like this:

Figure 2: List columns

I’ve added three custom columns to the list:

• Workflow Name: This process log may be used by multiple workflows.  I want to be able to filter and sort by workflow.
• Process ID: In our production system, we’ll have many workflows running simultaneously.  This column helps us to differentiate multiple audit messages and glue them together in views.
• Audit Message: This is the actual audit message.  I won’t be demonstrating it in this article, but understand that you can embed HTML into this field.  This is a useful trick if you want your audit message to be a link back to a task or a document in a library in the SharePoint site.

With that in hand, we just need a workflow to create audit messages.  To this end, I’ve defined another custom list called “Time Off Request” to support a time off request style business application.  The Time Off Request list allows employees to request time off.  I won’t try and create an actual time off workflow solution in this article, but I will use that list to demonstrate how to create the audit message.  To do so, we’ll use the “Create List Item” SharePoint action as shown:

Figure 3: Create List Item

This action allows us to create a new entry in a list and populate the list’s columns with whatever date we want.  The dialog box is fairly straight forward and this is what mine looks like:

Figure 4: Creating a List Item and Assigning columns values

The column, Process ID, is a little tricky.  I assigned Process ID equal to the ID of the current item as shown:

Figure 5: Assigning Process ID

Now that we’ve created the infrastructure and the implemented some logging on a real workflow, we can view the result.  Here’s that Process Log list with a view that breaks the audit messages out by Process ID, showing the name of the workflow, the audit message and when it was created:

Figure 6: Viewing the Audit

We can tell from the Figure 6 that Time Off Approval process workflow ran twice for Process ID number 1 and only ran once for process ID 2.  We can still get time stamps and naturally, we can create any kind of view that we want.

We can build on this core solution to make a much more useful and powerful auditing solution.  Consider these ideas:

• Define a more business friendly Process ID.  In this article, I selected the “Current Item:ID” because it was the most easy way to come up with an ID.  Consider using the title of the current item plus the ID or some other business key value.  Pick something you’ll want to use for sorting and grouping in views.
• Glue multiple “physical” SharePoint Designer workflow solutions into one logical whole.  Many times, we create multiple SharePoint Designer workflow solutions who work together to support a single business process.  For instance, it’s fairly common to add a workflow to a task list in order to populate it with some additional information of value to the people that have to work on that task.  The technique described in this article can allow you to create a single consolidated audit trail showing the actual sequence of events of these workflow operations in one single view.  Just use the same Process ID in both SharePoint Designer workflows and they will group and sort together in views for that consolidated view of what happened.
• Add additional columns.  You have full control over what you audit.  If you want, add more columns to capture additional audit information specific to a given workflow.

This approach does have one significant drawback: the audit list will grow forever.  This is obviously a bad thing and is precisely why the standard SharePoint history list is purged every 60 days.  Microsoft didn’t want users to inadvertently create workflow history lists that grow so large they become unwieldy and possibly a major maintenance headache.  You still need to solve that problem.  There are several solutions to this problem.  For end users, the easiest solution will be to manually clear out the audit history using a data view.  (If you want to keep the audit information ‘forever’ then export it to a spreadsheet first).

In summary, this article describes how to implement your own auditing solution.  You have complete control over what you audit, how long it sticks around and how you want to report on it (via views).  Determine which information you want to keep in your audit trail and define those columns.  Use SharePoint Designer’s Create List Item function to populate your audit information and then create as many views as you want on your audit list.  Remember that this list will never be cleared out so you need to devise a strategy to handle that end of things.

The end result: flexible auditing that speaks directly to your business requirements.

Happy auditing!

Paul Galvin, Microsoft MVP – SharePoint
Web site: Paul Galvin’s SharePoint Space

Paul is a Solutions Architect currently working most closely with Microsoft Office SharePoint Server 2007. He was recently awarded Microsoft MVP – SharePoint status for his work with the SharePoint community.

---

Notify me of comments to this article:
E-Mail:

---

Comments