Remove Custom Actions from the List Item Context Menu in SharePoint

Ehy! (that's "hey", spelt wrong).

Ever had a solution that (erroneously) creates additional custom actions on the list item context menu?

I had this problem this week. A Nintex Workflow that I'm importing (via PowerShell) into a site during a provisioning process does just this. Apparently this happens when you have a workflow with the "Enable workflow to start from the item menu" setting enabled, and then you export that workflow and import it into another site.

The issue becomes apparently after you re-import the workflow more than once. Each time the workflow is re-imported (e.g. in my case when a provisioning process updates a site), the a new version of the custom action is added to the lists context menu, but the old one is not removed.

And this is what you end up with...

That doesn't look very good, and what's worse, only one of those custom actions (menu items) works (the most recently added one).

To sort this out, you can use PowerShell or SharePoint Designer to remove the old custom actions. Since I'm working on a provisioning processing, I used PowerShell to remove the context menu items.

I wrote the PowerShell as a function I could call from another script in my provisioning process. The PowerShell uses the client object model (CSOM), so that it doesn't need to be run on the SharePoint server.

Here it is!

Param(
        [Parameter(Mandatory=$true, Position=1)] [Microsoft.SharePoint.Client.ClientContext] $ClientContext,  
        [Parameter(Mandatory=$true, Position=2)][String]$listName, 
        [Parameter(Mandatory=$true, Position=3)][String]$actionName, 
        [Parameter(Position=4)][switch]$leaveFirstInstance
    )
#Load the web and list 
$web = $ClientContext.Web
$lists = $web.Lists
$ClientContext.Load($web)
$ClientContext.Load($lists)
$ClientContext.ExecuteQuery()        
$list = $lists.GetByTitle($listName)
$ClientContext.Load($list)
$ClientContext.ExecuteQuery()  
if($list -eq $null){
    Write-host "Couldn't find the list." -F Red
    return
} 
#Load the custom actions on the list
$allCustomActions = $list.UserCustomActions       
$ClientContext.Load($allCustomActions)
$ClientContext.ExecuteQuery()
#Get a collection of the custom actions that are on the context menu (ECB), 
#and match the name of the custom action passed to the function
$customActions = $allCustomActions | ?{$_.Title -eq $actionName -and $_.Location -eq "EditControlBlock"}
$itemCount = $customActions.Count
$removeFromIndex  = $itemCount - 1
$baseIndex = 0  
#If the leaveFirstInstance parameter has been set, then set the base index to one
#This ensures we leave the first item (custom action) in the collection  
if($leaveFirstInstance){
    $baseIndex = 1
}    
Write-Host "Found $itemCount custom actions ($actionName) to process in list $listName"
if($removeFromIndex -ge $baseIndex){
    $countOfItemsRemoved = 0
    $itemsToRemove = @()
    #Loop through all the custom actions in the collection
    #and build an array of custom action id's (GUID's) to delete
    $customActions | %{
        if($([Array]::IndexOf($customActions, $_)) -le $removeFromIndex){
            #Write-host "Adding item $($_.Id) to the array of items to remove"
            $itemsToRemove += $_.Id
        }
        #Write-Host "$($_.Id) with Index: $([Array]::IndexOf($l.UserCustomActions, $_))"
    }
    #For each item in the list of GUID's, get the custom action from the list 
    #(using the GUID) and then call the DeleteObject() method.
    $itemsToRemove | %{
        #Because we're modifying the collection of custom ations in each loop, 
        #we need to reload the list each time (so that the collection of 
        #user actions is unmodified.
        $lists = $web.Lists
        $ClientContext.Load($web)
        $ClientContext.Load($lists)
        $ClientContext.ExecuteQuery()
        #After (re)loading the list/collection, get the custom action
        #and call the DeleteObject() method
        $list.UserCustomActions.GetById($_.Guid).DeleteObject()
        $countOfItemsRemoved++
    }
    Write-host "Of the $($itemsToRemove.Count) items flagged to process, and removed $countOfItemsRemoved of those items."
    $list.Update()
}

And you can call it from another script like this (in this example, a script located in the same directory):

#The URL to the site where the list exists
$url = "https://ican.tell.you.com"
#Create the client context
$ClientContext = new-object Microsoft.SharePoint.Client.ClientContext($Url)
$cc = new-object System.Net.CredentialCache
$uri = New-Object  System.Uri $url 
$cc.Add($uri, "NTLM",[System.Net.CredentialCache]::DefaultNetworkCredentials)
$ClientContext.Credentials = $cc
$ClientContext.AuthenticationMode = [Microsoft.SharePoint.Client.ClientAuthenticationMode]::Default
$ClientContext.RequestTimeout = "500000"
#Call the function to remove the custom action
.\Remove-CustomActionsFromECB.ps1 -ClientContext $ClientSiteContext -ListName "Service Requests" -ActionName "Submit Service Request to iClient" -LeaveFirstInstance:$false

Provisioning a new Nintex Workflow Content Database using PowerShell

Scenario:

I need to create a new Nintex Workflow Content database and associate it with a new SharePoint Site Collection as part of a PowerShell based solution provisioning process.

Problem:

There is no obvious way to do this; there are no methods in the web api, and nwadmin doesn't have any operations that remotely resemble adding a new content database.

Approach:

There are two ASPX pages in SharePoint Central Admin that allow administrators to create new Nintex Workflow Content databases and associate those databases to Site Collections. Since these pages must have code behind them, I thought I'd open up the Nintex dll's (using ILSpy) and see if I could find the code responsible for the functionality on these pages.

This approach worked perfectly, and it turned out that I only needed a few lines of PowerShell to create my new database and associated it with a Site Collection.

The caveat is, the PowerShell needs to be run from a PowerShell command prompt on the Server, so it won't work if you're solution is being built for Office 365, or if you need to use all client side (PowerShell) code.

Solution / Code:

#Load all the assemblies that we need to use            
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SharePoint.Administration') | Out-Null            
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow') | Out-Null            
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.Administration') | Out-Null            
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.Common') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.ContentDbMappingCollection') | Out-Null            
            
#Add the SharePoint PowerShell snapin (in case it's not already loaded)            
if(-not(Get-PSSnapin | Where-Object {$_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
 Add-PSSnapin Microsoft.SharePoint.PowerShell;            
}            
            
$NintextDatabaseName = "Nintex_Flintstones"            
$Url = "http://portaldev.bi.local/sites/flintstones";            
#Get the SharePoint Site that you want to create a separate Nintex Content database for.            
$site = Get-SPSite $Url            
#Get the content database for the SharePoint Site            
$siteContentDb = Get-SPContentDatabase -Site $site            
#Get the top level farm object. We'll use this to get access to the farms config database server             
#Note: The Microsoft.SharePoint.Administration.SPGlobalAdmin class is deprecated. 
#I'm using it here, only becuase I'm trying to keep my PowerShell code as close 
#as possible to the code used in the Nintex admin pages            
$globalAdmin = New-Object Microsoft.SharePoint.Administration.SPGlobalAdmin            
#Get the Nintext Configuration Database            
$configDatabase = [Nintex.Workflow.Administration.ConfigurationDatabase]::GetConfigurationDatabase();            
#Check if there is an existing Nintex Content database with the name we want to use            
$contentDatabase = $configDatabase.ContentDatabases.FindByDatabaseAndServerName($globalAdmin.ConfigDatabaseServer,$NintextDatabaseName);            
#If the an existing database with the same name we want to use wasn't found, then we'll add it.             
if($contentDatabase -eq $null)            
{            
    Write-Host "The Nintex Content Database $NintextDatabaseName does not exist." -f Yellow;            
    Write-Host "Creating a new Nintex Content Database with the following name: $NintextDatabaseName" -f Yellow;            
    #Create a SQL connections string            
    $connectionString = ([String]::Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", $globalAdmin.ConfigDatabaseServer,$NintextDatabaseName))            
    #Initialise a new DatabaseAttacher object using the connection string            
    $dbAttacher = New-Object Nintex.Workflow.Administration.DatabaseAttacher($connectionString, 0)            
    #Set properties on the DatabaseAttacher object.             
    $dbAttacher.AttachOptions.CreateNewDatabase = $true;            
    $dbAttacher.AttachOptions.ProvideAllWebApplicationsAccess = $true;            
    $dbAttacher.AttachOptions.IncludeStorageRecordStep = $false;            
    #Finally, call the Attach() method to create and attach the 
    #database to the SharePoint farm.             
    $attachResult = $dbAttacher.Attach();            
    #Handle the success and failure scenarios            
    if($attachResult.CanContinue)            
    {            
        Write-Host "Successfully created a new Nintex Content Database with the following name: $NintextDatabaseName" -f Green;            
        if($attachResult.Warnings)            
        {            
            Write-Host "The following warnings were logged via creating the Nintex Content Database:" -f DarkYellow            
            Write-Host $($attachResult.Warnings) -f DarkYellow            
        }            
        #Get the new database we just created             
        $contentDatabase = $configDatabase.ContentDatabases.FindByDatabaseAndServerName($globalAdmin.ConfigDatabaseServer,$NintextDatabaseName);                    
    }            
    else            
    {            
        Write-Host "Error creating the Nintex Content Database." -f Red            
        Write-Host $($attachResult.Errors) -f Red            
        return;            
    }            
}            
else            
{            
    Write-Host "The Nintex Content Database $NintextDatabaseName already exists." -f Green;            
}            
            
#If the database was successfully created (or already existed), 
#update the mappings to associate the Nintex Content database 
#with the SharePoint Site Collection.            
if($contentDatabase -ne $null)            
{            
    Write-Host "Updating the Nintex Content Database mappings for site: $($site.Url)" -f Yellow;            
    #Create a new ContentDbMapping object, and get the current content 
    #database mappings for the Site Collection            
    [Nintex.Workflow.ContentDbMapping]$contentDbMapping;            
    $contentDbMapping = [Nintex.Workflow.ContentDbMappingCollection]::ContentDbMappings.GetContentDbMappingForSPContentDb($siteContentDb.Id)            
    #if there are no content database mappings found, initialise a new 
    #ContentDatabaseMapping object            
    if($contentDbMapping -eq $null)            
    {            
        $contentDbMapping = New-Object Nintex.Workflow.ContentDbMapping            
    }            
    #Set the properties of the ContentDatabaseMapping object to associate
    #the Nintex Content Database with the Site Collection            
    $contentDbMapping.SPContentDbId = $siteContentDb.Id;            
    $contentDbMapping.NWContentDbId = $contentDatabase.DatabaseId;            
    #Call the CreateOrUpdate() method to save the changes            
    $contentDbMapping.CreateOrUpdate();                
    Write-Host "Successfully added a Nintex Content Database mapping for site: $($site.Url)" -f Green;            
}            

Quick and Dirty: Parsing logs with PowerShell

Recently I needed to parse a large number of SharePoint log files looking for particular strings. The strings I was looking for indicted errors that required further investigation.

There are a number of tools for doing this sort of activity. In this case, I choose to use PowerShell, because of the speed with which I could accomplish this task. It took me less than 5 minutes to write and test the script. And it saved me a lot of time parsing the log files!

Using the PowerShell script, I can quickly change my queries, re-run my queries, export results, and most importantly, query large numbers of log files quickly.

Here's the code I used.

# Set the path to the log files            
$path = "C:\Temp\Logs"            
# Get a collection of all the log files (anything ending in .log)            
$files = Get-ChildItem -Path $path -Filter "*.log"            
# Pipe the collection of log files to the ForEach-Object cmdlet             
# (the alias of ForEach-Object is %)            
$files | %{            
    # Call the OpenText method, to return a System.IO.StreamReader object            
    $file = $_.OpenText();            
    # Record the current line number (to use in the console output)            
    $lineNum = 1;            
    Write-Host "Checking file"$_.Name -f Yellow;            
    # Use the EndOfStream method (which returns true when you have reach the end            
    # of the file), read each line of the file.              
    while($file.EndOfStream -ne $true)            
    {            
        # Read the next line in the file            
        $line = $file.ReadLine();                    
        if($line -ne $null)            
        {            
            # Use the String ToLower and Contains methods to check for occurances            
            # of the strings (or values) you need to check the file for            
            # In this example, I'm looking for any instances of the text "error" or "exception"            
            if($line.ToLower().Contains("error") -or $line.ToLower().Contains("exception"))            
            {            
                # If the current lines contains a match, write the line number            
                # and line text out to the console            
                Write-Host "Line: $lineNum " -NoNewline -ForegroundColor Green;            
                Write-Host $line -f Red;            
            }            
        }             
        # Increment the line number            
        $lineNum++;                   
    }            
}            

Update the IIS bindings on all Servers in a SharePoint Farm using PowerShell Remoting

I blogged the other day about using PowerShell Remoting to add a SQL alias to multiple servers in a SharePoint Farm. Adding a SQL Alias to all the Servers in a SharePoint Farm using PowerShell and Remoting.

This post follows that one in theme. The migration project I'm working on at the moment required a change to the IIS bindings for one of the web applications. We needed to a add a default binding that would catch all requests other-wise not resolved, on port 443.

The binding in IIS looks like this: *:443:

I used the PowerShell Remoting technique described in the blog above to run a PowerShell script block on all of the 15 SharePoint servers in the farm. This is fast, and ensures the settings get applied consistently!

The key to this task, is to ensure you load the IIS PowerShell module in each script block. Each "remote session" is like openning a new PowerShell console; you must remember to load any additional modules or add-ins that are need by your script.

To the code.

Tasks:
1. Stop the default website (if it's running)
2. Add the default (catch-all) binding for SSL (port 443). In the example, we're adding that binding to the Yarletto IIS web application (not to be confused a SharePoint web application).
3. Remove the existing bindings on the IIS web application.


The first part of the script takes care of credentials, stopping the default website (if it's started) and adding the new binding.

# Add the SharePoint Snap-in            
Add-PSSnapin Microsoft.SharePoint.PowerShell            
            
# Create a credential object - this is needed to authenticate to the             
# remote server of each PowerShell Remote session.            
$account = Read-Host -Prompt "Enter the farm account";            
$password =  Read-Host -Prompt "Enter the farm account password" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($account,$password);
            
# Get a list of servers in the SharePoint farm. Then filter the list to servers
# that actually have the SharePoint binaries installed (omit DB and email servers)
$farm = Get-SPFarm            
$servers = $farm.Servers | ?{$_.Role -eq "Application"} | Select Name,Role            
            
# Send the list of servers to a for-each cmdlet - the alias is "%".             
$servers | %{             
 # For each loop, create a new PowerShell Remote Session            
 # Pass in the credential object to authenticate the remote session            
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Updating bindings on"$_.Name -f green;            
             
 # Invoke a PowerShell Script block in the remote session,             
 # using the Invoke-Command cmdlet.            
 # Invoke-Command will run the script block in the session passed             
 # into the Session parameter.            
 # At the end of the script block, notice that parameters have been             
 # passed into the script block            
 # using the -ArgumentList paramater.            
    Invoke-Command -Session $rs -Script {             
        param($hostname = "", $iisWebAppname = "")            
        Write-Host "Working on"$env:COMPUTERNAME;             
        Write-Host "Hostname:"$hostname                    
        Write-Host "IIS APP name"$iisWebAppname;            
        # Load the IIS Web Administration module, to get access to             
        # the IIS PowerShell cmdlet's            
        Import-Module "WebAdministration"             
        # Get the default IIS web site            
        $ws =  Get-Website "Default Web Site"            
        # If the website is running, stop it.            
        if($ws.state -eq "Started"){            
            Write-Host "Stopping default website." -f DarkMagenta            
            $ws.Stop();            
            Sleep 1;            
        }            
         # Use the Get-WebBinding cmdlet to search for an existing             
         # instance of the web binding we want to add            
        $b = $null            
        $b = Get-WebBinding | ?{$_.bindingInformation -eq "*:443:"}            
        # If the web binding doesn't already exist, then create it!            
        if($b -eq $null)            
        {            
            Write-host "Adding binding" -f DarkYellow            
            New-WebBinding -Name $iisWebAppname -Protocol https -Port 443 -IPAddress "*" -HostHeader $hostname                    
        }                    
    } -ArgumentList "","Yarletto"              
    # Finally, make sure you close the Remote session.            
    Remove-PSSession -Session $rs;                
}

The second part of the script removes the old bindings. This could be scripted more efficiently (as a function), but sometimes you just need to create a script in the shortest possible amount of time!

# Send the list of servers to a for-each cmdlet - the alias is "%".             
$servers  | %{            
    # For each loop, create a new PowerShell Remote Session            
    # Pass in the credential object to authenticate the remote session                
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Updating bindings on"$_.Name -f green;            
            
    # Invoke a script block using the remote session (as above)            
    Invoke-Command -Session $rs -Script {             
        param($hostname = "", $iisWebAppname = "")            
        Write-Host "Working on"$env:COMPUTERNAME;             
        Write-Host "Hostname:"$hostname                    
        Write-Host "IIS APP name"$iisWebAppname;            
        # Remember to load the IIS Web Administration module            
        Import-Module "WebAdministration"            
        # Check the IIS Binding exists            
        $b = $null            
        $b = Get-WebBinding -HostHeader $hostname -Port 443            
        # If the binding exists, delete it!            
        if($b -ne $null)            
        {            
            Write-host "Removing binding" -f DarkYellow            
            Remove-WebBinding -BindingInformation $b.bindingInformation                    
        }                    
    } -ArgumentList "yarletto.com.au","Yarletto"              
    # Finally, remember to close the session once you're finished with it!            
    Remove-PSSession -Session $rs;                
}

Adding a SQL Alias to all the Servers in a SharePoint Farm using PowerShell and Remoting

Today I needed to add a new SQL alias to all the servers in a large SharePoint Farm.

There are 15 servers in this farm (not including the SQL clusters). So I didn't want to logon to each server in the farm to add the alias manually.

I thought it would be great if I could just run a single PowerShell script on one of the SharePoint servers that added the alias to all the servers in the farm.

That's where PowerShell Remoting comes to the rescue! I can execute a script on multiple servers from a single server!

Here's how.

Cooking Time:
5 mins

Ingredients:
1 x Script to execute
1 x Credential (requires administrative permissions)
A handful of Servers

Method:
1. Create a credential object

$account = Read-Host -Prompt "Enter the farm account";            
$password =  Read-Host -Prompt "Enter the farm account password" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($account,$password);

2. Get all the "SharePoint" servers in the farm (a.k.a any server that has the SharePoint binaries installed on it).

I'm filtering the list of servers in the farm based on the Role = Application. This ensures we don't get SQL Servers and email servers.

$farm = Get-SPFarm            
$servers = $farm.Servers | ?{$_.Role -eq "Application"} | Select Name

3. Store the SQL Alias information in some variables

$aliasname = "HR"             
$sqlserver = "sqlserver\hrinstancename"            
$tcpalias = "DBMSSOCN," + $sqlserver

4. Pipe the list of servers to the Foreach-Object (%) cmdlet, and let the magic begin!

In each loop of the for-each block, create a new PSSession, using the server name and the credential object created earlier, to connect to the remote server.

Once you have the new remote PSSession, use the Invoke-Command cmdlet to run the PowerShell script in the remote session.  Pass the $aliasname and $tcpalias variables to Invoke-Command, so that they can be used in the script block.

The PowerShell for actually adding the aliases is a slightly modified version of a script from the guys at Habanero Consulting

Finally, remember to close the PSSession at the end of the block

$servers | %{                
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Adding SQL Aliases to"$_.Name -f green;            
    Invoke-Command -Session $rs -Script {             
  param($AliasName = "", $TCPAlias = "")            
        Write-Host "Working on"$env:COMPUTERNAME;
        $x86 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo"            
        $x64 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"
        if ((test-path -path $x86) -ne $True){write-host "$x86 doesn't exist";New-Item $x86}            
        if ((test-path -path $x64) -ne $True){write-host "$x64 doesn't exist";New-Item $x64}           
        $p = $null;            
        $p = Get-ItemProperty -Path $x86 -Name $AliasName -ErrorAction:SilentlyContinue
        if($p -eq $null){Write-Host "creating x86 alias" -f Yellow; New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAlias}            
                
        $p = $null;            
        $p = Get-ItemProperty -Path $x64 -Name $AliasName -ErrorAction:SilentlyContinue            
        if($p -eq $null){Write-Host "creating x64 alias" -f Yellow;New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAlias}            
    }                
    Remove-PSSession -Session $rs;                
} -ArgumentList $aliasname,$tcpalias

And that's it! It's as easy as that!
Kaaaaa PoW!

Deleting Orphaned SharePoint Databases

When you delete a Service Application, but not the data, databases are left behind. Should you want to clean up references to these databases later, here's how.

Use the Get-SPDatabase cmdlet to list all of the databases the Farm knows about. The output of this command is verbose, so pipe it to Format-List and select a subset of the properties.

Get-SPDatabase | FT Name,Exists

Notice how the IsAttachedToFarm and ExistsInFarm properties don't report true or false for most of the service applications?

It's not a problem! You can use the Exists property (on each database) to filter that list to just databases that SharePoint "thinks" don't exist.

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false} | %{Write-Host "DB"$_.Name"does not exist." -f red}

Or

$dbs | %{Write-Host "Database"$_.Name;if($_.Exists){Write-Host "DB Exists." -f Green}else{Write-Host "DB does not exist." -f red}}

To remove these database references, call the Delete() method, and then the Unprovision() method, on each database.

For Example:

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false}  | %{Write-Host "DB"$_.Name"does not exist. Deleting and cleaning up references." -f red; $_.Delete();$_.Unprovision()}

Use PowerShell to add and remove items in the Quick Launch menu on a SharePoint 2013 site

Here's a quick snippet of PowerShell that demonstrates adding menu items to the Quick Launch menu in a SharePoint 2013 site.

The example is based on a newly added Business Intelligence site, though the code will work on any site. The goal is to remove the existing quick launch menu items, and replace them with a list of report document libraries, grouped under a heading called, "Reports".

The original site looks like this:

The following PowerShell adds a list of links (to the report lists) onto the quick launch menu. Note that the new menu items are defined in the $listsToAddToNav array.

#SharePoint site url            
$weburl = "http://devhv131/sites/bi"            
#An array of SharePoint lists that you want to add to the navigation menu            
$listsToAddToNav = @('Building Management','Capital Programmes','Employee Services','Financial','General Reports','Payroll','Property','Purchasing');            
            
#Get the SPWeb object for the site url            
$w = get-spweb $weburl            
#Get the quick launch menu            
$ql = $w.Navigation.QuickLaunch;            
#Create the root node that all the lists will be displayed under.            
$n = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode("Reports","",$true);            
#Add the new node to the quick launch menu            
$n = $ql.AddAsFirst($n);             
#Add all the lists as new child nodes, to the "Reports" node            
foreach($list in $listsToAddToNav)            
{            
    #Create a new child node, for the current folder. The URL of the folder is constructed using the name of the folder, and the web URL            
 $cn = New-Object Microsoft.SharePoint.Navigation.SPNavigationNode($list,([String]::Format("{0}/{1}",$w.Url,$list.Replace(" ","%20"))),$true);             
    #Add the childnode to the parent node, which in this case, is the "Reports" navigation node.             
    #Add the child as the "last node". As the nodes being added are in alphabetical order, this will preserve the node order, as A-Z            
    $n.Children.AddAsLast($cn);            
    #Update (save) the navigation            
 $w.Update()            
}

After running the PowerShell above, the new menu items have been added.

This next bit of PowerShell removes the unwanted menu items from the quick launch. Note that the unwanted menu items are defined in the $nodesToDelete array.

#Create an array of nodes that you want to delete. The array contains the node names.        
$nodesToDelete = @('Dashboards','Data Connections','PerformancePoint Content','Recent','Libraries')            
#For each node, delete it from the Quick Launch menu            
foreach($dnname in $nodesToDelete)            
{            
 #Get the node            
 $dn = $w.Navigation.QuickLaunch | where { $_.Title -eq $dnname }            
 if($dn -eq $null){continue;}            
 Write-Host "Deleting navigation node, $dnname"            
    #If the node wasn't null, delete it!             
 $ql.Delete($dn);            
    #Update (save) the navigation            
 $w.Update();            
}

Finally, with the unwanted navigation menu items removed, the site now looks the way we want it!

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