Inventory report of All SharePoint site’s Document Libraries, Document Sizes

The Script will generate the report and show the each document, folder size in all of the SharePoint Document library on SharePoint Site.

The script is really quite straightforward – it simply iterates through all Web Applications, Site Collections, Webs, Document Library, and finally, each Document. I skipped any List that is not a Document Library (as well as the Central Admin site) and then build the report and exported in CSV file that you can open in Excel to use the Excel Pivot table.

The code is also taking care for the list view threshold if you have more items it will bring only 2000 items in one query so performance point of view you no need to worry when you run on production server.

What you have to do

  1. Copy the code and save as .ps1 file with any name
  2. Open the file and change the site collection URL [The site you want to generate the report]
  3. Execute the script, that’s it.

The generated report from Utility excel sheet looks like below.

CSV File Output (1)

You can make the Pivot table from the generated output file like below

Pivot Table Audit Version Report for Doc Library (1)

Here is the script :

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint"# Get a reference to the target site 
write-host "Script Started, Please wait..." 
Function Get-SPWebApplication() 
{    
  Param( [Parameter(Mandatory=$true)] [string]$WebAppURL ) 
  return [Microsoft.SharePoint.Administration.SPWebApplication]::Lookup($WebAppURL) 
} 
Function global:Get-SPSite() 
{ 
  Param( [Parameter(Mandatory=$true)] [string]$SiteCollURL ) 
  
   if($SiteCollURL -ne '') 
    { 
    return new-Object Microsoft.SharePoint.SPSite($SiteCollURL) 
   } 
} 
   
Function global:Get-SPWeb() 
{ 
 Param( [Parameter(Mandatory=$true)] [string]$WebAppURL ) 
  $site = Get-SPSite($WebAppURL) 
        if($site -ne $null) 
            { 
               $web=$site.OpenWeb(); 
            } 
    return $web 
} 
#EndRegion 
  
 Function GenerateVersionSizeReport() 
 {   
    #Define 'Web Application URL' as Mandatory Parameter 
    Param( [Parameter(Mandatory=$true)] [string]$WebAppURL ) 
   
 #Get the Web Application 
    $WebApp=Get-SPWebApplication($WebAppURL$rootSite = New-Object Microsoft.SharePoint.SPSite($WebAppURL) 
     #Write the CSV Header - Tab Separated 
 "Site Collection Name `t Site Name`t Library `t File Name `t File URL `t File Type `t Created Date `t Created By `t Last Modified `t Modified By `t No. of Versions `t Latest Version Size(MB) `t Versions Size(MB) `t Total File Size(MB)" | out-file VersionSizeReport.csv 
  #Arry to Skip System Lists and Libraries 
 $SystemLists =@("Pages""Converted Forms""Master Page Gallery""Customized Reports",  
                 "Form Templates""Images""List Template Gallery""Theme Gallery""Reporting Templates",  
         "Site Collection Documents""Site Collection Images""Site Pages""Solution Gallery",  
                               "Style Library""Web Part Gallery","Site Assets""wfpub") 
   #Get Last Year's Same day! 
 $DateFilter=([DateTime]::Now.AddYears(-1)) 
    #Loop through each site collection 
      #Loop through each site in the site collection 
     foreach($Web in $rootSite.AllWebs) 
   { 
            #Loop through  each List 
            foreach ($List in $Web.Lists) 
            { 
                #Get only Document Libraries & Exclude Hidden System libraries 
                if (  ($List.BaseType -eq "DocumentLibrary") ) 
                { 
write-host "Report generating is in Progress.....!" 
write-host "Report generating is in Progress for the Library Name:  " + $List.Title 
 
$spQuery = New-Object Microsoft.SharePoint.SPQuery 
$spQuery.ViewAttributes = "Scope='Recursive'"$spQuery.RowLimit = 2000 
$caml = '<OrderBy Override="TRUE"><FieldRef Name="ID"/></OrderBy>'  
$spQuery.Query = $caml  
 
do 
{ 
    $listItems = $List.GetItems($spQuery) 
    $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition 
    foreach($ListItem in $listItems) 
    { 
             
          #Consider items with 5+ versions And apply Date Filter 
                        if ( ($ListItem.Versions.Count -gt 0) -and ( $ListItem['Modified'-lt $DateFilter)) 
                        { 
          $versionSize=0 
  
                            #Get the versioning details 
                            foreach ($FileVersion in $ListItem.File.Versions) 
                            { 
                                $versionSize = $versionSize + $FileVersion.Size; 
                            } 
       #To Calculate Total Size(MB) 
       $ToalFileSize= [Math]::Round(((($ListItem.File.Length + $versionSize)/1024)/1024),2) 
         
                            #Convert Size to MB 
                            $VersionSize= [Math]::Round((($versionSize/1024)/1024),2) 
         
       #Get the Size of the current version 
       $CurrentVersionSize= [Math]::Round((($ListItem.File.Length/1024)/1024),2) 
 
 
 #Get Site Name  
                            if ($Web.IsRootWeb -eq $true)  
                            {  
                                $siteName = $Web.Title +" - Root";  
                            }  
                            else  
                            {  
                                $siteName$Site.RootWeb.Title + " - " + $Web.Title;  
                            }  
  
                            #Log the data to a CSV file where versioning size > 0MB! 
try 
{ 
                            if ($versionSize -ge 0)  
                            { 
                          
                                "$($WebAppURL) `t $($Web.Title) `t $($List.Title) `t $($ListItem.Name) `t $($Web.Url)/$($ListItem.Url) `t $($ListItem['File Type'].ToString()) `t $($ListItem['Created'].ToString()) `t $($ListItem['Author'].ToString()) `t  $($ListItem['Modified'].ToString()) `t $($ListItem['Editor'].ToString()) `t $($ListItem.Versions.Count) `t $CurrentVersionSize `t $($versionSize) `t $($ToalFileSize)" | Out-File VersionSizeReport.csv -Append 
                            } 
} 
catch 
{ 
write-host "Got error to write, skiping ..." 
} 
                        }            
} 
} 
while ($spQuery.ListItemCollectionPosition -ne $null) 
 }  
  } 
          } 
  $Web.Dispose()           
   } 
 $rootSite.Dispose()             
  GenerateVersionSizeReport "http://ptcpasaspapp01/sc6" 
    #Send message to console 
    write-host "Versioning Report Generated Successfully!" 
$userinput = Read-Host "Press Enter or any key to close this window"

About Krishana Kumar

Krishana Kumar is SharePoint Architect/Trainer having Architecture experience with high volumes at Enterprise level and global scale - creation of highly scalable solutions with global user base and geographically distributed architectural components. Good knowledge of SharePoint best practices and governance models. I hold Two Master degree in Computer Science with over 11 years of experience working on Microsoft Technologies specially SharePoint, Project, .NET and other Information Worker Technologies. Having good exposer in Client side scripting Angular.js, backbone and Node. I am currently responsible for SharePoint Infrastructure set up and leading teams in various medium and large scale projects, architecting, designing & installing SharePoint farms, developing custom components,, and providing advanced SharePoint administration and development training to teams and customers. I regularly speaks in various SharePoint User Groups and other Events. I have MCSA Windows Azure, MCSA Office 365, MCSE & MCSD SharePoint 2013, Microsoft Certified Developer (MCD) and holds MCPD, MCTIP and MCTS for SharePoint 2010, MCTS MOSS 2007 & WSS 3.0, MCPD, MCITP (EPM 2010 & 2007) and MCSD .NET.
This entry was posted in General Interest. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *