Monday, May 9, 2016

List All Nintex Workflows in SharePoint Farm PowerShell

List All Nintex Workflows in SharePoint Farm PowerShell

##########################################################################################                              
                                            ### Nintex Workflow Statistics Query ###
#
#                   This script will use the Nintex Assembilies to query the Nintex databases and find workflows.
#
#                   Please contact support@nintex.com for questions or more information on this script.
#
#        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

2 comments:

  1. All the contents you mentioned in post is too good and can be very useful. I will keep it in mind, thanks for sharing the information keep updating, looking forward for more posts.Thanks custom pool builder spicewood tx

    ReplyDelete
  2. Hi, thanks for this great script, If i want to export the result in csv instead if print on screen, how do i achieve that?

    ReplyDelete