Alternative Way to Maintain Workflow Retention

Going to take a quick break from my BCS series to talk about a request I just completed for a client. I wanted to provide an alternative method for SharePoint workflow history retention.  If a person needs to see what happened during the course of a workflow for an item they simply need to go to the item and view the link to the workflow from there to see the entire history laid out for them. However, as we all know; SharePoint will purge any completed or terminated workflows from a site. So if a user is to try and view the data after 60 days, they will not be able to see the summary any more. For some this is a big deal as they need to track that data for years.

What some may not know is that SharePoint hasn’t actually removed the data from the system. It’s all still there in the workflow history and task lists. However, what SharePoint does is actually removes the references between the list item and the workflow history. This is done so the references that SharePoint has to scan don’t grow too large and cause performance issue.

So what’s a solution? One solution is to un-hide the workflow history and allow users to view the list directly. Not what I would suggest. Does this look very user friendly to you?

A user is not going to know how to view the corresponding list and item information from a bunch of GUIDs.

So what can we do?  There are numerous posts out there on turning off the job that performs the retention or moving the retention date from the default of 60 days out for years.  These are valid options, though if you can go along without doing this you are decreasing your chances of having performance issues down the road.

I suggest the following alternative.  Create a backup history list.  It retains all the information from the workflow history, but is user friendly so they can link quickly to the item and the list the workflow was initiated from.  The solution I built is a PowerShell script that allows you to input a data parameter to grab any history items from that day forward, and has a config file that contains the sites and the different history lists (in case you have custom) that you want to retain.

While doing my research I found that custom workflows do not store all the same data as an OOTB workflow does.  If you take a look at that screenshot above again, notice the items where the Workflow Association ID is NOT a GUID and it is missing data from other columns?  Those are custom WF entries.  What that means, is we do not have an easy way of getting the list or the list item to reference back for the user.  But fret not!  I have a solution!

If it is a custom WF, grab that Workflow History Parent Instance ID and move through the lists of your site.  At each site, check to see if the list contains a column whose name matches the parent instance ID.  If it does, get the internal (static) name of that column and check to see if Workflow History Parent Instance GUID is found for one of the items.  This is done quite easily with a SPQuery.

Here’s the code you need:

#loop through each list.
foreach($spList in $spWeb.Lists)
{
  #If the list has the WorkflowAssociation column in it, then get the internal name
  if($spList.Fields[$spWFHListItem["WorkflowAssociation"]])
  {
    $wfAssociationInternalName = $spList.Fields[$spWFHListItem["WorkflowAssociation"]].StaticName;

     #build a query to find the item based on the Workflow Instance ID and the Association name
     $spFindParentItemQuery = BuildFindItemByWFParent $spWFHListItem.WorkflowInstance $wfAssociationInternalName;
     $spParentItem = $spList.GetItems($spFindParentItemQuery);

     #if we found the item, exit the foreach
     if($spParentItem)
     {
       #get the parent list and exit the for
       $spParentList = $spParentItem.ParentList;
       break;
     }
   }
}

 

BuildFindItemWFParent simply returns a SPQuery that looks like this:

#############################################################################################
#Function Name: BuildFindItemByWFParent #
#Parameters: $WorkflowInstanceID - GUID of the WF that is running on that item #
# $WorkflowAssociationID - ID of the workflow (basically the name) #
# #
#Return Value: SPQuery object #
#Purpose: Creates an SPQuery object that will return all items based on a the WF being run #
# and that workflow's Instance ID #
#############################################################################################

function BuildFindItemByWFParent($WorkflowInstanceID, $WorkflowAssociationID)
{
  $spQuery = New-Object Microsoft.SharePoint.SPQuery;
  $spQuery.Query =
  "<Where>
    <Geq>
      <FieldRef Name='$WorkflowAssociationID' />
      <Value Type='URL'>$WorkflowInstanceID</Value>
    </Geq>
  </Where>"

  return $spQuery;
}

Once you have this information it’s a simple step to build out your data fields and list items.  One thing I would like to mention.  When building your link back to the parent list and parent item, I suggest you use a hyperlink column so it can contain the title and the link.  To update a Hyperlink field you have to include both the URL and the title in the string like this:

$itemURL = $spWeb.Url + "/" + $parentItemURL + ", " + $parentItemTitle;
$newHistoryItem["Link to Item"] = $itemURL;

When you are done with building your list you will end up with something like this:

Still pretty ugly, so create a view that cleans it up and is more user friendly:

That should have everything you need to implement this alternative method.  Adding OOTB WF history entries is easy since you have the GUIDs for the list and ID for the item.  I haven’t finished cleaning up the source code for this which is why it isn’t posted.  Let me know if you need it and I will get it to you.

The config file is pretty straight forward:

<?xml version="1.0" encoding="UTF-8"?>
<HelperModules>
<Module></Module>
</HelperModules>
<Sites>
<Site URL="" SiteName="" HistoryListSource="" HistoryListDest="" WorkflowColumns="" LogOutPutPath="" />
</Sites>
<WFHistoryColumns>
<WFHistoryColumn SourceColumnName="Event Type" DestColumnName="WF History Event Type" />
<WFHistoryColumn SourceColumnName="Workflow History Parent Instance" DestColumnName="WorkflowHistory Parent Instance" />
<WFHistoryColumn SourceColumnName="Workflow Association ID" DestColumnName="WorkflowAssociation ID" />
<WFHistoryColumn SourceColumnName="Workflow Template ID" DestColumnName="WorkflowTemplate ID" />
<WFHistoryColumn SourceColumnName="List ID" DestColumnName="ListID" />
<WFHistoryColumn SourceColumnName="Primary Item ID" DestColumnName="PrimaryItem ID" />
<WFHistoryColumn SourceColumnName="User ID" DestColumnName="UserID" />
<WFHistoryColumn SourceColumnName="Date Occurred" DestColumnName="DateOccurred" />
<WFHistoryColumn SourceColumnName="Group Type" DestColumnName="GroupType" />
<WFHistoryColumn SourceColumnName="Outcome" DestColumnName="WFHistory Outcome" />
<WFHistoryColumn SourceColumnName="Duration" DestColumnName="WFHistory Duration" />
<WFHistoryColumn SourceColumnName="Description" DestColumnName="WFHistory Description" />
<WFHistoryColumn SourceColumnName="Data" DestColumnName="WFHistory Data" />
</WFHistoryColumns>
<WFHistoryEventTypes>
<WFHistoryEventType EventNumber="0" EventText="None" />
<WFHistoryEventType EventNumber="1" EventText="WorkflowStarted" />
<WFHistoryEventType EventNumber="2" EventText="WorkflowCompleted" />
<WFHistoryEventType EventNumber="3" EventText="WorkflowCancelled" />
<WFHistoryEventType EventNumber="4" EventText="WorkflowDeleted" />
<WFHistoryEventType EventNumber="5" EventText="TaskCreated" />
<WFHistoryEventType EventNumber="6" EventText="TaskCompleted" />
<WFHistoryEventType EventNumber="7" EventText="TaskModified" />
<WFHistoryEventType EventNumber="8" EventText="TaskRolledBack" />
<WFHistoryEventType EventNumber="9" EventText="TaskDeleted" />
<WFHistoryEventType EventNumber="10" EventText="WorkflowError" />
<WFHistoryEventType EventNumber="11" EventText="WorkflowComment" />
</WFHistoryEventTypes>

As you can see it contains the list information, where the source history list is and where you want to put the history backup. It also contains the cross reference of WF History columns and the ones I created. It also has a cross reference of the events that occurred so the user knows what happened specifically in the workflow history entry.

Once you have the script completed, you just need to add it to the Task Scheduler on the server so it runs automatically.  If you don’t run it regularly (monthly) then the Workflow History Parent Instance GUID will be removed from the list by the retention clean-up and you won’t be able to gather all the information.

Thanks for reading!