The Powershell script below generates a CSV report that counts the number of SharePoint Workflow History List Items for all site collections and sub-sites within a web application. It also displays a progress bar as you loop through multiple sites/sub-sites and gives you real time processing information.
Copy the PowerShell script below and paste it in a notepad and save it with a .ps1 extension in any of your local drives.
=====================================================================
param
(
$url
)
Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue
## SharePoint DLL
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Administration")
if(![string]::IsNullOrEmpty($url))
{
try
{
$webAppURI = New-Object Uri($url)
$spwebapp = [Microsoft.SharePoint.Administration.SPWebApplication]::Lookup($webAppURI)
}
catch [System.Exception]
{
Write-Warning "Web Application could not be found on the server. Check Webapplication URL before executing this script."
exit;
}
$spbasetypegenericlist=[Microsoft.SharePoint.SPBaseType]::GenericList
$output = @()
$heading = "Site Collection URL; Site URL; Site Name; List Title; List URL; List Item Count"
$filename = "." + $spwebapp.Name + "-" + $(Get-Date -Format MM-dd-yyyy-HH-mm) + ".csv"
#Write CSV file Headers
Out-File -FilePath $filename -InputObject $heading;
#Log file name
$logName = "." + $spwebapp.Name + "-" + $(Get-Date -Format MM-dd-yyyy-HH-mm) + ".log"
#Write-Output "Site Collection URL; Site URL; Site Name; List Title; List URL; List Item Count" | Out-File $logname
foreach ($spsite in $spwebapp.Sites)
{
try
{
#Progress Bar
$sitesProcessed = 0;
$siteMax = $spsite.AllWebs.Count;
foreach ($spweb in $spsite.AllWebs)
{
#Display Progress Bar on Site Completion
$sitesProcessed++;
$percent = ($sitesProcessed/$siteMax) * 100
Write-Progress -Activity "Looping through Sites" -PercentComplete $percent -CurrentOperation "$sitesProcessed / $siteMax" -Status "$spweb.Title"
try
{
$spgenericlists = $spweb.getlistsoftype($spbasetypegenericlist)
if ($spgenericlists -ne $null)
{
foreach ($list in $spgenericlists)
{
if($list -ne $null)
{
if ($list.basetemplate -eq "WorkflowHistory")
{
$output += $($spsite.Url + ";" + $spweb.Url + ";" + $spweb.Title + ";" + $list.Title + ";" + $list.DefaultViewUrl + ";" + $list.ItemCount)
}
}
}
}
}
catch
{
Write-Host "Exception thrown at" $spsite.Url $spweb.Url $list.Title
Write-Error ("Exception thrown at:" + $_)
#Write Exception to Log file
Write-Output "Exception thrown at" $spweb.Url $list.Title $list.DefaultViewUrl $_ | Out-File $logname -append
}
$spweb.Dispose()
}
}
catch [System.Exception]
{
Write-Host "Exception at" $spsite.Url $spweb.Url $list.Title
Write-Warning ("Exception thrown at: " + $_)
#Write Exception to Log file
Write-Output "Exception thrown at" $spweb.Url $list.Title $list.DefaultViewUrl $_ | Out-File $logname -append
}
finally
{
$spsite.Dispose()
}
}
if($output -ne $null)
{
$output | Out-File $filename -Append
Write-Host "Output file has been created successfully."
#Write-Output $output | Out-File $logname -append
}
else
{
Write-Warning "Error creating the CSV file."
#Write Exception to Log file
Write-Output "Error creating the CSV file" | Out-File $logname -append
exit
}
}
else
{
Write-Warning "Web Application URL parameter cannot be blank."
Write-Warning("Use Syntax: .GetAllWFHistoryItemCount.ps1 -url <Your Web App URL>")
exit
}
Write-Host "Finished"
==============================================================================
Automate the above .ps1 script as a batch utility, Copy and paste code below and save it with a .bat file extension, change the script file name and enter your web application URL, in the highlighted yellow section, save and run the automated batch file.
cd /d %~dp0
powershell -noexit -file ".GetWorkflowHistoryListItem.ps1" -url "https://sharepointfix.com" "%CD%"
pause
Run the batch file and import the CSV file generated into an Excel sheet. Delimit the columns with a "*" and then check the Count of Workflow History List Items for each of your Site Collection and Sub sites within.
Copy the PowerShell script below and paste it in a notepad and save it with a .ps1 extension in any of your local drives.
=====================================================================
param
(
$url
)
Add-PSSnapin Microsoft.SharePoint.PowerShell -ea SilentlyContinue
## SharePoint DLL
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Administration")
if(![string]::IsNullOrEmpty($url))
{
try
{
$webAppURI = New-Object Uri($url)
$spwebapp = [Microsoft.SharePoint.Administration.SPWebApplication]::Lookup($webAppURI)
}
catch [System.Exception]
{
Write-Warning "Web Application could not be found on the server. Check Webapplication URL before executing this script."
exit;
}
$spbasetypegenericlist=[Microsoft.SharePoint.SPBaseType]::GenericList
$output = @()
$heading = "Site Collection URL; Site URL; Site Name; List Title; List URL; List Item Count"
$filename = "." + $spwebapp.Name + "-" + $(Get-Date -Format MM-dd-yyyy-HH-mm) + ".csv"
#Write CSV file Headers
Out-File -FilePath $filename -InputObject $heading;
#Log file name
$logName = "." + $spwebapp.Name + "-" + $(Get-Date -Format MM-dd-yyyy-HH-mm) + ".log"
#Write-Output "Site Collection URL; Site URL; Site Name; List Title; List URL; List Item Count" | Out-File $logname
foreach ($spsite in $spwebapp.Sites)
{
try
{
#Progress Bar
$sitesProcessed = 0;
$siteMax = $spsite.AllWebs.Count;
foreach ($spweb in $spsite.AllWebs)
{
#Display Progress Bar on Site Completion
$sitesProcessed++;
$percent = ($sitesProcessed/$siteMax) * 100
Write-Progress -Activity "Looping through Sites" -PercentComplete $percent -CurrentOperation "$sitesProcessed / $siteMax" -Status "$spweb.Title"
try
{
$spgenericlists = $spweb.getlistsoftype($spbasetypegenericlist)
if ($spgenericlists -ne $null)
{
foreach ($list in $spgenericlists)
{
if($list -ne $null)
{
if ($list.basetemplate -eq "WorkflowHistory")
{
$output += $($spsite.Url + ";" + $spweb.Url + ";" + $spweb.Title + ";" + $list.Title + ";" + $list.DefaultViewUrl + ";" + $list.ItemCount)
}
}
}
}
}
catch
{
Write-Host "Exception thrown at" $spsite.Url $spweb.Url $list.Title
Write-Error ("Exception thrown at:" + $_)
#Write Exception to Log file
Write-Output "Exception thrown at" $spweb.Url $list.Title $list.DefaultViewUrl $_ | Out-File $logname -append
}
$spweb.Dispose()
}
}
catch [System.Exception]
{
Write-Host "Exception at" $spsite.Url $spweb.Url $list.Title
Write-Warning ("Exception thrown at: " + $_)
#Write Exception to Log file
Write-Output "Exception thrown at" $spweb.Url $list.Title $list.DefaultViewUrl $_ | Out-File $logname -append
}
finally
{
$spsite.Dispose()
}
}
if($output -ne $null)
{
$output | Out-File $filename -Append
Write-Host "Output file has been created successfully."
#Write-Output $output | Out-File $logname -append
}
else
{
Write-Warning "Error creating the CSV file."
#Write Exception to Log file
Write-Output "Error creating the CSV file" | Out-File $logname -append
exit
}
}
else
{
Write-Warning "Web Application URL parameter cannot be blank."
Write-Warning("Use Syntax: .GetAllWFHistoryItemCount.ps1 -url <Your Web App URL>")
exit
}
Write-Host "Finished"
==============================================================================
Automate the above .ps1 script as a batch utility, Copy and paste code below and save it with a .bat file extension, change the script file name and enter your web application URL, in the highlighted yellow section, save and run the automated batch file.
cd /d %~dp0
powershell -noexit -file ".GetWorkflowHistoryListItem.ps1" -url "https://sharepointfix.com" "%CD%"
pause
Run the batch file and import the CSV file generated into an Excel sheet. Delimit the columns with a "*" and then check the Count of Workflow History List Items for each of your Site Collection and Sub sites within.
