The SharePoint Swiss Army Knife

SharePoint Governance… a topic beaten to death right? Luckily for you that’s not what I’m here to talk about today. Today, I’ll be talking about how we can run reports that can actually help us analyze and potentially enforce governance rules upon your awesome user community that unwittingly turned on versioning for all of their Document Libraries and conveniently forgot to include a version limit number while they continue to upload 50 MB PowerPoint slide decks with the same file name over and over again, creating a bajillion versions that go back 5 years… phew. Yes that was meant to be a run-on sentence. But it’s quite a common problem isn’t it? So how do we deal with this…

Since I work in an environment with too many Site Collections and Document Libraries to troll through manually, I’ve written a handy dandy PowerShell script to do all the work for me. This script will give me a report with the following information:

The PowerShell Script (Download Link)

# This script will generate a report of lists of type "DocumentLibrary" 
# and will give details on all of the files and their versioning attributes.
#
# Author: Henry Ong
######################## Start Variables ########################
$siteURL = "http://SiteCollection" #URL to the Site Collection you want to analyze.
$filePath = "D:\PowerShellScripts\AllVersionedLibraries.csv"
######################## End Variables ########################
if(Test-Path $filePath)
{
 Remove-Item $filePath
}
Clear-Host
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
# Creates an object that represents a SharePoint List
function CreateNewListObject
{
 $listObject = New-Object system.Object
 $listObject | Add-Member -type NoteProperty -Name ListTitle -Value $list.Title
 $listObject | Add-Member -type NoteProperty -Name ListURL -Value $list.DefaultViewURL
 $listObject | Add-Member -type NoteProperty -Name ListAuthor -Value $list.Author
 $listObject | Add-Member -type NoteProperty -Name ListItemCount -Value $list.ItemCount
 $listObject | Add-Member -type NoteProperty -Name ListVersioningMajor -Value $list.EnableVersioning
 $listObject | Add-Member -type NoteProperty -Name ListVersioningMinor -Value $list.EnableMinorVersions
 $listObject | Add-Member -type NoteProperty -Name MajorVersionLimit -Value $list.MajorVersionLimit
 $listObject | Add-Member -type NoteProperty -Name MinorVersionLimit -Value $list.MajorWithMinorVersionsLimit

 return $listObject
}
# Creates an object that represents a SharePoint List Item
function CreateNewListItemObject
{
 $listItemObject = New-Object system.Object
 $listItemObject | Add-Member -type NoteProperty -Name ListTitle -Value $list.Title
 $listItemObject | Add-Member -type NoteProperty -Name ListURL -Value $list.DefaultViewURL
 $listItemObject | Add-Member -type NoteProperty -Name ListAuthor -Value $list.Author
 $listItemObject | Add-Member -type NoteProperty -Name ListItemCount -Value $list.ItemCount
 $listItemObject | Add-Member -type NoteProperty -Name ListVersioningMajor -Value $list.EnableVersioning
 $listItemObject | Add-Member -type NoteProperty -Name ListVersioningMinor -Value $list.EnableMinorVersions
 $listItemObject | Add-Member -type NoteProperty -Name MajorVersionLimit -Value $list.MajorVersionLimit
 $listItemObject | Add-Member -type NoteProperty -Name MinorVersionLimit -Value $list.MajorWithMinorVersionsLimit 
 $listItemObject | Add-Member -type NoteProperty -Name ListItemTitle -Value $item.Name
 $listItemObject | Add-Member -type NoteProperty -Name ListItemAuthor -Value $item["Modified By"]
 $listItemObject | Add-Member -type NoteProperty -Name ListItemLastMod -Value $item["Modified"]
 $listItemObject | Add-Member -type NoteProperty -Name ListItemVersions -Value $versions
 $listItemObject | Add-Member -type NoteProperty -Name ListItemFileSize -Value $fileSize
 $listItemObject | Add-Member -type NoteProperty -Name ListItemFileSizeCorpus -Value $fileSizeTotal

 return $listItemObject
}
$site = new-object microsoft.sharepoint.spsite($siteURL)
$customListObjects =@()
$allWebs
= $site.AllWebs

foreach ($web in $allWebs)
{
 foreach ($list in $web.Lists)
 {
  if($list.BaseType -eq "DocumentLibrary")
  {

   $customListObject = CreateNewListObject
   $customListObjects += $customListObject 

   if($customListObject.ListVersioningMajor -eq $true -or $customListObject.ListVersioningMinor -eq $true)
   {
    foreach($item in $list.items)
    {
     $versions = $item.versions.count
     $itemID = $item.UniqueId
     $file = $web.GetFile($itemID)
     $fileSize = $file.TotalLength/1000000
     $fileSizeTotal = $fileSize*$versions

     $customListItemObject = CreateNewListItemObject
     $customListObjects += $customListItemObject
    }
   }

   Write-Host $list.Title
   Write-Host $web.title
  }
 }

 $web.Dispose()
}
$site.dispose()
# Exporting the data to a CSV file
$customListObjects | Select-Object ListTitle,ListURL,ListAuthor,ListItemCount,ListVersioningMajor,ListVersioningMinor,MajorVersionLimit,MinorVersionLimit,ListItemTitle,ListItemAuthor,ListItemLastMod,ListItemVersions,ListItemFileSize,ListItemFileSizeCorpus | Export-Csv $filePath
write-host "Done"

Analyzing the Report

So once the report is run, I typically delete the first line with junk data that says “#TYPE Selected.System.Object” (anyone know how to not have that outputted?) and then convert everything into a table. That’s done by selecting all of the data (CTRL-A) and then going to the Insert tab of the Excel Ribbon and then selecting Table and include headers. Now with this information in a sortable/filterable format, I can go through any of the columns to better determine which Document Libraries or documents are potential problems. For example:

1. I’ll first filter on “ListVersioningMajor=TRUE” to figure out which ones have versioning turned on.

2. Then I can filter on “MajorVersionLimit” to figure out which ones don’t have a limit in place.

3. And then I’ll sort the “ListItemVersions” column to figure out which documents have a ton of versions.

Now that’s just one way to look at this report to figure out next steps, I’m sure you’ll find other pivots on this data that you’ll find useful.

Key Take Aways

Just having a governance plan in place is not good enough. Your governance plan needs to be actionable and enforceable. Using scripts like this to provide both proactive and reactive reporting can help you and your Site Owners by providing data-driven firepower to effectively manage your SharePoint environments. What am I doing with this data? Depending on the scenario I can:

1. Send the report to the Site Owner to mull over and figure out where and how much they want to limit the versioning or…

2. I can say hey, it looks like you’re not using versioning at all. Mind if I turn it off? And that’s a post for next time – How to automatically change the versioning settings for all Document Libraries in a Site Collection.