Retrieving All Changes Done to a SharePoint List using PowerShell

In this article, we will learn how we can use PowerShell to retrieve all changes that have been done to any SharePoint list of document libraries. We will extract information about what the change was (Update settings, Add document, etc), who did the change, as well as when the change was done. We will also be introducing the concept of a PowerShell class. Classes have been around since the early versions of PowerShell, but they always required you to explicitely load some of the C# engine into your scripts.​ With PowerShell version 5 (will be using the November 2014 preview for this article), classes have become a main citizen of the PowerShell language.

If you are familiar with the C# language, then learning PowerShell classes will be a no brainer, if you are not, then let us spend a few seconds explaining the major concepts of what a class is in the Object-Oriented world. A class can be used to represent any entity, physical or not. It contains a set of attributes and methods that can be perform on that entity. As an example, think about a Laptop Computer. If we were to write a class to represent this object, what properties would it have? It would probably contain information about it’s screen dimension, the amount of RAM and disk space it has, its color, its weight, etc. Now, if we were to list its method, we would probably be looking at having methods such as: Turn on, Shut down, Open/Close lid, etc.

For the purpose of this article, we will be writing a custom class that will expose information about a specific change that happened to a document library. The SharePoint Object model already contains an object named SPChange that contains some level of information regarding a change to a SharePoint entity. However, this object is missing one very important data point: ModifiedBy. Because of this, if we wish to capture all information that pertains to a change, we will need to create our own object. Classes in PowerShell are declared by using the reserved keyword class. In most cases it will contain a special method called the constructor, which is used to instantiate an instance of our class. It will list several properties, along with their data types (optional).

The class we will be creating for our example will contain 6 properties that will each hold information about a data point related to a change instance:

To code to bring this class to life will look like the following:

class ChangeLogEntry{
$ChangeScope = $null
[String] $LastModified = “Null”
[String] $ChangeType = $null
[String] $ModifiedBy = “Null”
[String] $ItemName = “Null”
[String] $EntryId = $null

    ChangeLogEntry(){}
}

Please note that I have left out the Data Type ([String]) for the ChangeScope variable on purpose to show you that it is optional to specify it in classes. It will still work ieven f you don’t specify it. Now, if you were to simply create a Powershell script containing only the code above, the script will still execute, but nothing will happen. In order to call into this class, you need to explicitely call its constructor method. The following line of code shows you how to call into our constructor method for our custom class:

$logEntry = [ChangeLogEntry]::new() 

This code will instantiate a new ChangeLogEntry object in memory and assign it into the $logEntry variable. We can then get and set each of the object’s properties as the following:

$logEntry.ModifiedBy = “Nik Charlebois”

$logEntry.ItemName = “MyDoc.Docx”

[…]

Let us now jump into the core of the subject: How to retrieve changes to a SharePoint list or document library using PowerShell. In the SharePoint object model, the SPList objects expose a method called GetChanges() which returns a collection of SPChange objects. The following screenshot show the result of exposing this collection of SPChange objects in PowerShell.

We can see from the above screenshot that the result is pretty much cryptic. What will will do, is obtain a reference to the collection of SPChange object, loop through each of them and then simply extract the relevant information from it, and assign it to a new ChangeLogentry object. In each loop, we will need to determine wether or not that change is at the List or Document level. This decision will be made based on wether or not the SPChange object has a value in its UniqueId field. For eaxample, let us look at the two entries in the following screenshot:

The first entry has a value in it UniqueId field, which indicates a change on a document, whereas the second doesn’t which indicates a change at the list level. Putting it all together, we obtain the following PowerShell script:

Add-PSSnapin Microsoft.SharePoint.PowerShell

$url = Read-Host “URL for the web”
$listName = Read-Host “List Name”

$web = Get-SPWeb $url
$list = $web.Lists[$listName]
$contentDBName = $web.Site.ContentDatabase.Name
$changes = $list.GetChanges()

#$changes | Select ChangeType, Time

$instance = “localhost”
$dbName = $web.Site.ContentDatabase.Name
foreach($change in $changes)
{
if($change.ListId -ne $null)
{
if($change.UniqueId -ne $null)
{
# We have a Document Event
$query = “SELECT ItemName, ModifiedBy FROM EventCache WHERE DocId = ‘” + $change.UniqueId + “‘”
$results = Invoke-SQLCmd -Query $query -ServerInstance $instance -Database $dbName

foreach($result in $results)
{
$logEntry = [ChangeLogEntry]::new()

if($result.ModifiedBy.Length -gt 1)
{
$logEntry.ModifiedBy = $result.ModifiedBy
}

                if($result.ItemName.Length -gt 1)
{
$logEntry.ItemName = $result.ItemName
}
$logEntry.ChangeType = $change.ChangeType
$logEntry.ChangeScope = “Document”
$logEntry.LastModified = $change.Time
$logEntry.EntryId = $change.UniqueId
$logEntry
}
}
else
{
$logEntry = [ChangeLogEntry]::new()
$logEntry.ChangeType = $change.ChangeType
$logEntry.ChangeScope = “List”
$logEntry.LastModified = $change.Time
$logEntry.EntryId = $change.ListId
$logEntry
}
}
}

class ChangeLogEntry{
$ChangeScope = $null
[String] $LastModified = “Null”
[String] $ChangeType = $null
[String] $ModifiedBy = “Null”
[String] $ItemName = “Null”
[String] $EntryId = $null

    ChangeLogEntry()
{}
}

The script will need to connect to the content database associated with the site collection containing the document library. In our case, we are trying to obtain a list of all changes made to a documents library named Documents that exists at the root of our site collection at http://localhost. The Content Database contains a table named EventCache that contains information about all changes that happened in our SharePoint site collection. We will be using the PowerShell Invoke-SQLCmd cmdlet in order to retrieve information out of the database and assign it to our custom object.

Save this code into a PowerShell script (.ps1) file and execute it.  The following screenshot show the result of the script’s execution for my document library.

We can see from the above screenshot that the information is now way more readable than it was when simply looking at the SPChange objects.