Automate SharePoint Online Site Inventory with Azure Automation

This guide explains how to configure and use the accompanying Azure Automation SharePoint Online Site Consumption Report runbook. Follow the setup steps, configure the required Automation variables and app permissions, then choose the separate read-only script viewer or existing M365Buddy script editor to customize, copy, or download the PowerShell file.

What the script does

When the runbook starts, it reads eight required Azure Automation variables, imports PnP.PowerShell, connects to the SharePoint admin center using certificate-based app authentication, and retrieves the tenant's site inventory. It loads Microsoft 365 group visibility into a cache, then processes sites one by one. A site failure is logged as a warning and recorded for the final console summary so the loop can continue.

After processing, the script exports successful records to a timestamped CSV in the runbook's temporary directory. It then sends the report as an email attachment using Microsoft Graph. If the CSV is 3 MB or larger, it attempts to compress it into a ZIP first; if that ZIP is still 3 MB or larger, the current Mail.Send-only approach throws an error rather than sending it.

1. Prerequisites and permissions

• An Azure subscription and permission to create or manage an Azure Automation Account and runbooks.
• A PowerShell runtime environment with a compatible version of PnP.PowerShell available to the runbook.
• An Entra ID application and certificate suitable for app-only SharePoint authentication.
• SharePoint application permission Sites.FullControl.All, as documented in the script, with admin consent where required. Review whether this broad permission is acceptable for your governance model.
• A Microsoft Graph application registration for the email/group lookup logic, with Group.Read.All and Mail.Send application permissions and admin consent.
• A valid sender mailbox, the required Exchange/Microsoft 365 authorization for the app to send from it, and the intended recipient list.

2. Create the Automation Account and runtime

In the Azure portal, open Automation Accounts and create or select the account that will host the runbook. Choose the subscription, resource group, and region according to your organization's standards. Then configure a PowerShell runtime environment compatible with the PnP.PowerShell version you intend to use.

Import or make PnP.PowerShell available in that runtime environment, and verify that package import completes before running the script. The runbook checks for several PnP commands after importing the module, which helps expose a missing or incompatible module early. Do not assume that a module installed on your workstation is automatically available in Azure Automation.

3. Configure the SharePoint certificate app

1. In Microsoft Entra admin center, create or select the app registration used for SharePoint access.
2. Upload the public certificate to the app registration.
3. Record the tenant identifier and application (client) ID.
4. Prepare the matching PFX content and its password for the runbook's protected configuration.
5. Grant and obtain admin consent for the approved SharePoint application permission.

The script reads PfxBase64 and PFX Password from Automation variables. It converts the password string to a SecureString and uses Connect-PnPOnline with -CertificateBase64Encoded, -CertificatePassword, -Tenant, and -ClientId. The certificate used by the runbook must match the certificate registered on the app.

4. Configure the Microsoft Graph mail app

The current implementation requests a Graph access token using the client-credentials flow and a client secret. The Graph app is used to read Microsoft 365 group visibility and send the report. Configure the required Graph application permissions and admin consent, and verify that the sender mailbox is authorized under your tenant's Exchange application access controls.

5. Create the eight Automation variables

In the Automation Account, open Variables and create each variable using the exact spelling shown below. The script treats all eight as required and stops if any is empty.

The script parses MailTo into individual recipients using commas, semicolons, or newlines. Check that the recipient list is correct before the first run. Where possible, mark secret variables as encrypted and limit who can read or edit Automation Account variables and runbook jobs.

6. Update the tenant-specific configuration

Open the downloaded script in a code editor and locate the configuration section near the top:

$AdminUrl = "https://5062x8energy-admin.sharepoint.com"

Replace the example tenant-specific URL with your SharePoint admin center URL, for example https://yourtenant-admin.sharepoint.com. Do not change it to the regular SharePoint home URL. Save the updated script and upload it to the runbook after reviewing the diff.

The configuration section also defines the CSOM request timeout, retry count and delays, Graph token refresh buffer, and the 3 MB attachment threshold. Those are script settings, not Automation variables, so changing them requires editing the script itself.

7. Understand the report columns

For each site processed successfully, the CSV currently contains these columns:

The tenant summary printed in the job output and included in the email body reports storage quota, consumed and available storage, total sites, private group sites, and public group sites. Group visibility is not a general site-sharing setting: non-group-connected sites may have a blank Office365Visibility value.

View or edit the full PowerShell script

Choose how you want to work with the runbook. The viewer opens a read-only version of the current script. The editor opens a separate M365Buddy page where you can modify a working copy, copy your changes, or download the updated .ps1 file.

The editor works on a local copy in your browser. It does not commit changes to the website or update an Azure Automation runbook. Download the edited file and upload it to the runbook after reviewing and testing your changes.

8. Import and publish the runbook

1. Download the .ps1 file using the link at the top of this guide.
2. Replace the tenant-specific $AdminUrl value and make any approved configuration changes.
3. In Azure Automation, create a PowerShell runbook and paste/import the reviewed script.
4. Select the intended runtime environment and confirm PnP.PowerShell is available.
5. Save the runbook, review it, and publish it when it is ready to test.

The script calls Get-AutomationVariable, so it is intended to run inside Azure Automation rather than directly in a normal PowerShell terminal unless you provide an equivalent test harness for those variables.

9. Run a controlled test

Start with a manual test run before creating a schedule. Monitor the job output and warnings. The script reports the discovered site count, periodically prints site progress, retries selected operations, records failures, exports successful results, and attempts email delivery.

• Confirm the job connects to the SharePoint admin center and discovers the expected number of sites.
• Verify that the CSV contains the expected columns and that sample site URLs, owners, admin values, visibility, and storage values look reasonable.
• Confirm that public/private visibility is present only where the associated Microsoft 365 group can be resolved.
• Check the job output for failed sites; failures are reported in the console/job stream and are not added as status/error columns to the CSV.
• Confirm the email arrives at the intended recipients and that the attachment opens correctly.

10. Understand email delivery and attachment limits

The runbook creates a timestamped CSV in the temporary directory, then uses Microsoft Graph's /users/{MailFrom}/sendMail endpoint to send the report. If the CSV is at least 3 MB, it compresses the CSV and attempts to attach the ZIP instead. If the ZIP also reaches the configured 3 MB threshold, the script throws an error and asks for an alternate delivery design.

The temporary CSV is not a durable report archive. If you need long-term retention, audit evidence, or a download link, extend the script to store the report in an approved SharePoint library or other governed storage location and apply the appropriate access and retention controls.

11. Schedule recurring runs

After the manual run is validated, create a schedule in Azure Automation and link it to the published runbook. Select a cadence suitable for the number of sites and the reporting need. Verify the schedule time zone and first run. Review job history, warnings, report delivery, and runtime after the first scheduled execution.

Troubleshooting

Security and operational checklist

• Replace the hard-coded tenant-specific admin URL before use.
• Keep PFX data, PFX password, and Graph client secret out of source control and protect them in Automation/secret storage.
• Review the broad SharePoint application permission and Graph application permissions with your security team.
• Track certificate and client-secret expiry and establish a rotation process.
• Restrict who can edit runbooks, read variables, and view job output.
• Test the report with a limited recipient list and verify the output before broad distribution.
• Plan for runtime, attachment-size, retention, and failed-site handling as the tenant grows.

Conclusion

This runbook combines SharePoint site inventory, owner/admin lookup, group visibility, storage reporting, CSV export, and Microsoft Graph email delivery in one scheduled workflow. The safest path is to configure the required variables carefully, replace the tenant-specific settings, validate permissions, and complete a manual test before enabling a recurring schedule.

Full script: View script · Edit script in M365Buddy.