Blog Details

Effective SharePoint Online administration requires comprehensive insights into site ownership, storage utilization, and group permissions. Manually gathering this information is time-consuming and error-prone. In this blog post, we'll explore a powerful PowerShell script that automates the collection and export of detailed reports on site owners, storage quotas, and SharePoint groups, streamlining your administrative tasks.

Why Automate SharePoint Online Reporting?

Automating SharePoint Online reporting offers numerous benefits, including:

• Efficiency: Save time and effort by automating data collection.
• Accuracy: Reduce the risk of human error in data gathering.
• Comprehensive Reporting: Generate detailed reports on site owners, storage, and groups.
• Auditing: Maintain a clear record of site configurations and permissions.
• Scalability: Easily manage reporting for large SharePoint Online environments.

Prerequisites

Before running the script, ensure you have the following PowerShell modules installed:

• Microsoft.Online.SharePoint.PowerShell (SharePoint Online Management Shell)
• PnP.PowerShell
• Microsoft.Graph

Install them using these commands:

PowerShell
Copy
Install-Module -Name Microsoft.Online.SharePoint.PowerShell
Install-Module -Name "PnP.PowerShell" -AllowClobber -Force
Import-Module PnP.PowerShell
Install-Module Microsoft.Graph -Scope CurrentUser
  

Understanding the PowerShell Script:

This script automates the collection and export of SharePoint Online site owners, storage quotas, and group permissions:

PowerShell
Copy
<#
.SYNOPSIS
Combines data from SharePoint Online into a single Excel workbook with separate worksheets for Site Owners, Storage Quota, and Groups Report.

.DESCRIPTION
This script connects to SharePoint Online Admin Center and retrieves information about site owners, storage quotas, and SharePoint groups for each site collection. It then exports this data into separate worksheets within a specified Excel workbook.

.PARAMETER AdminCenterURL
The URL of the SharePoint Online Admin Center to connect to.

.EXAMPLE
.\SharePointReports.ps1 -AdminCenterURL "https://abcd.sharepoint.com"

This example runs the script to gather SharePoint data from "https://abcd.sharepoint.com" and save report in CSV files.

.NOTES
Author: Kashyap Patel
Date: 22/06/2024
Version: 1.0

Prerequisites - Install the necessary modules:
Install-Module -Name Microsoft.Online.SharePoint.PowerShell (SharePoint Online Management Shell module)
Install-Module -Name "PnP.PowerShell" -AllowClobber -Force (PnP PowerShell module)
Import-Module PnP.PowerShell
Install-Module Microsoft.Graph -Scope CurrentUser (Microsoft.Graph module)
#>

# Parameters
param (
    [string]$AdminCenterURL
)

# Get the script directory
$ScriptDir = Split-Path -Parent $PSCommandPath

# Construct the CSV file paths
$SiteOwnersCSVPath = Join-Path -Path $ScriptDir -ChildPath "SiteOwners.csv"
$SPOStorageCSVPath = Join-Path -Path $ScriptDir -ChildPath "SPOStorage.csv"
$GroupsReportCSVPath = Join-Path -Path $ScriptDir -ChildPath "GroupsReport.csv"

# Get Credentials to connect
$Cred = Get-Credential

# Connect to SharePoint Online and Azure AD
Connect-SPOService -url $AdminCenterURL -Credential $Cred
Connect-AzureAD -Credential $Cred | Out-Null

# Get all Site Collections
$Sites = Get-SPOSite -Limit ALL

# Script 1: Get Site Owners
$SiteOwners = @()
$Sites | ForEach-Object {
    If ($_.Template -like 'GROUP*') {
        $Site = Get-SPOSite -Identity $_.URL
        # Get Group Owners
        $GroupOwners = (Get-AzureADGroupOwner -ObjectId $Site.GroupID | Select -ExpandProperty UserPrincipalName) -join "; "
    } else {
        $GroupOwners = $_.Owner
    }
    # Collect Data
    $SiteOwners += New-Object PSObject -Property @{
        'Site Title' = $_.Title
        'URL' = $_.Url
        'Owner(s)' = $GroupOwners
    }
}
# Export Site Owners report to CSV
$SiteOwners | Export-Csv -path $SiteOwnersCSVPath -NoTypeInformation

# Script 2: Get Site Storage Details
$ResultSet = @()
Foreach ($Site in $Sites) {
    Write-Host "Processing Site Collection :" $Site.URL -f Yellow
    $Result = New-Object PSObject
    $Result | Add-Member -MemberType NoteProperty -Name "SiteURL" -Value $Site.URL
    $Result | Add-Member -MemberType NoteProperty -Name "Allocated" -Value $Site.StorageQuota
    $Result | Add-Member -MemberType NoteProperty -Name "Used" -Value $Site.StorageUsageCurrent
    $Result | Add-Member -MemberType NoteProperty -Name "Warning Level" -Value $Site.StorageQuotaWarningLevel
    $ResultSet += $Result
}
# Export Result to CSV file
$ResultSet | Export-Csv $SPOStorageCSVPath -NoTypeInformation

Write-Host "Site Quota Report Generated Successfully!" -f Green

# Script 3: Get Site Groups
$GroupsData = @()
$Sites | ForEach-Object {
    Write-Host -f Yellow "Processing Site Collection:" $_.URL
    # Get SharePoint Online groups
    $SiteGroups = Get-SPOSiteGroup -Site $_.URL
    Write-Host "Total Number of Groups Found:" $SiteGroups.Count
    ForEach ($Group in $SiteGroups) {
        $GroupsData += New-Object PSObject -Property @{
            'Site URL' = $_.URL
            'Group Name' = $Group.Title
            'Permissions' = $Group.Roles -join ","
            'Users' = $Group.Users -join ","
        }
    }
}
# Export the data to CSV
$GroupsData | Export-Csv $GroupsReportCSVPath -NoTypeInformation

Write-Host -f Green "Groups Report Generated Successfully!"
  

Step-by-Step Breakdown:

• Parameters and Paths:
• The script accepts the SharePoint Admin Center URL as a parameter.
• It defines CSV file paths for each report.
• Credentials and Connections:
• The script prompts for credentials and connects to SharePoint Online and Azure AD.
• Site Collection Retrieval:
• It retrieves all SharePoint site collections using Get-SPOSite.
• Site Owners Report:
• It iterates through each site collection, retrieving owners and exporting the data to a CSV.
• Storage Quota Report:
• It retrieves storage quota details for each site collection and exports the data to a CSV.
• Groups Report:
• It retrieves group information for each site collection and exports the data to a CSV.

Best Practices and Considerations:

• Credentials: Store credentials securely.
• Permissions: Ensure the account has sufficient permissions.
• Error Handling: Add error handling for robust operation.
• Scheduling: Schedule the script for regular reporting.
• Customization: Customize the script for specific reporting needs.

Conclusion:

This PowerShell script simplifies SharePoint Online reporting, providing valuable insights into site ownership, storage utilization, and group permissions. Automating these tasks enhances efficiency and accuracy, supporting effective SharePoint administration.