Viewing SharePoint properties as lists, tables and CSV in PowerShell

There are often times where it can be useful to gather a list of object properties in SharePoint for reporting, operations, or governance purposes. Fortunately, this is a very easy task in SharePoint 2010 with PowerShell. Once you have set up a variable to define your object (site collection, site, list, item, etc.), you can use a single line of script to output its properties to the PowerShell console or text file in a list or table format.

If you are viewing the properties of a single object, then the best command to use is Format-List, which simply lists each property on a separate line. For example, the script below lists the properties of a site:

$web = Get-SPWeb http://portal
$web | Format-List

The output will look as follows:

However, it is quite likely that you only want to show specific properties associated with an object, rather than a very long list of all of them. For example, you may want to show the name of the site along with its URL, owners, created date, and the date an item was last modified. Here you can use the following command:

$web | Format-List -property Title, Url, SiteAdministrators, Created, LastItemModifiedDate

This will show the following output:

To save this output to a text file, use the Out-File command, as follows:

$web | Format-List -property Title, Url, SiteAdministrators, Created, LastItemModifiedDate | Out-File -FilePath c:\webdetails.txt

If you would like to include other properties in addition or instead of those above, then use this command to list all properties available for the object:

$web | Get-Member -MemberType Property

Don’t forget to dispose of your SPWeb object properly when you’re finished:

$web.Dispose()

The Format-List command works well for single objects, but for multiple objects it is best to show the results in a table. The command to use here is Format-Table. For example, you may want to show a list of all sites in a site collection, along with the properties shown in the previous example for each site:

$site = Get-SPSite http://portal
$site | Get-SPWeb -limit all | Format-Table -wrap -property Title, Url, SiteAdministrators, Created, LastItemModifiedDate

The output to this command will look as follows:

To save this output to a file, use the Out-File command, as follows:

$site | Get-SPWeb -limit all | Format-Table -wrap -property Title, Url, SiteAdministrators, Created, LastItemModifiedDate | Out-File -FilePath c:\webdetails.txt

However, there is a problem with this format if you want to use the data in a CSV file. As you can see from the SiteAdministrators column in the screenshot above, the Format-Table cmdlet wraps longer passages of text to new lines in the text file. If you were to try and create a CSV file from this data, it would treat the wrapped text as a new line. To overcome this, there is quite a neat Export-Csv cmdlet available in PowerShell, which you can use in this example as follows:

$site | Get-SPWeb -limit all | Select-Object Title,Url,SiteAdministrators | Export-Csv -Path c:\webdetails.csv –notype

This will output to a CSV file in the following format:

"Title","Url","SiteAdministrators"
"PAC Portal","http://portal","Microsoft.SharePoint.SPUserCollection"
"Search","http://portal/search","Microsoft.SharePoint.SPUserCollection"
"Team",http://portal/team","Microsoft.SharePoint.SPUserCollection

We’re not done yet though. You will see from the output above that we have another problem – it has exported our SiteAdministrators property values as “Microsoft.SharePoint.SPUserCollection” instead of the comma-delimited list of site administrators that we got from the Format-Table command. The only way I can come up with to fix this is to perform a join for this type of multi-valued property. This can be done by replacing the property name in the Select-Object command with @{Name=’PropertyName’;Expression={[string]::join(";", ($_.PropertyName))}} instead. So for our example, the Export-Csv command will now look as follows:

$site | Get-SPWeb -limit all | Select-Object Title,Url,@{Name=’SiteAdministrators’;Expression={[string]::join(";", ($_.SiteAdministrators))}} | Export-Csv -Path c:\webdetails.csv –notype

This will fix the CSV file to look like this:

"Title","Url","SiteAdministrators"
"PAC Portal","http://portal","i:0#.w|pacdomain2\spadmin;i:0#.w|pacdomain2\phil.childs"
"Search","http://portal/search","i:0#.w|pacdomain2\spadmin;i:0#.w|pacdomain2\phil.childs"
"Team","http://portal/team","i:0#.w|pacdomain2\spadmin;i:0#.w|pacdomain2\phil.childs"

Don’t forget to dispose of your SPSite object properly when you’re finished:

$site.Dispose()