Powershell Script to query and export Workflow History List Items across a Site Collection using CrossListQueryInfo in a .csv file

This Power Shell script queries across a given Site Collection using CrossListQueryInfo and CrossListQueryCache objects available in SharePoint 2010 OM. The script also generates a CSV file report of all Workflow History List items across the given site collection.

Add-PsSnapin Microsoft.SharePoint.PowerShell

## SharePoint DLL
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Publishing")
   
    $SiteCollUrl = "https://sharepointfix.com/sites/crosslistquerycache"
 
    $site = new-object Microsoft.SharePoint.SPSite($SiteCollUrl)
    $web = $site.OpenWeb()
   
    try
       {
        #Specify your CAML query here
        $caml = "" #You can specify your <Where/> clause here
        $lists= "<Lists ServerTemplate='140' Hidden='True'/>"
        $viewFields = "" #You can specify your <ViewFields/> here
        $webs = "<Webs Scope='SiteCollection' />"
        $queryInfo = new-object Microsoft.SharePoint.Publishing.CrossListQueryInfo
        $queryInfo.Webs = $webs
        $queryInfo.Lists = $lists
        $queryInfo.ViewFields = $viewFields
        $queryInfo.Query =$caml
        $crossListCache =  new-object Microsoft.SharePoint.Publishing.CrossListQueryCache –ArgumentList $queryInfo
        $allow= "TRUE"
        $results = $crossListCache.GetSiteDataResults($web, $allow)
        Write-Host "There are $($results.Data.Rows.Count) items."
        $results.Data | Export-Csv -Path "F:\WorkflowHistoryListItemReport.csv" -NoTypeInformation -Force       
       }
    catch
    {
        "Error encountered:" | Out-Default
        $_.Exception | Write-Error
    }
    finally
    {
        $web.Dispose()
        $site.Dispose()
    }
   
Remove-PsSnapin Microsoft.SharePoint.PowerShell
Write-Host "Finished"