Wednesday, January 16, 2013

Powershell script to get SharePoint Workflow History List Items Count

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.