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:

My Report Output Header

Description

ListTitle The title of the SharePoint list with BaseType = DocumentLibrary
ListURL The server relative URL of the SharePoint list. This script assumes you’re working with one Site Collection at a time.
ListAuthor The person that created the list.
ListItemCount The number of items in the list.
ListVersioningMajor Whether or not major versioning is enabled for this list.
ListVersioningMinor Whether or not minor versioning is enabled for this list.
MajorVersionLimit The number of major versions to keep if limited.
MinorVersionLimit The number of minor versions to keep if limited.
ListItemTitle The name of the list item/document being reported on.
ListItemAuthor The last person that modified the list item/document being reported on.
ListItemLastMod The last modified time stamp for the list item/document being reported on.
ListItemVersions The number of versions (major and minor) associated with the list item/document being reported on.
ListItemFileSize The file size for the current version of the list item/document being reported on.
ListItemFileSizeCorpus The aggregated storage space being used by all versions. Note: This is not an exact number but a simple estimate of the size of the current version multiplied by the number of versions associated with the list item/document being reported on.

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.

Advertisements