Toggle audience targeting using PowerShell

Audience targeting in SharePoint is a great way of hiding irrelevant information from users and likewise, only show relevant information. You could create a list with a lot of items and target those items to specific groups of users. Users who are targeted will see the items while other users will not see them… audience targeting. This has nothing to do with permissions. It’s just a filter. Users who are not targeted can still access those items if they want.

To be able to use audience targeting on a list, you have to enable it first. It’s disabled by default. To enable this, you can go to the list settings of your list and open the Audience Targeting settings. There you will find a checkbox to enable this.

Once it’s enabled, you will see an extra column in your list “Target Audiences” of the type “Audience Targeting”.


To disable it, go back into the audience targeting settings and uncheck the checkbox. The extra column will be removed.

That’s the UI way… now, how about PowerShell? Can you enable/disable audience targeting? Offcourse!

We only need to add a field with a specific ID and attributes to the list and it’s done.

Read more

Get Web Template Usage in SharePoint

Having a SharePoint platform which is widely adopted and used is cool. But as a SharePoint administrator I’m also interested in how this platform is used and where it’s used for. One of the things I like to do is to find out which kind of sites are used and how many of them exist.

This can also be quite useful when you are considering a migration to a new SharePoint version. Not all site templates are supported anymore in SharePoint. For example, the document workspaces, meeting workspaces, and group work templates. These are partially supported when migrating from SharePoint 2010 to SharePoint 2013 or are just supported for backward compatibility. At the same time, there are also custom solutions out there which add site templates and it’s good to know when preparing for a migration, if you have sites which are based on those kind of templates.

The tricky thing is… how to get that information. There’s a cmdlet which is called Get-SPWebTemplate. This gives you a list of all web templates which have been installed in the environment.


This doesn’t give you which templates are actually used. For this, you need to combine some information. The name of a web template consists of 2 parts:

  • Web Template
  • Configuration ID

When you look at the properties of an SPWeb object, you find these 2 specific properties in the list of properties. You need to combine these 2 with a “#” between them to have the webtemplate.Get-SPWebTemplate1

This allows you to identify the template for each web which exists in your farm. But this is only part of the info we need. We need the usage count of each used template and get something like this:


To get this output, I wrote a small script.

If you would like to sort the output of the script to have the most used templates first, you can execute it like this:

which gives you the following:



Get a document from an unattached SharePoint content database

Retrieving a lost document in SharePoint is easy. You go to the recycle bin and you restore it. That is, if it hasn’t been deleted more than 60 days ago. But what do you do when you need to retrieve a specific document which has been overwritten with an older version and yes, Murphy is in town… you don’t have versioning enabled?

In that case you better be having backups of the content database which contains the site where the document is stored because you need to retrieve the document directly from a backup of the content database.

Now, restoring a backup of a content database is probably not something you want to do to get a specific document because you will restore all sites in that content database to a previous state. 1 happy user vs. hundreds of angry ones… ouch! But if you don’t restore it, how are you going to get a good copy of the document?

Well, you need to restore the content database under a different name. Once it has been restored, you can use PowerShell to access it as an “unattached content database” where you can access the content as if it was attached to your web application.

The Get-SPContentDatabase cmdlet has a -ConnectAsUnattachedDatabase parameter which gives you a reference to an unattached content database.

Once you have the database reference, you can use the Get-SPSite cmdlet to get your site reference from that database.

From there on, you need the web and the list where the document is stored and offcourse, the document name. With all of this information, you can get a reference to the document item in the database. Only thing left to do is to extract this item as a document.

I combined everything into one convenient script which takes a number of parameters and get you the item from the database.

Read more

Export/Import the SharePoint Root Authority Certificate using PowerShell

Installing SharePoint is mostly a repetitive process with lots of small tweaks and actions. One of those small actions you need to do after adding a server into a SharePoint farm, is adding the “SharePoint Root Authority” Certificate to the Trusted Root Certification Authorities store of the server. You would think this happens automatically during the configuration process. Well, no.

The result is that when you add a server to an existing farm, or you create a new farm, SharePoint will add 3 certificates to the “SharePoint” certificate store on the server.


All of these certificates will have a status: “The issuer of this certificate could not be found”.


Is this a big problem? Does it break SharePoint? Well, no. SharePoint will work happily without it, but users can experience delays when logging into a site, performing a search and even experience HTTP timeouts when doing these things.

The reason is that since the certificate chain is not complete, the CRL (Certificate Revocation List) check is done over the internet. If the CRL server cannot be contacted (let’s say, due to the isolation of the server from the internet), the operation will time out after 15 seconds and the rendering of the page will happen after those 15 seconds. At the same time, 2 events are logged in the eventlog, which can be found in the CAPI2 eventlog. You need to enable the CAPI2 event logging first to see them.

This behaviour is well documented in KB2625048. This article is for SharePoint 2010, but it’s also valid for SharePoint 2013 and it provides 2 workarounds.

To fix this, 2 actions are required:
– Export the SharePoint Root Authority certificate from SharePoint
– Import it into the local certificate store

These tasks are outlined in the KB article but because it involves point and click, wizard style… I created a small script for this task, just because I can! And I really hate doing these kind of things every time again. It costs time, it costs money, and it’s so much more fun creating scripts to make you more efficient in what you do.

SharePoint 2013 Trial License – License Upgrade FAIL

I consider myself as someone who knows a lot of SharePoint. I don’t know everything and I’m learning new things every day. Which is good because this motivates me. One of those things I learned (the hard way) in the last couple of weeks is about licensing and the consequence of using a SharePoint TRIAL license. Let me elaborate a bit on this.

Last year, I installed 2 SharePoint farms at a client. When you install SharePoint, the first thing you need to provide, is the license key. Because nobody was able to provide me the key at that time, I used a trial key which is valid for 180 days. You can get this key over here.

A few weeks ago, I was notified that a Microsoft audit was on the way concerning licensing and they ran the Microsoft Assessment and Planning Toolkit to have an overview of the licenses. The result was that all involved SharePoint servers were identified as ENTERPRISE servers. This was a problem since they don’t have Enterprise licenses and were expecting Standard servers.

I was convinced I never activated any Enterprise features in both environments and ran some PowerShell scripts to see on all kind of levels if Enterprise features were active and to my surprise, they were activated on all sites. Since none of those features are used, I proceeded to deactivate all of them.

The report was created again… still Enterprise servers.

Then I remembered that I used a trial key and the Upgrade License page in Central Administration allows you to see the current license and replace that license with a different license.

And there was the culprit… the Trial key for SharePoint 2013 is a “SharePoint Server Trial with Enterprise Client Access License

My first reaction was: “I never saw an option to choose between a Standard and Enterprise Trial”. And that’s correct… there’s only 1 trial and that’s Enterprise!

I checked the page where you can find the evaluation version and you won’t find any reference or notification of the fact that the trial version is an Enterprise license!!!! Come on!? Why not? The only possible hint of this being an Enterprise version is the mentioning of “full-featured” in the Preinstall Information section. But that’s interpretation, right?

One would think that you could simply put in your Standard license key and “upgrade” that Enterprise trial to a Standard server license, right? Well, I wish it was that simple. enterprisetrial

It’s simply not possible. You cannot replace the Trial license with a Standard Server license. It expects an Enterprise server license key. Somehow this makes sense. When you look at SQL Server, the same thing applies. You can’t downgrade an Enterprise Edition to a Standard Edition either.


Well, not many I’m afraid. The only viable option I found was to uninstall SharePoint completely. And by uninstalling, I’m referring to this. That’s removing the binaries from the servers and reinstalling them. Upon installation, provide the Standard Server License key and get that show on the road. Luckily, you can hook up the databases from the old trial environment without issues.
This is such a situation where having a detailed installation/configuration documentation pays off. I make a habit of documenting everything in full detail with screenshots in OneNote, with the scripts I use and the parameters which are used for them. This makes it very easy to redo it if needed. So, doing this reinstallation was a breeze for me.

But I can imagine that if you don’t have such documentation or you have it but some wannabe professionals came in and started modifying things manually, not documenting a thing… you might be in a world of hurt.

As a seasoned SharePoint professional, I have to admit that I was a bit shocked of the fact I didn’t know this. This seems like a “Duh! Basic knowledge!” kind of thing. Definitely not my best day when I found out about this. 😳


Customize SSRS extension settings in SharePoint Integrated Mode

SQL Server Reporting Services (SSRS) is powerful way of presenting reports and data to your end users in SharePoint. But sometimes, the out of the box experience of some features of SSRS doesn’t work for your situation. A nice example of this is the field delimiter for CSV exports. This is by default a comma. But in some regions (like mine), the default delimiter is a semicolon and using a comma doesn’t work.
Another example is the different formats which are available for exporting. What if you want to remove some of those supported export formats?

When you start searching the net, you will find some information on how to achieve the above customizations by adding or changing some settings in the RSReportServer.config file. Check the link below for the information on that configuration file.

While this works fine for a regular SSRS installation, it will not work for a SharePoint integrated SSRS installation because you don’t have that config file on your system.

The solution for this is PowerShell. All of those customizations can be done this way by means of changing SSRS extension settings.

Change the CSV field delimiter

Remove export formats from the supported list

The script above removes the “TIFF” and “MHTML” formats from the list of available export formats. The complete list of formats which you can include or exclude is the following:

  • XML
  • CSV
  • ATOM
  • PDF
  • RGDI
  • RPL
  • WORD
  • HTML4.0

If you want to add it to the list, specify “True” as value for the “Visibility” element in the -ExtensionAttributes parameter.

Process items in a Drop Off library using PowerShell

Everyone knows the Drop Off library in SharePoint, right? It routes documents to a final destination, based on rules you define.
This works OK for documents which are uploaded manually to the Drop Off library but I found out that it doesn’t work for documents which are uploaded with PowerShell (or any other method which uses the object model). Even if I fill in all required metadata and make sure the document it checked in… it still won’t leave the Drop Off library.

Seems that there’s a timer job in SharePoint (Content Organizer Processing) which runs daily and processes items which are left behind in Drop Off libraries. That’s great but what if you want to process that document immediately?

I found an article from Steve Lineberry which explains how to do this in C#. He used reflection to break open the timer job and found out that it was calling an internal method.

I needed this in PowerShell, so I did some translation and created a PowerShell function which does the same.

SharePoint 2013 Promoted Links Wrapping

Earlier this week, I was creating a page in SharePoint which contained 2 Promoted Links web parts. One of those web parts had 12 links. The annoying thing about this web part is that there’s no wrapping when the tiles are displayed. If you have a limited number of links, you won’t have an issue. But if you have 12, like me, not all tiles will fit the screen and you will get scroll buttons. Not nice.


One of the solutions to have the tiles wrapped is Javascript. I found a script over here.

This script works well but has a limitation… it only works when you have 1 Promoted Links web part on your page. Once you have more, the results are somewhat… unpredictable, depending on the number of combined tiles of all the web parts.

Since I know a little of Javascript and JQuery, I figured… hey, why not enhance it and make it work for more web parts on a single page.

The results…

promotedLinks2 Looks a lot better, no? 😎

The script looks at each web part individually and wraps the tiles independently within the web parts.

Here’s the updated script.

I’m not an expert in JQuery, so if you like this script and you have suggestions to improve it, please get back to me so I can improve it and give the proper credits.

Filter a View on Liked By in SharePoint 2013

SharePoint 2013 allows users to rate individual items in a list or library. This rating can be in the form of a “star” rating from 0 to 5 or in the form of “likes”. This is disabled by default for a list or library.

The rating data is stored in 5 columns which are added to your list or library when you enable this functionality:

  • Number of Ratings
  • Number of Likes
  • Rating (0-5)
  • Rated By
  • Liked By

The columns “Number of Ratings”, “Number of Likes” and “Rating (0-5)” are visible to the end user. The other 2 columns “Rated By” and “Liked By” are hidden.

This information is transformed by SharePoint in a nice view which makes it somewhat sexier for the end user. You can see this in the screenshot below where the rating information is visualized in the “Favorited” column of my library.


The user will see a “Like” or “Unlike” link. At the same time, the number of likes is displayed together with a smiley.

So far so good.

Imagine you want to use this rating information to filter the list or library to show you only those items which have been liked or rated by you.

The columns you need for this information is the “Liked By” and “Rated By” columns. These columns are collections of users. The problem with this is that those 2 fields are not available for filtering when you use the UI.

You can use CAML to create a query which returns you only those items where the current user is contained in one of these fields.

The query above returns items which are liked by the user who executes the query.

When you look at the SPViewCollection.Add method, you notice that it accepts a “query” parameter.

So, to have a view filtered using the query, we can simply create the view using PowerShell.

This works… on premise.
But what if you have a SharePoint Online. You can still use PowerShell but you need a slightly different approach because instead of using the server object model of SharePoint, you need to use the client object model. You can use something like below to do the same on SharePoint Online. I must admit, I’m not an expert on CSOM with PowerShell (yet :mrgreen: ) and I’m still getting up to speed with this. I suppose the code below can be simplified. But hey… it gives you an idea on the general approach what you need to do to create a view with a query on a library which is hosted on a SharePoint Online .