Home » SharepointRSS

Missing Completed Workflows History

Hello all:

I cannot find any workflow histories on approved documents that were approved 2 months or more ago.  The document status says its approved but there are no histories showing under "Completed Workflows."  Is there some kind of retention policy in MOSS 2007?  This is causing a major auditing issue for us.  Where can I retrieve this history information from?  Are they gone for good?

Thanks in advance!

Shola.

 

47 Answers Found

 

Answer 1

Check the history  list manually.

Unless you specify otherwise (during workflow  association?), the workflow will use a hidden list "Workflow History".  Using .Net code running on one of your servers, get a handle to the list "Workflow History", set Hidden = false on the list, and update() the list to save the change.  Once the Workflow History list is visible, you can view it on the web like http://server.domain.tld/lists/Workflow%20History/AllItems.aspx.  You can then browse through the history by date to find  the workflow you're interested in.  I think you can then filter the list on the Workflow History Parent Instance (at a glance, this column looks like it holds a unique id for each workflow instance).  You could also just use the handle in .Net to query the list with code, but it's nice to be able to browse through the list if workflows  are run infrequently.
 

Answer 2

Thank you so much for your response!  What a relief to know that the workflow history  data still exists somewhere in the system!  I was able to view all the workflow  log/task histories at http://server/sites/<site_name>/lists/Workflow%20History/AllItems.aspx.  However, I didn't have to set the list's Hidden property to false....it was available when I accessed it.  Thanks again for sharing this knowledge.

However, do you know if there is a "setting" or a "flag" in MOSS that I need to turn on or off, so that these workflow histories show up for every document  that has gone through a workflow in the system?  Can Hidden be set to false for all existing documents?

Here is the scenario: an auditor would browse the list of documents  in the document library, click on the drop-down on the document name, and then select "Workflows."  The workflow screen should show all the workflows  that were completed  on the document under the "Completed Workflows" section at the bottom of the page.  This is not happening for all documents - it does not show these histories for workflows that were completed over 2 months  ago.

Here's my theory: since these histories are not deleted, I think there is some kind  of filter on the view of the workflows screen of the document properties that shows workflow histories only on recent workflow completions (< 2 months).  If this is true, how can this "filter" be disabled?

Thanks again,

Shola.

 

Answer 3

Developed a solution to this issue and thought I should share it on this forum - for the benefit of others.

The workflow  data is purged from the MOSS Workflow database table after 60 days.  Based on calls to Microsoft about this, it was by design and for performance reasons.  However, what good it the workflow functionality if proof of the workflows  are deleted?  How can companies meet various industry auditing requirements for SOX, ISO, TS, etc?  Microsoft gave us only one option: changing the 60 days to something higher by writing a script modifying the SPWorkflowTemplate.AutoCleanupDays property of the workflow associations.  But this does not bring the deleted data back?  I wrote a comprehensive script for this...let me know if you need it.

For anyone seriously considering using the MOSS workflows, I would advise you disable the "Workflow Auto Cleanup" timer service in Central Admin (central --> Operations -->Timer Job Definitions) to prevent any further deletions of your workflow data.  A lot of agonizing work went into researching this problem because it was not documented by Microsoft.  Not even the MOSS database schema.  I had to reverse engineer the database and study the stored procedures to uncover the truth about what happened to my data.

This research led to more awareness on this matter.  See Dave Wollerman blog on this at: http://www.sharepointblogs.com/llowevad/archive/2007/09/21/huge-workflow-issue-what-is-microsoft-thinking.aspx.

The workflow data was eventually restored from backup tapes - 6 months  worth.  We restored data to the Workflow table and our workflow history  data now appear under the workflow properties of each document.

I hope this helps....

 

Answer 4

Could you provide more details about the workflow  cleanup?  Please name the tables in the content-database which are affected and which stored procedures are used to do the cleanup.  Does this cleanup affect the "Workflow History" list or just the "Workflow" and "Workflow Association" tables in the content-database?
 

Answer 5

Sure!  The database tables concerned are Workflow and WorkflowAssociation; however, the workflow  table stores the actual workflow data and WorkflowAssociation is sort of like a "parent" to the related workflows  and contains the number of days to keep the workflows (AutoCleanUpDays column).  The stored procedures are proc_AutoCleanupWorkflows,proc_AutoDropWorkflows and proc_DropWorkflow; however, proc_AutoCleanupWorkflows initiates the purge process (checking the number of days in workflowassociation).

No, the cleanup effect does not affect the "Workflow History" list - only the database tables.  The Workflow History list is sort of like an "events log" of workflow activities.  It records the activities of the workflow process as it happens.  It is a good "backup" though; however, it is not user-friendly for my users.

Again, beware of the "Workflow Auto Cleanup" timer or increase the AutoCleanUpDays value.  I hope this answers your questions....

 

Answer 6

Thanks!  That's exactly what I needed to know.
 

Answer 7

Wow I cant beleive this! We use the workflows  extensively and will definatly be getting audited by ISO, CMMI, etc. This is not cool, does the script you have restore the workflow  histories back to be associated with the individual docs instead of having to view the workflow histories list?
 

Answer 8

Jrice:

It isn't cool at all.  My script does not restore the workflow  histories.  It only provides a way to increase the number of days to keep the workflows  in the SQL database e.g from the default 60 days to say 365 days (depending on your industry audit policy).  To view the workflow histories at the individual document  level (doc properties), you will have to restore the completed  workflows from database backups.  The system workflow history  list does not get deleted; however, it is not user-friendly.

Goodluck!

 

Answer 9

Ok, i understand im going to need to restore the completed  workflows from backups. Im kind  of fuzzy on what exactly to restore... I see workflow  and workflow association tables, do I go inside of them and restore something? If I restore the entire table, wont that overwrite workflows  that are going on right now? What exactly did you do to restore your completed workflows? I image Ill have to restore about 6 months  worth.

Thanks
 

Answer 10

I understand your confusion.  It took me a while to figure out what to restore too.  I had to restore about 3 months  of data.  Here is what I did:

Backup your production database before you perform any of these steps!!!
Disable the automatic purge process in MOSS Central Admin (Workflow Auto Cleanup timer)
I restored the backed up databases in monthly increments on a test server (3 databases in my case).
Now, every workflow  must have a workflow association. The Id column keeps each row unique.  Same applies to the workflowassociation table: Id keeps each row unique.
For each database, extract the workflowassociation and workflow data into a new table, starting from the latest database.  You can use the following SQL query below to keep the data unique.  You are pretty much inserting workflowassociation data from the restored database into another table in your test database:

Retrieve WorkflowAssociation data:
INSERT INTO testDB.dbo.Temp_WorkflowAssociation_table
SELECT     Id, BaseId, ParentId, Name, Description, StatusFieldName, SiteId, WebId, ListId, ContentTypeId, InstanceCount, InstanceCountDirty, TaskListId, HistoryListId, TaskListTitle, HistoryListTitle, Configuration, AutoCleanupDays, Author, Created, Modified, InstantiationParams, PermissionsManual, Version
FROM         month3_restore.dbo.WorkflowAssociation
WHERE     (Id NOT IN
                          (SELECT     Id
                            FROM testDB.dbo.Temp_WorkflowAssociation_table))

Do the same for the workflow tables:

Retrieve Workflow data:
INSERT INTO testDB.dbo.Temp_Workflow_table
SELECT     Id, TemplateId, ListId, SiteId, WebId, TaskListId, AdminTaskListId, ItemId, ItemGUID, Author, Modified, Created, InternalState, LockMachineId, 
LockMachinePID, InstanceDataVersion, InstanceDataSize, InstanceData, Modifications, HistorySize, History, StatusVersion, Status1, Status2, Status3, Status4, Status5, Status6, Status7, Status8, Status9, Status10, TextStatus1, TextStatus2, TextStatus3, TextStatus4, TextStatus5
FROM         month3_restore.dbo.Workflow
WHERE     (Id NOT IN
                          (SELECT     Id
                            FROM testDB.dbo.Temp_Workflow_table))
Then inject the temp workflow and workflowassociation data into your production database.  Remember to inject the workflowassociation data first (because the workflow data depends on the workflowassociation):
WorkflowAssociation injection:
INSERT INTO MOSS_prod.dbo.WorkflowAssociation
SELECT     Id, BaseId, ParentId, Name, Description, StatusFieldName, SiteId, WebId, ListId, ContentTypeId, InstanceCount, InstanceCountDirty, TaskListId, HistoryListId, TaskListTitle, HistoryListTitle, Configuration, AutoCleanupDays, Author, Created, Modified, InstantiationParams, PermissionsManual, Version
FROM         testDB.dbo.Temp_WorkflowAssociation_table
WHERE     (Id NOT IN
                          (SELECT     Id
                            FROM          MOSS_prod.dbo.WorkflowAssociation))

Workflow injection:
INSERT INTO MOSS_prod.dbo.Workflow
SELECT     Id, TemplateId, ListId, SiteId, WebId, TaskListId, AdminTaskListId, ItemId, ItemGUID, Author, Modified, Created, InternalState, LockMachineId,
LockMachinePID, InstanceDataVersion, InstanceDataSize, InstanceData, Modifications, HistorySize, History, StatusVersion, Status1, Status2, Status3,
Status4, Status5, Status6, Status7, Status8, Status9, Status10, TextStatus1, TextStatus2, TextStatus3, TextStatus4, TextStatus5
FROM         testDB.dbo.Temp_Workflow_table
WHERE     (Id NOT IN
                          (SELECT     Id
                            FROM          MOSS_prod.dbo.Workflow))

Check the workflow properties of your documents  in your document  libraries to verify if the histories reappear.

I hope this helps...

 

Answer 11

Hi Shola,

We're experiencing the same issue in Sharepoint.  Can you email me your comprehensive script to modify the AutoCleanupDays property to a higher number of days?

Please email to knguyen10@hotmail.com

Thanks,

Kevin

 

Answer 12

Can you share your opensource AutoCleanupDays script? Thank you. Ceci.
 

Answer 13

Kevin and Ceci:

Sorry for the delay on this.  I know how urgent these things can be....I have been there :-)

Here is the script.  Compile in VS and run on MOSS box with account that has MOSS admins rights (MOSS service account, maybe).

Code Snippet

/*

* Date: September 17, 2007

*

* Program Description:

* ====================

* This program is a workaround for Microsoft Office SharePoint Server 2007

* bug #19849, where the AutoCleanupDays is set to 60 by default and by design

* in MOSS installations. This program gives the customer the oppotunity to

* change this number.

workflow  histories would not show after 60 days by default.

*/

using System;

using System.Collections.Generic;

using System.Text;

using Microsoft.SharePoint;

using Microsoft.SharePoint.Workflow;

namespace ShowWFs

{

classProgram

{

staticstring siteName;

staticint newCleanupDays, assoCounter;

staticstring libraryName, wfAssoName;

staticSPSite wfSite;

staticSPWeb wfWeb;

staticSPList wfList;

staticvoid Main(string[] args)

{

try

{

switch (args.Length)

{

case 0: //no parameters entered by user

{

System.Console.WriteLine("Error: No arguments entered (site, library, workflow and days)");

showHelpUsage();

break;

}

case 4: //correct number of parameters

{

siteName = args[0];

libraryName = args[1];

wfAssoName = args[2];

newCleanupDays = Convert.ToInt32(args[3]);

assoCounter = 0;

wfSite = newSPSite(siteName);

wfWeb = wfSite.OpenWeb();

wfList = wfWeb.Lists[libraryName];

SPWorkflowAssociation _wfAssociation = null;

foreach (SPWorkflowAssociation a in wfList.WorkflowAssociations)

{

if (a.Name == wfAssoName)

{

a.AutoCleanupDays = newCleanupDays;

_wfAssociation = a;

assoCounter++;

}

else

{

_wfAssociation = a;

}

}

wfList.UpdateWorkflowAssociation(_wfAssociation);

System.Console.WriteLine("\n" + wfAssoName + ": " + assoCounter.ToString() + " workflow association(s) changed successfuly!\n");

break;

}

default: //default number of parameters

{

System.Console.WriteLine("Incorrect number of arguments entered (" + args.Length.ToString() + " arguments)");

showHelpUsage();

break;

}

}

}

catch (Exception e)

{

System.Console.WriteLine("An error has occurred. Details:\n" + e.ToString());

}

finally

{

if (wfSite != null)

wfSite.Dispose();

if (wfWeb != null)

wfWeb.Dispose();

System.Console.WriteLine("\nFinished setting AutoCleanupDays!");

}

}

staticvoid showHelpUsage() //help screen

{

System.Console.WriteLine("\n\nMOSS Workflow Set AutoCleanup Usage:");

System.Console.WriteLine("====================================");

System.Console.WriteLine("ShowWFs siteURL library workflow days");

System.Console.WriteLine(" - siteURL (e.g. http://serverURL/site)");

System.Console.WriteLine(" - library (e.g. \"Shared Documents\")");

System.Console.WriteLine(" - workflow (e.g. \"Approval\")");

System.Console.WriteLine(" - days for auto clean up (e.g. 120)");

}

}

}

I hope this helps...

Shola.

 

Answer 14

Wow.  I can't even begin to tell you what a BAD idea this is. 

If you run this script on your SP farm your entire installation will become unsupported.  If you call Microsoft for support and they determine that you have been in mucking about with the SP database tables they will not help you. 

If it's been said once on these forums (and elsewhere), it's been said a thousand times: Stay out of the database.  Making any modifications to the SP databases is not supported and is, as I said, a really bad idea.  Even reading directly from the tables is not recommended.

The problem that you are trying to solve (workflow history  trimming) is a non-problem if you plan your architecture properly.  The History List is not and was never intended to be a permanent audit trail.  There are other mechanisms in SP (auditing, records center) that are and should be used if you need that functionality. 

The Dave Wollerman blog posting that someone linked to earlier in this thread is nothing more than an incendiary, headline-grabbing attempt to create a problem where none exists. 

I apologize for taking a hard line approach on this but the solution presented above potentially causes more problems and probably doesn't fix any audit problems as auditers generally don't take too kindly to finding out that their audit trails have been manipulated. 

If you are in this situation where your History List has already been trimmed and you need to get it back, I would strongly recommend that you look at a solution that does not involve writing anything back into the SP database AND that you change your workflows  to use a proper auditing/logging mechanism.

Dave

 

Answer 15

It is a bad idea to run the SQL query code posted on 07 Jan 2008 because changing the database in any way will make the SharePoint installation unsupported.  Running the .NET script posted on 17 Apr 2008 is perfectly fine because supported changes should be made through the API.  Reading just the first three sentences in David's post may lead one to think the 17 April script is the unsupported script due to the placement of the post.  At least, that's my understanding of the situation.

 

Answer 16

Yes, apologies - the C# code is OK to run, the SQL script is not.  Thanks for clarifying that as I was not clear.  I tried to reply directly to the SQL post but the forum still stuck my reply at the end.

The C# code, however, still indicates that you are using the History List as an audit trail and that is still not a good idea.

;-)

-Dave

 

Answer 17

For those who want to insure that their workflows  automatically check and (if necessary) adjust the AutoCleanupDays value the following code snippets are donated to the public domain.   To use the code:

Put a CodeActivity into your workflow, preferably somewhere at the top.  Altenatively, if you have a wokflow intialization method, just call the code from there.  Your choice.
Paste the code below into your workflow.  I developed this in Visual Studio 2008, but there is nothing "magic" that should prevent it from working in Visual Studio 2005.
Adjust the constants at the top.   And before anyone asks, yes, I considered putting in a workflow  initialization InfoPath form to capture the desired number of days before auto cleanup information, but hey, this is sample code and I have to leave something for the reader to do, don't I?

Code Snippet

#region Constants

privateconstint NUMBER_OF_YEARS_BEFORE_AUTO_CLEANUP = 3;

privateconstfloat NUMBER_OF_DAYS_PER_YEAR = 365.25f;

privateconstint WORKFLOW_AUTO_CLEANUP_DAYS = (int)(NUMBER_OF_DAYS_PER_YEAR * NUMBER_OF_YEARS_BEFORE_AUTO_CLEANUP);

#endregion

Since workflows get a SPWorkflowActivationProperties variable named workflowProperties "free of charge" when the workflow starts, we leverage that as follows (assume we have a CodeActivty with a method named AdjustWorkflowCleanupDays):

Code Snippet

#region AdjustWorkflowCleanupDays

///

/// Checks the number of days before auto cleanup will begin on the

/// workflow and changes it if necessary.

///

privatevoid AdjustWorkflowCleanupDays(object sender, EventArgs e)

{

string message = string.Empty;

string outcome = string.Empty;

// Get the workflow association for this workflow

SPList list = workflowProperties.List;

SPWorkflowAssociation wfa = list.WorkflowAssociations[workflowProperties.Workflow.AssociationId];

// if not already set to the desired value, change it and save the updated workflow association.

if (wfa.AutoCleanupDays == WORKFLOW_AUTO_CLEANUP_DAYS)

{

message = string.Format("Workflow template {0} AutoCleanUpDays is already set to {1}", wfa.Name, wfa.AutoCleanupDays);

outcome = "AutoCleanupDays Unchanged";

}

else

{

int oldAutoCleanupDays = wfa.AutoCleanupDays;

wfa.AutoCleanupDays = WORKFLOW_AUTO_CLEANUP_DAYS;

list.UpdateWorkflowAssociation(wfa);

message = string.Format("Workflow template {0} AutoCleanUpDays has been changed from {1} to {2}", wfa.Name, oldAutoCleanupDays, wfa.AutoCleanupDays);

outcome = "AutoCleanupDays Changed";

}

CreateWorkflowHistoryEntry(SPWorkflowHistoryEventType.WorkflowComment, outcome, message, string.Empty);

}

#endregion

The one utility routine for creating the workflow history  comments used in the above code snippet is actually in my workflow utility library, but here it is as a standalone to help make sure the above code will compile cleanly:

Code Snippet

#region CreateWorkflowHistoryEntry

///<summary>

/// Creates an entry in the workflow's History Log

///</summary>

///<param name="HistoryEventType"></param>

///<param name="Outcome"></param>

///<param name="Description"></param>

///<param name="OtherData"></param>

publicvoid CreateWorkflowHistoryEntry(SPWorkflowHistoryEventType HistoryEventType, string Outcome, string Description, string OtherData)

{

SPWorkflow.CreateHistoryEvent(workflowProperties.Web, workflowProperties.WorkflowId, (int)HistoryEventType, this.workflowProperties.OriginatorUser, newTimeSpan(1)

, Outcome, Description, OtherData);

}

#endregion

Until Microsoft provides a supported, out of the box list archiving capability for SharePoint, I have to politely disagree with David Mann.  This is a serious SOX-compliance issue and there is no straight-forward way to guarantee SOX compliance in SharePoint without a lot of custom work.

PeopleSoft wouldn't dare pull a stunt like the one Microsoft is pulling when it comes to audit compliance and SharePoint workflow history; otherwise, they wouldn't be able to sell into major companies like the last one I worked at (which had both PeopleSoft HRMS and PeopleSoft Finance).

 

Answer 18

Fred:

You don't have to politely disagree, you can be impolite...

;-)

This code is fine.  I don't disagree with the business problem, I disagree with the implementation.  The History List, even with this code in place, is not supposed to be an audit trail for SOX compliance or anything else.  At worst, this was badly mis-marketed, and I am likely as guilty as anyone else outside of Microsoft for that.

Mea culpa.

However, understand that by leaving History entries in the list using the default LogToHistoryList activity as this code will do has the potential to cause other problems as you will fairly quickly blow the magic-2000 items limit out of the water. 

Your comment about requiring "a lot of custom work" is untrue.  For not much more code than you have here, I have written a replacement activity for the default LogToHistoryList activity that will avoid the AutoCleanUpDays problem without causing the problems associated with >2000 items in a list.  One of these days I'll get around to cleaning the code up and making it available.

Again, to clarify as I was unclear before, this code is fine and will not make your environment unsupported.  It will fix the problem of the AutoCleanUpDays, but it will (in an environment of even moderate usage) cause other problems to manifest.  A better approach is to architect a properly designed and implemented solution that writes to an alternate History list and makes use of folders to avoid the >2000 items in a container problem.

Dave

 

Answer 19

For those of you who are into PowerShell, here's a routine that will set the AutoCleanupDays for every workflow  association found on a given SPList to a desired value (sorry about the way copy/paste completely loses all the indentation):

Code Snippet

# SPAdjustAutoCleanupDays.ps1

# Author: Fred Morrison, Senior Software Engineer, Exostar, LLC

#

# Purpose: Adjust SharePoint Workflow Association AutoCleanupDays value, where necessary

# on all workflow associations for a specified List.

#

# Parameters:

# siteName - The SharePoint Site to look at

# listName - The SharePoint List to look at

# newCleanupDays - The number of days to set the workflow association AutoCleanupDays value to, if not already set.

#

# Example call: SPAdjustAutoCleanupDays http://workflow2/FredsWfTestSite FredsNewTestList 180

#

# following makes it easier to work with SharePoint and also means you have to run this script on the SharePoint server

[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") | Out-Null

# capture command line arguments

$siteName=$args[0] # ex: http://workflow2/FredsWfTestSite/

$listName=$args[1] # ex: FredsNewTestList

[int] $newCleanupDays= [System.Convert]::ToInt32($args[2]) # ex: 1096

Write-Host$siteName

Write-Host$listName

Write-Host$newCleanupDays

# get a reference to the SPSite object

$wfSite=New-Object-TypeNameMicrosoft.SharePoint.SPSite$siteName

[Microsoft.SharePoint.SPWeb] $wfWeb=$wfSite.OpenWeb()

Write-Host$wfWeb.ToString()

# get a reference to the SharePoint list we wish to examine

[Microsoft.SharePoint.SPList] $wfList=$wfWeb.Lists[$listName];

Write-Host$wfList.Title

[Microsoft.SharePoint.Workflow.SPWorkflowAssociation] $wfAssociation=$null

[Microsoft.SharePoint.Workflow.SPWorkflowAssociation] $a=$null

[int] $assoCounter= 0

[string] $message=''

# Look at every workflow association on the SPList and make sure the AutoCleanupDays value is correctly set to the desired value

for( $i=0; $i-lt$wfList.WorkflowAssociations.Count; $i++)

{

$a=$wfList.WorkflowAssociations[$i]

[string] $assocName=$a.Name

Write-Host$a.Name

if ( $a.AutoCleanupDays-ne$newCleanupDays )

{

$oldValue=$a.AutoCleanupDays

$a.AutoCleanupDays=$newCleanupDays

# save the changes

$wfList.UpdateWorkflowAssociation($a)

$message="Workflow association $assocName AutoCleanupDays was changed from $oldValue to $newCleanupDays"

}

else

{

$message="Workflow association $assocName AutoCleanupDays is already set to $newCleanupDays - no change needed"

}

Write-Host$message

}

Write-Host'Done'

 

Answer 20

David Mann [MVP] wrote:

However, understand that by leaving History entries in the list using the default LogToHistoryList activity as this code will do has the potential to cause other problems as you will fairly quickly blow the magic-2000 items limit out of the water. 

Your comment about requiring "a lot of custom work" is untrue.  For not much more code than you have here, I have written a replacement activity for the default LogToHistoryList activity that will avoid the AutoCleanUpDays problem without causing the problems associated with >2000 items in a list.  One of these days I'll get around to cleaning the code up and making it available.

Again, to clarify as I was unclear before, this code is fine and will not make your environment unsupported.  It will fix the problem of the AutoCleanUpDays, but it will (in an environment of even moderate usage) cause other problems to manifest.  A better approach is to architect a properly designed and implemented solution that writes to an alternate History list and makes use of folders to avoid the >2000 items in a container problem.



Your comments about 2000 items don't sound right to me.  Re-read this earlier post:

Shola Salako wrote:

Sure!  The database tables concerned are Workflow and WorkflowAssociation; however, the workflow  table stores the actual workflow data and WorkflowAssociation is sort of like a "parent" to the related workflows  and contains the number of days to keep the workflows (AutoCleanUpDays column).  The stored procedures are proc_AutoCleanupWorkflows,proc_AutoDropWorkflows and proc_DropWorkflow; however, proc_AutoCleanupWorkflows initiates the purge process (checking the number of days in workflowassociation).

No, the cleanup effect does not affect the "Workflow History" list - only the database tables.  The Workflow History list is sort of like an "events log" of workflow activities.  It records the activities of the workflow process as it happens.  It is a good "backup" though; however, it is not user-friendly for my users.

Again, beware of the "Workflow Auto Cleanup" timer or increase the AutoCleanUpDays value.  I hope this answers your questions....



Workflow auto cleanup does absolutely nothing to the actual Workflow History list (like https://mysite.mydomain.com/lists/Workflow History).  It removes some data from the database that isn't stored in a regular SharePoint list, but in its own table in the database.  The content of the Workflow History list is maintained indefinitely regardless of whether autocleanup is set for 60 days or 60 months, so having an autocleanup of 60 days for your workflows does not save you from having a SharePoint list with a million items.  Autocleanup alleviates a different bottleneck than the 2000-items-in-a-list issue.  Please correct me if I am wrong.

Sorry if this double-posts.  I got an error the first time, not sure if it went through.

 

Answer 21

Nope, you are correct.  Apparently, I am doomed to never get anything right on this thread...

The problem (for me) is that I've never used the default history  List in a production workflow.  I've always done my logging elsewhere so that I can have more control over it and actually use it as an audit history so I've never had to actually deal with the AutoCleanUpDays myself.

So, to attempt once again to get this right...the code is fine, it won't cause any problems, it won't open you up to other problems, it won't cause you grow hair on your knuckles.  Assuming there isn't some other funky thing I'm not aware of because I don't use it, you still do have the 2000 item problem any time you use the default History list.

Thanks for catching this and correcting me...

Dave

 

Answer 22

David Mann [MVP] wrote:

Your comment about requiring "a lot of custom work" is untrue.  For not much more code than you have here, I have written a replacement activity for the default LogToHistoryList activity that will avoid the AutoCleanUpDays problem without causing the problems associated with >2000 items in a list.  One of these days I'll get around to cleaning the code up and making it available.

Dave: The mere fact that you had to write a "replacement activity" proves my point that SharePoint, out of the box, is not ready for prime time when it comes to actively supporting regulatory compliance rules such as those embodied in Sarbanes-Oxley (SOX).

Compared to PeopleSoft HRMS and PeopleSoft Finance, SharePoint has a long, LONG way to go before it can be trusted to work (again, out of the box) with all the compliance rules and regulations that most businesses in the USA are stuck with as a result of the nanny-state government mentality that pervades the thought process of federal, state and local government officials.I know readers of this post located outside the USA have their own set of regulatory headaches to deal with, so don't feel slighted by my emphasis on SOX-compliance and SharePoint.

Bottom line: SharePoint users should NOT have to cobble together their own home-brew SOX-compliance solutions.It should come delivered with the Enterprise version of MOSS (sorry WSS-only folks, you have to pay for stuff like that, which means MOSS).

 

Answer 23

Fred,

I think this is devolving into a pissing match...

However, I'll toss a little more fuel into the fire. 

You're comparing apples and oranges.  SharePoint is neither PS HRMS nor PS Finance.  While I have never worked with either of those packages, I am slightly familiar with what they do and they are different beasts from SharePoint.  If I need to spend a few hours to write a simple component to do what I need or spend many millions more bringing in a whole other application, I'll spend a couple hours, thanks.  Should I "have to"?  Who cares?  I don't have the luxury of living in that ideal world.

Again, I'm speaking somewhat from a position of ignorance here, but to the best of my knowledge, PS is not generally installed and then run in production exactly out-of-the-box.  There is always some customization.  Does that make PS a cobbled together, home-brew solution?  No.  It's a tool used to get you 80% to your goal and you customize the last 20%.

To the best of my knowledge, MS has never claimed that SP is SOX-compliant.  Can you make it SOX (or whatever else)-compliant?  Yes.  Will you need to do some work?  Yes.  Will it be cheaper and easier than PS or some other monolithic ERP/HRMS/Finance app?  Almost certainly yes.

SharePoint is not the answer to all problems and honestly one of the biggest problems it faces is that people try to make it that answer.  It isn't, folks.  It's a tool.  Use it for the right job and you'll be fine.  Use it for the wrong job and you deserve whatever headaches you get. 

Does SP have warts?  Yes, and when the time, place and audience are appropriate, I point them out.  What I won't do is get all bent out of shape because it doesn't work exactly the way I want right now.  If that's the case, I have and will continue to recommended to clients that SP may not be the right tool for them to meet their particular need.

Your mileage may vary...

Dave

PS: MS has a page that lists partners and solutions that can make SP SOX compliant: http://www.microsoft.com/office/showcase/2007/sox/partnersol.mspx

 

Answer 24

Maybe a little late on this one but I was just reading the workflow  chapter in the new Best Practices for Sharepoint 2007 by Ben Curry and he covered a bit on this.  From an audit standpoint, the workflow history  is just a doc lib and there can be edit/updated/deleted which is not good for audit standpoint.   The book suggested using the records management piece of Sharepoint to store audit logs of workflow (or anything else) if you want to guarantee it is accurate can not be altered.

Just some FYI
 

Answer 25

David Mann [MVP] said:

Wow.  I can't even begin to tell you what a BAD idea this is. 

If you run this script on your SP farm your entire installation will become unsupported.  If you call Microsoft for support and they determine that you have been in mucking about with the SP database tables they will not help you. 

If it's been said once on these forums (and elsewhere), it's been said a thousand times: Stay out of the database.  Making any modifications to the SP databases is not supported and is, as I said, a really bad idea.  Even reading directly from the tables is not recommended.

The problem that you are trying to solve (workflow history  trimming) is a non-problem if you plan your architecture properly.  The History List is not and was never intended to be a permanent audit trail.  There are other mechanisms in SP (auditing, records center) that are and should be used if you need that functionality. 

The Dave Wollerman blog posting that someone linked to earlier in this thread is nothing more than an incendiary, headline-grabbing attempt to create a problem where none exists. 

I apologize for taking a hard line approach on this but the solution presented above potentially causes more problems and probably doesn't fix any audit problems as auditers generally don't take too kindly to finding out that their audit trails have been manipulated. 

If you are in this situation where your History List has already been trimmed and you need to get it back, I would strongly recommend that you look at a solution that does not involve writing anything back into the SP database AND that you change your workflows  to use a proper auditing/logging mechanism.

Dave





I have been away for a while...been busy on quite some challenging SharePoint assignments.  I see a lot of activity on this thread and I would like to thank everyone for their comments, suggestions and criticism.

In response to Dave Mann's earliest post on this thread, there are no good ideas or bad ideas in certain situations.  Rather,  there are solutions/workarounds or failure.  In this particular instance,  this client already had a small MOSS implementation and my assignment was consolidating all document  management farms (MOSS, SPS, SP 2001, Lotus Notes, file shares) into one new MOSS farm.

This existing MOSS solution was audited on a regular basis by an industry certifying party and then they ran into this severe problem (missing histories) that could jeoparize the existence of their business.  I had two options: (1) blame Microsoft and wait several months  for a Microsoft fix (this does not recover lost data) while the business risks loosing its certification OR (2) find  a solution for the immediate business problem.  It is important that I point out that the pressing issue here is recovering the missing  data (preventing future loss is secondary).

Yes, touching the MOSS database directly is unsupported by Microsoft; however, how else can selective data be restored into MOSS without compromising the integrity of the entire implementation?  Are there 3rd party tools available for this?  This MOSS problem was one of the first of its kind.  What would you do in this situation if I may ask?

I agree that the initial implementation was incorrectly planned, a Records Center would have been more appropriate.  That is why requirements gathering and planning are so important.  Again, there are no good or bad ideas in certain situations.  There is simply success or failure.

Best regards,
Shola.



SHQ - Knowledge Is Indeed Power. Share it and empower the world.
 

Answer 26

Hi,
I have an interesting situation at hand. Not sure if its because of the workflow  data purge in SQL Server. Does this happen after the purge operation?

Situation: 
1) A user submitted a document  for approval using SharePoint's OOB Approval workflow. 
2) The approval process went smoothly without any glitches till all participants completed  their tasks.
3) However, all of a sudden, the "Approval" column that shows the outcome of workflow (like "In Progress", "Approved" etc) went blank for all documents! The column is just empty!
4) And the task list item for that specific workflow just disappeared.

Newer workflows  created using SharePoint OOB Approval and Approval Parallel workflows are running normally now.
Could you guys help me solve this mystery?

Thank You.

 

Answer 27

Hi Shola,

Your code was most helpful. However, if there are 2 workflows  attached and the one to update is the first one in the enumeration, nothing will happen. I think you should remove the else-part (no need to remember an association if no changes were made). For fail-safe purpose, you would execute the update only if the counter = 1.

 

Answer 28

Shola, reg your post on 8 Jan 2008 with sql script for restoring wf - it doesn't show the way you restored tasks for workflows... or you didn't ?
Thanks
 

Answer 29

Shola,

This happened to me as well.  If you could send me the script you created to bring back the deleted data I would be eternally greatful.

Jeanna Johnson
jjohnson@aeronix.com
Aeronix, Inc.
 

Answer 30

DenisGO, I apologize for the slow response.  I restored only the workflows  and their associate histories for each document/item.  I didn't restore the completed  workflow tasks -- can't remember is the tasks were even affected.  I will look into this soon.

Jeanna, the C# script was pasted earlier in this thread.  The script only gives you the option to change the number of days that the histories would be available in your database; it does not restore the data.  The SQL queries on the other hand (also available on an earlier post on this thread) restores your data from available backups.  However, please use it with great care and caution.  It requires some SQL expertise.  Make sure you backup your databases first.

You could also disable the workflow  Auto Cleanup timer service for your web application (Central Admin --> Operations --> Timer Job Definitions).

I am surprised MS has not done something about this issue already.

Please let me know if you need anything else.

Best Regards,
Shola Salako
 

Answer 31

SharePoint runs a timer job that purges workflow  associations from the documents  and tasks. This job only removes the association not the data from the Workflow history  list. So you will not get the workflow history of an item which is older than 60 days or more.  To resolve this issue we have added a menu in the ECBItemToolbar using feature. Following steps are used to resolve this issue.

1.Create a view (AuditView) in the Workflow History List. Following column should be displayed in the view.

a.Date Occurred

b.Event Type

c.User ID

d.Description

e.Outcome

2.Create an Element.xml file using the following code.

<?xml version="1.0" encoding="utf-8" ?>

<Elements xmlns="http://schemas.microsoft.com/sharepoint/">

<CustomAction

Id="UserInterfaceCustomActions.ECBItemToolbar"

RegistrationId="101" <!-- list ID of your list -->

RegistrationType="List"

Location="EditControlBlock"

Sequence="111"

Title="Show WF History"

Description="Show WF History">

<UrlAction Url="~site/Lists/Workflow%20History/AuditView.aspx?FilterField1=Item&amp;FilterValue1={ItemId}&amp;FilterField2=List&amp;FilterValue2={ListId}"/>

</CustomAction>

</Elements>

3.Create Feature.xml file using the following code.

<?xml version="1.0" encoding="utf-8"?>

<FeatureId="<Create your own Feature ID>"

Title="Workflow Audit Report"

Description="<Feature Description>"

Version="1.0.0.0"

Hidden="FALSE"

Scope="Web"

DefaultResourceFile="core"

xmlns="http://schemas.microsoft.com/sharepoint/">

<ElementManifests>

<ElementManifest Location="Elements.xml"/>

</ElementManifests>

</Feature>

Deploy your feature using the stsadm command. After deployment of the feature a new link (Show WF History) will be added on the Items dropdown menu. Click on this link to display the Workflow History of that item.

 

Answer 32

Thanks for posting this, Sunil.  I will try this as soon as I can and post some feedback.  Thanks again!

Regards,
Shola Salako
 

Answer 33

I'm interested in this as well, but I guess it will only show each task Event Type. Is it possible to preserve the workflow  status (canceled, approved, rejected, in progress) ?
 

Answer 34

oguime, Have you tried the solution???

The above feature will give you filtered workflow history  list of an item. please try it......
 

Answer 35

Hi Sunil,

This solution worked for me!
Thanks a lot for your post.

Thanks
 

Answer 36

The scripts for adjusting the AutoCleanUpDay and the workarounds with the context menu entry all sound fine and reasonable. But since I should make sure not to overload the workflow history  list with log entries entries, I need to delete them at a certain point in time (same applies for the workflow  tasks). So even if I have only, say 10 workflows  running a day (which is little I belive), I might come up with a lot more log entries and tasks (depending on the workflow structure). Let's assume 100 log entries in the history list and 100 tasks a day. How do I make sure that the completed  tasks and the log entries are deleted once the workflow has been completed? Otherwise I might overload both lists, what I do not want.

I know that I can code some custom Timer job or something similar, but shouldn't there be a simple and easy to use out of the box solution?

Any ideas?

Thanks for helping.

-Alex
 

Answer 37

Thanks a lot Sunil. This solution works for a library having single workflow  running. In case there are more than one workflow running on this library; it will display status  from all workflows.

Regards,

- Deepak
 

Answer 38

Hello Sunni,

Do I just create two files a folder with name I want to use under C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\FEATURES\ and create two files above in this new folder and register it using stsadm and that's it, or do you have additional instruction for me?

Thanks
 

Answer 39

Hi Yoyo,

you can reset IIS using iisrest command.

Thanks
 

Answer 40

So lets talk about a REAL solution...please repond if you have a good idea.  My best guess is that I can add a link to my document  comments (which I created with a publishing append feature so I cannot lose comments).  So after looking at a workflow  status link I see the following:

http://mysharepointsite/_layouts/WrkStat.aspx?List={ListId}&WorkflowInstanceID={workflowinstanceid}

There are two properties in a workflow that may be related to this link, can anyone confirm?

workflowProperties.HistoryListID = List

workflowID = workflowinstanceID

If I build my own URL and plop it into my comments field.....woudl this create a hard coded link to my workflow history?  My dev server is offline so I cannot confirm....




 

Answer 41

HI Sunil,

I have one small question,

Do I create a new folder (with any given name)  in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\FEATURES, or do I replace the current feature.xml file in C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\12\TEMPLATE\FEATURES\WorkflowHistoryList  with the custom feature.xml file  ??

And do I also have to register the element.xml thru the stsadm  ?  (And if I need to replace a current element(s).xml file, what is the folder path? )

Thanks for your answer.

Br,
M.
 

Answer 42

Shola,

We are interested in the script that you wrote.  Please sent the script to sob07@hotmail.com

Thank you,

Jessica
 

Answer 43

Just read all threads,  haven't try yet,  but it seems  definition beat C# beat SQL statement in SharePoint area.  thanks for Shola, David, Sunil and everyone in this post giving me a very valuable instruction to what I am gona to work at.   I will get back how I solve the problem in my project.   thanks again. 
 

Answer 44

Am I correct in thinking that the Workflow Auto Cleanup job runs against ALL workflow history  lists, rather than just the default list in each site?

Reason I am asking is when you setup a workflow, there is the option to either use the Workflow History list or create a new history list.  If the job only runs against the default site history list, then using a different list for each workflow  should mean the workflow is retained.

 

Answer 45

Workflow Auto Cleanup job runs separately for each Web Application. Pls refer to Central Admin -> Operations- > Timer Job Definitions

Regards

 

Answer 46

Shola, I tried these SQL queries, modified with our variables, of course, on our test environment.

There were some records copied, but the workflow history  did not appear. Are there any other suggestions from your side, please?

Ravie.

 

Answer 47

This is a very Interesting Thread.  My Customer does not look the Autoclean Feature.  I have disabled it in Central Admin.  I have used stsadm -o getproperty -pn job-workflow-autoclean -url "http://test "  with my urls in it to check the settings.  Every Web Application = <PropertExist = "Yes" Value "off">.   But to my chagrin it is still cleaning out the tables.  We restore the tasks from the Recycle bin so we have a workaround to keep the customer happy.  I have set up auditing and that shows that the User deleting the tasks is me.  The problem with Jobs is that there seems to be Jobs and then there is SharePoint Jobs.  I have checked the SSP Jobs with stsadm -o enumssptimerjobs -title <SSP Name>.  I have checked services.msc But there is no BFO ( Blinding Flash of the Obvious) that shows me the timer job or job that is deleting the tasks and sending them to the recycle bin.  I have also tried using the SC ( Server Controller) command line but i can't seem to figure out what services or job using my credentials that is cleaning up the tasks ( Workflow table).  Thanks to this excellent thread i have found the Work flow history table, but it is not being auto cleaned.   I would love it if this list would autoclean!!!!!!. 

So any ideas where I should look to see if the job-workflow auto clean parameters are set for that one table?

Can I set the Workflow Auto clean to focus on specfic lists?

And I am still running MOSS 2007

Did I post in the Wrong area?

 

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter