Consolidated Workflow Report From SharePoint Online Tenant

Introduction

 
In this tutorial, we will learn about how we can get consolidated workflow reports from the SharePoint Online tenant using PowerShell CSOM and PnP PowerShell. This script will scan through all the sites in a tenant and will export all workflows associated with the lists into a CSV file.
 

Workflows report from SharePoint Online using PowerShell CSOM & PnP

 
Using the below PowerShell CSOM & PnP code, we can get all workflows from the SharePoint Online tenant, 
  1. Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking    
  2. #Load SharePoint CSOM Assemblies    
  3.     #Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"    
  4.     #Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"    
  5.     cls    
  6.     $fileName = "Tenant_workflow_Report" #'yyyyMMddhhmm   yyyyMMdd    
  7.     $enddate = (Get-Date).tostring("yyyyMMddhhmmss")    
  8.     #$filename =  $enddate + '_VMReport.doc'      
  9.     $logFileName = $fileName +"_"+ $enddate+"_Log.txt"       
  10.     $invocation = (Get-Variable MyInvocation).Value      
  11.     $directoryPath = Split-Path $invocation.MyCommand.Path    
  12.     
  13.      $directoryPathForLog=$directoryPath+"\"+"LogFiles"    
  14.      if(!(Test-Path -path $directoryPathForLog))      
  15.         {      
  16.             New-Item -ItemType directory -Path $directoryPathForLog    
  17.             #Write-Host "Please Provide Proper Log Path" -ForegroundColor Red       
  18.         }         
  19.    
  20. #$logPath = $directoryPath + "\" + $logFileName     
  21. $logPath = $directoryPathForLog + "\" + $logFileName       
  22. $isLogFileCreated = $False       
  23.    
  24. #DLL location      
  25. $directoryPathForDLL=$directoryPath+"\"+"Dependency Files"    
  26. if(!(Test-Path -path $directoryPathForDLL))      
  27.         {      
  28.             New-Item -ItemType directory -Path $directoryPathForDLL    
  29.             #Write-Host "Please Provide Proper Log Path" -ForegroundColor Red       
  30.         }     
  31.    
  32. #DLL location    
  33.     
  34. $clientDLL=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"    
  35. $clientDLLRuntime=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"    
  36.     
  37. Add-Type -Path $clientDLL    
  38. Add-Type -Path $clientDLLRuntime      
  39.    
  40. #File Download location      
  41. $directoryPathForFileDownloadLocation=$directoryPath+"\"+"Download Workflow Details"    
  42. if(!(Test-Path -path $directoryPathForFileDownloadLocation))      
  43.         {      
  44.             New-Item -ItemType directory -Path $directoryPathForFileDownloadLocation    
  45.             #Write-Host "Please Provide Proper Log Path" -ForegroundColor Red       
  46.         }     
  47.    
  48. #File Download location      
  49.     
  50. function Write-Log([string]$logMsg)      
  51. {       
  52.     if(!$isLogFileCreated){       
  53.         Write-Host "Creating Log File..."       
  54.         if(!(Test-Path -path $directoryPath))      
  55.         {      
  56.             Write-Host "Please Provide Proper Log Path" -ForegroundColor Red       
  57.         }       
  58.         else       
  59.         {       
  60.             $script:isLogFileCreated = $True       
  61.             Write-Host "Log File ($logFileName) Created..."       
  62.             [string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)       
  63.             Add-Content -Path $logPath -Value $logMessage       
  64.         }       
  65.     }       
  66.     else       
  67.     {       
  68.         [string]$logMessage = [System.String]::Format("[$(Get-Date)] - {0}", $logMsg)       
  69.         Add-Content -Path $logPath -Value $logMessage       
  70.     }       
  71. }       
  72. #Object array to hold workflow details.      
  73. $WorkflowDetailsForSPOSite=@()      
  74. #The below function will read all workflows from a site and return the array output.        
  75. Function Get-WorkflowAssociationsDeatilsForEachSiteInTenant()    
  76. {           
  77.     param    
  78.     (    
  79.         [Parameter(Mandatory=$true)] [string] $SPOSiteURL,                  
  80.         [Parameter(Mandatory=$true)] [string] $UserName,    
  81.         [Parameter(Mandatory=$true)] [string] $Password    
  82.     )    
  83.     Try     
  84.     {    
  85.         $securePassword= $Password | ConvertTo-SecureString -AsPlainText -Force      
  86.         #Setup the Context    
  87.         $context = New-Object Microsoft.SharePoint.Client.ClientContext($SPOSiteURL)    
  88.         $context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName, $securePassword)      
  89.            
  90.         $web = $context.Web    
  91.         $context.Load($web)    
  92.         $context.Load($web.Webs)         
  93.        
  94.         $context.executeQuery()      
  95.         #Check if any subsite is available on the site.      
  96.         if ($web.Webs.Count -ne 0)    
  97.         {    
  98.             foreach ($subweb in $web.Webs)    
  99.             {                      
  100.                 Get-WorkflowAssociationsDeatilsForEachSiteInTenant -SPOSiteURL $subweb.url -UserName $userName -Password $password    
  101.             }    
  102.         }    
  103.     
  104.         #Loading all lists for the particular site.    
  105.         $context.Load($web.Lists)    
  106.         $context.ExecuteQuery()     
  107.      
  108.         foreach($list in $web.Lists)    
  109.          {         
  110.             $context.Load($list.WorkflowAssociations)       
  111.             $context.ExecuteQuery()     
  112.      
  113.             foreach($wfAssociation in $list.WorkflowAssociations)    
  114.              {    
  115.                 if($wfAssociation.name -notlike "*Previous Version*")    
  116.                     {    
  117.                     $row=new-object PSObject    
  118.                     add-Member -inputObject $row -memberType NoteProperty -name "Site Title" -Value $web.Title    
  119.                     add-Member -inputObject $row -memberType NoteProperty -name "Site URL" -Value $web.Url    
  120.                     add-Member -inputObject $row -memberType NoteProperty -name "List Title" -Value $list.Title    
  121.                     add-Member -inputObject $row -memberType NoteProperty -name "Workflow Name" -Value $wfAssociation.Name    
  122.                     add-Member -inputObject $row -memberType NoteProperty -name "Workflow Type" -Value "SharePoint List"    
  123.                     $WorkflowDetailsForSPOSite+=$row    
  124.                 }    
  125.             }    
  126.         }    
  127.         return $WorkflowDetailsForSPOSite       
  128.               
  129.     
  130.     }    
  131.     catch    
  132.     {    
  133.       write-host "Error: $($_.Exception.Message)" -foregroundcolor Red    
  134.       $ErrorMessage = $_.Exception.Message +"in exporting workflow details!:"     
  135.       Write-Host $ErrorMessage -BackgroundColor Red    
  136.       Write-Log $ErrorMessage           
  137.     
  138.     }    
  139. }    
  140.    
  141. #Parameters    
  142. #$siteURL="https://globalsharepoint2019.sharepoint.com/sites/ModernTeamSiteTestByPnP"    
  143. $adminUrl = "https://globalsharepoint2019-admin.sharepoint.com/"    
  144. $downloadLocation=$directoryPathForFileDownloadLocation +"\"+ "SPOTenantWorkflowReport.csv"    
  145. $userName = "YourSPOUserName"    
  146. $password = "YourSPOPassWord"    
  147. $securePassword= $password | ConvertTo-SecureString -AsPlainText -Force    
  148.    
  149. #Parameters ends here.    
  150.     
  151. $SecurePassword = $Password | ConvertTo-SecureString -AsPlainText -Force    
  152. $Credentials = New-Object -TypeName System.Management.Automation.PSCredential -argumentlist $userName, $SecurePassword    
  153. #Import-Module Microsoft.Online.SharePoint.PowerShell -DisableNameChecking    
  154. #Retrieve all site collection infos    
  155. #Connect-SPOService -Url $AdminUrl -Credential $Credentials    
  156. #$sites = Get-SPOSite     
  157. Connect-PnPOnline -Url $adminUrl -Credentials $Credentials    
  158. $allTenantSites=Get-PnPTenantSite    
  159. #Get-WorkflowAssociationsDeatilsForEachSiteInTenant -SPOSiteURL $siteURL -UserName $userName -Password $password | Export-Csv $downloadLocation    
  160. if($allTenantSites.Count -gt 0)    
  161. {    
  162.    $finalWorkflowReport =foreach($oneSite in $allTenantSites)    
  163.    {    
  164.       Get-WorkflowAssociationsDeatilsForEachSiteInTenant -SPOSiteURL $oneSite.URL -UserName $userName -Password $password    
  165.    }    
  166.       $finalWorkflowReport | Export-Csv $downloadLocation -NoTypeInformation    
  167.    }    
  168. Write-host "All workflows have been exported Successfully from the SharePoint Online Tenant." -BackgroundColor Green     
Notes about the above code
  • We must use the “Get-PnPTenantSite” to get all sites from the tenant, the other command “Get-PnPSite“, if we use this, it will give only the tenant admin site URL, like below.
Get-PnPSite-in-SharePoint-Online1
  • Similarly, in order to get all sites from the tenant, we can use the “Get-SPOSite” command as well. However, if you have .net framework mismatch version installed in your PowerShell – this command will not work, we will get an error like "Connect-SPOService : Method not found: ‘!!0[] System.Array.Empty()‘"
Get-SPOSite-Error-in-SharePoint-Online2
  • If we use the above code as is this will export all workflows from the tenant. However, if we want to export all workflows from a particular site we can comment the foreach loop and just we need to call the function "Get-WorkflowAssociationsDeatilsForEachSiteInTenant" as below: 
  1. Get-WorkflowAssociationsDeatilsForEachSiteInTenant -SPOSiteURL $siteURL -UserName $userName -Password $password | Export-Csv $downloadLocation  
Now, let’s execute the above script.
 
Get-all-workflows-from-SharePoint-Online-Tenant-using-PowerShell-CSOM3 
  • After the successful execution of the script, a CSV file with the name of "SPOTenantWorkflowReport.csv" will be created in the script location directory – however, you can change the download location to your desired location. 
Get-all-workflows-from-SharePoint-Online-Tenant-using-PowerShell-CSOM4
 
Now, let’s look at all workflows exported as CSV reports. 
 
CSV-Report-Get-all-workflows-from-SharePoint-Online-Tenant-using-PowerShell-CSOM
 
We can see in the CSV report, all workflows from the SharePoint Online tenant have been exported.
 

Prerequisites to execute the above script

 
You need to place the below two DLLs in your script directory "Dependency Files" folder as like below: 
 
SharePoint Client Dlls
 

Install PnP PowerShell

 
To install the "SharePointPnPPowerShellOnline" we need to run the below PowerShell command which will install PowerShell Package Management and then install the PowerShell Modules from the PowerShell Gallery. 
  1. (New-Object Net.WebClient).DownloadString('https://raw.githubusercontent.com/sharepoint/PnP-PowerShell/master/Samples/Modules.Install/Install-SharePointPnPPowerShell.ps1')  
Change the value of the variables in the parameters section like below.
  1. #Parameters  
  2. #$siteURL="https://globalsharepoint2019.sharepoint.com/sites/ModernTeamSiteTestByPnP"  
  3. $adminUrl = "Your SPO Admin URL Like - https://globalsharepoint2019-admin.sharepoint.com/"  
  4. $downloadLocation=$directoryPathForFileDownloadLocation +"\"+ "SPOTenantWorkflowReport.csv"  
  5. $userName = "YourSPOUserName"  
  6. $password = "YourSPOPassWord"  
  7. $securePassword= $password | ConvertTo-SecureString -AsPlainText -Force  
  8. #Parameters ends here.  

Summary

 
In this article, we have learned how we can generate a workflow consolidated report from SharePoint Online tenant using the PowerShell CSOM and PnP.
 
Download
 
The above code can be downloaded from here.