Better SharePoint Lists and List Items in PowerShell
Those who've read some of my previous articles or attended my PowerShell trainings know how much emphasis I put on PowerShell's object-centric nature. The great power of PowerShell lies in its ability to dynamically create objects and members from many different object frameworks, including .NET, COM, WMI, XML, and ADO.NET data sources. SharePoint lists are another example of a data source which would be nice to be able to treat as a collection of objects in PowerShell. While there are some good options for working with SharePoint list items as objects in C# such as LINQ to SharePoint and ADO.NET Data Services, these options are not readily available in PowerShell.
In this post, we'll discuss the information returned by SPList and SPListItem objects and a way to retrieve the actual columns and values stored in a SharePoint list as first-class properties of PowerShell objects. To assist with this you can utilize the SPList module attached to this post. Store the module folder in one of the paths in the $env:PSModulePath variable (e.g. \windows\system32\WindowsPowerShell\v1.0\Modules) and run the following on a SharePoint server to get started:
PS:> Import-Module SPList
To begin, let us discuss how to access information about lists via PowerShell. In the SharePoint hierarchy, the final level for which an out-of-the-box cmdlet exists is the SPWeb object. SPWeb objects represent individual sites in a site collection and contain lists and folders. The simplest way to retrieve all lists in a SharePoint site is as follows (of course, change the value for $SiteUrl to a URL in your farm):
PS:> $SiteUrl = "https://sharepoint10"
PS:> $web = Get-SPWeb $SiteUrl
PS:> $web.Lists | Sort-Object -Property Title | Format-Table -Property Title
No default formatting is defined for SPList objects, so if you don't specify a little formatting via the Format-Table command (as above) or a custom format.ps1xml file you'll end up with way too much information scrolling down your screen. The module included with this post includes a format.ps1xml file which causes SPList objects to be returned as a nicely formatted table or list by default.
If you like, the module available with this post includes a Get-SPList function. The function requires a list name and a SPWeb reference. The SPWeb reference can be an actual URL or can be taken from the pipeline. Two usage examples for the Get-SPList function follow:
PS:> $SiteUrl = "https://sharepoint10"
PS:> Get-SPList -ListName *Announce* -Web $SiteUrl
PS:> Get-SPSite -Identity $SiteUrl | Get-SPWeb | Get-SPList *announce*
The latter example will retrieve all lists with the letters "announce" in their title in all sites (webs) in the specified site collection. Try it!
Once you have a list you can retrieve the items available in that list. Doing so with the object model would look like this:
PS:> $ListName = "Announcements"
PS:> $List = $web.Lists[$ListName]
PS:> $List.Items | Format-Table -Property Title, ID
Again, by default the formatting isn't particularly pretty so we fix it up with Format-Table. And again, the module included with this post includes some prettier formatting instructions for SPListItem objects.
Compare the output of properties and values returned for a list item in PowerShell with the actual column names and values in the SharePoint list (for example in the Web UI). You'll discover that the information returned in PowerShell (from the object model) is actually only metadata about the list item, not the actual list item column names and values themselves. We'll shortly introduce a function which will retrieve the actual data for each SharePoint list item, but first let's see how you'd accomplish this via the object model itself.
First, you generally have to know the name of the column you're looking for in advance. We'll use the Body column in the Announcements list. To retrieve the values for the Title and Body fields for these list items, do this:
PS:> $List.Items | Format-Table -Property Title, @{Label="Body";Expression={$_["Body"]}}
Not pretty is it? Notice how we have to use index notation and hardcode the name of the column we want to retrieve. This is brittle - for example if the list item happened to not have a "Body" column we'd likely get an error. We also have little ability to pipe these results down the pipeline for further evaluation and filtering. For these reasons I've written a function called Get-SPListItem which returns an object whose members (properties) represent the actual column names in the SharePoint list and their respective values for each list item. It's part of the module included with this post and usage looks like this:
PS:> $WebUrl = "https://sharepoint10"
PS:> Get-SPList -ListName Announcements -Web $WebUrl | Get-SPListItem
That's it! The objects it returns contain a property BaseListItem which refers to the original list item (with object metadata) and then a variable number of dynamic properties which reflect the actual data stored on the list item. The way it works is by scanning the list of fields associated with each list item via that item's Fields property and then creating a dynamic property named after the field and with the value stored on the item for that field. Because it looks at the Fields property for each list item it works even on Lists that contain multiple content types (and hence different Fields collections for each list item).
If you need the metadata directly for a list item and want to use the function, I've included an "-OriginalOnly" switch which will return the original SPListItem object.
One of my original uses for these functions was to help in reviewing the "Review Problems and Solutions" list generated by the Health Analyzer after its checks. Items get added to this list and are assigned a HealthReportSeverity of 0-4, with 0 representing a critical error, 1 an error, 2 a warning, and 4 success. Using the functions in the SPList module you can automatically retrieve latest results and even filter by severity. Based on what you find, sending an email message or otherwise tripping some sort of alarm shouldn't be too difficult. Following are some genericized examples which should help you get started in any environment:
PS:> $CAWebApp = Get-SPWebApplication -IncludeCentralAdministration | ? {$_ -is [Microsoft.SharePoint.Administration.SPAdministrationWebApplication] }
PS:> $CAWeb = Get-SPSite -WebApplication $CAWebApp | Get-SPWeb | ? {$_.Title -eq "Central Administration"}
PS:> Get-SPList -Web $CAWeb -ListName "Review Problems and Solutions"
PS:> Get-SPList -Web $CAWeb -ListName "Review Problems and Solutions" | Get-SPListItem
PS:> Get-SPList -Web $CAWeb -ListName "Review Problems and Solutions" | Get-SPListItem | Format-List *Health*
PS:> Get-SPList -Web $CAWeb -ListName "Review Problems and Solutions" | Get-SPListItem | ? {$_.HealthReportSeverity -match "^[012]"} | Format-List *Health*
With this background you're close to being able to set properties on lists and list items as well. For guidance, check out this post by fellow PFE Kirk Evans.
Comments
- Anonymous
March 05, 2012
I was playing around with the "Review Problems and Solutions" list in a script a little while back. I found a way that might be easier to get a reference to it:$healthReportList = [microsoft.sharepoint.administration.health.sphealthreportslist]::LocalThought I'd share in case you would want to use. - Anonymous
July 23, 2012
FYI, you can't download the file until you sign in. So I made a throwaway. This post would be more useful if you'd have just linked the file. - Anonymous
August 23, 2012
A very good post! How do we use this to work with MOSS 2007? Since we do not have most of the OOB PowerShell cmdlets for MOSS I am unable to use it to get data out of MOSS lists. - Anonymous
August 27, 2012
Hi Ashok - The main difference with 2007 is you don't have the Get-SPWeb cmdlet. You need to get a reference to the SPWeb differently; this will work:$Url = 'http://sp.mydomain.com'$Site = New-Object -TypeName Microsoft.SharePoint.SPSite $Url$Web = $Site.OpenWeb($Url)You could also wrap this into a custom Get-SPWeb function to mimic the 2010 functionality:function Get-SPWeb ([String] $Url) { $Site = New-Object -TypeName Microsoft.SharePoint.SPSite $Url $Site.OpenWeb($Url)}Hope this helps!Josh - Anonymous
November 21, 2012
Hi, I'm a beginner in sharepoint 2010,On a windows server 2008R2, sharepoint 2010, I launched get-splist asking for the following fields: FileRef, FileSizeDisplay, Author, Editor, Created, Modified10 files on 1000 Listed (Listed at 12.00 and at 18.00, on 25 Oct 2012) had the same problem:the file size was different between the two listings. Below there's an example.1) /../Documents/Folder/file.xls 31232 18;#Patrizia 18;#Patrizia 12/05/2009 11.27 02/02/2011 11.432) /../Documents/Folder/file.xls 28160 18;#Patrizia 18;#Patrizia 12/05/2009 11.27 02/02/2011 11.43Is there possibile that something running in sharepoint could change the size (as compressions...)?Precisely the command I launched two times is this: Get-SPList -ListName $DLOld -Web $UrlSPOld |Get-SPListItem|select FileDirRef,FileRef,FileSizeDisplay,Author,Editor,Created,Modified|export-csv -delimiter '|' $DLCsvThanks!Leo. - Anonymous
September 05, 2014
Have things in PowerShell and SP 2010 gotten better in the last 2 years regarding the handling of lists, or is this SPLists module still a useful tool for working with lists? - Anonymous
November 13, 2014
Thank you!!! I now see some light.