Change MMS Field Term Set using CSOM and PowerShell


The script below allows you to change Term Set ID to a Column in SharePoint Online using Client Side Object Model.  One of my client used an MMS Field in multiple libraries without using Site Column so I had to write this script to change the ID.  You can change the script to do it on all the sites and sub sites.  You must use latest CSOM Components from Github for better performance.

$Creds = Get-Credential

Connect-PnPOnline -Url https://sharepointmvp-Admin.sharepoint.com -Credential $Creds

Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll”
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Taxonomy.dll”

$site = https://tenant.sharepoint.com
#Get the Client Context and Bind the Site Collection
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($site)

#Authenticate
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName , $Creds.Password)
$ctx.Credentials = $credentials

#Fetch the users in Site Collection

$ListTitle = “Documents”
$FieldTitle = “Description”

$Web = $ctx.Site.RootWeb
$ctx.Load($Web)
$DocLib = $Web.Lists.GetByTitle($ListTitle)
$ctx.Load($DocLib)

$Fields = $DocLib.Fields
$ctx.Load($Fields)
$ctx.ExecuteQuery()
$PrimaryTag = $Fields | Where {$_.InternalName -eq “TermRenameTest1”}

$taxStore = [Microsoft.SharePoint.Client.Taxonomy.TaxonomySession]::GetTaxonomySession($ctx) 
$ctx.Load($taxStore) 
$ctx.ExecuteQuery() 

$termStore = $taxStore.TermStores.GetByName(“Taxonomy_nWOs0zXx5ZUobXtn3QOy+A==”)
$Groups = $termStore.Groups 
$ctx.Load($termStore) 
$ctx.Load($Groups) 
$ctx.ExecuteQuery() 

$Group = $Groups.GetByName(“HR Group”) 
$termSet = $Group.TermSets.GetByName(“New”) 
$ctx.Load($termSet) 
$ctx.ExecuteQuery() 

if($PrimaryTag)
{
  $field = [Microsoft.SharePoint.Client.ClientContext].GetMethod(“CastTo”).MakeGenericMethod([Microsoft.SharePoint.Client.Taxonomy.TaxonomyField]).Invoke($ctx, $PrimaryTag)
  $field.SspId = $termStore.Id
  $field.TermSetId = $termSet.Id
  $field.Update()
  $ctx.ExecuteQuery()
}

$ctx.ExecuteQuery()

Update: Script is also available

https://gallery.technet.microsoft.com/Change-MMS-Field-Term-Set-efdf705f

Create SharePoint Online Site Inventory using CSOM


There is no simply method available in SharePoint Online to see the structure of the SharePoint sites.  The script below tries to get some inventory which may help you may be during the migration or may be documentation of SharePoint Online Sites.  The script iterates all sites and sub sites. The script uses Basic Client Side Object Model components.  I suggest you download the latest Components from GitHub.

$Creds = Get-Credential
$Creds.UserName
 
Connect-SPOService -Url https://tenant-Admin.sharepoint.com -Credential $Creds
Connect-MsolService -Credential $Creds
$Users = Get-MsolUser -All
$UnLicensedUsers = Get-MsolUser -UnlicensedUsersOnly
$Users.Count
$UnLicensedUsers.Count
$Users.Count – $UnLicensedUsers.Count
 
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll”
Add-Type -Path “C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll”
 
$SitesIncludingPersonal = Get-SPOSite -IncludePersonalSite $true -Limit All -Detailed
$SitesIncludingPersonal | Select * | Export-Csv -Path C:\temp\PondSites1.csv
$Sites = Get-SPOSite -Limit All -Detailed
foreach($asite in $Sites)
{
  Set-SPOUser -Site $asite.Url -LoginName $Creds.UserName -IsSiteCollectionAdmin $true -ErrorAction SilentlyContinue
}
$Sites | Export-Csv -Path C:\temp\PondSites.csv -NoClobber -NoTypeInformation
foreach($asite in $Sites)
{
  $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($asite.Url)
  #Authenticate
  $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName , $Creds.Password)
  $ctx.Credentials = $credentials
 
  #Fetch the users in Site Collection
  $ctx.Load($ctx.Web.Webs)
  $ctx.ExecuteQuery()
  $Count=0
  foreach($aWEb in $ctx.Web.Webs)
  {
    $Count++;
  }
  Write-Host $asite.Url $Count
}
 
$Databases = $null
$Databases = @();
foreach($aSite in $Sites)
{
  $Users = Get-SPOUser -Site $aSite.Url -Limit All | Select * -Verbose
  foreach($User in $Users)
  {
    $DB = New-Object PSObject
    Add-Member -input $DB noteproperty ‘SiteUrl’ $aSite.Url 
    Add-Member -input $DB noteproperty ‘DisplayName’ $User.DisplayName
    Add-Member -input $DB noteproperty ‘LoginName’ $User.LoginName
    Add-Member -input $DB noteproperty ‘IsSiteAdmin’ $User.IsSiteAdmin
    Add-Member -input $DB noteproperty ‘IsGroup’ $User.IsGroup
    $Databases += $DB
  }
}
$UsersOutput = “C:\temp\AllSitesUsers.csv”
$Databases | Export-Csv -Path $UsersOutput -NoTypeInformation -Force
                                                                      
 
$Databases = $null
$Databases = @();
foreach($asite in $Sites)
{
  $Groups = Get-SPOSiteGroup -Site $asite.Url -Limit 100 | Select *
  foreach($Group in $Groups)
  {
    $DB = New-Object PSObject
    Add-Member -input $DB noteproperty ‘SiteUrl’ $aSite.Url 
    Add-Member -input $DB noteproperty ‘DisplayName’ $Group.Title
    Add-Member -input $DB noteproperty ‘LoginName’ $Group.LoginName
    Add-Member -input $DB noteproperty ‘OwnerLoginName’ $Group.OwnerLoginName
    Add-Member -input $DB noteproperty ‘OwnerTitle’ $Group.OwnerTitle
    $RolesString = “”
    foreach($Role in $Group.Roles)
    {
      $RolesString+=$Role
      $RolesString+=”,”
    }
    Add-Member -input $DB noteproperty ‘Roles’ $RolesString
    $Databases += $DB
  }
}
 
$UsersOutput = “C:\temp\AllSiteGroups.csv”
$Databases | Export-Csv -Path $UsersOutput -NoTypeInformation -Force
 
$Databases = $null
$Databases = @();
foreach($asite in $Sites)
{
  $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($asite.Url)
  #Authenticate
  $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName , $Creds.Password)
  $ctx.Credentials = $credentials
 
  #Fetch the users in Site Collection
  $ctx.Load($ctx.Web.Webs)
  $Lists = $ctx.Web.Lists
  $ctx.Load($Lists)
  $ctx.ExecuteQuery()
  foreach($List in $Lists)
  {
    if($List.Hidden -eq $false)
    {
      if($List.ItemCount -gt 100)
      {
        $DB = New-Object PSObject
        Add-Member -input $DB noteproperty ‘SiteUrl’ $asite.Url 
        Add-Member -input $DB noteproperty ‘Title’ $List.Title
        Add-Member -input $DB noteproperty ‘ListType’ $List.BaseType
        Add-Member -input $DB noteproperty ‘ItemCount’ $List.ItemCount
        $Databases += $DB
        Write-Host $aSite.Url $List.Title $List.ItemCount
      }
      
    }
  }
 
  foreach($aWeb in $ctx.Web.Webs)
  {
    $Lists = $aWeb.Lists
    $ctx.Load($Lists)
    $ctx.ExecuteQuery()
    foreach($List in $Lists)
    {
      if($List.Hidden -eq $false)
      {
        if($List.ItemCount -gt 100)
        {
          $DB = New-Object PSObject
          Add-Member -input $DB noteproperty ‘SiteUrl’ $aWeb.Url 
          Add-Member -input $DB noteproperty ‘Title’ $List.Title
          Add-Member -input $DB noteproperty ‘ListType’ $List.BaseType
          Add-Member -input $DB noteproperty ‘ItemCount’ $List.ItemCount
          $Databases += $DB
          Write-Host $aSite.Url $List.Title $List.ItemCount
        }
      
      }
    }
  }
  Write-Host $asite.Url
}
 
$UsersOutput = “C:\temp\Libraries.csv”
$Databases | Export-Csv -Path $UsersOutput -NoTypeInformation -Force
$Databases | Out-GridView
 
 
$Databases = $null
$Databases = @();
foreach($asite in $Sites)
{
  #$asite = “https://leapthepond.sharepoint.com”
  $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($asite.url)
  #Authenticate
  $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName , $Creds.Password)
  $ctx.Credentials = $credentials
 
  #Fetch the users in Site Collection
  $ctx.Load($ctx.Web.Webs)
  $Lists = $ctx.Web.Lists
  $ctx.Load($Lists)
  $ctx.ExecuteQuery()
  foreach($List in $Lists)
  {
    if($List.Hidden -eq $false)
    {
      $ctx.Load($List)
      $ctx.ExecuteQuery()
      if($List.WorkflowAssociations.Count -gt 0)
      {
        $DB = New-Object PSObject
        Add-Member -input $DB noteproperty ‘SiteUrl’ $asite.Url 
        Add-Member -input $DB noteproperty ‘Title’ $List.Title
        Add-Member -input $DB noteproperty ‘ListType’ $List.BaseType
        Add-Member -input $DB noteproperty ‘WorkflowsCount’ $List.WorkflowAssociations.Count
        $Databases += $DB
        Write-Host $List.Title $List.ItemCount
      }
      
    }
  }
 
  foreach($aWeb in $ctx.Web.Webs)
  {
    $Lists = $aWeb.Lists
    $ctx.Load($Lists)
    $ctx.ExecuteQuery()
    foreach($List in $Lists)
    {
      if($List.Hidden -eq $false)
      {
        if($List.ItemCount -gt 100)
        {
          $DB = New-Object PSObject
          Add-Member -input $DB noteproperty ‘SiteUrl’ $aWeb.Url 
          Add-Member -input $DB noteproperty ‘Title’ $List.Title
          Add-Member -input $DB noteproperty ‘ListType’ $List.BaseType
          Add-Member -input $DB noteproperty ‘WorkflowsCount’ $List.WorkflowAssociations.Count
          $Databases += $DB
          Write-Host $List.Title $List.ItemCount
        }
      
      }
    }
  }
  Write-Host $asite.Url
}
 
$UsersOutput = “C:\temp\Libraries.csv”
$Databases | Export-Csv -Path $UsersOutput -NoTypeInformation -Force
$Databases | Out-GridView

Export Server Certificates to CSV and Email


We have large SharePoint environments where we use different certificates.  Certificates expires over the period of times and some time admins leave old certificates on the servers which cause confusions.  To Avoid this behavior I wrote the script below to check Root and Personal certs from all servers that I need and save it to CSV.  I have not made the servers list SharePoint specific so you can add as many servers as you needed.  The script also sends the CSV as email.  I hope it might save time for others.

Add-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue

#The mail address of who will receive the backup exception message

 

$from

 

= “someone@domain.com”

#Send email function

 

function

 

SendMail($subject, $body, $file)

{

try

{

#Getting SMTP server name and Outbound mail sender address

$caWebApp = (Get-SPWebApplication -IncludeCentralAdministration) | ? { $_.IsAdministrationWebApplication -eq $true }

$smtpServer = $caWebApp.OutboundMailServiceInstance.Server.Address

$smtp = new-object Net.Mail.SmtpClient($smtpServer)

#Creating a Mail object

$message = New-Object System.Net.Mail.MailMessage

$att = New-Object System.Net.Mail.Attachment($file)

$message.Subject = $subject

$message.Body = $body

$message.Attachments.Add($att)

$To = “someone@domain.com”

$message.To.Add($to)

$message.From = $from

#Creating SMTP server object

#Sending email

$smtp.Send($message)

Write-Host “Email has been Sent!”

}

catch [System.Exception]

{

Write-Host “Mail Sending Error:” $_.Exception.Message -ForegroundColor Red

}

}

 

function

 

Get-Cert($computer){

$ro=[System.Security.Cryptography.X509Certificates.OpenFlags]“ReadOnly”

$lm=[System.Security.Cryptography.X509Certificates.StoreLocation]“LocalMachine”

$store=new-object System.Security.Cryptography.X509Certificates.X509Store(“\\$computer\My”,$lm)

$store.Open($ro)

$store.Certificates

}

 

function

 

Get-RootCert($computer){

$ro=[System.Security.Cryptography.X509Certificates.OpenFlags]“ReadOnly”

$lm=[System.Security.Cryptography.X509Certificates.StoreLocation]“LocalMachine”

$store=new-object System.Security.Cryptography.X509Certificates.X509Store(“\\$computer\root”,$lm)

$store.Open($ro)

$store.Certificates

}

 

$Servers

 

= @(“Server1”,“Server2”)

$datestring

 

= (Get-Date).ToString(“s”).Replace(“:”,“-“)

$file

 

= “E:\temp\Certificates-$env:COMPUTERNAME$datestring.csv”

$Databases

 

= @();

foreach

 

($Server in $Servers)

{

$Certs = Get-Cert($Server)

foreach($Cert in $Certs)

{

$FriendlyName = $cert.FriendlyName

$Thumbprint = $Cert.Thumbprint

$Issuer = $Cert.Issuer

$Subject = $Cert.Subject

$SerialNumber = $Cert.SerialNumber

$NotAfter = $Cert.NotAfter

$NotBefore = $Cert.NotBefore

$DnsNameList = $cert.DnsNameList

$Version = $cert.Version

$DB = New-Object PSObject

Add-Member -input $DB noteproperty ‘ComputerName’ $Server

Add-Member -input $DB noteproperty ‘FriendlyName’ $FriendlyName

Add-Member -input $DB noteproperty ‘DnsNameList’ $DnsNameList

Add-Member -input $DB noteproperty ‘ExpirationDate’ $NotAfter

Add-Member -input $DB noteproperty ‘IssueDate’ $NotBefore

Add-Member -input $DB noteproperty ‘Thumbprint’ $Thumbprint

Add-Member -input $DB noteproperty ‘Issuer’ $Issuer

Add-Member -input $DB noteproperty ‘Subject’ $Subject

Add-Member -input $DB noteproperty ‘SerialNumber’ $SerialNumber

$Databases += $DB

}

$RootCerts = Get-RootCert($Server)

foreach($Cert in $RootCerts)

{

$FriendlyName = $cert.FriendlyName

$Thumbprint = $Cert.Thumbprint

$Issuer = $Cert.Issuer

$Subject = $Cert.Subject

$SerialNumber = $Cert.SerialNumber

$NotAfter = $Cert.NotAfter

$NotBefore = $Cert.NotBefore

$DnsNameList = $cert.DnsNameList

$Version = $cert.Version

$DB = New-Object PSObject

Add-Member -input $DB noteproperty ‘ComputerName’ $Server

Add-Member -input $DB noteproperty ‘FriendlyName’ $FriendlyName

Add-Member -input $DB noteproperty ‘DnsNameList’ $DnsNameList

Add-Member -input $DB noteproperty ‘ExpirationDate’ $NotAfter

Add-Member -input $DB noteproperty ‘IssueDate’ $NotBefore

Add-Member -input $DB noteproperty ‘Thumbprint’ $Thumbprint

Add-Member -input $DB noteproperty ‘Issuer’ $Issuer

Add-Member -input $DB noteproperty ‘Subject’ $Subject

Add-Member -input $DB noteproperty ‘SerialNumber’ $SerialNumber

$Databases += $DB

}

}

 

# $Databases | Out-GridView

 

$Databases

 

| Sort FriendlyName | Export-Csv -Path $file -NoTypeInformation -Append -Force

SendMail

 

“Farm Sertificates” “Server Certificates” $file

Script is also available at

https://gallery.technet.microsoft.com/Export-Server-Certificates-eba16e6e