SharePoint–Use PowerShell to Create a CSV Report of All Lists and Items In a Site

Dealing with SharePoint sites in the business, there are a lot of owners who are curious about who on their team contributes to the site, what documents were getting updated, and how many times versions were being created.  Now this can all be gathered from the audit reports (assuming you have them turned on) and you can view them as well.  However, they don’t really lend themselves to a very user friendly experience (hence all these third party audit log viewers that have recently popped up).  And there are companies that don’t want to deal with the overhead of audit events taking up space in their content database.  I was asked for a simple “at-a-glance” sort of report that would list out all the lists and all the files on a site, who created them and what version they were.

With PowerShell this is actually pretty easy.  And while we won’t be able to get a complete history if the files have gone beyond their version retention limits, in most cases it satisfied the business need to just send them a spreadsheet with all the files, what versions were stored in the site, when they were created and by whom.  Since this is done as a spreadsheet and of course in SharePoint we have Excel Services, that means we can also create some pretty interesting graphs and charts about site usage, who our most prolific contributors are, how often the site is updated and how recent the information is.

Here is the basic code for generating an excel spreadsheet of all the items in all the lists for a particular SPWeb:

$web = Get-SPWeb http://<SharePoint Site>
ForEach($list in $web.Lists) 
{
 ForEach($item in $list.Items)
 { 
  ForEach($version in $item.Versions)
  {    
    $fullURL = "$($web.URL)$($list.URL)/$($version.URL)"    
    "{0}`t{1}`t{2}`t{3}`t{4}" -f $fullURL, $version.VersionLabel, $version.CreatedBy.User.UserLogin, $version.Created, $list.BaseType  | out-file Versions.csv -Append  
  }   
 } 
} 

Obviously from here it could be modified to create a spreadsheet containing all of the SPWebs by including this ForEach loop:

$allWebs = Get-SPWebApplication | Get-SPSite -Limit All | Get-SPWeb -Limit All

ForEach ($web in $allwebs )

Or filtered for only the IsCurrentVersion property:

If ($Version.IsCurrentVersion -eq $true )

or otherwise modified to give you additional information about site usage which can then be linked into the BI tools of SharePoint to create additional dimensions and measures so that site and business owners can gain a better understanding of who is contributing to their sites, what kind of content is being added, and what sort of frequency updates occur.  You can even create KPIs for stale content.

Leave a Reply