Powershell script to get workflow tasks for each list item

Question

I have a lot of number of webs (sub-sites) within a site collection that have lists that have list items with Approval workflows running on them.

How would I get all tasks for each list item in powershell? I see that the "tasks" list has the 'Related Content' column which has the title of the list item. How to I do a union between the two lists (the task list and the item list) in powershell?

Thanks for your help!

Answer 1

Here is the complete PowerShell code using that you will get workflow report from a given web application.

`If ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) 
{
  Add-PSSnapin "Microsoft.SharePoint.PowerShell"
 }

 $SizeLog = "C:\temp\test.csv"
 #this creates the CSV if it does not already exist
 $headers = "SiteRootWebTitle", "SiteCollUrl","ContentDatabaseName","SizeInGB","CurrentDate"
$psObject = New-Object psobject
foreach($header in $headers)
{
 Add-Member -InputObject $psobject -MemberType 
 noteproperty -Name $header -Value ""
}
$psObject | Export-Csv $SizeLog -NoTypeInformation


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

  Function WriteWFDetails($wfAssociations, $wfType, 
  $listTitle, $OutputFile, $includePreviousVersions) 
  {
if($includePreviousVersions) {
    $wfAssociations = ($wfAssociations | Sort-Object Name)
}
else {
    $wfAssociations = ($wfAssociations | ?{-Not ($_.Name -like "*Previous Version*")} | Sort-Object Name)
}
foreach($wfAssociation in $wfAssociations) {
    $record =",$wfType,`"$($listTitle)`",`"$($wfAssociation.Name)`",`"$($wfAssociation.TaskListTitle)`",`"$($wfAssociation.HistoryListTitle)`","
    $record += "$($wfAssociation.RunningInstances),$($wfAssociation.Created),$($wfAssociation.Modified)"
    #Export-Csv $SizeLog -inputobject $record -append -Force
    #Add-Content $OutputFile $record
    $record | Out-File -Append -FilePath $OutputFile
}
}

 Function AllWorkflows($siteUrl, $OutputFile, 
 $includePreviousVersions)
 {
  $siteColl  = Get-SPSite $siteUrl
if($firstSiteCount -eq 1)
{
Set-Content $OutputFile "WebUrl,SiteWFCount,ListWFCount"
Add-Content $OutputFile ",SiteWF or ListWF or CTWF?,ListName or List/CTName,WorkflowName,TaskList,HistoryList,RunningInstances,Created,Modified"
}
foreach($web in $siteColl.AllWebs)
{
    $webUrl = if($web.IsRootWeb) { "$($web.Url) -- RootWeb" } else {  $web.Url }

    if($includePreviousVersions) {
        $siteWFCount = $web.WorkflowAssociations.Count
        $contWFCount = $web.ContentTypes.WorkflowAssociations.Count
        $listWFCount = $web.Lists.WorkflowAssociations.Count
    }
    else {
        $siteWFCount = ($web.WorkflowAssociations | ? {-Not ($_.Name -like "*Previous Version*")}).Count
        $contWFCount = ($web.ContentTypes.WorkflowAssociations | ? {-Not ($_.Name -like "*Previous Version*")}).Count
        $listWFCount = ($web.Lists.WorkflowAssociations | Select Name | ? {-Not ($_.Name -like "*Previous Version*")}).Count
    }

    if($listWFCount -gt 0 -or $siteWFCount -gt 0 -or $contWFCount -gt 0) {
        $record = "`"$webUrl`",$siteWFCount,$listWFCount"
        #Add-Content $OutputFile $record;
        $record | Out-File -Append -FilePath $OutputFile

        WriteWFDetails $web.WorkflowAssociations "SiteWF" "" $OutputFile $includePreviousVersions
        if($contWFCount -gt 0) {
            WriteWFDetails $web.ContentTypes.WorkflowAssociations "CTWF" "" $OutputFile $includePreviousVersions
        }
        foreach($list in $web.Lists)
        {
            if($list.WorkflowAssociations.Count -gt 0) {
                WriteWFDetails $list.WorkflowAssociations "ListWF" $list.Title $OutputFile $includePreviousVersions
            }
            foreach($ct in ($list.ContentTypes | ? {$_.WorkflowAssociations.Count -gt 0})) {
                WriteWFDetails $ct.WorkflowAssociations "ListCTWF" "$($list.Title)/$($ct.Name)" $OutputFile $includePreviousVersions
            }
        }            
    }
    $web.Dispose()
}
$siteColl.Dispose()
  }

#parameter 3 - pass $TRUE to include previous workflow versions

$ArrayWebApp=@("http://testserver01:1111","http://testserver01:2222");
  $firstSiteCount=1;
  foreach($oneItemInArray in $ArrayWebApp)
  {
  $webApplication=Get-SPWebApplication 
  $oneItemInArray
  foreach($oneSite in $webApplication.Sites)
  {

    #Get the Site object
    $site = Get-SPSite $oneSite.Url       
    AllWorkflows $oneSite.Url "C:\Temp\WorkflowReport.csv" $FALSE       
    $firstSiteCount++       

 }

 }

` Notes

When you want to union workflow task with parent list - you loop thru the task list - set debugger inside the for each loop, type $taskitem variable of foreach loop then type $taskitem.Properties in the console, hit enter, now you will see all association from parent main list and task list, accordingly you need to map the field to get list item details.Here key point is the $taskitem.Properties.