Sitecore Workflow History — SQL

How do I retrieve the list of items which had their workflow state updated within a specified period?

Solution

Naturally I would resort to Powershell to generate such a query, but at times as we know, we do not know the internal fields or where to start, i.e. iterate from all items? (which will be extremely inefficient)

I thought to myself to explore the database further, and BINGO, there is the ‘Workflow History’ table.

SQL Table Name:

[Sitecore Master DB].[schema].[WorkflowHistory]

This table (screenshot below) contains the following key fields:

• ID: Unique ID for each item in Workflow History
• ItemID: Item which workflow state has been updated
• Language: Language of item
• Version: Item version
• Old State: Old workflow state of item
• New State: New workflow state of item
• Text: Comments’ values if any
• User: User who executed the workflow item update
• Date: Date/Time when workflow state of item was updated

Run the following query to retrieve:

Add a simple ‘WHERE’ clause for your required date range and VOILA!

Hope this helps!

Photo by Julien Lanoy on Unsplash