Thursday, August 21, 2014

SharePoint 2010 / 2013 - PowerShell - How to get ALL documents in a Farm exported to a CSV file

Hello fellow SharePoint'ers :)

It's been a while but given the success of my last PowerShell script I thought I would post another one I have been asked to produce recently.

This one is heavily inspired from no other than the famous Gary Lapointe's excellent script:

(Gary if you are reading this, just wanted to thank you for the awesome stsadm custom extensions I was using heavily in my SharePoint 2003/2007 days!) :)

I have only made the following enhancements to it:
  • Made sure the SharePoint PowerShell snapIn is added at the start of the script if not already there
  • Added some "Write-Host" lines to give an idea where the script is at when scanning the entire farm as it can be a long running process...
  • Added the "File Extension" calculation, for some reason the $item.File.Item["FileType"] was not always returning a value on non-english SharePoint site collections. So had to use [System.IO.FileInfo] in the end
  • Generated the output CSV filename automatically based on the time the script is run. I just like to do that... makes sure it never overwrites previous exports and allows you not to have to modify the script every time.
  • Wrapped the entire function call in "[Microsoft.SharePoint.SPSecurity]::RunWithElevatedPrivileges" as I noticed that when the scan was hitting the "My Site" web application it would struggle with my sites of employees who had left, because the credentials under which you are running the script need to be site owner (either primary or secondary admin on the site collection - basically site collection administrator). Web application security policies are not enough in this case.
Here is the script:


#Add SharePoint PowerShell SnapIn if not already added
 if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

cls;

function Get-DocInventory() {
    [void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")

    $farm = [Microsoft.SharePoint.Administration.SPFarm]::Local
    foreach ($spService in $farm.Services) {
        if (!($spService -is [Microsoft.SharePoint.Administration.SPWebService])) {
            continue;
        }

        foreach ($webApp in $spService.WebApplications) {
            if ($webApp -is [Microsoft.SharePoint.Administration.SPAdministrationWebApplication]) { continue }

            Write-Host "Web Application: " $webApp.Name

            foreach ($site in $webApp.Sites) {

                Write-Host "Site: " $site.Url
                
                foreach ($web in $site.AllWebs) {
                
                    Write-Host "`tWeb: " $web.Url
                
                    foreach ($list in $web.Lists) {
                    
                        if ($list.BaseType -ne "DocumentLibrary") {
                            continue
                        }
                        
                        Write-Host "`t`tDocument Library: " $list.Title
                        
                        foreach ($item in $list.Items) {
                        
                            # Get the file extension if there is one
                            $extension = [System.IO.Path]::GetExtension($item.File.Name)
                            if ($extension.Length -gt 0) { $extension = $extension.Substring(1) }
                            $extension = $extension.ToLower()
                        
                            $data = @{
                                "Web Application" = $webApp.ToString()
                                "Site" = $site.Url
                                "Web" = $web.Url
                                "List" = $list.Title
                                "Item ID" = $item.ID
                                "Item Title" = $item.Title
                                "Item Name" = $item.File.Name 
                                "File Size KB" = $item.File.Length / 1KB
                                "File Type" = $extension
                                #"File Type" = $item.File.Item["FileType"]
                                "Item URL" = $item.Url
                                "Item Created" = $item["Created"]
                                "Item Created By" = ($item["Created By"] -as [Microsoft.SharePoint.SPFieldLookupValue]).LookupValue 
                                "Item Modified" = $item["Modified"]
                                "Item Modified By" = ($item["Modified By"] -as [Microsoft.SharePoint.SPFieldLookupValue]).LookupValue
                            }
                            New-Object PSObject -Property $data
                        }
                    }
                    $web.Dispose();
                }
                $site.Dispose()
            }

        }
    }
}

[Microsoft.SharePoint.SPSecurity]::RunWithElevatedPrivileges({ 

Write-Host "Start: $(Get-Date)"

<#
# The following just makes sure that columns are ordered
Get-DocInventory | Select-Object -Property `
    "Web Application", `
    "Site", `
    "List", `
    "Item ID", `
    "Item Title", `
    "Item Name", `
    "File Size KB", `
    "File Type", `
    "Item URL", `
    "Item Created", `
    "Item Created By", `
    "Item Modified", `
    "Item Modified By" `
    | Out-GridView
#>

# Uncomment the following line if you wish to output to a gridview
#Get-DocInventory | Out-GridView

# Uncomment the following lines if you wish to output to a CSV file
# SET THE EXPORT FILEPATH
$today = (Get-Date -Format yyyy-MM-dd-hh-mm)
$exportFilePath = "C:\Temp\FarmDocInventory_" + $today + ".csv";

Get-DocInventory | Export-Csv -NoTypeInformation -Path $exportFilePath

Write-Host "End: $(Get-Date)"

});