SharePoint online script

Question

Hi,

I want to get an excel report of site collections in a tenant with below details but somehow I am not able to create a script for the same, can someone please help ?

Fields:

Site Name

Site URL

Created Date

Last Modified Date

Site Owner's Email address

Answer 1

Hi @Paul, Alok,

Thank you for posting in this community.

First of all, thanks to Pascal for the answer, but after my testing, it doesn't correctly export all owners of the current site set when the owners of a site are an M365 group.

So, we need to query the M365 group to get the owners of the site collections.

I suggest you use PowerShell provided by Pascal to export the site names, site URLs, Created Date and Last Modified Date.

For the site owners please use the following PnP PowerShell. This is because when you have many site collections, it takes a while to query them. Importing the information separately will increase the efficiency.

#Set Variables
$SiteURL = "https://yourdomain-admin.sharepoint.com/"

Connect-PnPOnline -Url $SiteURL 
  
#Get All Site collections
$SitesCollection = Get-PnPTenantSite

$Results = @()
#Loop through each site collection
ForEach($Site in $SitesCollection)
{
    If($Site.Template -like 'GROUP*')
    {
        #Get Group Owners
        try{
            $GroupOwners = (Get-PnPMicrosoft365GroupOwners -Identity ($Site.GroupId)  | Select -ExpandProperty Email) -join "; "
        }catch{
        Write-Host "This Site Collection has errors:" $Site.URL -f Yellow
}
    }
    Else
    {
        #Get Site Owner
        $GroupOwners = $Site.Owner
    }
    $Result = New-Object PSObject
    $Result  | Add-Member NoteProperty Title($Site.Title)
    $Result  | Add-Member NoteProperty URL($Site.URL)
    $Result  | Add-Member NoteProperty URL($Site.URL)
    $Result  | Add-Member NoteProperty Owners($GroupOwners)
    $Results+=$Result
}
try{
#Export Result to csv file
$Results|  Export-Csv C:\YourPath\SiteCollectionOwners.csv" -notypeinformation
Write-Host "Successfully!" -f Green
}catch{
Write-Host "Failed!" -f Red
}

Here is the result of my test, I get all site owners successfully:

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

You might want to consider the export functionality in the SharePoint admin portal:

https://[YourTenant]-admin.sharepoint.com/layouts/15/online/AdminHome.aspx#/siteManagement/

Answer 3

You can achieve what you want with PnP Powershell.

PNP documentation and installation guide:
https://pnp.github.io/powershell/articles/installation.html

I achieved it with using the following script:

# Connect to the admin site collection
Connect-PnPOnline -Url "https://<yourtenant>-admin.sharepoint.com" -Interactive

$sites = Get-PnPTenantSite | Select-Object Title, Url, Created, LastContentModifiedDate, Owner

$sites | Export-Csv -Path "C:\path\to\your\report.csv" -NoTypeInformation

Disconnect-PnPOnline