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"

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.

Powershell script to get SharePoint Page Layouts inventory and its usage across site collection

I was writing a PowerShell script that needed to do 2 things primarily:

1. Get an inventory of all Page Layouts deployed across a SharePoint 2010 Farm.
2. To find out whether or not any of these Page Layouts were in use or not within its various sites/sub-sites. To simply put it the other way, did any of the sites/sub-sites had Pages created out of any of the available Page Layouts in the farm.

The first requirement was not too hard to accomplish, we have the PublishingSite.GetPageLayouts in the SharePoint OM, but I had to scratch my head a little bit for the second one though.

Iterating through Pages library within numerous site/sub-sites for approximately 20,000 site collections in the Farm, in order to find out the Page Layout usage would be too expensive and time consuming. There had to be a better/faster approach to handle this scenario.

The clue was SharePoint 2010's OOTB related resources inventory. Go to “Site Actions” -> “Site Content and Structure” and select “Master Page Gallery”. See snapshot below:

The OOTB Show Related Resources functionality allows a user to select any of the Page Layouts/resources within the Master Page and Page Layouts Gallery and mentions files that uses it across the site collection.

Now there has to be a way to achieve the same programmatically as well.
I started exploring SPFile object model and voila, I found the SPFile.BackwardLinks property, see the MSDN article: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.spfile.backwardlinks.aspx

So my sample PowerShell code snippet does 2 things:

1. Gets an Inventory of all Page Layouts for a Publishing Site, see sample PowerShell script code :

$objSite = Get-SPSite "http://sharepointfix.com/sites/PageLayoutUsageReport"

[Microsoft.Sharepoint.Publishing.PublishingSite]$publishingSite = New-Object Microsoft.SharePoint.Publishing.PublishingSite($objSite)

#Gets all Page Layouts for the current Site Collection, no need to iterate all subwebs
$pageLayouts = $publishingSite.GetPageLayouts($false)

#I can make use of Export-CSV to generate a .csv file for reporting purposes. That works like a charm.
#But Powershell also provides you with a lovely Grid View interface, try doing something like this:

$pageLayouts | Out-GridView

## Of course, I will wrap the above script in a foreach block to iterate through all web applications and site collections in the SP 2010 Farm.

2. Get Page Layout Usage - The sample code now makes the use of SPFile.BackwardLinks property in order to find whether the Layout is in use or not

## Iterate through all the Publishing Page Layouts
foreach($layout in $pageLayouts)
{
      ## Get Page Layout File object
      [Microsoft.SharePoint.SPFile]$file = $publishingSite.RootWeb.GetFile($layout.ServerRelativeUrl);

#Backward links contain the URLs of all files that link to the current file from the current site collection and its # sites/subsites.
# Pages that link to the current file from another Web site or site collection are not included in the backward
# link information.

# returns $true, if SPFile.BackwardLinks.Count is greater than 0
 $IsPageLayoutInUse = ($file -ne $null -and $file.BackwardLinks -ne $null -and       $file.BackwardLinks.Count -gt 0)
}

The above script is just a primer to the wide array of possibilities that PowerShell offers as a tool.
I hope this post has helped you in someway or the other. Happy Programming :)

Powershell script to check whether SharePoint 2010 Feature is activated for a particular Sub-site

The Powershell script below checks whether any given feature, in this case its the PublishingWeb Feature is activated or not for a particular Sub-Site.


Here I have shown 2 approaches: 1st one checks the Feature Folder Name and 2nd approach checks the FeatureID activated for that particular Sub-site. Choose the way best suitable in your case.


Add-PsSnapin Microsoft.SharePoint.PowerShell


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


$featureFolderName = "PublishingWeb"
$subSiteURL = "http://sharepointfix/sites/home/USA"


#Approach 1 - Check using Feature Name
#Get Feature ID based on the Feature Name and ensure whether its already activated or not at the current sub-site scope
$FeatureID = Get-SPFeature -Web $subSiteURL | Where {$_.DisplayName -eq $featureFolderName}


if($FeatureID -ne $null)
     {
      #Approach 2 -  Check using Feature ID
      #Check whether Feature to be activated is already activated for this sub-site
      if (Get-SPFeature -Web  $subSiteURL | Where {$_.ID -eq $FeatureID.Id})
      {
       Write-Host $featureFolderName "is already activated at :" $subSiteURL 
       }
      else
      {
       Enable-SPFeature -Identity $featureFolderName -Confirm:$false -Url $subSiteURL 
       Write-Host $featureFolderName "has been activated at :" $subSiteURL
      }
     }
else
{
      Enable-SPFeature -Identity $featureFolderName -Confirm:$false -Url $subSiteURL 
      Write-Host $featureFolderName "has been activated at :" $subSiteURL 
}


Remove-PsSnapin Microsoft.SharePoint.PowerShell


Echo Finish

PowerShell script to Enumerate Sites, Sub-sites and print them in a .csv file

The Powershell script below enumerates/iterates through all Site Collections and Sub-sites for a Web Application and prints the output in a .csv file.

1. Copy the code below and modify the variables highlighted in yellow below, save the following as IterateAllSitesSubsites.ps1 file:


Add-PsSnapin Microsoft.SharePoint.PowerShell

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

$webApplicationURL = "http://dev-sp-2010:1000"
set-variable -option constant -name out -value "C:\PrintAllSitesSubsites.csv"

$webApp = Get-SPWebApplication $webApplicationURL

if($webApp -ne $null)
{
"Web Application : " + $webApp.Name | Out-File $out -Append

foreach($siteColl in $webApp.Sites)
{
   if($siteColl -ne $null)
   {
"Site Collection : " + $siteColl.Url | Out-File $out -Append

foreach($subWeb in $siteColl.AllWebs)
{
if($subWeb -ne $null)
{
#Print each Subsite
#Write-Host $subWeb.Url
"Subsite : " + $subWeb.Name + " - " + $subWeb.Url | Out-File $out -append
                 
$subWeb.Dispose()
}
else
{
Echo $subWeb "does not exist"
}
}
$siteColl.Dispose()
}
else
{
Echo $siteColl "does not exist"
}
}
}
else
{
Echo $webApplicationURL "does not exist, check the WebApplication name"
}

Remove-PsSnapin Microsoft.SharePoint.PowerShell

Echo Finish

2. To automatically run the above .ps1 script as a batch utility, Copy and paste the code below and save it with a .bat file extension

cd /d %~dp0
powershell -noexit -file ".\IterateAllSitesSubsites.ps1" "%CD%"
pause

Run the above .bat file, on the receipt of success message, traverse to the configured path and find the .csv file with all the sites/sub-sites printed.

Here is an alterante way of doing the same thing in a quick way using Powershell CMD:

Get-SPWebApplication "http://SPFix/" | Get-SPSite -Limit All | Get-SPWeb -Limit All | Select Title, URL, ID | Export-CSV C:\IterateAllSitesSubsites.ps1.csv -NoTypeInformation