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:

  • ChangeScope: Will have a value of Document if the change is related to a document in the document library or List if it is related to the list itself;
  • LastModified: Will contain the date and time at which the change occured;
  • ChangeType: Will contain information about the type of change (Update, Delete, Add);
  • ModifiedBy: Will contains information about who did the change;
  • ItemName: Will contain information about the name of the item (document) if the change has a ChangeScope of Document;
  • EntryId: Will contain information about the GUID of the change. Note that this will not be unique. It acts king f like the Correlation Id works. It is a way to regroup changes together;

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.

20150125.png

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:

20150125-02.png

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.

20150125-03.png

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.

Get Search Results in SharePoint 2013 using PowerShell

I’ve started playing with some of the search APIs in SharePoint 2013 lately, and wanted to transpose my experiments into PowerShell to see just how easy it was to do. I have been developping a C# client that runs directly on the server, prompts the user to enter a keyword and lists all search results received back from the SharePoint Search Service Application. I managed to replicate this client application in PowerShell by creating a 10 lines script. I basically converted over the C# code into PowerShell.

Before getting started, we need to make sure we load the Microsoft.Office.Server.Search.Query Assembly within our PowerShell session. Our code will by using objects from that namespace.

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.Office.Server.Search.Query”)

My code uses the Microsoft.Office.Server.Search.Query.KeywordQuery object to retrieve search results back. This object takes in a reference to a Site Collection to perform the search against.

$site = Get-SPSite http://localhost

$keywordQuery = New-Object Microsoft.Office.Server.Search.Query.KeywordQuery($site)

Next step is to prompt the user to input a keyword for the search results. Once obtained, we will assign this keyword to QueryText property our newly created KeywordQuery object.

$keyword = Read-Host “Search Term”

$keywordQuery.QueryText = $keyword

We now have an object that represent our query to send to the server, but in order to execute this Query, we need to instantiate a second object, the Microsoft.Office.Server.Search.Query.SearchExecutor. This object exposes a method name ExecuteQuery() to which we wil pass our query object in order to get search results back.

$searchExec = New-Object Microsoft.Office.Server.Search.Query.SearchExecutor

$searchResults = $searchExec.ExecuteQuery($keywordQuery)

The last step for us is to look at the Table property of the returned object in order to get a list of items that have been identified as matching our search criterias. For clarity’s sake, we will only be exposing the Title, Path, Author, LastModifiedTime and IsDocument property of each search result.

$table = $searchResults.Table

$table | Select Title, Path, Author, LastModifiedTime, IsDocument

So to prove that my code works, I will execute a search using the SharePoint Search interface, search for the word “Gadgets”. Here is what the interface returns for results.

20150110.png

We can see from the screenshot above that search for the word “Gadgets” returns 2 results. Now, let us put our PowerShell script together:

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.Office.Server.Search.Query”)
Write-Host “`r`n”

$site = Get-SPSite http://localhost
$keywordQuery = New-Object Microsoft.Office.Server.Search.Query.KeywordQuery($site)
$keyword = Read-Host “Search Term”
$keywordQuery.QueryText = $keyword
$searchExec = New-Object Microsoft.Office.Server.Search.Query.SearchExecutor
$searchResults = $searchExec.ExecuteQuery($keywordQuery)

Write-Host “`r`n”
$table = $searchResults.Table
Write-Host $table.Length” Results Found” -BackgroundColor “Green” -ForegroundColor “Black”
$table | select Title, Path, Author, LastModifiedTime, IsDocument

Now, if we were to execute the above PowerShell script against my local SharePoint farm, here is what the result would look like:

2015011002.png

We have therefore been able to obtain the exact same results for our SharePoint 2013 Search Web interface compared with our PowerShell version. This is a neat little script that could prove to be very useful in many scenarios.

Manage Health Analyzer Rules in SharePoint 2013 using PowerShell

We all know and hate the Health Analyzer that comes with SharePoint 2013 out-of-the-box.​ The Health Analyzer is a nice concept that Microsoft introduced back in SharePoint 2010, but it quickly becomes more annoying than anything. It is there to help you keep in line with the best practices that have been established by Microsoft, but for dev environments it is probably not something you wish to use. I know that personally, all of the development machines I have built all have that good all red notification bar at the top of Central Administration.

20150106-01.png

Most of the time, at least for me, the same errors always come back: “Databases exist on servers running SharePoint Foundation”, “The current server is running low on memory”, etc. Even if the fact that these errors show up in Central Administration all the time is not critical and does not affect performance, it still is freaking annoying to me. So I decided to go and use PowerShell to disable some of the Health Analyzer Rules in my dev environments so that they don’t show up anymore.

The first thing for you to know is that in order for you to acquire a list of all Health Analyzer Rules that exist on the server, you need to call the Get-SPHealthAnalysisRule PowerShell cmdlet. This returns a complete list of all rules on the server. On my current dev environment, running the December 2014 CU, I have a total of 72 rules defined. In my case I am specifically looking to disable the “Database exist on servers running SharePoint Foundation” rule, because all of my dev machines are mainly a Single Farm instance having the SQL Server running directly on the SharePoint Server. In order for me to find the rule I am looking for, I can use a wildcard Where query on the list of all rules, querying on the summary property which represents the description of the error:

Get-SPHealthAnalysisRule | Where{$_.Summary -like ‘*Databases exist on servers running SharePoint Foundation*’}

20150106-02.png

In order for us to disable it, we need to assign this result to a variable, and then set its Enabled property to false. Note that you could also use the Disable-SPHealthAnalysisRule cmdlet.

$rule = Get-SPHealthAnalysisRule | Where{$_.Summary -like ‘*Databases exist on servers running SharePoint Foundation*’}

$rule.Enabled = $false

$rule.Update()

20150106-03.png

Now, you need to understand that simply disabling a Health Analysis Rule is not automatically going to remove its associated errors from the list in Central Administration. As we can see in the next screenshot, I still have my error displayed in the list. First off, you’ll need to go and delete the error entry from the Error List in Central Administration, for this example, I’ve done it manually.

20150106-04.png

What we need to do now is to run the timer job responsible for checking the Health Analyzer Rules. Problem is, there are about 25 timer jobs related to the Health Analyzer to choose from.

20150106-05.png

Now, you can try to go and guess which one to run by yourself, but I prefer to do it using a method of mine called “Brute Force”.  I simply get a reference to all timer jobs responsible for the Health Analyzer, loop through each of them and run them on the spot. The following line of PowerShell will allow you to achieve this. It gets all timer jobs that have the word “Health” in their names, and assigned them to a variable, then we loop through each entry in that variable, and call the RunNow() method on each.

$healthJobs = Get-SPTimerJob | Where {$_.Name -like ‘*Health*’}

foreach($healthJob in $healthJobs){$healthJob.RunNow()}

Once this has been executed, we should be able to see that the error is no longer in our list in Central Administration.

20150106-06.png

Initiate the Visio Graphics Service Application in SharePoint 2013 using PowerShell

​In this article, we will learn how we can generate a new instance of the Visio Graphics Service Application in SharePoint 2013 using PowerShell. Currently, SharePoint 2013 (with the December 2014 Cumulative Update) offers 13 different PowerShell cmdlets that are related to the Visio Graphics Service. If you are ever interested, this full list of cmdlets can be obtained by running the following line of PowerShell:

Get-Command | Where{$_.Name – like ‘*SPVisio*’}

20150104-01.png

In order to instantiate the Service Application, the first step if to obtain a reference to a Service Application Pool. In our case, we are simply going to go and create a new one entirely and we will name it “Visio Services” for clarity’s sake. This can be achieved by calling the following line of PowerShell:

New-SPServiceApplicationPool -Name “Visio Services” -Account “Contoso\Administrator”

20150104-02.png

Once the Service Application Pool has been created, we are ready to move on to the actual Service Application instance creation. For this to happen, simply type in the following line into the SharePoint PowerShell console. This will actually create the Service Application instance, and assign it to a variable. Note that this operation may take up to a few minutes to complete.

$visioServiceApp = New-SPVisioServiceApplication -ApplicationPool “Visio Services” -Name “Visio Service Application”

We can see from the figure below, that at this point, we have our Visio Graphics Service Application entry shown in Central Administration. Let us now create the Service Application Proxy associated with this Service Application entry.

20150104-03.png

The Service Application entry can be created by calling the following line of PowerShell, passing it the variable that contains the Service Application instance we created in the previous step.

New-SPVisioServiceApplicationProxy -ServiceApplication “Visio Service Application”

20150104-04.png

Then looking at the central administration Service Application page, we can now see that the proxy instance has been created.

20150104-05.png

We’re almost done. Now that we have our Service Application and Proxy up and running, we still need to enable the Visio Graphics Service. To achieve this, we need to enter the following lines of PowerShell. This will get a reference to the Visio Graphics Service, and pass it to the Start-SPServiceInstance cmdlet which will enable it.

$visioService = Get-SPServiceInstance | Where{$_.TypeName -like ‘*Visio*’}
Start-SPServiceInstance $visioService

To confirm that everything is up and running, navigate to the “Services on Server” page in the Central Administration and ensure the Visio Graphics Service is marked as started.

20150104-06.png

You should now be all set and ready to use the Visio Graphics Service Application to interact with your Visio diagrams in SharePoint!