Lockout Content Database for Migration


Below is a basic script to lockout Content Database for new site collections.  It sets the max to current sites count and warning to less than 1 of count.  May help someone.

$ALLDB = Get-SPContentDatabase
foreach($DB in $ALLDB)
{
    $Count = $DB.Sites.Count
    if($Count -gt 0)
    {
        $CountWarnining = $Count – 1
        Set-SPContentDatabase $DB.Name -MaxSiteCount $Count -WarningSiteCount $CountWarnining
    }
}

Get-SPContentDatabase | Select Name, Current*, Max*, WarningSiteCount | Out-GridView

Advertisements

Export Site Collection Admins from SharePoint Online Sites


Basic but handy script to export Site Collection Administrators using SharePoint Online Client Object Model and SharePoint Online Management Shell.  I hope there is a better to way get that information as the script can take long time to run if you have large number of users.  I ran the script on multiple tenants with 90K users.  I have a minor updated script that also works for SharePoint Online sites created by Office 365 Groups.

$Creds = Get-Credential

$site = ‘https://tenant.sharepoint.com’
$spoadminsite = ‘https://tenant-admin.sharepoint.com’

Connect-SPOService -Url “https://ucsfonline-admin.sharepoint.com” -Credential $Creds
Connect-MsolService -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.UserProfiles.dll”

$Sites = Get-SPOSite -Limit All

$OneDriveWithcollection = @()
$SitesData = IMport-csv -Path $OneDrivePath
foreach($Site in $Sites)
{
    $Site.Owner
    $Url = $Site.Url
    Write-host $Url
    Set-SPOUser -Site $Url -LoginName $Creds.UserName -IsSiteCollectionAdmin $true
    $context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
    $spoadminusers = Get-SPOUser -Site $Url | where {$_.IsSiteAdmin}
    foreach($Admin in $spoadminusers)
    {
        Write-Host “Site :” $Url
        $OneDriveData = “” | Select “Url” , “OwnerName”, “Email”
        $OneDriveData.Url = $Url
        $OneDriveData.OwnerName = $Admin.LoginName
        $OneDriveData.Email = $Admin.Email   
        $OneDriveWithcollection += $OneDriveData
    }
   
}

$OneDriveWithItemsPath= “C:\temp\SPO-SitesAdmins.csv”
$OneDriveWithcollection | Export-Csv $OneDriveWithItemsPath -NoTypeInformation -Encoding UTF8

Update: The script is also available

https://gallery.technet.microsoft.com/Export-Collection-Admins-e8c0c176

Get OneDrive for Business Item Count SharePoint Online PowerShell


$Creds = Get-Credential

$site = ‘https://tenant.sharepoint.com’
$spoadminsite = ‘https://tenant-admin.sharepoint.com’

Connect-MsolService -Credential $Creds
Connect-SPOService -Url $spoadminsite -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.UserProfiles.dll”

#Get the Client Context and Bind the Site Collection
$context = New-Object Microsoft.SharePoint.Client.ClientContext($site)

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

#Fetch the users in Site Collection
$users = Get-MsolUser -All

#Create an Object [People Manager] to retrieve profile information
$people = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($context)
$context.ExecuteQuery()
$collection = @()
Foreach($user in $users)
{
 
    $ClaimsUserFormat = “i:0#.f|membership|$($user.UserPrincipalName)”                 
    $userprofile = $people.GetPropertiesFor($ClaimsUserFormat)
    $context.Load($userprofile)
    $context.ExecuteQuery()
    Write-Host $userprofile.Email
    Write-Host $user.LoginName
    Write-Host $userprofile.AccountName
    if($userprofile.AccountName -ne $null)
    {
        Write-Host “Email : ” $userprofile.Email
        $upp = $userprofile.UserProfileProperties

        $profileData = “” | Select “FirstName” , “LastName” , “AccountName” , “PersonalUrl” , “Email” , “LoginName”
        $profileData.LoginName = $user.UserPrincipalName
        $profileData.FirstName = $upp.FirstName
        $profileData.LastName = $upp.LastName
        $profileData.Email = $upp.WorkEmail
        $profileData.AccountName = $upp.AccountName
        $profileData.PersonalUrl = $userprofile.PersonalUrl
        $collection += $profileData

    }
}

$OneDrivePath= “C:\temp\SPO-UserInformation.csv”
$collection | Export-Csv $OneDrivePath -NoTypeInformation -Encoding UTF8

$OneDriveWithcollection = @()
$SitesData = IMport-csv -Path $OneDrivePath
foreach($Site in $SitesData)
{
  $Url = $Site.PersonalUrl
  if($Url.Contains(“Person.aspx”))
  {
    Write-host “No profile”
  }
  else
  {
    Write-host $Url
    $context = New-Object Microsoft.SharePoint.Client.ClientContext($Url)
    Write-Host $Site.LoginName
    $SiteUrl = $Url.Remove($Url.Length-1, 1)
    Set-SPOUser -Site $SiteUrl -LoginName $Creds.UserName -IsSiteCollectionAdmin $true

    #Authenticate
    $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName , $Creds.Password)
    $context.Credentials = $credentials
    $Web = $context.Web
    $context.Load($Web)
    $List = $context.Web.Lists.GetByTitle(“Documents”)
    $context.Load($List)
    $context.ExecuteQuery();
    Write-Host “Items Found :” $List.ItemCount
    $OneDriveData = “” | Select “Email” , “ItemCount”, “LastItemModifiedDate”, “LastItemUserModifiedDate”
    $OneDriveData.Email = $Site.Email
    $OneDriveData.ItemCount = $List.ItemCount
    $OneDriveData.LastItemModifiedDate = $Web.LastItemModifiedDate   
    $OneDriveData.LastItemUserModifiedDate = $Web.LastItemUserModifiedDate
    $OneDriveWithcollection += $OneDriveData
  }
}

$OneDriveWithItemsPath= “C:\temp\SPO-OneDriveItems.csv”
$OneDriveWithcollection | Export-Csv $OneDriveWithItemsPath -NoTypeInformation -Encoding UTF8

Update: The script is also available below

https://gallery.technet.microsoft.com/Get-OneDrive-for-Business-04b3632c

Send Custom Email Invite for B2B Users in SPO using PowerShell


If external sharing is enabled in SharePoint Online then we can invite users by simply typing in Email address and they will asked to login using a Live ID or an Office 365 Credentials but in controlled envirornment it is not a good Idea and B2B Guest account is used to control that.  The below script send a custom invitation to users from a CSV file.  You can specify the Site Urls that the user has access to.  The script uses a Azure AD Security Group which we assumed is already added to the sites.  The invite is sent and then user is automatically added to the group.  You would need Azure AD PowerShell Module to get this done.  The invitation email is based on Office 365 SMTP for secure email.

CSV Format
Email,DisplayName,SharePointSite,ADGroupName,SPOGroup
user@gmail.com,UserName,”https://tenant.sharepoint.com,https://tenant.sharepoint.com/sites/Test,https://tenant.sharepoint.com/teams/migration/”,Dynamic Group,Members

param
(
  [String]
  [Parameter(Mandatory=$false)]
  $CSVPath
)
$EmailFrom = “Help-SP@mail.com”
function Send-Mail($To,$Body,$Credentials)
{
  try
  {
    $emailSmtpServerPort = “587”
    $emailSmtpUser = $Credentials.UserName
    
    $emailMessage = New-Object System.Net.Mail.MailMessage
    $emailMessage.From = New-Object System.Net.Mail.MailAddress($Credentials.UserName)
    $emailMessage.To.Add($To)
    $emailMessage.Subject = “Invitation to Access: Lumileds SharePoint Online Sites”
    $emailMessage.IsBodyHtml = $true #true or false depends
    $emailMessage.Body = $Body
 
    $SMTPServer = “smtp.office365.com”
    $SMTPClient = New-Object System.Net.Mail.SmtpClient($SMTPServer,$emailSmtpServerPort)
    $SMTPClient.UseDefaultCredentials = $false
    $SMTPClient.EnableSsl = $true
   
    $SMTPClient.Credentials = New-Object System.Net.NetworkCredential($Credentials.UserName, $Credentials.Password)
    #$SMTPClient.Credentials = New-Object System.Net.NetworkCredential($UserName, $Password)
 
    $SMTPClient.Send($emailMessage)
  }
  catch
  {
    $ErrorMessage = $_.Exception.Message
    $FailedItem = $_.Exception.ItemName
    Write-Host “Send Mail Failed :” $ErrorMessage $FailedItem
  }
 
}

$CSVPath = “C:\temp\O365GuestUsersLum.csv”
function Get-ADGroupFromTenant($GroupName)
{
  $groupID = “”
  $AzureADGroup = Get-AzureADGroup -All $true | Where-Object {$_.DisplayName -eq $GroupName}
  if($AzureADGroup)
  {
    $groupID = $AzureADGroup.ObjectId
  }
  else
  {
    $AzureADGroup = New-AzureADGroup -DisplayName $ADGroupName -MailEnabled $false -SecurityEnabled $true
    $groupID = $AzureADGroup.ObjectId
  }

  $groupID = $AzureADGroup.ObjectId
  if(!$groupID)
  {
    Write-Host “Group Could not be created. Please check the connectivit.”
  }
  return $groupID
}

$Creds = Get-Credential -Message “Please enter Office 365 Administrator credentials.”
Connect-AzureAD -Credential $Creds

$invitations = Import-Csv $CSVPath
if($invitations)
{
    foreach ($externalUser in $invitations) {
      if($externalUser.Email)
      {
        try
        {
          Write-Host “Adding user $($externalUser.Email)”
          $SharePointUrls = $externalUser.SharePointSite
          $option = [System.StringSplitOptions]::RemoveEmptyEntries
          $AllUrls = $SharePointUrls.split(“,”,$option)
          #loop over each user in the CSV and create an invite for that user but does not email the user
          try
          { 
            $result= New-AzureADMSInvitation -InvitedUserEmailAddress $externalUser.Email -InvitedUserDisplayName $externalUser.DisplayName -InviteRedirectUrl $AllUrls[0] -InvitedUserMessageInfo $messageInfo -SendInvitationMessage $false
          }
          catch
          {
            Write-Host “User $($externalUser.Email)… Its already exists…” -ForegroundColor Yellow
          }
          $inviteurl = $result.InviteRedeemUrl
          $userid = $result.InvitedUser.Id
          try
          {
            #automatically add the new user to your Security Group
            Add-AzureADGroupMember -ObjectId $groupID -RefObjectId $userid
          }
          catch
          {
            Write-Host “Failled to Add User $($externalUser.Email) to the Group… Its already exists…” -ForegroundColor Yellow
          }
          #send the user a custom email from your Office 365 tenant. Supports HTML.
          $LinkHTML = “<a href=’$inviteurl’>click here</a>”
          $Sites = “”;
       
          foreach($site in $AllUrls)
          {
            $Sites += “<a href=’$site’>$site</a><br/>”
          }
       
          $Image = “<a href=’http://www.domain.com’ title=’lumileds’> <img src=’http://www.domain.com/uploads/images/design/Logo_RGB_195x40.png’/></a>”
       
          #$a = $a + “<tr>”
          #$a = $a + “<td>”
          #$a = $a + “</td>”
          #$a = $a + “</tr>”
       
          $a = “”
       
          $a = “<TABLE style=’border-width: 1px;width:95%;’>”
          $a = $a + “<tr><td>$Image <h1>SharePoint Online Access Invitation!</h1><br><strong>”
          $a = $a + “</td></tr>”
       
          $bt = “”
          $bt = $bt + “<strong>Hello</strong> $($externalUser.DisplayName),<br />”
          $bt = $bt + “You have been invited to access the following SharePoint Site(s).<br/><br/>$Sites<br/>”
          $bt = $bt + “You must $LinkHTML, click or copy the link listed below.<br /><br />”
          $bt = $bt + “<strong>Note:</strong> After completing the signup, please add the SharePoint sites to your browser favourites. Do not use the same invitation link again to login to the site as.”
          $bt = $bt + “the link will expire once it is used.<br/><br/>$inviteurl <br/><br/>”
          $bt = $bt + “You can use your organization’s (Office 365 Only) email and password or a “
          #$bt = $bt + “Microsoft Live ID (Live.com, Hotmail.com, Outlook.com) and password to login to SharePoint site(s).<br /><br />”       
          $bt = $bt + “Thanks,<br/><strong>Office 365 Administrator</strong><br/>”
       
          $a = $a + “<tr><td>$bt”
          $a = $a + “</td></tr></table>”

          Send-Mail -To $result.InvitedUserEmailAddress -Credentials $Creds -Body $a
          Write-Host “User $($result.InvitedUserEmailAddress) is added as Guest and invitation email has been sent…” -ForegroundColor Green
        }
        catch
        {
            Write-Host “Failled to Add User $($externalUser.Email)… But script will continue…” -ForegroundColor Yellow
        }
      }
     
    }
}

Update: Code is also available below.

https://gallery.technet.microsoft.com/Send-Custom-Email-Invite-9b690297

Find URL from Text File using PowerShell


$FilePath = “C:\temp\data.txt”
$Regex = “(?:(?:https?|ftp|file):\/\/|www\.|ftp\.)(?:\([-A-Z0-9+&@#\/%=~_|$?!:,.]*\)|[-A-Z0-9+&@#\/%=~_|$?!:,.])*(?:\([-A-Z0-9+&@#\/%=~_|$?!:,.]*\)|[A-Z0-9+&@#\/%=~_|$])”
$FirstLink = “”
foreach($line in [System.IO.File]::ReadLines($FilePath))
{
       $FirstLink = $line | Where-Object {$_ -match $Regex}
       if($FirstLink)
       {
         Write-Host “Found Download Link…” -ForegroundColor Yellow
         break
       }
}
$FirstLink

Change MMS Field Term Set Default value using CSOM and PowerShell


The script below allows you to change Term Column Default Value 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 default value.  The script is iterating all sites and sub sites to find the fix the column.  It is a best practice to use Site Column.  You must use latest CSOM Components from Github for better performance.

$Creds = Get-Credential
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”

$Databases = $null
$Databases = @();

$ctx = New-Object Microsoft.SharePoint.Client.ClientContext(“https://tenant.sharepoint.com”)
#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)
$ctx.Load($ctx.Web.Webs)
$Lists = $ctx.Web.Lists
$ctx.Load($Lists)
$ctx.ExecuteQuery()

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

$termStore = $taxStore.TermStores.GetByName(“Taxonomy_H2lgNrFxKmSlqqMA0l4JFw==”)
$Groups = $termStore.Groups 
$ctx.Load($termStore) 
$ctx.Load($Groups) 
$ctx.ExecuteQuery()

$Group = $Groups.GetByName(“Site Collection – tenant.sharepoint.com”) 
$termSet = $Group.TermSets.GetByName(“PrimaryTags”) 
$ctx.Load($termSet) 
$ctx.ExecuteQuery() 

foreach($List in $Lists)
{
  if($List.Hidden -eq $false)
  {
      if($Title -eq “Site Assets” -or $Title -eq “Master Page Gallery”)
      { continue }
      if($List.BaseType -eq “DocumentLibrary”)
      {
            $Fields = $List.Fields
            $ctx.Load($Fields)
            $ctx.ExecuteQuery()
            $PrimaryTag = $Fields | Where {$_.Title -eq “Primary Tag”}
            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()
            }
      }
  }
}
foreach($aWeb in $ctx.Web.Webs)
{
    $Lists = $aWeb.Lists
    $ctx.Load($Lists)
    $ctx.ExecuteQuery()
    foreach($List in $Lists)
    {
        $Title = $List.Title
        if($Title -eq “Site Assets” -or $Title -eq “Master Page Gallery”)
        { continue }
        if($List.BaseType -eq “DocumentLibrary”)
        {
            $Fields = $List.Fields
            $ctx.Load($Fields)
            $ctx.ExecuteQuery()
            $PrimaryTag = $Fields | Where {$_.Title -eq “Primary Tag”}
            if($PrimaryTag)
            {
              $field = [Microsoft.SharePoint.Client.ClientContext].GetMethod(“CastTo”).MakeGenericMethod([Microsoft.SharePoint.Client.Taxonomy.TaxonomyField]).Invoke($ctx, $PrimaryTag)
              if(!$field.DefaultValue.Contains(“#Project”))
              {
                $field.DefaultValue = “44;#Project Management|bf247376-f948-45b0-9dc6-69b94e9978f4”
                $field.Update()
              }
            }
            Write-Host $aSite.Url $List.Title $List.ItemCount
        }
    }
  }

Update: The script is also available

https://gallery.technet.microsoft.com/The-script-below-allows-31b6ee7f

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