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
  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. }
  20. #$logPath = $directoryPath + "\" + $logFileName
  21. $logPath = $directoryPathForLog + "\" + $logFileName
  22. $isLogFileCreated = $False
  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. }
  32. #DLL location
  34. $clientDLL=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"
  35. $clientDLLRuntime=$directoryPathForDLL+"\"+"Microsoft.SharePoint.Client.dll"
  37. Add-Type -Path $clientDLL
  38. Add-Type -Path $clientDLLRuntime
  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. }
  48. #File Download location
  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)
  90. $web = $context.Web
  91. $context.Load($web)
  92. $context.Load($web.Webs)
  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. }
  104. #Loading all lists for the particular site.
  105. $context.Load($web.Lists)
  106. $context.ExecuteQuery()
  108. foreach($list in $web.Lists)
  109. {
  110. $context.Load($list.WorkflowAssociations)
  111. $context.ExecuteQuery()
  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
  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
  138. }
  139. }
  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
  149. #Parameters ends here.
  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.