Monday, June 18, 2012

Powershell script to get Site Collection Pages and List Items count using SPSiteDataQuery object

This PowerShell script queries a given Site Collection and returns all its Pages and List Item count recursively using SPSiteDataQuery object model.

Add-PsSnapin Microsoft.SharePoint.PowerShell

## SharePoint DLL
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Publishing")

# CHANGE THE FOLLOWING LINE TO POINT TO THE DESIRED PORTAL SITE COLLECTION
$web = Get-SPWeb "http://sharepointfix/sites/spsitedataquery"

if($web -ne $null)

    # Adding fields to the view
    $viewFields = New-Object System.Collections.Specialized.StringCollection
    $viewFields.Add(“Title”)
    $viewFields.Add(“Name”)

    # Construct the query object for publishing pages
    $queryPages = New-Object Microsoft.SharePoint.SPSiteDataQuery
    $queryPages.Lists = "<Lists ServerTemplate=""850""/>"

    #Specify your clause here
    $queryPages.Query = ""
    $queryPages.RowLimit = 1000000
    $queryPages.ViewFields = $viewFields
    $queryPages.Webs = "<Webs Scope=""Recursive""/>"
   
    # Construct the query object for list items
    $queryItems = New-Object Microsoft.SharePoint.SPSiteDataQuery
    $queryItems.Lists = "<Lists ServerTemplate=""100""/>"

    #Specify your clause here
    $queryItems.Query = ""
    $queryItems.RowLimit = 1000000
    $queryItems.ViewFields = $viewFields
    $queryItems.Webs = "<Webs Scope=""Recursive""/>"
   
    # Execute both queries and display the count of the number of pages and items
    $pagesData = $web.GetSiteData($queryPages)
    $itemsData = $web.GetSiteData($queryItems)
    Write-Host "There are $($pagesData.Rows.Count) pages and $($itemsData.Rows.Count) items."
   
    $web.Dispose()
}

Remove-PsSnapin Microsoft.SharePoint.PowerShell

Write-Host "Finished"

Benefits of SPSiteDataQuery over SPQuery is that SPSiteDataQuery queries the whole site collection including all its subsites recursively and brings back the desired data.