How to Generate Workflow Reports Using Powershell Script in SharePoint 2010

Introduction

This articles outlines how to generate workflow reports (in-progress workflows and completed workflows) using a PowerShell script in SharePoint 2010.

Why PowerShell script

Consider a scenario where you configure a workflow for a list to alert you when a new item is added or there are any changes to an existing item. In this case each item would have a separate instance of workflow.

Consider a list with thousands of items, where it is tedious to check the workflow status on each item manually, here PowerShell is useful.

Functionality

The PowerShell script explained in this article loops through all the sites under a site collection and all the lists under each web and all the items under each list and generates 2 separate reports, one to hold the in-progress workflow details and the other to hold the completed workflow details.

The following piece of code generates the workflow reports:

  1. Function WorkflowReports()  
  2. {  
  3.   
  4.     $output = $scriptbase + "\" + "InProgressWorkflows.csv"  
  5.     "SiteCollection" + "," + "WebURL" + "," + "List" + "," + "ItemName" + "," + "ItemGUID" + "," + "WorkflowInstanceID" | Out-File -Encoding Default -FilePath $Output;  
  6.   
  7.     $output1 = $scriptbase + "\" + "OtherWorkflows.csv"  
  8.     "SiteCollection" + "," + "WebURL" + "," + "List" + "," + "ItemName" + "," + "ItemGUID" + "," + "WorkflowInstanceID" | Out-File -Encoding Default -FilePath $Output1;  
  9.   
  10.     Write-host "Getting workflow report" -fore magenta  
  11.     $SiteCollectionURL = read-host "Enter site collection URL "  
  12.     $siteCollection = get-spsite $SiteCollectionURL -ea silentlycontinue  
  13.     if($SiteCollection -ne $null)  
  14.     {  
  15.         foreach($web in $SiteCollection.allwebs)  
  16.         {  
  17.             $lists = $web.lists  
  18.             foreach($list in $lists)  
  19.             {  
  20.                 foreach($item in $list.items)  
  21.                 {  
  22.                     foreach($workflow in $item.workflows)  
  23.                     {  
  24.                         if($workflow.iscompleted -eq $false)  
  25.                         {  
  26.                             $SiteCollectionURL + "," + $web.url + "," + $list.Title + "," + $workflow.ItemName + "," + $workflow.ItemGUID + "," + $workflow.InstanceId | Out-File -Encoding Default  -Append -FilePath $Output;  
  27.                         }  
  28.                         else  
  29.                         {  
  30.                             $SiteCollectionURL + "," + $web.url + "," + $list.Title + "," + $workflow.ItemName + "," + $workflow.ItemGUID + "," + $workflow.InstanceId | Out-File -Encoding Default  -Append -FilePath $Output1;  
  31.                         }  
  32.                     }  
  33.                 }  
  34.             }  
  35.         }  
  36.         Write-host "Reports generated and available at the script location " -fore green  
  37.     }  
  38.     else  
  39.     {  
  40.         write-host "Invalid site collection.... please check the URL...." -fore red  
  41.     }  
  42.   
  43. }  
Complete Code
  1. $LogTime = Get-Date -Format yyyy-MM-dd_hh-mm  
  2. $LogFile = ".\WorkflowDetailsPatch-$LogTime.rtf"  
  3.  
  4. # Add SharePoint PowerShell Snapin  
  5.   
  6. if ( (Get-PSSnapin -Name Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null ) {  
  7.     Add-PSSnapin Microsoft.SharePoint.Powershell  
  8. }  
  9.   
  10. $scriptBase = split-path $SCRIPT:MyInvocation.MyCommand.Path -parent  
  11. Set-Location $scriptBase  
  12.   
  13. write-host "TESTING FOR LOG FOLDER EXISTENCE" -fore yellow  
  14. $TestLogFolder = test-path -path $scriptbase\Logs  
  15. if($TestLogFolder)  
  16. {  
  17.     write-host "The log folder already exist in the script location" -fore yellow  
  18.     $clearlogfolder = read-host "Do you want to clear the log folder (y/n)"  
  19.     if($clearlogfolder -eq 'y')  
  20.     {  
  21.         write-host "The user choosen to clear the log folder" -fore yellow  
  22.         write-host "Clearing the log folder" -fore yellow  
  23.         remove-item $scriptbase\Logs\* -recurse -confirm:$false  
  24.         write-host "Log folder cleared" -fore yellow  
  25.     }  
  26.     else  
  27.     {  
  28.         write-host "The user choosen not to clear the log files" -fore yellow  
  29.     }  
  30. }  
  31. else  
  32. {  
  33.     write-host "Log folder does not exist" -fore yellow  
  34.     write-host "Creating a log folder" -fore yellow  
  35.     New-Item $Scriptbase\Logs -type directory  
  36.     write-host "Log folder created" -fore yellow  
  37. }         
  38.  
  39. #moving any .rtf files in the scriptbase location  
  40. $FindRTFFile = Get-ChildItem $scriptBase\*.* -include *.rtf  
  41. if($FindRTFFile)  
  42. {  
  43.     write-host "Some old log files are found in the script location" -fore yellow  
  44.     write-host "Moving old log files into the Logs folder" -fore yellow  
  45.     foreach($file in $FindRTFFile)  
  46.         {  
  47.             move-item -path $file -destination $scriptbase\logs  
  48.         }  
  49.     write-host "Old log files moved successfully" -fore yellow  
  50. }  
  51.   
  52. start-transcript $logfile  
  53.   
  54. Function WorkflowReports()  
  55. {  
  56.     $output = $scriptbase + "\" + "InProgressWorkflows.csv"  
  57.     "SiteCollection" + "," + "WebURL" + "," + "List" + "," + "ItemName" + "," + "ItemGUID" + "," + "WorkflowInstanceID" | Out-File -Encoding Default -FilePath $Output;  
  58.     $output1 = $scriptbase + "\" + "OtherWorkflows.csv"  
  59.     "SiteCollection" + "," + "WebURL" + "," + "List" + "," + "ItemName" + "," + "ItemGUID" + "," + "WorkflowInstanceID" | Out-File -Encoding Default -FilePath $Output1;  
  60.     Write-host "Getting workflow report" -fore magenta  
  61.     $SiteCollectionURL = read-host "Enter site collection URL "  
  62.     $siteCollection = get-spsite $SiteCollectionURL -ea silentlycontinue  
  63.     if($SiteCollection -ne $null)  
  64.     {  
  65.         foreach($web in $SiteCollection.allwebs)  
  66.         {  
  67.             $lists = $web.lists  
  68.             foreach($list in $lists)  
  69.             {  
  70.                 foreach($item in $list.items)  
  71.                 {  
  72.                     foreach($workflow in $item.workflows)  
  73.                     {  
  74.                         if($workflow.iscompleted -eq $false)  
  75.                         {  
  76.                             $SiteCollectionURL + "," + $web.url + "," + $list.Title + "," + $workflow.ItemName + "," + $workflow.ItemGUID + "," + $workflow.InstanceId | Out-File -Encoding Default  -Append -FilePath $Output;  
  77.                         }  
  78.                         else  
  79.                         {  
  80.                             $SiteCollectionURL + "," + $web.url + "," + $list.Title + "," + $workflow.ItemName + "," + $workflow.ItemGUID + "," + $workflow.InstanceId | Out-File -Encoding Default  -Append -FilePath $Output1;  
  81.                         }  
  82.                     }  
  83.                 }  
  84.             }  
  85.         }  
  86.         Write-host "Reports generated and available at the script location " -fore green  
  87.     }  
  88.     else  
  89.     {  
  90.         write-host "Invalid site collection.... please check the URL...." -fore red  
  91.     }  
  92. }  
  93.   
  94. WorkflowReports  
  95.   
  96. write-host ""  
  97. write-host "SCRIPT COMPLETED" -fore green  
  98. stop-transcript  
Execution procedure
  • Step 1: Copy the script to the SharePoint server
  • Step 2: Launch the SharePoint management shell
  • Step 3: Navigate to the script path
  • Step 4: Execute the script

Enter the site collection URL to generate the workflow reports under that specific site collection. The output file is placed under the same location where the PowerShell script is placed.

Conclusion

The script can be modified to run against the SharePoint Farm or a specific web application or specific web/site. Thus this article outlines how to generate workflow reports using a PowerShell script.