Office 365: Extract a CSV file with all your SharePoint Teamsites running in your tenant

[Fabrice Romelard]

When you are managing an Office 365 tenant, you can need to export the current list of SharePoint sites running into.

That PowerShell script will help you to do that into and CSV file:

[string]$username = "YourAdminAccount@yourtenant.onmicrosoft.com"
[string]$PwdTXTPath = "C:\SECUREDPWDFOLDER\ExportedPWD-$($username).txt"
[string]$ExportAllUserLogin = ""

function Load-DLLandAssemblies
{
    [string]$defaultDLLPath = ""

    # Load assemblies to PowerShell session

    $defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.dll"
    [System.Reflection.Assembly]::LoadFile($defaultDLLPath)

    $defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.SharePoint.Client.Runtime.dll"
    [System.Reflection.Assembly]::LoadFile($defaultDLLPath)

    $defaultDLLPath = "C:\Program Files\SharePoint Online Management Shell\Microsoft.Online.SharePoint.PowerShell\Microsoft.Online.SharePoint.Client.Tenant.dll"
    [System.Reflection.Assembly]::LoadFile($defaultDLLPath)
}

cls
Write-Host " ---------------------------------------------- "
Load-DLLandAssemblies
Write-Host " ---------------------------------------------- "

$secureStringPwd = ConvertTo-SecureString -string (Get-Content $PwdTXTPath)
$adminCreds = New-Object System.Management.Automation.PSCredential $username, $secureStringPwd

Connect-SPOService -Url https://yourtenant-admin.sharepoint.com -credential $adminCreds -ErrorAction SilentlyContinue -ErrorVariable Err

$CSVFileToExport = "C:\YOUREXPORTFOLDER\AllSiteCollectionInformation.csv"
if (Test-Path $CSVFileToExport) {
    Remove-Item $CSVFileToExport -Force
}

$ExportSiteCollectionList = ""

#Retrieve all site collection infos
$sitesInfo = Get-SPOSite -Template "STS#0" -Limit ALL | Sort-Object -Property url | Select *
[int]$i = 1;

Write-Host "--------------------------------------------------------------------------------------------"
#Retrieve and print all sites
foreach ($site in $sitesInfo)
{
    $ExportAllUserLogin = ""
    Write-Host "SiteColl Number:", $i, "- of:", $sitesInfo.Count;
    $i += 1;
   
    Write-Host "SPO Site collection:", $site.Url, "- Title:", $site.Title
    Write-Host "   => External Sharing:", $site.SharingCapability
    Write-Host "   => Site Template Used:", $site.Template
    Write-Host "   => Storage Quota:", $site.StorageQuota, "- Storage used:", $site.StorageUsageCurrent
    Write-Host "   => Percent Usage:", $($site.StorageUsageCurrent / $site.StorageQuota * 100), "%"
   
    Write-Host "   => Resource Quota:", $site.ResourceQuota, "- Resource used:", $site.ResourceUsageCurrent
    #Write-Host "   => Owner:", $site.Owner

    #Get-SPOUser -Limit ALL -Site $site.Url -IsSiteCollectionAdmin $true < NOT WORKING
   
    $AllUsers = Get-SPOUser -Limit ALL -Site $site.Url #| FT LoginName, IsSiteAdmin -AutoSize #%{if($_.IsSiteAdmin -eq $True)
    $AllUsers | %{if($_.IsSiteAdmin -eq $True) {
                $ExportAllUserLogin += $_.LoginName + '|';
        }
    }

    Write-Host "--------------------------------------------------------------------------------------------"

    $ExportSiteCollectionList += @(
          [pscustomobject]@{
            SiteURL = $site.Url
            SiteTitle = $site.Title
            SharingCapability = $site.SharingCapability
            StorageQuota = $site.StorageQuota
            StorageUsageCurrent = $site.StorageUsageCurrent
            PercentQuotaUsed = $($site.StorageUsageCurrent / $site.StorageQuota * 100)
            SiteCollAdmin = $ExportAllUserLogin
            }  )

}

 Write-Host $ExportSiteCollectionList

$ExportSiteCollectionList | Export-Csv -Path  $CSVFileToExport -Encoding UTF8

Write-Host " ---------------------------------------------- "

You can use your CSV file for other admin or reporting/listing tasks 

Fabrice Romelard [MVP]

Reference used:

• http://sharepoint.stackexchange.com/questions/86842/extract-all-list-names-from-sharepoint-site-to-c...

Original post in french:

• Office 365: Extraire dans un fichier CSV une liste de tous les Teamsites présents dans votre tenant ...