PowerShell; SharePoint Online Inventory and User Activity Report

I am frequently asked to generate various reports for SharePoint Online. Usually, I can get the requested information from the Microsoft 365 Reports console, this time was different. We had just merged two Microsoft 365 tenants. The data move had worked according to the tools. The problem was that independent reports showed people were still using the old target sites after the cut date.

Microsoft’s SharePoint activity reports obscure the user’s identity for security reasons. We specifically needed to identify who was accessing data in the old tenant to troubleshoot the issue. There is a setup option you can toggle to display user identities in the reports. I turned on the switch and waited five business days with no results. I couldn’t wait any longer and needed to pivot to another solution. If you want to try, the Microsoft documentation is here.

I decided to go with a PowerShell generated report instead. Power BI would also have been a good choice, but I had an ace up my sleeve. I have been using a PowerShell script written by Salaudeen Rajack on his SharePoint Diary blog for quite a while. I knew that it was almost perfect for this project. I would just need to craft a method for looping it through all of the sub-sites in our old tenant.

Salaudeen’s script is based on the open-source PnP SharePoint PowerShell module. He uses it to create a PowerShell function. This function takes a folder and file level inventory of all the documents stored in any given SharePoint site. It generates a report showing the URL, total size, type, creation date, last accessed date, along with the user account.

I didn’t want to authenticate hundreds of times as the function looped through each site in the tenant. So, I registered the PnP module as an App in Entra ID and used certificate-based authentication. Again, Salaudeen’s blog provides excellent instructions.

All that was left was to generate a list of all the individual site URLs. Then I needed to pass them through a logic loop to call the report function. The resulting report is perfect for understanding your SharePoint Online environment as a whole.

Import-Module -Name PnP.PowerShell
#Function to collect site Inventory
Function Get-PnPSiteInventory{
[cmdletbinding()]
   param([parameter(Mandatory = $true, ValueFromPipeline = $true)] $Web)
    #Skip Apps
    If($Web.url -notlike "$SiteURL*") { return }
   
    Write-host "Getting Site Inventory from Site '$($Web.URL)'" -f Yellow
 
    #Excluded libraries
    $ExcludedLists = @("Form Templates", "Preservation Hold Library")
                               
    #Get All Document Libraries from the Site
    $Lists= Get-PnPProperty -ClientObject $Web -Property Lists
    $Lists | Where-Object {$_.BaseType -eq "DocumentLibrary" -and $_.Hidden -eq $false -and $_.Title -notin $ExcludedLists -and $_.ItemCount -gt 0} -PipelineVariable List | ForEach-Object {
        #Get Items from List  
        $global:counter = 0;
        $ListItems = Get-PnPListItem -List $_ -PageSize $Pagesize -Fields Author, Created, SMTotalSize -ScriptBlock `
                 { Param($items) $global:counter += $items.Count; Write-Progress -PercentComplete ($global:Counter / ($_.ItemCount) * 100) -Activity "Getting Inventory from '$($_.Title)'" -Status "Processing Items $global:Counter to $($_.ItemCount)";}
        Write-Progress -Activity "Completed Retrieving Inventory from Library $($List.Title)" -Completed
     
            #Get Root folder of the List
            $Folder = Get-PnPProperty -ClientObject $_ -Property RootFolder
           
            $SiteInventory = @()
            #Iterate through each Item and collect data          
            ForEach($ListItem in $ListItems)
            {  
                #Collect item data
                $SiteInventory += New-Object PSObject -Property ([ordered]@{
                    SiteName  = $Web.Title
                    SiteURL  = $Web.URL
                    LibraryName = $List.Title
                    ParentFolderURL = $Folder.ServerRelativeURL
                    Name = $ListItem.FieldValues.FileLeafRef
                    Type = $ListItem.FileSystemObjectType
                    ItemRelativeURL = $ListItem.FieldValues.FileRef
                    CreatedBy = $ListItem.FieldValues.Author.Email
                    CreatedAt = $ListItem.FieldValues.Created
                    ModifiedBy = $ListItem.FieldValues.Editor.Email
                    ModifiedAt = $ListItem.FieldValues.Modified
                    FileSize =  $ListItem.FieldValues.SMTotalSize.LookupID
                })
            }
            #Export the result to CSV file
            $SiteInventory | Export-CSV $CSVReport -NoTypeInformation -Append
        }
}
#Parameters
$CSVReport = "C:\Temp\SharePoint_Site_Inventory_And_Activity_Report.csv"
$Pagesize = 2000
#Connect to the SharePoint 365 admin console with a registerd app and get all of the SharePoint Site's URLs
Connect-PnPOnline -Url "https://yoursite-admin.sharepoint.com/" -Interactive -ClientId xxxxxxxxxx-xxxxx-xxxxxxx-xxxxxxxx-xxxx
$Sites = Get-PnPTenantSite
#Call the Function for all SharePoint Online sites in the tenant, one at a time.
Foreach ($Site in $Sites){
    $SiteUrl = $site.Url
    #Connect to each site with the registered app before calling the function for it.
    Connect-PnPOnline -Url $SiteURL -Interactive -ClientId xxxxxxxxxx-xxxxx-xxxxxxx-xxxxxxxx-xxxx
    Get-PnPSubWeb -Recurse -IncludeRootWeb | ForEach-Object { Get-PnPSiteInventory $_ }
}

I ran the script above on a tenant with around 500 sub-sites and about a thousand users. It took nine and a half hours to finish. The resulting CSV file was 42 megabytes in size. You’ll need to know the URL for your SharePoint Online admin console. You’ll also need the ClientID from the Entra app you registered.

The script is configured for an interactive logon so that it will work with MFA. After the first logon prompt each sub-site will use the same session. Once you have generated the report, there is a lot you can do with Excel to highlight various aspects.