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

Advertisements

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

 

Get Site collection Administrators for SharePoint Online using PowerShell


SharePoint Online sites can have thousands of users.  There are times when people are asked to get site collection admins using SharePoint Online Management Shell or Get-SPOUser cmdlet with Where $_.IsSiteAdmin.  This will result in timeout if there are lot of users on the site.  Instead I decided to use a different approach.  First get all the users from site into a CSV and then make the check. 

image

$Creds = Get-Credentials

$site = ‘https://sharepoint-admin.sharepoint.com’
Connect-SPOService -Url $site -Credential $Creds

$AllUsers = Get-SPOUser -Site https://site.sharepoint.com -Limit all | select DisplayName, LoginName,IsSiteAdmin
$AllUsers | Export-Csv -Path C:\temp\allusers.csv -NoTypeInformation -Force
$Data = Import-Csv C:\temp\allusers.csv
foreach($aUser in $Data)
{
  if($aUser.IsSiteAdmin -eq “True”)
  {
    Write-Host $aUser.DisplayName $aUser.LoginName
  }
}

Add Fields to List using CSOM and PowerShell


A very basic script to add site columns to SharePoint Online List using Client Side Object Model and PowerShell.  This is a starter script and can be upgraded to allow support for content types as well. 

#$Creds = Get-Credential -Message “Please enter SPO Admin Credentials.”
$SPOSiteURL = https://tenant.sharepoint.com/
#Add references to SharePoint client assemblies and authenticate to Office 365 site – required for CSOM
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll” -ErrorAction Stop
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll” -ErrorAction Stop

$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName, $Creds.Password)
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SPOSiteURL)
$ctx.Credentials = $credentials
$ctx.Load($ctx.Web);
Write-Host “Please enter Library Title” -ForegroundColor Yellow
$LibraryName = Read-Host
$List = $ctx.web.Lists.GetByTitle($LibraryName)
$ctx.Load($List);
$ctx.ExecuteQuery()

if($List -eq $null)
{
  return;
}

$Fields = $ctx.web.AvailableFields
$ctx.Load($Fields)
$ctx.ExecuteQuery()

$CustomerRepType = $ctx.Web.AvailableFields | Where {$_.Title -eq “CustomerRepType”}
$DocumentType = $ctx.Web.AvailableFields | Where {$_.Title -eq “DocumentType”}
$ctx.Load($CustomerRepType)
$ctx.Load($DocumentType)

$LegalDocumentTypes = $ctx.Web.AvailableFields | Where {$_.Title -eq “LegalDocumentType”}
$Product = $ctx.Web.AvailableFields | Where {$_.Title -eq “Product”}
$ctx.Load($LegalDocumentTypes)
$ctx.Load($Product)

$Solution = $ctx.Web.AvailableFields | Where {$_.Title -eq “Solution”}
$WWRegion = $ctx.Web.AvailableFields | Where {$_.Title -eq “WWRegion”}
$ctx.Load($Solution)
$ctx.Load($WWRegion)
$ctx.ExecuteQuery()

#Add fields to the list
$List.Fields.Add($CustomerRepType)
$List.Fields.Add($DocumentType)
$List.Fields.Add($LegalDocumentTypes)
$List.Fields.Add($Product)
$List.Fields.Add($Solution)
$List.Fields.Add($WWRegion)
$List.Update()
$ctx.ExecuteQuery()

#Add fields to the default view
$DefaultView = $List.DefaultView
$DefaultView.ViewFields.Add(“CustomerRepType”)
$DefaultView.ViewFields.Add(“DocumentType”)
$DefaultView.ViewFields.Add(“LegalDocumentTypes”)
$DefaultView.ViewFields.Add(“Product”)
$DefaultView.ViewFields.Add(“Solution”)
$DefaultView.ViewFields.Add(“WWRegion”)
$DefaultView.Update()
$ctx.ExecuteQuery()

Script is also available

https://gallery.technet.microsoft.com/Add-Colums-to-List-CSOM-ee62a96a

Bulk Upload Files to SharePoint Online with Metadata


There are many scripts on Technet that helps you upload the content from File Shares to SharePoint Online or OneDrive for Business but none of them actual copy the user information from file shares.  The script below migrates metadata like created by and created and modificed dates along with the files.  The script uses SharePoint Online Client Side Object Model.  I hope this helps someone.  

Note: The actual script was written by someone at Technet which I do not remember, I just made serious modifications to make it possible to extract and upload metadata.

[CmdletBinding()]
param(
  [Parameter(Mandatory=$True,Position=1)]
  [String]$UserName,
  [Parameter(Mandatory=$True,Position=2)]
  [String]$Password,
  [Parameter(Mandatory=$True, Position=3)]
  [String]$SiteURL,
  [Parameter(Mandatory=$True, Position=4)]
  [String]$DocLibName,
  [Parameter(Mandatory=$True, Position=5)]
  [String]$Folder,
  [Parameter(Mandatory=$False, Position=6)]
  [Switch]$Checkin,
  [Parameter(Mandatory=$False, Position=7)]
  [string]$MetaDataCSV,
  [Parameter(Mandatory=$False, Position=8)]
  [Switch]$O365
)

<#
    Add references to SharePoint client assemblies and authenticate to Office 365 site – required for CSOM

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

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”

function GetUserLookupString{
    [CmdletBinding()]
    param($context, $userString)
   
    try{
        $user = $context.Web.EnsureUser($userString)
        $context.Load($user)
        ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500
       
        # The “proper” way would seem to be to set the user field to the user value object
        # but that does not work, so we use the formatted user lookup string instead
        #$userValue = New-Object Microsoft.SharePoint.Client.FieldUserValue
        #$userValue.LookupId = $user.Id
        $userLookupString = “{0};#{1}” -f $user.Id, $user.LoginName
    }
    catch{
        Write-Host “Unable to ensure user ‘$($userString)’.”
        $userLookupString = $null
    }
   
    return $userLookupString
}

function ExecuteQueryWithIncrementalRetry([Microsoft.SharePoint.Client.ClientContext] $context, $retryCount, $delay)
{
    if ($retryCount -eq $null) { $retryCount = 5 } # default to 5
    if ($delay -eq $null) { $delay = 500 } # default to 500
    $retryAttempts = 0
    $backoffInterval = $delay
    if ($retryCount -le 0)
    {
        throw New-Object ArgumentException(“Provide a retry count greater than zero.”)
    }

    if ($delay -le 0)
    {
        throw New-Object ArgumentException(“Provide a delay greater than zero.”)
    }

    # Do while retry attempt is less than retry count
    while ($retryAttempts -lt $retryCount)
    {
        try
        {
            $context.ExecuteQuery()
            return
        }
        catch [System.Net.WebException]
        {
            $response = [System.Net.HttpWebResponse]$_.Exception.Response
            # Check if request was throttled – http status code 429
            # Check is request failed due to server unavailable – http status code 503
            if ($response -ne $null -and ($response.StatusCode -eq 429 -or $response.StatusCode -eq 503))
            {
                # Output status to console. Should be changed as Debug.WriteLine for production usage.
                Write-Host “CSOM request frequency exceeded usage limits. Sleeping for $backoffInterval seconds before retrying.”

                # Add delay for retry
                Start-Sleep -m $backoffInterval

                # Add to retry count and increase delay.
                $retryAttempts++
                $backoffInterval = $backoffInterval * 2
            }
            else
            {
                throw
            }
        }
    }
    throw New-Object Exception(“Maximum retry attempts $retryCount, has be attempted.”)
}

<#
    Define Functions
#>

<#
    Upload File – This function performs the actual file upload
#>
function UploadFile($DestinationFolder, $File)
{
    #Get the datastream of the file, assign it to a variable
    $FileStream = New-Object IO.FileStream($File.FullName,[System.IO.FileMode]::Open)

    #Create an instance of a FileCreationInformation object
    $FileCreationInfo = New-Object Microsoft.SharePoint.Client.FileCreationInformation

    #Indicate whether or not you would like to overwrite files in the event of a conflict
    $FileCreationInfo.Overwrite = $True

    #Make the datastream of the file you wish to create equal to the datastream of the source file
    $FileCreationInfo.ContentStream = $FileStream

    #Make the URL of the file equal to the $File variable which was passed to the function.  This will be equal to the source file name
    $FileCreationInfo.url = $File

    #Add the file to the destination folder which was passed to the function, using the FileCreationInformation supplied.  Assign this to a variable so that it can be loaded into context.
    $Upload = $DestinationFolder.Files.Add($FileCreationInfo)
    if($Checkin)
    {
        $Context.Load($Upload)
        #$Context.ExecuteQuery()
        ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500
        if($Upload.CheckOutType -ne “none”)
        {
            $Upload.CheckIn(“Checked in by Administrator”, [Microsoft.SharePoint.Client.CheckinType]::MajorCheckIn)
        }
    }
    $Context.Load($Upload)
    #$Context.ExecuteQuery()
    ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500
   
    if($MetaDataCSVData)
    {
      $MetaDataItem = $MetaDataCSVData | ? FilePath -eq $File.FullName
      Write-Host “User does not exist in metadata file.  Standard metadata will be used…” -ForegroundColor Yellow
      if($MetaDataItem)
      {
        Write-Host “Adding Metadata to File Item…$($File.FullName)”
        $listItem = $Upload.ListItemAllFields
        $Context.Load($listItem)
        #$Context.ExecuteQuery()
        ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500
   
        $Item = $List.GetItemById($listItem.Id);
        $Context.Load($Item)
        #$Context.ExecuteQuery()
        ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500
     
        $Item[“Description0”] = $MetaDataItem.Description      
        $Author = GetUserLookupString $Context $MetaDataItem.Author
        if($Author)
        {
          $Item[“Author”] = $Author
        }
        $Editor = GetUserLookupString $Context $MetaDataItem.Editor
        if($Editor)
        {
          $Item[“Editor”] = $Editor
        }
       
        [System.DateTime]$CreatedOnDate = $MetaDataItem.CreatedOn
        [System.DateTime]$ModifiedOnDate = $MetaDataItem.ModifiedOn
        $Item[“Created”] = $CreatedOnDate
        $Item[“Modified”] = $ModifiedOnDate
        try
        {
          $Item.Update()
        }
        catch
        { Write-Host “Exception Occured… But script will continue” }
        #$Context.ExecuteQuery()
        ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500
      }
    }
   
   
 
}

<#
    Create Folder Function.
#>
function PopulateFolder($ListRootFolder, $FolderRelativePath)
{
    #split the FolderRelativePath passed into chunks (between the backslashes) so that we can check if the folder structure exists
    $PathChunks = $FolderRelativePath.split(“\”)

    #Make sure we start with a fresh WorkingFolder for every folder passed to the function
    if($WorkingFolder)
    {
        Remove-Variable WorkingFolder
    }

    #Start with the root folder of the list, load this into context
    $WorkingFolder = $ListRootFolder
    $Context.load($WorkingFolder)
    $Context.ExecuteQuery()

    #Load the folders of the current working folder into context
    $Context.load(($WorkingFolder.folders))
    #$Context.executeQuery()
    ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500

    #Set the FileSource folder equal to the absolute path of the folder that passed to the function
    $FileSource = $Folder + $FolderRelativePath
   
    #Loop through the folder chunks, ensuring that the correct folder hierarchy exists in the destination
    foreach($Chunk in $PathChunks)
    {
        #Check to find out if a subfolder exists in the current folder that matches the patch chunk being evaluated
        if($WorkingFolder.folders | ? {$_.name -eq $Chunk})
        {
            #Log the status to the PowerShell host window
            Write-Host “Folder $Chunk Exists in” $WorkingFolder.name -ForegroundColor Green

            #Since we will be evaluating other chunks in the path, set the working folder to the current folder and load this into context.
            $WorkingFolder = $WorkingFolder.folders | ? {$_.name -eq $Chunk}
            $Context.load($WorkingFolder)
            $Context.load($WorkingFolder.folders)
            #$Context.ExecuteQuery()
            ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500

        }
        else
        {
            #If the folder doesn’t exist, Log a message indicating that the folder doesn’t exist, and another message indicating that it is being created
            Write-Host “Folder $Chunk Does Not Exist in” $WorkingFolder.name -ForegroundColor Yellow
            Write-Host “Creating Folder $Chunk in” $WorkingFolder.name -ForegroundColor Green
           
            #Load the working folder into context and create a subfolder with a name equal to the chunk being evaluated, and load this into context
            $Context.load($WorkingFolder)
            $Context.load($WorkingFolder.folders)
            #$Context.ExecuteQuery()
            ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500
            $WorkingFolder= $WorkingFolder.folders.add($Chunk)
            $Context.load($WorkingFolder)
            $Context.load($WorkingFolder.folders)
            #$Context.ExecuteQuery()
            ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500
           
        }

    }

    #Folder is confirmed existing or created – now it’s time to list all files in the source folder, and assign this to a variable
    $FilesInFolder = Get-ChildItem -Path $FileSource | ? {$_.psIsContainer -eq $False}
   
    #For each file in the source folder being evaluated, call the UploadFile function to upload the file to the appropriate location
    Foreach ($File in ($FilesInFolder))
    {

        #Notify the operator that the file is being uploaed to a specific location
        Write-Host “Uploading file ” $file.Name “to” $WorkingFolder.name -ForegroundColor Cyan

        #Upload the file
        UploadFile $WorkingFolder $File

    }
   
   
   
}

<#
    Bind your context to the site collection
#>
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)

<#
    Create a credential object using the username and password supplied
#>
if($O365)
{
    $Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))
}
else
{
    $Creds = New-Object System.Net.NetworkCredential($UserName, (ConvertTo-SecureString $Password -AsPlainText -Force))

}

<#
    Set the credentials that are used in the context.
#>
$Context.Credentials = $Creds

<#
    Retrieve the library, and load it into the context
#>
$List = $Context.Web.Lists.GetByTitle($DocLibName)
$Web = $Context.Web;
$Context.Load($Web)
$Context.Load($List)
$List.EnableVersioning = $false
$List.Fields.GetByInternalNameOrTitle(“Author”).ReadOnlyField = $false;
$List.Fields.GetByInternalNameOrTitle(“Editor”).ReadOnlyField = $false;
$List.Fields.GetByInternalNameOrTitle(“Created”).ReadOnlyField = $false;
$List.Fields.GetByInternalNameOrTitle(“Modified”).ReadOnlyField = $false;
$List.Update()
#$Context.ExecuteQuery()
ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500

$MetaDataCSV = “C:\temp\Metadata.csv”
$MetaDataCSVData = Import-Csv -Path $MetaDataCSV

#Get a recursive list of all folders beneath the folder supplied by the operator
$AllFolders = Get-ChildItem -Recurse -Path $Folder |? {$_.psIsContainer -eq $True}

#Get a list of all files that exist directly at the root of the folder supplied by the operator
$FilesInRoot = Get-ChildItem -Path $Folder | ? {$_.psIsContainer -eq $False}

#Upload all files in the root of the folder supplied by the operator
Foreach ($File in ($FilesInRoot))
{

    #Notify the operator that the file is being uploaded to a specific location
    Write-Host “Uploading file ” $File.Name “to” $DocLibName -ForegroundColor Cyan

    #Upload the file
    UploadFile($list.RootFolder) $File
   

}

#Loop through all folders (recursive) that exist within the folder supplied by the operator
foreach($CurrentFolder in $AllFolders)
{
    #Set the FolderRelativePath by removing the path of the folder supplied by the operator from the fullname of the folder
    $FolderRelativePath = ($CurrentFolder.FullName).Substring($Folder.Length)
   
    #Call the PopulateFolder function for the current folder, which will ensure that the folder exists and upload all files in the folder to the appropriate location
    PopulateFolder ($list.RootFolder) $FolderRelativePath
}

$List.Fields.GetByInternalNameOrTitle(“Author”).ReadOnlyField = $true;
$List.Fields.GetByInternalNameOrTitle(“Editor”).ReadOnlyField = $true;
$List.Fields.GetByInternalNameOrTitle(“Created”).ReadOnlyField = $true;
$List.Fields.GetByInternalNameOrTitle(“Modified”).ReadOnlyField = $true;
$List.EnableVersioning = $True
$List.Update()
#$Context.ExecuteQuery()
ExecuteQueryWithIncrementalRetry -context $Context -retryCount 5 -delay 500

Script is also available below

https://gallery.technet.microsoft.com/Bulk-Upload-Files-to-b9ed3126

Add Owner Group to Users OneDrive for Business


Every OneDrive for business site in SharePoint Online has a primary and secondry owner.  There are situations when primary owner leaves the company but during the initial setup administrator did not entered the secondry administrator value.  This value should be a group instead of a User.  Around a year ago I faced this situation with a customer who had over 40K OneDrive Sites created but now no one had ownership of these sites.  To add the site collection owner on these sites I used the script below.  This hopefuly will help someone else.

$Creds = Get-Credential -Message “Please enter SPO Admin Credentials.”
Start-Transcript -Path C:\temp\transcript.txt
Connect-MsolService -Credential $Creds

$SPOAdminSiteURL = “https://tenant-admin.sharepoint.com/”
#Add references to SharePoint client assemblies and authenticate to Office 365 site – required for CSOM
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll” -ErrorAction Stop
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll” -ErrorAction Stop
Add-Type -Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll” -ErrorAction Stop

Connect-SPOService -Url $SPOAdminSiteURL -Credential $Creds

$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName, $Creds.Password)
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SPOAdminSiteURL)
$ctx.Credentials = $credentials
$ctx.Load($ctx.Web);
$ctx.ExecuteQuery()

$NewSites =$null;
$NewSites = @();

$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($ctx)
$PeopleLoader =[Microsoft.SharePoint.Client.UserProfiles.ProfileLoader]::GetProfileLoader($ctx)

#To Get the Group ID. Add the Group in People Picker in Site Collection Admin dialog on SPO Admin Center and then use the IE Developer Tools to click on it name
$CSVPath = “C:\Temp\UniUsers.csv”
$IAMCloudOneDriveID = “c:0-.f|rolemanager|s-1-5-21-3431014192-700181988-4181250490-129537758”
try
{
    $Data = Import-Csv -Path $CSVPath
   
    if($Data)
    {
        $Count = 0;
        foreach($User in $Data)
        {
            $Count++;
            try
            {
         
              $Start = (Get-Date).Second
              $Name = $($User.UserName).Trim();
              Write-Host “Getting Prifle Number $Count of $($Data.Count) for User $Name” -ForegroundColor Yellow
              $AccountName = “i:0#.f|membership|$Name”
              $UserProfile = $PeopleManager.GetPropertiesFor($AccountName)
              $ctx.Load($UserProfile)
              $ctx.ExecuteQuery()
             
              if($UserProfile.DisplayName -eq $null -and $UserProfile.Email -eq $null)
              {
                Write-Host “This user $Name does not exist in User Profile System…” -ForegroundColor Red
                continue
              }
            }
            catch
            {
              Write-Output “Error processing a profile “
            }
            $OneDriveUrl = $UserProfile.PersonalUrl
            if($OneDriveUrl.Contains(“Person”))
            {
              try
              {
                $PeopleManager.SetSingleValueProfileProperty($AccountName, “PersonalUrl”, “”);
                $ctx.ExecuteQuery();
                $OneDriveUrl = $null;
              }
              catch
              {
                $PeopleManager.SetSingleValueProfileProperty($AccountName, “PersonalSpace”, “”);
                $ctx.ExecuteQuery();
                $OneDriveUrl = $null;
              }
             
            }
           
            If ($OneDriveUrl -ne $null)
            {
                try
                {
               
                  $SiteUrl = “”
                  Write-Host “Starting to Add Group on User OneDrive for:” $AccountName -ForegroundColor Yellow
                  $SiteUrl = $UserProfile.PersonalUrl
                  Write-Host “”
                  $SiteUrl = $SiteUrl.Remove($SiteUrl.LastIndexOf(“/”),1)
                  $Site = Get-SPOSite -Identity $SiteUrl
                  if($Site)
                  {
                    Write-Host “Adding The Cloud Group …”-ForegroundColor Yellow
                    Set-SPOUser -Site $Site.Url -LoginName $IAMCloudOneDriveID -IsSiteCollectionAdmin $true -ErrorAction SilentlyContinue
                    Write-Host “Group Added Successfully in $($Site.Url)…” -ForegroundColor Green
                  }
                }
                catch
                {
                  Write-Output “Error processing a profile “
                }
             }
             else
             {
                Write-Host “Personal Url is null for $Name. So Creating personal Site…”
                $Email = $UserProfile.Email
                [string]$emails = $Email
                $PeopleLoader.CreatePersonalSiteEnqueueBulk($Email);
               
                Write-Host “Creating User Profile for $($Email)” -ForegroundColor Green
              
                $NewSite = New-Object PSObject
                Add-Member -input $NewSite noteproperty ‘AccountName’ $AccountName
                $NewSites += $NewSite
            }
            $End = (Get-Date).Second
            $Secs = $End – $Start
            $ExpectedTime = ($Data.Count – $Count) * $Secs
            Write-Host “Total Profiles $($Data.Count) Processed Profiles $Count” -ForegroundColor Cyan
            Write-Host “The script took $Secs second(s) to run.” -ForegroundColor DarkMagenta
            Write-Host “Expect this to complete in $(($ExpectedTime/60)) minutes or $(($ExpectedTime/60)/60) Hours” -ForegroundColor Red
           
        }
        foreach($object in $NewSites)
        {
            $AccountName = $object.AccountName
              $UserProfile = $PeopleManager.GetPropertiesFor($AccountName) 
              $ctx.Load($UserProfile)
              $ctx.ExecuteQuery()
             
              $SiteUrl = $UserProfile.PersonalUrl
              if($SiteUrl.Length -gt 0)
              {
                Write-Host “Personal Site Found for $AccountName …” -ForegroundColor Yellow
                $SiteUrl = $Url.Remove($Url.LastIndexOf(“/”),1)
                Set-SPOUser -Site $SiteUrl -LoginName $IAMCloudOneDriveID -IsSiteCollectionAdmin $true -ErrorAction SilentlyContinue
                Write-Host “Group Added Successfully in $($Site.Url)…” -ForegroundColor Green
                $continue = $false
              }
        }
    }
}
catch
{
    Write-Host “Failed.” -ForegroundColor Yellow
}
Stop-Transcript

Script is also available here

https://gallery.technet.microsoft.com/Add-Owner-Group-to-Users-06003db8

Get OneDrive for Business Site URL for Users using CSOM and PowerShell


Get OneDrive Site URL for Users using SharePoint Online Client Object Model and PowerShell.  This is a very basic script but useful.  I have seen some scripts and people are still usin the traditional SharePoint User Profile Web Service Report for reporting from User profile system.  The script below can do the same job nicely.  You just specify the name of the user and it find the oneDirve Site URL.

param
(
  #Must be SharePoint Administrator URL
  [Parameter(Mandatory = $false)]
  [ValidateNotNullOrEmpty()]
  [string] $SPOAdminUrl,

  #Must be SharePoint Administrator URL
  [Parameter(Mandatory = $true)]
  [ValidateNotNullOrEmpty()]
  [string] $InputFilePath
)

$InputFilePath = “C:\Temp\OneDriveUsers.txt”

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”

$Users = Get-Content -Path $InputFilePath

if ($Users.Count -eq 0)
{
  Write-Host $(“Unexpected user count: [{0}]” -f $Users.Count) -ForegroundColor Red
  return
}

#$SPOAdminUrl = “https://sharepointmvp-admin.sharepoint.com/”

$Creds = Get-Credential -Message “Please enter SharePo”
$SPOCreds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName,$Creds.Password)
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
Connect-SPOService -Url $SiteURL -Credential $Creds
$Context.Credentials = $SPOCreds

$profileloader = [Microsoft.SharePoint.Client.UserProfiles.ProfileLoader]::GetProfileLoader($Context)
$PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context) -ErrorAction Inquire

Write-Host “Script Completed”

foreach($aUser in $Users)
{
    Write-Host “Trying to access User Profile for Personal Site…” -ForegroundColor Yellow
   
    $ClaimsUserFormat = “i:0#.f|membership|$aUser”
   
    $UserProfile = $PeopleManager.GetPropertiesFor($ClaimsUserFormat) 
    $Context.Load($UserProfile)
    $Context.ExecuteQuery()
 
    $Profile = $UserProfile | Select-Object PersonalUrl
    $SiteUrl = $Profile.PersonalUrl
   
    if($SiteUrl.Length -gt 0)
    {
      Write-Host “Personal Site Found for $aUser Starting to Loading files…” -ForegroundColor Yellow
      $SiteUrl = $Url.Remove($Url.LastIndexOf(“/”),1)
      $Site = Get-SPOSite -Identity $SiteURL
      Write-Host “Site URL is $($SiteUrl)” -ForegroundColor Yellow
      $continue = $false
    }
}