SharePoint Reporting using PnP PowerShell through Azure Automation
This article explains how to configure an Azure Automation runbook to execute PnP PowerShell and retrieve SharePoint site collection data across a Microsoft 365 tenant. This method is especially useful for tracking weekly changes within a SharePoint environment, such as during preparation for large initiatives like tenant-to-tenant migrations, and allows administrators to run scripts on a schedule without interactive sign-in. The solution uses Azure Automation, a managed cloud service that runs scripts in a headless environment. Runbooks behave like standard PowerShell scripts but execute non-interactively, meaning without prompts and output is available only after completion through job logs and output streams. 1. Create an Azure Automation Runbook To get started, you’ll need an Azure subscription to create the resources required for Azure Automation. While the Azure Portal interface may change over time, the underlying steps remain the same – follow the steps outlined below. a. Go to the Azure Portal and login with your Azure credentials b. Create a new Azure Automation Account using the Create a resource button and searching for Automation c. Fill out the details for the Azure Automation Account as desired and click Review + Create at the bottom left followed by clicking on Create on the review screen d. Once the resource has been created, click on Go to resource to open the Azure Automation resource 2. Configure the Azure Automation Account Now your Azure Automation Account has been created, proceed with the next paragraphs to configure it for using PnP PowerShell a. Add the PnP PowerShell module to the Azure Automation Account 1. Navigate to Modules which is located on the left side menu of the function app under the Shared Resources header. 2. Click on Add a module at the top of the screen IMPORTANT Currently, the only stable version of PnP PowerShell compatible with Azure Automation 7.2 runbooks is 2.12.0, as newer releases are not yet supported. Since the Browse from Gallery option only allows installation of the latest version, you must use the Upload from File option to upload a supported version instead. 3. Open up a PowerShell 7 console and execute the following command to download PnP PowerShell 2.12.0 Save-Module -Name PnP.PowerShell -RequiredVersion 2.12.0 -Path C:\temp 4. Using Windows File Explorer, go to the folder where you downloaded the PnP PowerShell package. You should see a folder called PnP.PowerShell in there. Right click on it and choose the option Compress to ZIP file. 5. Select Browse for file, Runtime version 7.2 (recommended) and click on the folder icon next to PowerShell module file and select the zipped up PnP.PowerShell.zip file generated in the previous step. 6. Click on Import to start the download and importing process. It could take up to 10 minutes for the import to complete. You can check the import status by changing the Module type filter to Custom. 7. Once it's done, it will show the status Available 3. Using Managed Identity for authentication The recommended approach is to use a managed identity in Azure to allow your Automation runbook to connect to Microsoft Graph and SharePoint Online via PnP PowerShell. This method lets you grant permissions directly to the runbook’s identity, eliminating the need for client secrets or certificates that could otherwise be exposed or misused. a. Enabling managed identity for an Azure Automation Runbook In your Azure Automation account, in the left menu, go to Identity under Account Settings Ensure you are on the System assigned tab and flip the switch for Status to On, if not already done Click the Save button and confirm your action in the dialog box that will be shown A new entry will now automatically be created in Entra ID for this app having the same name as your Automation account. This can be verified by going into Entra ID -> Enterprise Applications (under Manage). You may have to remove the default filters for your app to show. b. Assigning permissions to the managed identity Next, we shall use Graph PowerShell to assign permissions to the managed identity associated with the Automation account. 1. Using a CLI of your choice, run the following command. This will connect your PowerShell session to Microsoft Graph with AppRoleAssignment.ReadWrite.All permissions to read and modify app role assignments across your tenant. Connect-MgGraph -Scopes "AppRoleAssignment.ReadWrite.All" 2. Run the following commands to grant Sites.FullControl to the Graph API $GraphApp = Get-MgServicePrincipal -Filter "AppId eq '00000003-0000-0000-c000-000000000000'" $TargetResoruce = Get-MgServicePrincipal -filter "displayname eq 'pnppowershelldocumentationsample'" $Role = $GraphApp.AppRoles | Where-Object {$_.Value -eq "Sites.FullControl.All"} $AppRoleAssignment = @{} $AppRoleAssignment.Add("PrincipalId",$TargetResoruce.Id) $AppRoleAssignment.Add("ResourceId",$GraphApp.Id) $AppRoleAssignment.Add("AppRoleId",$Role.Id) $RoleAssignment = New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $TargetResoruce.Id -BodyParameter $AppRoleAssignment Note: AppId 00000003-0000-0000-c000-000000000000 is the standard identifier for the Graph API in Azure 3. Run the following commands to grant Sites.FullControl to the SharePoint Online API $SpApp = Get-MgServicePrincipal -Filter "AppId eq '00000003-0000-0ff1-ce00-000000000000'" $TargetResoruce = Get-MgServicePrincipal -filter "displayname eq 'pnppowershelldocumentationsample'" $Role = $SpApp.AppRoles | Where-Object Value -eq "Sites.FullControl.All" $AppRoleAssignment = @{} $AppRoleAssignment.Add("PrincipalId",$TargetResoruce.Id) $AppRoleAssignment.Add("ResourceId",$SpApp.Id) $AppRoleAssignment.Add("AppRoleId",$Role.Id) $RoleAssignment = New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $TargetResoruce.Id -BodyParameter $AppRoleAssignment Note: AppId 00000003-0000-0ff1-ce00-000000000000 is the standard identifier for the SharePoint Online API in Azure 4. Once permissions are granted, verify them in Entra ID (Entra ID -> Enterprise Applications (under Manage) -> Look up the app associated with your automation account (you may need to remove filters) -> Permissions (under Security) -> Verify permissions exist (as shown below) IMPORTANT API permission updates may take 10 minutes or more to propagate across Microsoft 365. During this time, scripts executed immediately against SharePoint can fail. If that happens, allow additional time for propagation and then retry the operation. 4. Create a Runbook We're now ready to create a Runbook in which your PnP PowerShell script will run. a. In the Azure Portal, in the left menu, click on Runbooks under Process Automation b. Click on Create a runbook at the top of the screen c. Give the Runbook a name, select the Runbook type PowerShell and for the Runtime version choose 7.2 (recommended) and click on Create at the bottom left. d. On the Edit PowerShell Runbook page, enter your PnP PowerShell code in the large white area, i.e. 5. Connect-PnPOnline 'https://tenant.sharepoint.com' -ManagedIdentity 7. Get-PnPWeb Once done, click on Save at the top of the screen and then on Test pane to test your Runbook. e. Click on Start to start testing the Runbook. It might take a few minutes for the Runbook to start. Once it's done, you will see the output of your PnP PowerShell script in the large black output section. 5. Generate a Tenant-Wide SharePoint Sites Report Next, we will execute a script to collect information for each SharePoint site in your tenant. The data will be exported to a CSV file and then uploaded to a designated folder within a SharePoint document library. This script uses the Get-PnPTenantSite cmdlet to gather the data. a. Open up the Runbook you had created and bring up the edit PowerShell page. Enter the following PnP PowerShell code in the large white area. You will need to update the values specific to your tenant. # Site info $adminUrl = "https://tenant-admin.sharepoint.com" $targetSiteUrl = "https://tenant.sharepoint.com/sites/M365Migration" $LibraryName = "Shared Documents" $TargetFolder = "Exports" # Timestamped file $TimeStamp = Get-Date -Format "yyyyMMdd_HHmm" $FileName = "ActiveSites_$TimeStamp.csv" # Create a safe temp folder in the runbook $TempFolder = $env:TEMP $TempPath = Join-Path -Path $TempFolder -ChildPath $FileName # Make sure the folder exists if (-not (Test-Path $TempFolder)) { New-Item -Path $TempFolder -ItemType Directory | Out-Null } # Connect to SharePoint Connect-PnPOnline $adminUrl -ManagedIdentity Write-Output "Connected to PnP" $sites = Get-PnPTenantSite -Detailed $sites | Select-Object ` Title, Url, Template, StorageUsageCurrent, StorageQuota, Owner, LockState, SharingCapability, TimeCreated, LastContentModifiedDate | Export-Csv -Path $TempPath -NoTypeInformation -Encoding UTF8 Write-Output "CSV created at $TempPath" Connect-PnPOnline $targetSiteUrl -ManagedIdentity # Upload the csv file Add-PnPFile -Path $TempPath -Folder "$LibraryName\$TargetFolder" Write-Output "File uploaded successfully to $LibraryName\$TargetFolder" # Close out Disconnect-PnPOnline Remove-Item $TempPath -Force b. Click on Save at the top of the screen and then on Test pane to test your Runbook c. Click on Start to start testing the Runbook d. After the run completes, check the specified document library for the CSV export and verify that a new report has been created successfully e. Close the code pane and click on Publish to publish this version of your runbook 6. Automate task to run on weekly schedule Next, we will explore how to automate this process to run weekly without requiring any user interaction. a. Create a schedule 1. In the Azure Portal, navigate to the Azure Automation account 2. Click on Schedules under Shared Resources 3. Click on Add a schedule 4. Enter name, description and start date. Select recurring to specify a schedule for job to run 5. Select week under recurrence and specify day of the week. Set an expiration date. Finally, click on Create. b. Link runbook to schedule 1. Open the runbook that you set up previously for your PnP PowerShell script 2. Click on Link to schedule 3. Select the schedule, parameters and run settings, and then click on OK 4. Verify schedule in the Runbook schedules page COMPLETE The script will now run automatically each week according to the defined schedule. At the specified date and time, a new report will be generated and uploaded to the document library without any manual steps. This means the process of collecting SharePoint site data across your tenant is now fully automated and requires no user interaction.Recover a Domain from an Old Microsoft 365 Test Tenant
Hi everyone, I’m trying to register my domain abc.com in a new Microsoft 365 tenant, but I’ve discovered that it was previously associated with a test tenant set up by a former colleague. I no longer have access to that old tenant and would like to reclaim the domain so I can use it in my current M365 environment. Has anyone tried any alternatives in addition to Microsoft support? Can Microsoft release the domain if I can prove ownership (via DNS or registrar)? Has anyone dealt with a similar situation, and how long does the process usually take? Appreciate any guidance or shared experiences! Thanks in advance.
Adding Proxy Addresses in AD Before Tenant-to-Tenant Migration Cutover
We're in the process of migrating users from another M365 tenant into our own, which is synced with on-prem AD. Before the cutover, we'd like to add the proxy addresses from the source tenant to our AD and have them sync to the cloud once the domain is added to our M365 tenant. Would this work as expected, or are there any potential issues to be aware of?
Ongoing Compatibility Issues Between Outlook and Google Frustrate Users
There has been a long-standing compatibility issue between Outlook and Google. After every update, Outlook users face various problems, particularly with Google-based email servers. This situation significantly frustrates users and negatively impacts their productivity. Many businesses rely on Google's infrastructure while choosing Outlook as their email client. However, this lack of compatibility between the two platforms creates challenges for both individual users and organizations. It's high time these tech giants resolved this issue. Users who depend on Google-based email servers and have invested in Outlook are left deeply disappointed by these recurring problems.
Urgent help needed- An issue with online EXCEL 365 sheets
I used to work with my team via 365 excel sheets online. We used to work as 3 or 4 at the same time to enter data and so on. With every start of opening the sheet, it used to give a small pop up box says (only me OR everyone) and by choosing only me, it totally makes you have a separate screen especially when you filter something without affecting the other team members work.. during the last 2 days, it stopped to gives this small pop up box says (only me OR everyone) and with every-time someone filter something, it just be obligated to all screens.. For example if I am working on something on the 2nd of may and someone came to filter the sate choosing 3rd of may, My screen totally turns to the 3rd of May. so, I can't work separately. My question is how to get that fixed again as before or how to get this pop up box says (only me OR everyone) again. I am working on online file. Thank you for your time and help in advance
