• Find lists and libraries based on just about any feature (e.g., versioning, content approval, date created).
  • Find all libraries that use a selected content type.
  • Find all items based on a selected column (including finding all documents created by a user).
  • Find all documents of a certain type (e.g., .docx).
  • Find all files larger than a certain size.
  • List all libraries, their file counts, and total file size.


Permissions

A reminder before we get started: Just because you can use PowerShell does not mean that you can ignore SharePoint security. See "Inventorying SharePoint Using PowerShell" for the basic security requirements. You will need to have at least read permissions to the content you want to explore. You can add the account you're using with PowerShell as a site collection administrator to every site collection or use SharePoint Central Administration user policies to give your account full read permissions in each application (select Central Administration, Application Management, then select an application, then click User Policy).


A Warning

The scripts I will show you can put quite a load on the server while they're running and can impact your users. You might want to run these scripts only outside of business hours. Before starting with these scripts, please review "Inventorying SharePoint Using PowerShell."


General Pattern for the Sample Scripts

All the sample scripts in this article are "one-liners." Although they are formatted into multiple lines to fit the article layout, you can enter them on one line. You can also enter the scripts on multiple lines if you break them after the pipe ( | ) or add a back tick ( ` ) at the end of each line. For writing and test scripts, I highly recommend using the Windows PowerShell Integrated Scripting Environment (ISE) over the command prompt.
The scripts in this article follow this pattern:
  • Select a web, a site collection of webs, or the entire farm of webs:
    • Get-SPWeb http://sharepoint/sites/training
    • Get-SPSite http://sharepoint/sites/training -Limit All | Select -ExpandProperty AllWebs
    • Get-SPSite http://sharepoint/sites/training -Limit All | Select -ExpandProperty AllWebs OR
    • Get-SPSite http://sharepoint/sites/training -Limit All | Get-SPWeb -Limit All
    • Get-SPSite -Limit All | Select -ExpandProperty AllWebs OR
    • Get-SPSite -Limit All | Get-SPWeb -Limit All
  • Expand the Lists property and pass each list one at a time though the pipe:
    • Select -ExpandProperty Lists
  • Filter the lists:
    • Where { $_.GetType().Name -eq "SPDocumentLibrary" }
  • Expand the list's Items collection and pass each item one at a time though the pipe:
    • Select -ExpandProperty Items
  • Filter the items:
    • Where { $_.Name -like "*airplane*" }
  • Select and format the results:
    • Select Name, @{Name = "URL";Expression = {$_.ParentList.ParentWeb.Url + "/" + $_.Url}
  • And in some cases, summarize the results:
    • Group ParentWebUrl | Select Name, Count
  • And for brevity, common aliases have been used -- for example, Select = Select-Object, Where = Where-Object, Group = Group-Object.

A Single Web, Site Collection, Application, or an Entire Farm? 

In the examples that follow, we will start from a single SharePoint site (a web) with a statement something like this one:
Get-SPWeb http://sharepoint/sites/training/salestraining |
   Select -ExpandProperty Lists |
   Select Title
You can expand these examples to inventory a full site collection, a web application, or an entire farm by piping from the higher-level objects and including details in your final Select about the higher levels.
All lists in the site collection:

Get-SPSite http://sharepoint/sites/training -Limit All |
   Select -ExpandProperty AllWebs |
   Select -ExpandProperty Lists |
   Select ParentWebUrl, Title

All site collections in a single web application:
Get-SPSite -WebApplication http://sharepoint -Limit All |
   Select -ExpandProperty AllWebs |
   Select -ExpandProperty Lists |
   Select ParentWebUrl, Title
All site collections in the farm:
Get-SPSite -Limit All |
   Select -ExpandProperty AllWebs |
   Select -ExpandProperty Lists |
   Select {$_.ParentWeb.Url}, Title
Note: In this last example, we replaced ParentWebUrl with {$_.ParentWeb.Url} because ParentWebUrl is an application-relative URL and does not show us the full URL of the application.
Tip: Getting quick counts. If you only want to know the count of the items found, you can wrap a PowerShell expression in parentheses and add the Count property. So to see how many lists there are in the entire farm, you could use this statement:

( Get-SPSite -Limit All |
   Select -ExpandProperty AllWebs |
   Select -ExpandProperty Lists ).Count

Finding Lists of a Certain Type

There are two basic list types: lists and libraries. To find one or the other, you can test for the list type by calling GetType().
To find only lists:
Get-SPWeb http://sharepoint/sites/training |
   Select -ExpandProperty Lists |
   Where { $_.GetType().Name -eq "SPList" } |
   Select Title
To find only libraries:
Get-SPWeb http://sharepoint/sites/training |
   Select -ExpandProperty Lists |
   Where { $_.GetType().Name -eq "SPDocumentLibrary" } |
   Select Title
Many lists and libraries are used internally by SharePoint and are typically hidden from users. Examples of these include the Master Page Gallery and the Workflows library. We can exclude these lists from most of our searches as they will not contain end-user content. To do so, add "-and -not $_.hidden" to the Where statement.
Get-SPWeb http://sharepoint/sites/training |
   Select -ExpandProperty Lists |
   Where { $_.GetType().Name -eq "SPDocumentLibrary" -and
           -not $_.hidden } |
   Select Title


Finding Lists and Libraries by Just About Any Property

By changing the property you filter on in the Where clause, you can find lists and libraries based on just about any property or feature. Following is an example that finds lists and libraries that support versioning. Just edit the Where clause to search for another property.
Get-SPWeb http://sharepoint/sites/training |
   Select -ExpandProperty Lists |
   Where { -not $_.hidden -and
           $_.EnableVersioning -eq $true} |
   Select ParentWebUrl, title
Here are some of the features you might search for (where $_ is a list or library):
  • Versioning: $_.EnableVersioning -eq $true
  • Content approval enabled: $_.EnableModeration -eq $true
  • External Lists: $_.HasExternalDataSource -eq $true
  • Is email enabled: $_.EmailAlias -ne $null or just $_.EmailAlias
  • Are RSS feeds enabled: $_.AllowRssFeeds -eq $true
  • Attachments enabled (for lists only) $_.EnableAttachments -eq $true
  • Folders enabled: $_.EnableFolderCreation -eq $true
  • Has folders: $_.Folders.Count -gt 0
  • Has no items: $_.ItemCount -eq 0 (Note: External lists will always report ItemCount=0.)
  • Has more than x items: $_.ItemCount -gt 1000
  • Has associated workflows: $_.WorkflowAssociations.count -gt 0
  • Is displayed on QuickLaunch: $_.OnQuickLaunch -eq $true
  • Has broken inheritance (unique permissions) $_.HasUniqueRoleAssignments -eq $true (I'll cover permissions in more detail in an upcoming article.)
  • Who created the library: $_.Author -like "sharepoint\stellas"
  • Dates: Find lists and libraries based on when they were created or the last time an item was added/modified or deleted.
  • $_.LastItemModifiedDate -gt (Get-Date 1/1/2013)
    • Created
    • LastItemModifiedDate
    • LastItemDeletedDate
  • Or any combination of the previous statements by using -Or, -And, or -Not and grouping the conditions with parentheses. Example: ( $_.Author -like "sharepoint\stellas" -or $_.Author -like "sharepoint\samc" ) -and $_.LastItemModifiedDate -gt (Get-Date 1/1/2013)

Finding Lists Using a Certain Content Type

Although you could filter on the title of a list, that isn't a very reliable way to find all Announcement lists or all document libraries. Your site owners can name their lists anything they like. Because lists can support more than one content type, a better way is to search for lists that support a certain content type.
You first might want to display all the content types used in a site collection, so that you will know their names:
Get-SPWeb http://sharepoint/sites/training |
   Select -ExpandProperty ContentTypes |
   Select Name, ID | Sort Name
Now let's find all the Announcement lists in a site collection. To do so, we will filter the list of lists by content type.
Get-SPSite http://sharepoint/sites/training |
   Select -ExpandProperty AllWebs |
   Select -ExpandProperty Lists |
   ForEach { ForEach($ct in $_.ContentTypes)
     { if($ct.Name -eq "Announcement") {$_} } } |
   Select ParentWebUrl, Title
This example needs some explaining! The outer ForEach accepts a List object from the pipe and runs the code in the brackets ( { } ). That code checks each content type "(ForEach($ct in $_.ContentTypes))" to verify that its name is "Announcement" and, if so, sends the list "( $_ )" on to the next pipe. From there, we can then select the URL and Title of the list. Here's a reformatted version to better show the nesting:
... | ForEach      for each item in the pipe
    {
      ForEach($ct in $_.ContentTypes) for each item in the content type collection
        {
                 If it's the one we want, pass it ($_) on to the next pipe
          if($ct.Name -eq "Announcement") {$_}
        }
    }
| ...


Finding List Items and Library Documents of a Certain Type

Documents are stored in two places: libraries and as list attachments. Although the library's item properties will have most of the information you'll need, some data such as file size is available from a child object named File. Files in lists are found in the Attachments property.
We will look first at finding documents in libraries. There are a number of ways we might want to find list items and documents:
  • by a word in the document's title
  • by a value in a column
  • by file type (e.g., .doc, .docx, .ppt, .pdf)
  • by size
  • by content type (as discussed earlier)
  • by age
  • by a feature such as versioning or content approval
  • any combination of these criteria
Tip: When filtering content using Where-Object (or just Where), you can use the operators -Like and -Match. Like uses wildcards ("*" and "?"), as shown in the following example; Match uses regular expressions.
Where { $_.Name -Like "*share*" }
By a word in the document's title. By using a Where cmdlet with a -like test, we can find all the matching items in all libraries in a site. Folders and document sets are also items in a library and will be included if they match. We will add a test for content types later to be able to select or exclude items such as folders. The following example displays the file size and the URL to the file. Note that the file size is extracted from the library item's File property.
                              Get-SPWeb http://sharepoint/sites/training |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                         -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Where { $_.Name -like "*airplane*" } |                                 Select Name, {$_.File.Length}, url
By a value in a column. You can filter your search on any of the columns in the list or library. To filter on a column, you will first need a list of the columns in the library. Columns are referred to as fields in SharePoint lists. Here's how to get the list of columns/fields on the Shared Documents library:
Get-SPWeb http://sharepoint/sites/training |
   Select -ExpandProperty Lists |
   Where { $_.Title -eq "Shared Documents" } |
   Select -ExpandProperty Fields | Select Title, InternalName
To filter the list of documents using a field, just add a Where-Object { $_["fieldname"] -eq "sometext"} to the pipeline.
Notes:
  • There is no period after the "$_" when working with fields -- for example, $_["Created By"].
  • The field name is case sensitive.
  • You can use either the field's Title or InternalName, but the InternalName is more reliable as the site owner can rename column titles.
The following example finds all the libraries created by a user:
                              Get-SPWeb http://sharepoint/sites/training |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                        -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Where { $_["Created By"] -like "*sharepoint\stella*" } |                                 Select Name, url, {$_["Created By"]}
Note that fields that store users and look up fields include an ID number, a delimiter (";#"), and the text value. You will either need to use a regular expression pattern to match the text after the delimiter or use -like with a leading wildcard.
The next example finds all the documents in the farm with a specified column value. (In this case, find every document created by sharepoint\stellas.)
                              Get-SPSite -Limit All |                                 Get-SPWeb -Limit All |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                        -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Where { $_["Created By"] -like "*stella*" } |                                 Select Name,                                        @{ Name = "URL";                                           Expression = {$_.ParentList.ParentWeb.Url + "/" + $_.Url }                                         }
Note: The "@{ Name=""; Expresion={…} }" is used to create a derived, or calculated, column.
By file type. Looking for files by their file extension is simply a matter of searching for the extension text within the name of the file. To find ".docx" files, you just pipe the list of items to "Where { $_.Name -Like "*.docx" }". Although you could find all ".doc" and ".docx" files with "Where { $_.Name -Like "*.doc*" }", a more precise approach would be to test for both extensions with "Where { $_.Name -Like "*.doc" -or $_.Name -Like "*.docx" }".
                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                         -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Where { $_.Name -Like "*.docx" } |                                 Select Name,                                       @{Name="URL";                                        Expression={$_.ParentList.ParentWeb.Url + "/" + $_.Url}}
If you want to find more than one type, simply add "-or" to the Where clause:
Where {     $_.Name -Like "*.docx"
        -or $_.Name -Like "*.xlsx"
        -or $_.Name -Like "*.pptx" }
By size. File size is just another field, so testing for size is similar to several of the previous examples. Be careful, though -- there's more than one file size property!
  • $_.File.Length (This is numeric and is probably the best choice.)
  • $_["File Size"] (This is the same value as $_.File.Length but is text, not a number.)
  • $_.File.TotalLength (This is the same as $_.File.Length, except for web part pages where the value includes the storage needed for the page and the web parts.)
                              Get-SPWeb http://sharepoint/sites/training |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                         -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Where { $_.File.Length -gt 5000 } |                                 Select Name, {$_.File.Length},                                        @{Name="URL";                                        Expression={$_.ParentList.ParentWeb.Url + "/" + $_.Url}}
By age. To filter by date, we can compare against a date or a calculated date. You will need to know the field name or the internal name. The following example finds all documents in the farm created before 12/1/2011:
                              Get-SPSite -Limit All |                                 Get-SPWeb -Limit All |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                         -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Where { $_["Created"] -lt (get-date "12/1/2011") } |                                 Select Name, {$_["Created"]},                                    @{Name="URL";Expression={$_.ParentList.ParentWeb.Url + "/" + $_.Url}}
You can also use the Get-Date cmdlet to do date math. Get-Date returns today's date by default. By wrapping the cmdlet in parentheses, we can then call the .Net DateTime methods to calculate any future or past date. Some of the methods include AddMinutes, AddDays, and AddYears. Here's an example that finds all documents more than three years old and uses (Get-Date).AddYears(-3):
                              Get-SPWeb http://sharepoint/sites/training/salestraining |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                         -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Where { $_["Created"] -lt (Get-Date).AddYears(-3) } |                                 Select Name, {$_["Created"]},                                    @{Name="URL";Expression={$_.ParentList.ParentWeb.Url + "/" + $_.Url}}
By a feature such as versioning or content approval. Versioning is a great feature of SharePoint, when properly used. Site owners often turn on versioning without setting version limits. As a result, you can end up with hundreds of versions as users edit a document. List and library items have a property called Versions that contains the collection of all versions of an item. We can get the count of Versions to find documents with excessive numbers of versions. The following example finds all documents with more than 20 versions:
                              Get-SPWeb http://sharepoint/sites/training/salestraining |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                         -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Where { $_.Versions.Count -gt 20 } |                                 Select Name, @{Name="URL";Expression={$_.ParentList.ParentWeb.Url + "/" + $_.Url}},                                        {$_.versions.count}
We can speed up the previous example by checking only those lists that have the property EnableVersioning set to $true.
                              Get-SPWeb http://sharepoint/sites/training/salestraining |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                         -not $_.Hidden -and                                         $_.EnableVersioning -eq $true } |                                 Select -ExpandProperty Items |                                 Where { $_.Versions.Count -gt 20 } |                                 Select Name, @{Name="URL";Expression={$_.ParentList.ParentWeb.Url + "/" + $_.Url}},                                        {$_.versions.count}

List All Libraries, Their File Counts, and Total File Size

The previous sample scripts returned lists of things. Sometimes you just want counts. PowerShell includes a Group-Object cmdlet that does this nicely.
Example: How many non-hidden lists are in each site (SPWeb)?
                              Get-SPSite -Limit All |                                 Get-SPWeb -Limit All |                                 Select -ExpandProperty Lists |                                 Where { -not $_.hidden } |                                 Group ParentWebUrl |                                 Select Name, Count |                                 FT -AutoSize
In this example, we are getting all site collection, all webs, and all the lists that are not hidden, then grouping on the URL of the web. The last step is to select the name and count of the group. For neatness, you can use Format-Table (FT) to adjust the widths of the columns.
Example: How many documents are in each library in a web?
                              Get-SPWeb http://sharepoint/sites/training |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                         -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Group {$_.ParentList.ParentWeb.Url + "/" + $_.ParentList.Title} |                                 Select Name, Count |                                 FT -AutoSize
The only trick in this example is the grouping on an expression to get the full path to the library. The count was easy as the Group-Object cmdlet automatically added the count. To get subtotals by library will take a little more work.
Example: Get the count and total size of the documents that are in each library in a web.
                              Get-SPWeb http://sharepoint/sites/training |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                 -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Group {$_.ParentList.ParentWeb.Url + "/" + $_.ParentList.Title} |                                 Select Name, count,                                        @{Name='Total';                                          Expression={$_.Group |                                             ForEach-Object `                                              -Begin {$total=0;} `                                              -Process {$total+=[int]$_.File.Length} `                                              -End {$total} `                                          }                                         } |                                 format-table -AutoSize
All the lines down to the Group statement are the same as in the previous example. In the Select statement, we are displaying the name and count of the group, then using a ForEach to loop through each item in the group to add up the total.
Note the back ticks ( ` ) in the ForEach section. These are "continued on next line" marks that are needed because we broke the ForEach into multiple lines. You could write the same expression without the back ticks if it is all on one line.
ForEach-Object -Begin { ... } -Process { ... } -End { ... }
To filter by file type and size, we just add a Where statement to filter the items.
                              Get-SPWeb http://sharepoint/sites/training |                                 Select -ExpandProperty Lists |                                 Where { $_.GetType().Name -eq "SPDocumentLibrary" -and                                         -not $_.Hidden } |                                 Select -ExpandProperty Items |                                 Where { $_.Name -like "*.docx" -and                                         $_.File.Length -gt 10000000 } |                                 Group {$_.ParentList.ParentWeb.Url + "/" + $_.ParentList.Title} |                                 Select Name, Count |                                 FT -AutoSize
Tip: How large is a megabyte? Some say 1,000,000 bytes; others say 1,048,576 bytes. If you want to use the second example of a megabyte and you don't have all your powers of two memorized, then let PowerShell do the work! Just type two letters: KB, MB, GB, or TB. There are no spaces between your number and the two letters.
  • 1KB = 1024
  • 1MB = 1048576
  • 1GB = 1073741824
  • 1TB = 1099511627776
So, for example, to find files larger than 10MB, just type this:

$_.File.Length -gt 10MB

From Inventory to Content to Security

In the first article in this series, we got a start on inventorying core SharePoint objects such as site collections and websites. In this article, we focused on content, such as finding all documents of a certain type (e.g., .docx) or larger than a certain size; listing all libraries, their file counts, and total file size; or finding all libraries that use a specific content type. In the next article, we'll focus on users and security, such as listing all groups and their members; all site owners in all sites; all site collection administrators; all users who have access to a file; everything to which a user has access; and all sites, libraries, folders, and documents that have unique permissions (i.e., broken inheritance).