locked
Missing Completed Workflows History

    Question

  • 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.

    Wednesday, August 22, 2007 8:34 PM

Answers

  • 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....

    Monday, October 15, 2007 5:11 PM

All replies

  • 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.
    Thursday, August 23, 2007 8:11 PM
  • 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.

    Friday, August 24, 2007 1:02 PM
  • 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....

    Monday, October 15, 2007 5:11 PM
  • 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?
    Monday, October 15, 2007 7:10 PM
  • 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....

    Monday, October 15, 2007 7:47 PM
  • Thanks!  That's exactly what I needed to know.
    Monday, October 15, 2007 7:50 PM
  • 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?
    Wednesday, January 02, 2008 10:15 PM
  • 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!

     

    Thursday, January 03, 2008 5:02 PM
  • 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
    Monday, January 07, 2008 4:56 PM
  • 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...

    Monday, January 07, 2008 6:54 PM
  • 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

     

     

    Tuesday, April 15, 2008 4:39 PM
  • Can you share your opensource AutoCleanupDays script? Thank you. Ceci.
    Thursday, April 17, 2008 2:52 PM
  • 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

    {

    class Program

    {

    static string siteName;

    static int newCleanupDays, assoCounter;

    static string libraryName, wfAssoName;

    static SPSite wfSite;

    static SPWeb wfWeb;

    static SPList wfList;

    static void 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 = new SPSite(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!");

    }

    }

    static void 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.

     

     

    Thursday, April 17, 2008 3:40 PM
  • 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

    Tuesday, April 22, 2008 8:11 PM
  • 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.

    Tuesday, April 22, 2008 8:37 PM
  • 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

     

     

    Tuesday, April 22, 2008 9:07 PM
  • 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:

    1. 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.
    2. 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.
    3. 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

    private const int NUMBER_OF_YEARS_BEFORE_AUTO_CLEANUP = 3;

    private const float NUMBER_OF_DAYS_PER_YEAR = 365.25f;

    private const int 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.

    ///

    private void 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>

    public void CreateWorkflowHistoryEntry(SPWorkflowHistoryEventType HistoryEventType, string Outcome, string Description, string OtherData)

    {

    SPWorkflow.CreateHistoryEvent(workflowProperties.Web, workflowProperties.WorkflowId, (int)HistoryEventType, this.workflowProperties.OriginatorUser, new TimeSpan(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).

    Tuesday, June 03, 2008 6:55 PM
  • 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

     

     

    Tuesday, June 03, 2008 7:59 PM
  • 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 -TypeName Microsoft.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'

     

     

    Tuesday, June 03, 2008 8:44 PM
  •  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.

    Tuesday, June 03, 2008 11:57 PM
  • 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

     

    Wednesday, June 04, 2008 1:29 AM
  •  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).

     

    Wednesday, June 04, 2008 12:48 PM
  • 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

     

    Wednesday, June 04, 2008 1:42 PM
  •  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
    Tuesday, August 19, 2008 8:41 PM
  •  
    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.
    Friday, October 31, 2008 6:50 PM
  • 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.

    Wednesday, February 18, 2009 5:25 PM
  • 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.

    Wednesday, February 25, 2009 3:32 PM
  • 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
    Tuesday, March 24, 2009 7:52 PM
  • 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.
    Wednesday, May 13, 2009 7:45 PM
  • 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
    SHQ - Knowledge Is Indeed Power. Share it and empower the world.
    Wednesday, May 13, 2009 8:39 PM
  • 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"?>

    <Feature  Id="<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.

    • Proposed as answer by sunil.kumar Wednesday, December 02, 2009 10:28 AM
    • Edited by sunil.kumar Thursday, December 03, 2009 11:20 AM
    Wednesday, December 02, 2009 10:26 AM
  • Thanks for posting this, Sunil.  I will try this as soon as I can and post some feedback.  Thanks again!

    Regards,
    Shola Salako
    SHQ - Knowledge Is Indeed Power. Share it and empower the world.
    Wednesday, December 02, 2009 2:38 PM
  • 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) ?
    Monday, December 07, 2009 2:09 PM
  • oguime, Have you tried the solution???

    The above feature will give you filtered workflow history list of an item. please try it......
    Tuesday, December 08, 2009 3:21 PM
  • Hi Sunil,

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

    Thanks
    Mehmood
    Tuesday, December 15, 2009 12:11 PM
  • 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
    ECSpand Services for your SharePoint - Find out more at www.ecspand.com
    Tuesday, December 29, 2009 12:43 PM
  • 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
    Wednesday, January 20, 2010 6:30 AM
  • 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
    Thursday, February 18, 2010 7:43 PM
  • Hi Yoyo,

    you can reset IIS using iisrest command.

    Thanks
    Tuesday, February 23, 2010 1:03 PM
  • 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....




    Wednesday, March 03, 2010 6:52 PM
  • 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.
    • Edited by MdenBroeder Monday, March 15, 2010 9:16 AM forgot one item
    • Proposed as answer by Naveen Kumar_G Friday, July 02, 2010 8:23 AM
    Monday, March 15, 2010 9:14 AM
  • Shola,

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

    Thank you,

    Jessica
    Wednesday, March 17, 2010 1:34 PM
  • 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. 
    Wednesday, April 14, 2010 4:01 AM
  • 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.

    Wednesday, April 21, 2010 3:18 PM
  • Workflow Auto Cleanup job runs separately for each Web Application. Pls refer to Central Admin -> Operations- > Timer Job Definitions

    Regards

    Tuesday, June 08, 2010 6:56 AM
  • 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.

    Thursday, October 14, 2010 2:31 PM
  • 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?

     


    Chris Niels Sieler MOSS Bear
    Wednesday, October 20, 2010 7:53 PM
  • Sunil,

    Thanks for the post. Unfortunately it does not work in the MOSS sites. Is their a quick edit for that? Works great in my WSS sites though.

     


    Greg McAllister
    Tuesday, December 07, 2010 5:25 PM
  • I'm locking this thread (started in 2007 with posts marked as answers) as it has become unmanageable with almost 50 posts.

    If you have similar problems with todays' versions of the pre-SP 2010 products, start a new thread.

     

    Moderator


    SP 2010 "FAQ" (mainly useful links): http://wssv4faq.mindsharp.com/default.aspx
    WSS3/MOSS FAQ (FAQ and Links) http://wssv3faq.mindsharp.com/default.aspx
    Both also have links to extensive book lists and to (free) on-line chapters
    Tuesday, September 20, 2011 4:59 AM