chad.austin@nintex.com

Finding all of the workflows in your farm using PowerShell

Blog Post created by chad.austin@nintex.com Support on Apr 9, 2015

We've had a couple of people request the ability to pull back information on the workflows in their farm. This script will return the results of all workflows found inside of the Nintex databases associated with your farm.

 

There are a couple of things to note:

 

  • Workflows that have been designed, but never run will not show in the results (no record of them in the Nintex DBs)
  • If you have performed a recent purge on the Nintex databases, the workflows may not show up.
  • If you receive errors when running the script, please allow it to run to completion. The script uses what It finds in the Nintex DB and resolves certain information (SiteID > Site collection URL, Etc.) and may error if a Site Collection/Site/List and/or workflow is not found. This will not affect the results of the workflows that are found.

 

 

PowerShell

##########################################################################################                            

#                                         ### Nintex Workflow Statistics Query ###

#
#                   This script will use the Nintex Assembilies to query the Nintex databases and find workflows.
#

#

#        Please ensure you run this script as Administrative account that has rights to each Nintex database
#
###########################################################################################

#Adding SharePoint Powershell Snapin
Add-PSSnapin Microsoft.SharePoint.PowerShell -EA silentlycontinue

# The Line below will suppress error messages, uncomment if you are seeing errors but still receiving results.

#$ErrorAction = 'silentlycontinue' 

# Loading SharePoint and Nintex Objects into the PS session
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow")
[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow.SupportConsole")
[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow.Administration")
[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Forms.SharePoint.Administration")

# Grab Nintex Config database name

$CFGDB = [Nintex.Workflow.Administration.ConfigurationDatabase]::OpenConfigDataBase().Database

# Creating instance of .NET SQL client
$cmd = New-Object -TypeName System.Data.SqlClient.SqlCommand

$cmd.CommandType = [System.Data.CommandType]::Text

# Begin SQL Query
$cmd.CommandText = "SELECT
               i.WorkflowName,
               i.SiteID,
               i.WebID,
               i.listid,
      pw.Author
FROM dbo.WorkflowInstance I
inner join WorkflowProgress P
               ON I.InstanceID = P.InstanceID
Inner join [$CFGDB].dbo.publishedworkflows pw
on i.WorkflowID = pw.WorkflowId
GROUP BY GROUPING SETS((i.siteid, i.webid, i.listid, i.workflowname, pw.Author), ());"


$indexes = @()

# Call to find all Nintex Content Databases in the Nintex Configuration Database, then execute the above query against each. 
foreach ($database in [Nintex.Workflow.Administration.ConfigurationDatabase]::GetConfigurationDatabase().ContentDatabases)
{

$reader = $database.ExecuteReader($cmd)

# Creating a table
while($reader.Read())
{
$row = New-Object System.Object

if(![string]::IsNullOrEmpty($reader["SiteID"])){
$Site = $(Get-SPSite -identity $reader["SiteID"])
}

if(![string]::IsNullOrEmpty($reader["WebID"])){
$SubSite = $Site.Allwebs[[Guid]"$($reader["WebID"])"]
}
if(![string]::IsNullOrEmpty($reader["ListID"])){
$List = $SubSite.Lists[[Guid]"$($reader["ListID"])"]
}

#Adding Query results to table object
$row | Add-Member -MemberType NoteProperty -Name "Workflow Name" -Value $reader["WorkflowName"]
$row | add-member -MemberType NoteProperty -Name "Database" -value $Site.ContentDatabase.Name
$row | Add-Member -MemberType NoteProperty -Name "Site Collection" -Value $Site.Url
$row | Add-Member -MemberType NoteProperty -Name "Subsite" -Value $SubSite
$row | Add-Member -MemberType NoteProperty -Name "List" -Value $List.title
$row | Add-Member -MemberType NoteProperty -Name "Author" -Value $reader["Author"]

$indexes += $row
}
}

#Print results on screen
$indexes  | FT -autosize
Write-host "Total Workflows in all DataBases:" $indexes.Count

IMG_WF_PS_Script2.png

Attachments

Outcomes