Export SharePoint Online Site Collection Information to SQL Server


There is easy way to collection reports when you have large number of Site collections on SharePoint Online.  The new Admin center will help but sometime customers have external system where they want to review and control site collection information.  The script below will iterate site collections, sites, lists and permissions and store them to a SQL Server Database.  It uses SharePoint Online Client components so you must downloaded and install the latest version of client Components from PowerShell Gallary using the method below

https://www.nuget.org/packages/Microsoft.SharePointOnline.CSOM

Download nuget command line (I saved it to c:\nuget)

    Command Prompt: 

    1. cd C:\nuget
    2. nuget.exe install Microsoft.SharePointOnline.CSOM

    Powershell (elevated)

    1. cd C:\nuget
    2. Install-Package -Name ‘Microsoft.SharePointOnline.CSOM’ -Source .\Microsoft.SharePointOnline.CSOM.16.1.8412.1200
    3. Import-Module ‘C:\nuget\Microsoft.SharePointOnline.CSOM.16.1.8412.1200\lib\net45\Microsoft.SharePoint.Client.dll

    https://gallery.technet.microsoft.com/Export-SharePoint-Online-27920e67

    Add Owner to SharePoint Online List PowerShell


    This script uses SharePoint Client Side Object Model to Add Owner to selected SharePoint Online Libraries and Lists.  You need to install the latest SharePoint Online Client Components from Github or download them from Microsoft download center.  You can change the script to only find list by title and then add the owner.  The script creates the required objects and then add the User as owner. 

    $User = “user@domain.onmicrosoft.com”
    #$User = Read-host “Please enter Office365 Admin User name username@domain.onmicrosoft.com. “
    $Creds = Get-Credential

    Connect-SPOService -Url https://tenant-admin.sharepoint.com -Credential $Creds
    $site = Get-SPOSite https://site.sharepoint.com/teams/HCPTS

    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’

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

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

    #Fetch the users in Site Collection

    $Web = $ctx.Web;
    $ctx.Load($Web)
    $Lists = $Web.Lists
    $ctx.Load($Lists)
    $ctx.ExecuteQuery()

    $SAPLists = $Lists | ?{$_.Title -Like “SAP Data Quality Review*” } | Select Title
    $SAPLists | Select Title

    foreach($alist in $SAPLists)
    {

       Write-Host $alist.Title
      $OwnersGroupTitle = “Site Owners”
      $OwnerGroup = $Web.SiteGroups.GetByName($OwnersGroupTitle)

      $FullControl = $web.RoleDefinitions.GetByName(“Full Control”)
      # Create a role assignment and apply the ‘read’ role.
      $roleAssignment = New-Object Microsoft.SharePoint.Client.RoleDefinitionBindingCollection($ctx)
      $roleAssignment.Add($FullControl)

     
      $SelectedList = $Web.Lists.GetByTitle($alist.Title)
      $ctx.Load($SelectedList)
      $ctx.Load($SelectedList.RoleAssignments.Add($OwnerGroup,$roleAssignment))
      $SelectedList.Update()
      $ctx.ExecuteQuery()
    }

    Script is also available below

    https://gallery.technet.microsoft.com/Add-Owner-to-SharePoint-4f13d94f

    Disable OneDrive Sync for SharePoint Online Site Libraries


    https://gallery.technet.microsoft.com/Disable-OneDrive-Sync-for-71f99ceb

    Write-Host “Please Enter SharePoint Online Service Administrator Credentials!”
    $Creds = Get-Credential

    $SuperAdmin = “SCAadmin@tenant.onmicrosoft.com”

    $AdminUrl = ‘https://admin.sharepoint.com’

    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”

    Connect-SPOService -Url $AdminUrl -Credential $Creds
    $spocredentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Creds.UserName , $Creds.Password)

    $Sites = Get-SPOSite -Limit All | Select-Object Url
    Write-Host “Total Sites are ” $Sites.Count -ForegroundColor Green

    foreach($Site in $Sites)
    {
        try
        {
            Set-SPOUser -Site $Site.Url -LoginName $SuperAdmin -IsSiteCollectionAdmin $true -Verbose
            $SiteUrl = $Site.Url
            Write-Host $SiteUrl

            #Get the Client Context and Bind the Site Collection
            $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
            $ctx.Credentials = $spocredentials
            #Authenticate
            $Rootweb = $ctx.Site.RootWeb
            $ctx.Load($Rootweb)
            $ctx.Load($Rootweb.Webs)
            $ctx.ExecuteQuery()

            Write-Host $Rootweb.Title
            write-host $Rootweb.ExcludeFromOfflineClient

            $Rootweb.ExcludeFromOfflineClient = $true
            $Rootweb.Update()
            $ctx.ExecuteQuery()

            #Fetch the users in Site Collection
            foreach($web in $Rootweb.Webs)
            {
                try
                {
                    $ctx.Load($Web)
                    $ctx.ExecuteQuery()
                    Write-Host $web.Title
                    write-host $web.ExcludeFromOfflineClient
                    $web.ExcludeFromOfflineClient = $true
                    $web.Update()
                    $ctx.ExecuteQuery()
                }
                catch
                {
                    Write-Host “Exception at Sub Web. Script will continue” -ForegroundColor Red
                }
            }

        }
        catch
        {
            Write-Host “Exception at Root Web. Script will continue” -ForegroundColor Red
        }
    }