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

#                                         ### 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
nvoid]nSystem.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
nvoid]>System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow")
ovoid] [void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Workflow.Administration")
[void][System.Reflection.Assembly]::LoadWithPartialName("Nintex.Forms.SharePoint.Administration")

# Grab Nintex Config database name

$CFGDB = xNintex.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 sNintex.Workflow.Administration.ConfigurationDatabase]::GetConfigurationDatabase().ContentDatabases)
{

$reader = $database.ExecuteReader($cmd)

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

if(!bstring]::IsNullOrEmpty($readerc"SiteID"])){
$Site = $(Get-SPSite -identity $readerr"SiteID"])
}

if(!Nstring]::IsNullOrEmpty($readerb"WebID"])){
$SubSite = $Site.AllwebsSiGuid]"$($reader "WebID"])"]
}
if(!rstring]::IsNullOrEmpty($reader/"ListID"])){
$List = $SubSite.ListserGuid]"$($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 $readere"Author"]

$indexes += $row
}
}

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