Generate list of all the Excel or SSRS Report files in a SharePoint site collection – using power Shell script or SQL query

1) We use this power shell script to get all the Excel – xlsx file without Hidden property:
$file = “C:\xyz\AllReporttestFilesList.txt”
Get-SPSite https://abc.xyz -Limit All |
Select -ExpandProperty AllWebs |
Select -ExpandProperty Lists |
Where { $_.GetType().Name -eq “SPDocumentLibrary” -and
-not $_.Hidden } |
Select -ExpandProperty Items |
Where { $_.Name -Like “*.xlsx” } |
Select Name,
@{Name=”URL”;
Expression={$_.ParentList.ParentWeb.Url + “/” + $_.Url}} | Out-File $file
2) We use this power shell script to get all the Excel – xlsx file with the entire url, as mentioned in the following blog:

Copying the script, just in case if the link doesn’t work:
Param($siteUrl,
$siteCollectionUrl= “https://abc.xyz”,
$includeVersions = $false,
$includeSystemFiles = $false,
$includePages = $false,
$filename = “C:\xyz\AllRDLFilesList.csv”,
$author,
[Parameter(ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)][Alias(‘Site’)][Microsoft.SharePoint.SPSite]$siteCollection,
[Parameter(ValueFromPipeline=$true, ValueFromPipelineByPropertyName=$true)][Microsoft.SharePoint.SPWeb]$web
)

Write-Host “Script started at” (Get-Date).ToString()

$global:itemList = New-Object System.Collections.Generic.List[PSObject]

Function GetFilesFromSite([Microsoft.SharePoint.SPWeb] $currentWeb)
{
foreach($list in $currentWeb.lists)
{
Write-Host $list.Title “($($list.ParentWebUrl)/$($list.RootFolder.Url))”
if ( ($list.BaseType -eq “DocumentLibrary”) -and ( ($includeSystemFiles) -or ( ($list.DefaultViewUrl -notlike “*_catalogs*”) -and ($list.DefaultViewUrl -notlike “*Style Library*”) ) ) )
{
Write-Host “— Including” $list.Title
foreach($item in $list.items) {
if ( ( ($includePages) -or ($item.Url -notlike “*.aspx”) ) -and ( ($author -eq $null) -or ($author -like $item.File.Author.DisplayName) ) )
{
if ($item.Url -like “*.rdl”)
{
$listItem = New-Object PSObject -Property @{SiteCollectionUrl = $currentWeb.Site.Url;
SiteUrl = $currentWeb.Url;
DocumentLibrary = $list.Title;
Title = $item.Title;
FileSize = ($item.file).length;
Url = $item.Url;
MajorVersion = ($item.file).MajorVersion;
ModifiedDate = $item.file.TimeLastModified;
Author = $item.File.Author.DisplayName;
FileExtension = [System.IO.Path]::GetExtension($item.Url);
}
}
$global:itemList.Add($listItem);

}
}
}
}
}

# get the site collection if it hasn’t been passed via pipe but a URL has been
if ( ($siteCollection -eq $null) -and ($siteCollectionUrl -ne $null) )
{
$siteCollection = Get-SPSite -Identity $siteCollectionUrl
}

# no site collection has been passed so check to see if a subsite has been passed instead
if ($siteCollection -eq $null)
{
if ( ($web -eq $null) -and ($siteUrl -ne $null) )
{
$web = Get-SPWeb -Identity $siteUrl
}

GetFilesFromSite $web
$web.Dispose()
}
else
{
foreach($web in $siteCollection.AllWebs)
{
Write-Host “***” $web.Url “***”
GetFilesFromSite $web
$web.Dispose()
}

$siteCollection.Dispose()
}

Write-Host “Writing ” $filename
$itemList | Select-Object SiteCollectionUrl, SiteUrl, DocumentLibrary, Url, Title, MajorVersion, FileSize, FileExtension, Author, ModifiedDate | Export-Csv $filename -NoType

# release the array since it will be quite large
$itemList = $null

Write-Host “Script finished at” (Get-Date).ToString()

3) Direct SQL query to SQL server SharePoint Contenet Db:
SELECT distinct [DirName] as ReportLocation
,[LeafName] as ReportName
FROM [Content_db].[dbo].[AllDocs]
where [LeafName] like ‘%.rdl’ or [LeafName] like ‘%.xls’ or [LeafName] like ‘%.xlsx’

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s