Nintex workflow statistics Powershell Query : SharePoint 2010

Hi Friends,

In many of the organizations we are using the Nintex Workflows as the third party tool to create more user friendly and easy workflows. But since everyone is migrating from SharePoint 2010 to SharePoint online we always requires to know the details of the workflows exists in SharePoint 2010 Environment. Here is the PowerShell script that gives all the necessary information like

• Web
• Site
• List Name
• Workflow Name
• Last run of the workflow
• Author

This information is very useful when migration needs to be performed. You can even modify the script to get more information out of the available data.

Copy

x

 

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

#                                         ### 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 t.SiteID

 ,t.WebID

 ,t.ListID

 ,t.Author

 ,t.LastAccessed

 ,t.WorkflowName

FROM (SELECT j.SiteID

 ,j.WebID

 ,j.ListID

 ,pw.Author

 ,CONVERT(VARCHAR(11),j.StartTime)as LastAccessed

 ,j.WorkflowName

 , ROW_NUMBER() over (PARTITION by j.WorkflowID order by j.StartTime desc) as rn

 FROM [NW2010DB].dbo.WorkflowInstance j

 inner join [NW2010DB].dbo.WorkflowProgress P

    ON j.InstanceID = P.InstanceID

 inner join [NW2010DB].dbo.publishedworkflows pw

 on j.WorkflowID = pw.WorkflowId

 ) as t

where t.rn =1"

​

$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 "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"]

        $row | Add-Member -MemberType NoteProperty -Name "Last Accessed" -Value $reader["LastAccessed"]

  $details = @{         

                WorkflowName     = $reader["WorkflowName"]           

                SiteCollection      = $Site.Url

                SubSite   = $SubSite

                List    = $List.title

                Author   = $reader["Author"]

                LastAccessed  = $reader["LastAccessed"]

            } 

        $indexes += New-Object PSObject -Property $details

    }

}

​

#Print results on screen

$indexes  | export-csv -Path c:\output.csv -NoTypeInformation

##Write-host "Total Workflows in all DataBases:" $indexes.Count

Happy Coding
-Sumit Kanchan