Powershell output content of two arrays based on a join


Hi

 

I am querying sql to gather some nintex information from the SharePoint Nintex database. Nintex only holds SharePoint siteid. So I need powershell to get me the url (and some additional data) from the results output by sql I can do this using the :

 

$GUIDS = ($DS.Tables[0] | select -ExpandProperty  siteid) | Format-Table -HideTableHeaders | Out-String
foreach($line in $guids)
{Get-Spsite -identity $line | Select -property ID, URL, OWner, Hostname | Export-Csv -Path c: empurl.csv -NoTypeInformation }

 

But I also need the csv output to include the workflowinitiator, workflowname, and activityname that I have in the array $workflowdetails.

How do I combine the output into one and export to csv This is what I have so far:

 

[CmdletBinding()]

param
(
[Parameter(Mandatory=$True)]
[string]$SQLServerInstance,
[Parameter(Mandatory=$True)]
[string]$NintexConfigDBName
)
Add-PSSnapin Microsoft.SharePoint.PowerShell
$ConnectionTimeout = 30
$Query = "SELECT DISTINCT i.workflowname,i.siteid,i.workflowinitiator, a.activityname FROM dbo.workflowinstance
i inner join WorkflowProgress P on I.InstanceID=P.InstanceID inner join Activities A on P.ActivityID=A.ActivityID WHERE a.activityname IN ('Call web service','Execute SQL','Query LDAP','Query XML', 'Start workflow in Nintex Workflow Cloud ', 'Update XML ', 'Web request ', 'Capture document set version ', 'Copy to file share', 'Create list', 'Declare as record ', 'Delete drafts', 'Delete item ', 'Delete multiple items', 'Delete previous versions', 'Discard check out ','Query list','Send document set to repository', 'Send document to repository','Set approval status ','Set item permissions', 'Undeclare as record ', 'Update multiple items', 'Action set', 'Commit pending changes', 'Run parallel actions', 'State machine ', 'Pause for... ', 'Pause until... ', 'Wait for check out status change ', 'Wait for item update ', 'Create site ','Create site collection','Decommission site collection ', 'Delete site', 'Publish Workflow', 'Assign Flexi task', 'Complete workflow task ' ) "

$QueryTimeout = 120

$conn=new-object System.Data.SqlClient.SQLConnection
$ConnectionString = "Server={0};database={1};Integrated Security=True;Connect Timeout={2}" -f $SQLServerInstance,$NintexConfigDBName,$ConnectionTimeout
$conn.ConnectionString=$ConnectionString
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
$da.fill($ds)
$conn.Close()
$DS.Tables[0]
$workflowdetails = ($DS.Tables[0] | select -ExpandProperty siteid, workflowinitiator, workflowname,activityname ) | Format-Table -HideTableHeaders | Out-String
$GUIDS = ($DS.Tables[0] | select -ExpandProperty siteid) | Format-Table -HideTableHeaders | Out-String
foreach($line in $guids)
{Get-Spsite -identity $line | Select -property ID, URL, OWner, Hostname | Export-Csv -Path c: empurl.csv -NoTypeInformation }

 

 

 
 

0 replies

Be the first to reply!

Reply