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.


#################################################################################                         
#                                         ### 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

Comments

Popular posts from this blog

SharePoint Framework (SPFx) : Cascade dropdown in webpart properties

Checking user permission in SharePoint Framework (SPFx) webpart

SharePoint Image Gallery