Sunday, May 6, 2018

Locating where a term set is used in SharePoint using search

Joanne Klein asked on Twitter if there is a way to get all places where a specific term set is used in SharePoint.


One way would be to iterate all site collections and check if the term set was present in the Hidden Taxonomy List, which stores all used terms on a site collection (and if you have no idea what that is, that’s ok as well).

Hidden Taxonomy List at /Lists/TaxonomyHiddenList/AllItems.aspx

Another option which is the solution I proposed, is to use search. Of course you need to have read access to the items where the term set is used for it to be 100% accurate as well items using terms from a term set has to be present in the search index.

Note: If you programmatically use app-tokens, you can have god-search mode – which is very scary and dangerous indeed.

The idea is to at least find all site collections for starters where a term set is being used. One little known fact is that when you add a metadata column to a list or library, in addition to the label of the term, both the term id and the term set id is also made searchable. This means that if you free-text search for the id of the term set, and find all unique site URL’s, you’re good to go.

I first locate the id of the term set in question from the term store management page:


This term is then used as your search query. You can search any way you like, but I’ll use PnP PowerShell. And to get unique Site URL’s I’m using a collapse specfication of SPSiteURL:1. This means return one item only per unique value in the managed property SPSiteURL. You can also add a refiner for SPSiteURL, but if you have tons of site collections, you could risk not getting all values.

The following PnP PowerShell command will list all site url’s where the term is used (collapse spec is available in the dev branch or will be available in the June release of PnP PowerShell).

Submit-PnPSearchQuery -Query "1e4ec7de-f5ad-49f7-a268-45e8d6b3fa64" `
-CollapseSpecification "SPSiteUrl:1" -RelevantResults `
-SelectProperties "SPSiteUrl" | select SPSiteUrl


So in total, 11 site collections are using the Company term set, including the content type hub.


Sometimes you need to get an overview of where a term or term set in SharePoint is being used, and as for many other things SharePoint, search is a ubiquitous tool which often solves the task at hand. This time the crux is to utilize the little known feature of collapse specifications, in order to find each site collection where a term set has been used.

You could take this a step further and put the results into a Power BI report, as it’s possible to do REST search queries against SharePoint from Power BI. It’s not super simple, but doable, and maybe I’ll write about that in a later post.