How NOT To: Auditing via the workflow history

I regularly get asked if the workflow history list and task list in SharePoint and Office 365 can be used for auditing purposes. The quick answer from my point of view is “No, definitely not”.

No? Why is that?

There is a timer job  in SharePoint  which permanently deletes every workflow instance and related task entries that are older than 60 days from the time a workflow is completed or cancelled. This means that the entry point for the workflow history is removed, the entries in the workflow history persist though. Users can still access the history list via this link: The list is a hidden list and you can access it through this link http://[servername]/[sitename]/lists/Workflow History

On-premise, the 60 day retention can be disabled or changed to a different time frame. However with changing those settings you will likely soon run into performance problems. This is due to the lists growing in size over time. Whether or not you decide to change or turn off the workflow retention period, it is considered best practice to create separate workflow history lists and task lists for each workflow.

In Office 365 on the other hand, there is no way to change the timer service as you need to be part of the Farm Administrators group. Obviously that is only Microsoft people 🙂 The timer job here gets executed at a regular interval as defined in the Office 365 directory.

So, you can capture and retain all sorts of events and data. But why is it not a good enough mechanism to use that data for auditing purposes? It is just not secure. Any user with edit permissions on the site can go into the list and temper with the data. And making everything secure enough will cost you a lot of time and money.

Your Options

  1. SharePoint Auditing: SharePoint has a built in auditing capability. The good news is, the data collected survives migrations, upgrades and is permanent. On the other hand, the reports are not very end user friendly and turning on auditing in SharePoint (on-prem) can decrease performance by up to 15%. So be careful when going for this option. more information on SharePoint auditing can be found here.
  2. List Data: You can leverage additional fields in SharePoint lists to store audit information as part of a list item or document. While this is great for an end user viewing the trail, it entails the same security concerns as using a SharePoint history list.
  3. External Repositories: I’ve used this approach on multiple occasions with clients in highly regulated industries (mining for example). As part of the workflow, any information that is required, was written into a database. That gives you a few advantages:
    1. No need to mess with the timer job. History and task lists stay lean and clean. All required information is hosted in a database.
    2. Reports can be pulled using Reporting Service, or other reporting tools without users having edit access to the actual data
    3. It functions as a secure store that no one can touch. Well, apart from the admin
    4. You have one single source of truth and regular database backups ensure that your auditing information is available all the time

Conclusion

Auditing for workflows in SharePoint is a tricky thing and not many people are aware of the 60 day retention period. I hope this post helps you understand workflow auditing and options available to you. Having said that, I am keen to understand how other people are approaching this topic. Either way, it is important to keep this in mind when designing your solutions and making them safe for any auditing purpose.

 

 

Tags: , ,

5 Responses to “How NOT To: Auditing via the workflow history”

  1. Grace October 17, 2016 at 2:03 PM #

    How would you go about executing Option 2?

    • Patrick Hosch October 18, 2016 at 6:59 AM #

      Let’s assume you have a list with fields Title, Product and Product Price. If you want to track approver comments and approver name, then you simply add those as list columns. Once the workflow finished the approval and whatever else it needs to do, you can update the approver comments and names columns to keep track of it. SImple but not very secure. Depends on your requirements at the end of the day.

  2. Karl March 21, 2017 at 1:51 PM #

    How would you go about executing Option 3? 🙂

    • Patrick Hosch March 31, 2017 at 12:00 PM #

      Hi Karl, what we did as part of some projects was, set up a custom table in SQL and write the audit information into that table using the Execute SQL action. Alternatively with the new DocGen action, you could create a PDF with all the required audit information and archive that.

Trackbacks/Pingbacks

  1. Newsletter – Episode 58 – Belarus SharePoint Community Blog - April 5, 2016

    […] How NOT To: Auditing via the workflow history […]

Leave a Reply