Using Know Your Workflow for Migration Planning

! This article is out of date! Please reach out to your account manager or CSM if you are interested in running the script in your environment.

The Know Your Workflow Script

The Nintex Know Your Workflow (KYWF) script is a handy tool provided by Nintex that allows you to get a better understanding of the workflows that are running within your SharePoint On-Prem environment. In this post we're going to review how this script can be used to not only audit where your workflow are, but also help build a Master Workflow List to help you prepare for your migration to cloud.

This download will include the script itself and instructions on how the script can be run. You will run the script on one of your Web Front End SharePoint Servers that are licensed for Nintex, and script will output a zipped file with several different csv files inside.

While each of these files are used for different purposes, the file that we're most interested for this exercise is the WorkflowStatistics file (which will have the file name format of: NWUsageStats-<Database Name>-WorkflowStatistic-<Script Execution Date>) as this will have the most information about the workflows in your environment related to migration prioritization.

Note: By default the Know Your Workflow script will retrieve workflow data from workflows that have been run within the last 90 days. To find and review workflow data from instances that have not run within the last 90 days you can modify the values on the following lines of the script:

• Line 578: $database 90 → Update the 90 to the number of days back you would like to include
• Line 582: $database 90 → Update the 90 to the number of days back you would like to include

Nintex Workflow Farm Inventory

Now that we have the workflow statistics output, we are also going to grab another output from our SharePoint environment that we will use with the KYWF output to help build our Master Workflow List. From within Nintex Administration the Farm Workflow Inventory will provide us with a list of all of the Nintex workflows that are within our SharePoint environment.

This can be exported into a CSV that will provide us with huge amount of information related to the location of our workflows, who has modified them, and their Workflow IDs:

Note: If you are seeing a discrepancy in the number of workflows between the Know Your Workflow output and your Farm Workflow Inventory, this is most likely due to the way that data is stored in the databases and where the two etracts get their data from. 

• The Farm Workflow Inventory is pulling data from the configuration database, and will reflect the number of published workflows in the farm.
• Know Your Workflow is pulling data from the content databases, and will only return information on the workflow if there is still instance data within the content database. If you do regular cleanup of the instance data in your content databases, some workflows may not appear in the KYWF output.

Building One List To Rule Them All

We're now going to take both of these datasets and combine them into the Master Workflow List template that I've created and linked below. While using this template is not required for building out your workflow list, I've found that this formatting makes it much easier to analyze the workflow data.

Our next step is simply copying the output from the KYWF Workflow Statistics sheet into the Know Your Workflow tab in our Master Workflow List.

With that completed, we're going to copy some (but not all) of the data from the Farm Workflow Inventory export into our Master Workflow List tab.

We will first copy the information from columns A - G into the Workflow List tab:

We will then also copy the information from columns M - N into the Workflow List tab:

At this point columns B - J should now have data populated into them within our Master Workflow List sheet:

Pulling It All Together

Now we are going to use one of the more powerful Excel functions to help associate the information from the Farm Workflow Inventory and the Know Your Workflow script output to complete our Master Workflow List and then use that information for analysis. By using the Excel VLOOKUP function we can pull in the Workflow Size, Avg. Action Execution, and Total Run data from the Know Your Workflow script and relate that with the workflows from the Workflow Inventory.

I've included the VLOOKUP functions that I used in my template below for each column, however the VLOOKUPs should also already exist with the template so the data may pre-populate after you've completed the steps above:

• Column K (Size Action Count): =VLOOKUP(J4,'Know Your Workflow'!B:L,6,FALSE)
• Column L (Avg Action Execution): =VLOOKUP(J4,'Know Your Workflow'!B:L,5,FALSE)
• Column M (Total Runs): =VLOOKUP(J4,'Know Your Workflow'!B:L,11,FALSE)

Note: The VLOOKUP function in this worksheet is relating the values from the MaxDesignerActions, AvgActionExecutions, and TotalRun columns in the Know Your Workflow Script based on the Workflow ID. If there is a #N/A value that is displayed in a couple of the columns of your Master Workflow List this will indicate that there is no workflow in the Know Your Workflow output that has that Workflow ID.

So...What Does All This Mean?

Alright, we now have all of the data pulled into our Master Workflow List, and finally we can do some analysis on the data and start understanding what workflows are going to need the most attention before we start our migration to the cloud.

When the team here at Nintex is reviewing workflow inventories there are really three factors that we look at; Workflow Size, Action Execution vs. Action Count, and Instance Count.  I've outlined how we think about each of them below to help you start prioritizing your own workflow lists:

1. Workflow Size

• > 200 actions
  • While there are no hard workflow size limits in Office 365, we recommend reviewing any workflow that is over 200 actions. These will often end up taking longer to refactor after the migration, and a migration is a good opportunity to review the workflow and understand if it should really continue to be a single workflow in O365 or if it should actually be broken up into a series of smaller workflows that are chained together. This can potentially help with performance, and will also make the workflow easier to troubleshoot down the road.
• Single action workflows 
  • Frequently these are just test workflows, and while most workflows with less < 5 actions do not count against your subscription usage, it can count as data being moved via your migration tool, so it might be worth just deleting the workflow now and help get the environment as clean as possible prior to your migration
• Workflows with no actions
  • Wait there can be workflows with 0 actions? Technically no, you need at least one action on the canvas to publish a workflow. However, the Know Your Workflow Script will only see workflow actions if they've visible to the workflow history within SharePoint On-Prem. As such, if you have hidden all of the actions on the design canvas, then the workflow can appear as if it has no actions. These workflows should definitely be reviewed to understand if there are potentially massive workflows that are "hiding" by not writing their actions back to the history.

2. Avg. Action Execution vs. Action Count

• High Actions Execution to Action Count Ratio
  • When moving to the cloud, one of the biggest concerns around workflow performance is workflow throttling (learn more here), and one of the leading causes of workflow throttling is when workflows loop through thousands (or hundreds of thousands) of list items.
  • To try and identify workflows that have large loops, we will often look for workflows that have an average action execution count that is approximately five times larger than the action count. This means that the number of actions that were executed within the workflow instances is on average five times more than the number of actions that are on the canvas.
    • If you do have workflows that have large loops in them you can usually work around the throttling issue via slight workflow design change, such as the one outlined here: https://help.nintex.com/en-US/o365/o365/O365WorkFlow/BestPractices/BP-Safelooping.htm
• Low Action Execution to Action Count Ratio
  • Every once and a while when reviewing workflow inventories we will also see an instance where a workflow has a large number of actions (~150), but has an average action execution count of 5 - 10. This will often indicate what we like to call a "short and wide" workflow design, where the workflow has extensive branching conditions, but usually only ever runs through a few "tiers" of the workflow and then completes
    • Example: 
  • Often these designs can be reviewed and improved by breaking the branching conditions into different workflows that will trigger based on a conditional start that will greatly reduce the number of workflow actions.

3. Total Runs

• This one is pretty straight forward. If the workflow has run thousands of times, it's probably worth migrating, and if it's only run once in the last nine months it is probably worth reviewing the workflow to understand if it's not supposed to run very often, or if it should be deprecated prior to the migration.

While there are other data points that can also help continue refining your migration inventory (such as the number of initiators which will indicate how many people trigger the workflow) the three factors above are the ones that will most likely have the largest impact on your inventory analysis. I hope that this post has helped provide some insight into how the Know Your Workflow script can be used to build a Master Workflow List prior to your move to the cloud, and how you can start reviewing and prioritizing your workflows as you start your migration journey!