Report of All Taxonomy Fields containing a term in SharePoint Tenancy

Recently we had a request to find fields/columns in all lists across the tenancy which have a specific Taxonomy term because we needed to report on field usage across all site collections. However, we found that getting a report of all Taxonomy fields in your SharePoint tenancy that is linked to a specific Term Set can get quite daunting because there is no direct SharePoint Query to fetch the associations.

The technical challenge is that using PnP PowerShell, the Taxonomy fields are returned as a generic SP.Field type and not of type SP.TaxonomyField. Hence the Taxonomy field metadata values such as Group ID and Termset ID are absent.

To resolve the above limitation, we used the Field $field.SchemaXml to find the specified values.

Note: Querying the Term store while searching for a specific termset by using Get-PnPTerm can add a lot of latency time. Hence to decrease the additional time we could download the entire term store to an Excel file and use that Excel file as the master data for matching. Below is the command to get an export of all Taxonomy values as a CSV file.

Export-PnPTaxonomy -Path "[path]\taxonomyreport.csv" -Delimiter "," -IncludeID

Steps:

The steps to retrieve and check for taxonomy fields can be found below.

Get all lists in a web site
Get the Taxonomy fields for a List
Read the schema.xml and search for a TermsetID and AnchorID (Thanks to @Colin Philips (http://itgroove.net/mmman/) for finding the correct xpath parsing for xml)
Match the data with the above Taxonomy report for Group ID, Termset ID, and Anchor ID with Term ID that is column is linked to
In case of a match, save the values into the report.

The below code uses PnP PowerShell. For a quick set up of PnP PowerShell, please refer to this blog.

	foreach($item in $sitesdata)
	{
	Write-Host "Started Traversing Site " $item.Url -ForegroundColor Yellow
	Connect-PnPOnline -siteURL $item.Url
	$site = Get-PnPSite
	if($site.Url -eq $item.Url)
	{
	$lists = Get-PnPList
	Write-Host "Found " $lists.Count " Lists. Processing.. Please wait.."
	foreach($list in $lists)
	{
	$taxonomyfields = Get-PnPField -List $list \| Where-Object {$_.TypeAsString -eq "TaxonomyFieldType"}
	if($taxonomyfields.Count -gt 0)
	{
	foreach($field in $taxonomyfields)
	{
	$xml =[XML]$field.SchemaXml
	$TermSetId = ($xml \| Select-Xml "//Name[text()='TermSetId']/following-sibling::Value/text()").Node.Value
	$TermId = ($xml \| Select-Xml "//Name[text()='AnchorId']/following-sibling::Value/text()").Node.Value
	$filterData = $taxonomydata \| Where-Object {$_.TermsetID -eq $TermSetId -and (($_.Level1TermId -eq $TermId) -or ($_.Level2TermId -eq $TermId) -or ($_.Level3TermId -eq $TermId))}
	$GroupId = $filterData[0].GroupID;
	$GroupName = $filterData[0].Group;
	$TermsetName = $filterData[0].TermSet;
	$Level1TermName = $filterData[0].Level1Term;
	$obj = New-Object PSObject;
	$obj \| Add-Member NoteProperty ID $($count);
	$obj \| Add-Member NoteProperty SiteUrl $($item.Url);
	$obj \| Add-Member NoteProperty TaxonomyPresence "Present";
	$obj \| Add-Member NoteProperty ListName $($list.Title);
	$obj \| Add-Member NoteProperty FieldName $($field.Title);
	$obj \| Add-Member NoteProperty FieldInternalName $($field.InternalName);
	$obj \| Add-Member NoteProperty GroupId $($GroupId);
	$obj \| Add-Member NoteProperty GroupName $($GroupName);
	$obj \| Add-Member NoteProperty TermSetId $($TermSetId);
	$obj \| Add-Member NoteProperty TermSetName $($TermsetName);
	$obj \| Add-Member NoteProperty TermId $($TermId);
	$obj \| Add-Member NoteProperty TermName $($Level1TermName);
	$results += $obj;
	}
	}
	else
	{
	$obj = New-Object PSObject;
	$obj \| Add-Member NoteProperty ID $($count);
	$obj \| Add-Member NoteProperty SiteUrl $($item.Url);
	$obj \| Add-Member NoteProperty TaxonomyPresence "No Taxonomy fields found";
	$obj \| Add-Member NoteProperty ListName $($list.Title);
	$obj \| Add-Member NoteProperty FieldName "";
	$obj \| Add-Member NoteProperty FieldInternalName "";
	$obj \| Add-Member NoteProperty GroupId "";
	$obj \| Add-Member NoteProperty GroupName "";
	$obj \| Add-Member NoteProperty TermSetId "";
	$obj \| Add-Member NoteProperty TermSetName "";
	$obj \| Add-Member NoteProperty TermId "";
	$obj \| Add-Member NoteProperty TermName "";
	$results += $obj;
	}
	}
	}
	else
	{
	$obj = New-Object PSObject;
	$obj \| Add-Member NoteProperty ID $($count);
	$obj \| Add-Member NoteProperty SiteUrl $($item.Url);
	$obj \| Add-Member NoteProperty TaxonomyPresence "Site is not accessible";
	$obj \| Add-Member NoteProperty ListName "";
	$obj \| Add-Member NoteProperty FieldName "";
	$obj \| Add-Member NoteProperty FieldInternalName "";
	$obj \| Add-Member NoteProperty GroupId "";
	$obj \| Add-Member NoteProperty GroupName "";
	$obj \| Add-Member NoteProperty TermSetId "";
	$obj \| Add-Member NoteProperty TermSetName "";
	$obj \| Add-Member NoteProperty TermId "";
	$obj \| Add-Member NoteProperty TermName "";
	$results += $obj;
	}
	$count++;
	Write-Host "Processing Finished .."
	}

	if($results.Count -gt 0)
	{
	Write-Host "Creating Report" -ForegroundColor Red
	$fileName = "SiteCollectionTaxonomyFields.csv"
	Write-Host "Exporting file " $fileName
	$results \| Export-Csv $fileName -NoTypeInformation
	Write-Host "Creating Report Completed" -ForegroundColor Green
	}

view raw

Find Taxonomy Fields in SharePoint Tenancy

hosted with ❤ by GitHub

Conclusion:

Hence with the above approach, we can retrieve the taxonomy fields for all site collections. Be aware that the above process can take about half a day or more to run depending on the number of site collections and taxonomy fields in your tenancy. Be sure to give it enough time to run before prematurely cancelling it.

Happy Coding!! 😊

	foreach($item in $sitesdata)
	{
	Write-Host "Started Traversing Site " $item.Url -ForegroundColor Yellow
	Connect-PnPOnline -siteURL $item.Url
	$site = Get-PnPSite
	if($site.Url -eq $item.Url)
	{
	$lists = Get-PnPList
	Write-Host "Found " $lists.Count " Lists. Processing.. Please wait.."
	foreach($list in $lists)
	{
	$taxonomyfields = Get-PnPField -List $list \| Where-Object {$_.TypeAsString -eq "TaxonomyFieldType"}
	if($taxonomyfields.Count -gt 0)
	{
	foreach($field in $taxonomyfields)
	{
	$xml =[XML]$field.SchemaXml
	$TermSetId = ($xml \| Select-Xml "//Name[text()='TermSetId']/following-sibling::Value/text()").Node.Value
	$TermId = ($xml \| Select-Xml "//Name[text()='AnchorId']/following-sibling::Value/text()").Node.Value
	$filterData = $taxonomydata \| Where-Object {$_.TermsetID -eq $TermSetId -and (($_.Level1TermId -eq $TermId) -or ($_.Level2TermId -eq $TermId) -or ($_.Level3TermId -eq $TermId))}
	$GroupId = $filterData[0].GroupID;
	$GroupName = $filterData[0].Group;
	$TermsetName = $filterData[0].TermSet;
	$Level1TermName = $filterData[0].Level1Term;
	$obj = New-Object PSObject;
	$obj \| Add-Member NoteProperty ID $($count);
	$obj \| Add-Member NoteProperty SiteUrl $($item.Url);
	$obj \| Add-Member NoteProperty TaxonomyPresence "Present";
	$obj \| Add-Member NoteProperty ListName $($list.Title);
	$obj \| Add-Member NoteProperty FieldName $($field.Title);
	$obj \| Add-Member NoteProperty FieldInternalName $($field.InternalName);
	$obj \| Add-Member NoteProperty GroupId $($GroupId);
	$obj \| Add-Member NoteProperty GroupName $($GroupName);
	$obj \| Add-Member NoteProperty TermSetId $($TermSetId);
	$obj \| Add-Member NoteProperty TermSetName $($TermsetName);
	$obj \| Add-Member NoteProperty TermId $($TermId);
	$obj \| Add-Member NoteProperty TermName $($Level1TermName);
	$results += $obj;
	}
	}
	else
	{
	$obj = New-Object PSObject;
	$obj \| Add-Member NoteProperty ID $($count);
	$obj \| Add-Member NoteProperty SiteUrl $($item.Url);
	$obj \| Add-Member NoteProperty TaxonomyPresence "No Taxonomy fields found";
	$obj \| Add-Member NoteProperty ListName $($list.Title);
	$obj \| Add-Member NoteProperty FieldName "";
	$obj \| Add-Member NoteProperty FieldInternalName "";
	$obj \| Add-Member NoteProperty GroupId "";
	$obj \| Add-Member NoteProperty GroupName "";
	$obj \| Add-Member NoteProperty TermSetId "";
	$obj \| Add-Member NoteProperty TermSetName "";
	$obj \| Add-Member NoteProperty TermId "";
	$obj \| Add-Member NoteProperty TermName "";
	$results += $obj;
	}
	}
	}
	else
	{
	$obj = New-Object PSObject;
	$obj \| Add-Member NoteProperty ID $($count);
	$obj \| Add-Member NoteProperty SiteUrl $($item.Url);
	$obj \| Add-Member NoteProperty TaxonomyPresence "Site is not accessible";
	$obj \| Add-Member NoteProperty ListName "";
	$obj \| Add-Member NoteProperty FieldName "";
	$obj \| Add-Member NoteProperty FieldInternalName "";
	$obj \| Add-Member NoteProperty GroupId "";
	$obj \| Add-Member NoteProperty GroupName "";
	$obj \| Add-Member NoteProperty TermSetId "";
	$obj \| Add-Member NoteProperty TermSetName "";
	$obj \| Add-Member NoteProperty TermId "";
	$obj \| Add-Member NoteProperty TermName "";
	$results += $obj;
	}
	$count++;
	Write-Host "Processing Finished .."
	}

	if($results.Count -gt 0)
	{
	Write-Host "Creating Report" -ForegroundColor Red
	$fileName = "SiteCollectionTaxonomyFields.csv"
	Write-Host "Exporting file " $fileName
	$results \| Export-Csv $fileName -NoTypeInformation
	Write-Host "Creating Report Completed" -ForegroundColor Green
	}

Share this:

Like this:

Related

Leave a Reply Cancel reply