Description

Our company discovered during an internal audit that workflow history had been removed from several documents within our main corporate document libraries. A CAR was generated requiring that IT find a way to view the existing workflow history of a document in order to meet the requirements for both internal and external audits.

Problem

Workflow history has been removed from documents in two main libraries. This information is very important for routine audits of our corporate document repository.

By default, SharePoint runs a timer job that purges workflow associations from the documents and tasks. This means that when you look at the workflow history for an item you will not see workflow history older than 60 days. The information about the workflow is still in the tasks and workflow history lists, but the association between the workflow and the item has been purged from the database

Solution

This shortcoming was brought to our attention in early May 2008. We discovered that disabling the job prevented the autocleanup for any documents from March till present, but we still needed a way to view the workflow history of the document workflows prior to disabling the timer job.

Disable the Workflow Auto Cleanup Timer Job

Go to SharePoint Central Administration and stop the ‘Workflow Auto Cleanup’ timer job. You do this in the Central Administration > Operations > Timer Job Definitions > Workflow Auto Cleanup" - this is the simplest option, which switches off the clean-up totally.

This option was disabled only on the portal.myinfinera.com application. The timer job is still active on all other applications such as team.myinfineral.com and personal.myinfinera.com.

Having the job disabled for all sites and workflows on portal.myinfinera.com is short term fix. In the long term, performance may potentially be impacted without the job running from time to time. We may want to consider setting the autocleanup job to run once a year as long as we are still able to view the workflow history.

 

Link a Document to Workflow History

Even though the Workflow Auto Cleanup removes the associations from a document, the workflow history is still retained in a separate list. This is a hidden list called “Workflow History.” All information regarding the workflows from a given document will be visible, but not in an easy to read format.

The best solution for linking documents to their respective workflow history was to be able to all a link to the properties of the document that would pull in all of the workflow history from the hidden list.

The following steps were taken to add a new column to each document’s properties.

View existing workflow history in each library

The following links retain all of the workflow history for each document library. These are the lists that we worked with to create the association with workflow and the document in both libraries.

· Docs - http://sharepointserver/Docs/Lists/Workflow%20History

 

Create a new view called “Audit View”

· Choose the columns as shown in Figure 1

Figure 1

• Sort by Date Occurred
• Filter by the List ID as shown in Figure 2
• For Docs Library, use {c87274a2-13f1-4c64-93fb-b6eba5c6c0e7}

Figure 2

· Group the listings by Primary Item ID and then by Workflow History Parent Instance as shown in Figure 3.

Figure 3

Discover the Filtered DocID URL

In the Workflow History, many columns will be displayed with GUIDs. Since the Audit View is already grouped by Primary Item ID and Workflow History Parent Instance, we needed to filter the listing by the Primary Item ID to find all workflows associated with a single document. After filtering the Primary Item ID, we came up with the following format:

Docs

1. http://sharepointserver/Docs/Lists/Workflow%20History/auditview.aspx?View=%7B426D5F02-A5D0-4E8E-AC13-4388C179D8ED%7D&FilterField1=Item&FilterValue1=128

Adding the column to the document properties

In both the Docs library, we wanted to make it easy for auditors to discover the workflow history for that document in the event that the WorkflowAutoCleanup had removed it.

We started by creating a new column in the library. Under Settings> Document Library Settings, we selected Create column. This column is a Calculated column which is based on the Workflow History filtered Primary Item ID, which is also included in the DocID. Since the DocID is combined with the Class, we needed a way to strip out the 4-digit ClassCode and append only the DocID to the filtered URL.

Calculated Formula as Single line of text

Docs

• =CONCATENATE("http://sharepointserver/Docs/Lists/Workflow%20History/auditview.aspx?View=%7B426D5F02-A5D0-4E8E-AC13-4388C179D8ED%7D&FilterField1=Item&FilterValue1=",RIGHT(DocID,LEN(DocID)-SEARCH("-",DocID,1)))

The links are reflected in the Views, which are based on the GUIDs of the Audit Views in each list. Because this new column was added, it will automatically add it to the default views in the Docs library. We opted to remove this column from the list view.

An auditor now has the ability to view the properties of the document as shown in Figure 4

Figure 4

The properties will display the new link to the Workflow History filtered by the DocID as shown in Figure 5, or by clicking the Workflows link in the toolbar.

Figure 5

Clicking the Audit History Link then takes the user to the history page, displaying all items from each workflow. If multiple workflows were created for a document, they will all be shown, grouped by each Workflow History Parent Instance as shown in Figure 6.

Figure 6

Links to Workflow History Discussions

• Huge MOSS Workflow Issue... What is Microsoft Thinking!!!!
• SharePoint Workflow History Cleanup jobs ...
• SPWorkflowAssociation.AutoCleanupDays
• MSDN Forums: Missing Completed Workflows History
• Job-workflow-autoclean: Stsadm property (Office SharePoint Server)

 

Microsoft Support Case

Microsoft Support Case Discussion
As consequence, there are two workaround approaches:

1. Increase the days in the Field ‘AutoCleanUpDays’ on a per workflow basis by any
   tools or scripts (using the GUI or SharePoint API. No direct SQL changes)
2. Try to prevent running the stored procedure ‘proc_AutoCleanupWorkflows’

Drawbacks for these work around solutions are:

Work around 1, change days in field ‘AutoCleanUpDays’

• No UI support, direct changes to database without UI are not recommended and not
  supported and therefore on your own risk
• Need to write custom code and event handlers to have managed database changes,
• see also http://msdn2.microsoft.com/en-us/library/microsoft.sharepoint.workflow.spworkflowtemplate.autocleanupdays.aspx

Work around 2, prevent stored procedure ‘proc_AutoCleanupWorkflows’

• You need to make sure, that the stored procedure never runs again, even when the
  system is restarted
• Performance may suffer after a certain time because of a large number of
  records

For work around 2, prevent stored procedure ‘proc_AutoCleanupWorkflows’ you can use
either:

1. The UI and stop the ‘Workflow Auto Cleanup’ timer job. You do this in the
   Central Administration > Operations > Timer Job Definitions > Workflow Auto
   Cleanup" - this is the simplest option, which switches off the clean-up totally. Of
   course this way all the workflows will stop being cleaned up and the performance
   may suffer finally.
2. Use stdadm.exe program: C:\Program Files\Common Files\Microsoft Shared\web
   server extensions\12\BIN>stsadm -o setproperty -propertyname job-workflow-autoclean
   -propertyvalue "yearly at Jan 1 15:00:00" -url <Http://server> - this gives you a
   better control over the Auto Cleanup job, since you can set how often it should be
   run (not only on/off as in the previous option). But still it is global - it will
   run for all the workflows

---

Posted 08-27-2008 1:01 PM by drewdevo