Thursday, 6 March 2014

Get a List of Fields in a Site Collection that are using a Managed Metadata TermSet

Ever wondered how many fields are referencing a Managed Metadata Termset? It's going to be a long and boring job using the Web UI to click through every web... and every list in every web... and every field in every list, looking for all the fields referencing a particular termset. Just writing that in a sentence was long enough!

This is the sort of job where PowerShell really shines!

The example below demonstrates creating a script (with a number of functions) to recurse through a site collection, creating a report of all the fields using a termset.

Just take me to the Microsoft TechNet Gallery, so I can download the script:  Find all SPFields that are using a Managed Metadata TermSet

The basic PowerShell used to check a field is:

$termSetId = "e07cab2f-ef85-473e-a4a7-1104b5daf192"            
$field = (Get-SPWeb "http://mdysp13").Lists["Documents"].Fields["Country"]            
if($field.GetType().Name -eq "TaxonomyField"){            
 if($field.TermSetId.ToString() -eq $termSetId){            
  Write-Host "Houston, we have a match!" -foregroundcolor darkyellow;            
 }            
}

Or, for a collection of fields:

$fieldCollection = (Get-SPWeb "http://mdysp13").Lists["Documents"].Fields            
$termSetId = "e07cab2f-ef85-473e-a4a7-1104b5daf192"            
foreach($field in $fieldCollection)            
{            
 if($field.GetType().Name -ne "TaxonomyField"){            
  continue;            
 }            
 if($field.TermSetId.ToString() -ne $termSetId){            
  continue;            
 }            
 #if we get to here, we have a match!            
}

I hear you say, "That's awesome Matt, but where the hell do I get the Taxonomy TermSet ID from?!"

Well, that's quite easy.

$w = Get-SPWeb "http://mdysp13";                        
$tsession = Get-SPTaxonomySession -Site $w.Site;                        
$tsession.GetTermSets("Countries",1033) | FT Name,ID
#Or, if you want to get a term set based on the SPWeb's default language ID            
$tsession.GetTermSets("Countries",$w.Language) | FT Name,ID

Pretty cool huh?

If you want to get a list of all the termsets, then you can write a simple function to return all the termsets as a list.

function List-AllTermSets{            
 [CmdletBinding()]            
  Param(             
    [parameter(Mandatory=$true, ValueFromPipeline=$true)][Microsoft.SharePoint.SPWeb]$web            
   )            
 $termSetInfo = New-Object psobject            
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "Store" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "StoreId" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "Group" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "GroupId" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "TermSet" -value ""
 $termSetInfo | Add-Member -MemberType NoteProperty -Name "TermSetId" -value ""
             
 $tsession = Get-SPTaxonomySession -Site $web.Site;            
 $tstores =  $tsession.TermStores;             
 $list = @();            
 foreach($tstore in $tstores)            
 {            
  $tgroups = $tstore.Groups;            
  foreach($tgroup in $tgroups)            
  {            
   $tsets = $tgroup.TermSets;            
   foreach($tset in $tsets)            
   {            
    $tinfo = $null;            
    $tinfo = $termSetInfo | Select-Object *;            
    $tinfo.Store = $tstore.Name;            
    $tinfo.StoreId = $tstore.ID;            
    $tinfo.Group = $tgroup.Name;            
    $tinfo.GroupId = $tgroup.ID;            
    $tinfo.TermSet = $tSet.Name;            
    $tinfo.TermSetId = $tSet.ID;            
    $list += $tinfo;            
   }            
  }             
 }            
 return $list;            
}

So, what if I want all of this scripted? A function I can call that generates a report. Well, prepare to roll up your sleeves and poise your fingers over the Ctrl+C key combo!

We need a couple of functions for this,performing the following tasks;

1. A function to get a list of all the taxonomy (managed metadata) fields in a field collection referencing a termset
2. A function to call that will report on all the taxonomy (managed metadata) fields in the web, the webs lists, and the webs sub webs, that are referencing a given termset.

I've outlined each function below. If you'd rather just download the script, download it from the Microsoft TechNet Gallery here: Find all SPFields that are using a Managed Metadata TermSet

1. Get a list of all the fields (in a field collection) using a termset

function Get-FieldsUsingTermSet            
{            
 [CmdletBinding()]            
  Param(             
    [parameter(Mandatory=$true, ValueFromPipeline=$true, Position=1)][Microsoft.SharePoint.SPFieldCollection]$fieldCollection,            
    [parameter(Mandatory=$true, Position=2)][Microsoft.SharePoint.Taxonomy.TermSet]$TermSet            
   )            
 $MetadataField = New-Object psobject            
 $MetadataField | Add-Member -MemberType NoteProperty -Name "ParentListUrl" -value ""
 $MetadataField | Add-Member -MemberType NoteProperty -Name "ParentListTitle" -value ""
 $MetadataField | Add-Member -MemberType NoteProperty -Name "FieldTitle" -value ""
 $MetadataField | Add-Member -MemberType NoteProperty -Name "FieldId" -value ""            
             
 $matches = @();            
 foreach($field in $fieldCollection)            
 {            
  if($field.GetType().Name -ne "TaxonomyField"){            
   continue;            
  }            
  if($field.TermSetId.ToString() -ne $TermSet.Id.ToString()){continue;}            
  $tf = $MetadataField | Select-Object *;            
  $tf.ParentListUrl = $field.ParentList.ParentWeb.Url;            
  $tf.ParentListTitle = $field.ParentList.Title;            
  $tf.FieldTitle = $field.Title;            
  $tf.FieldId = $field.ID;            
  $matches += $tf;            
 }            
 return $matches;            
}

2. A parent function to bring it together, that will give you some options (like recursively checking the web,  searching just web level fields)

function Get-ManagedMetadataFieldUses            
{            
 [CmdletBinding()]            
  Param(             
    [parameter(Mandatory=$true, ValueFromPipeline=$true, Position=1)][Microsoft.SharePoint.SPWeb]$web,            
    [parameter(Mandatory=$true, Position=2)][Microsoft.SharePoint.Taxonomy.TermSet]$TermSet,
    [parameter(Mandatory=$false, Position=4)][switch]$Recurse,            
    [parameter(Mandatory=$false, Position=5)][switch]$WebLevelFieldsOnly            
   )             
             
 $matches = @();             
 $matches += Get-FieldsUsingTermSet $web.Fields $TermSet;            
             
 if($WebLevelFieldsOnly -eq $false)            
 {            
  foreach($list in $web.Lists)            
  {            
   $matches += Get-FieldsUsingTermSet $list.Fields $TermSet            
  }            
 }            
             
 if($Recurse)            
 {            
  foreach($subweb in $web.Webs)            
  {            
   $matches += Get-ManagedMetadataFieldUses $subweb $TermSet $Recurse $WebLevelFieldsOnly;            
  }            
 }            
             
 return $matches            
}

Examples of using the script to create some reports.

1. Download the script from here:
2. Save the script somewhere. "C:\Temp" is a good place!
3. If you haven't already, set the PowerShell execution policy to Bypass (this will allow you to import all PowerShell scripts)

Set-ExecutionPolicy -ExecutionPolicy Bypass -Scope CurrentUser

4. Import the script into PowerShell.

Import-Module C:\Temp\Get-ManagedMetadataFieldUses.ps1

5. Run a few commands to get a termset to report on. In this example, I get a termset called "Countries"

#Get the SPWeb object            
$w = Get-SPWeb http://mdysp13;            
#Get the taxonomy session used by the SPWeb's site            
$tsession = Get-SPTaxonomySession -Site $w.Site;            
#Get all the TermSets with the name "Countries", and the web's default Language ID
$termSets = $tsession.GetTermSets("Countries",$w.Language)            
#Display the TermSets found            
$termSets | FT @{Label="Group";Expression={($_.Group).Name}},Name,ID            
#Select the first TermSet            
$termSet = $termSets[0]



6. Call the Get-ManagedMetadataFieldUses function, and store the results in the $matchingFields variable.

$matchingFields = Get-ManagedMetadataFieldUses -web $w -TermSet $termSet -Recurse

Do some reporting!!

Display all of the results in the raw format.

$matchingFields | FT



Display all of the results, grouping them by the Site. This view of the data will show you how many fields in each site (or web) are referencing the termset)

$matchingFields | Group-Object ParentListUrl



This improves on the previous command, displaying all of the results, grouping them by the Site. In this view, all the fields are listed, grouped under the site they belong to.

$matchingFields | Group-Object ParentListUrl | Select -ExpandProperty Group  | Format-Table -GroupBy ParentListUrl



Finally, group the objects into a Hash Table. This will allow you to directly reference a web URL, to a get a list of fields in that web that reference the termset.

$hashTable = $matchingFields | Group-Object ParentListUrl -AsHashTable -AsString
$hashTable."http://mdysp13" | FT ParentListTitle,FieldTitle,FieldId -AutoSize



And "even more finally", you can export your results to a CSV file for further analysis!

$matchingFields | Export-CSV  -Path C:\temp\fieldreport.csv -NoTypeInformation -Delimiter "`t"

Download the full script from the Microsoft TechNet Gallery here: Find all SPFields that are using a Managed Metadata TermSet