Get all column values from a SharePoint item using PowerShell

There are many column values associated with a single item in SharePoint that are not easily visible using the browser UI or SharePoint Designer – for example, the item GUID, Content Type ID, various paths and URLs to the item, and others. Sometimes, it can also be handy to view a list of internal column names and values for determining what to use in content queries, data views, PowerShell scripts, XSLT, etc.

The script below enables you to specify a single item or file in a list or document library and output a table containing a full list of the column display names, internal names, and their associated values for the item.

First, run the following script:

function Get-SPItemValues {
    #Ask for the web, list and item names
    $WebName = Read-Host "Please enter the web address:"
    $ListName = Read-Host "Please enter the list or library name:"
    $ItemName = Read-Host "Please enter the item title or file name:"

    #Set up the object variables
    $web = Get-SPWeb $WebName
    $list = $web.Lists[$ListName]
    [string]$queryString = $null

    #Check if the item is a file or list item and run a different query accordingly
    if ($list.BaseType -eq "DocumentLibrary") {
        $queryString = "<Where><Eq><FieldRef Name='FileLeafRef' /><Value Type='File'>" + $ItemName + "</Value></Eq></Where>"
    }
    else
    {
        $queryString = "<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>" + $ItemName + "</Value></Eq></Where>"
    }

    #Create the CAML query to find the item
    $query = New-Object Microsoft.SharePoint.SPQuery
    $query.Query = $queryString
    $item = $list.GetItems($query)[0]

    #Walk through each column associated with the item and
    #output its display name, internal name and value to a new PSObject
    $item.Fields | foreach {
        $fieldValues = @{
            "Display Name" = $_.Title
            "Internal Name" = $_.InternalName
            "Value" = $item[$_.InternalName]
        }
        New-Object PSObject -Property $fieldValues | Select @("Display Name","Internal Name","Value")
    }

    #Dispose of the Web object
    $web.Dispose()
}

Once you have run the script, you can call the function using the following command:

Get-SPItemValues

You will be then asked to enter a site name….

….a list or document library name….

….followed by the title (if a list item) or file name (if in a document library) of the item.

The output from the command will look similar to the screenshot below:

As the script creates the table using the New-Object PSObject command, you can output it in a number of different ways and formats. For example, to output the table sorted by the “Display Name” column, type the following command:

Get-SPItemValues | Sort-Object -Property "Display Name"

To output the table contents to a CSV file, type the following command:

Get-SPItemValues | Sort-Object -Property "Display Name" | Export-Csv -NoType -Path c:\columnvalues.csv

To output the table contents to a grid view, where the output is displayed in an interactive table (requires Microsoft .NET Framework 3.5 with Service Pack 1), type the following command:

Get-SPItemValues | Sort-Object -Property "Display Name" | Out-GridView

Below is a screenshot showing how the grid view looks when using Windows PowerShell ISE:

Easy object disposal in SharePoint 2010 PowerShell scripts

If you’ve done any type of SharePoint development, you’ll know about SPSite and SPWeb object disposal (see here if not). I’m not going to cover the subject again, except to say that you also need to build in disposal practices when using PowerShell scripts for SharePoint to avoid retaining the objects in memory, potentially causing performance issues on your servers.

PowerShell will automatically dispose of objects used in certain types of commands - e.g., those that appear on a single line or as part of a piped command, but you will need to manually dispose of objects for commands where you are instantiating a variable using Get-SPSite or Get-SPWeb. For example, PowerShell will automatically handle object disposal for the following command:

Get-SPWebApplication | Get-SPSite -limit all | ForEach-Object { write-host $_.Url  }

But, as the command below creates a variable called $web based on the SPWeb object, this will require disposal by specifying the $web.dispose() command after it has been used:

$web = Get-SPWeb http://portal
#PowerShell script goes here
#Now dispose of SPWeb object
$web.dispose()

Now, for all you IT Pro’s out there, this alone may scare you into never using PowerShell to manage SharePoint again (and I wouldn’t blame you). However, help is at hand with the Start-SPAssignment and Stop-SPAssignment cmdlets introduced in SharePoint 2010. To overcome the hassle of having to know when to dispose of an object and when not to, all you need to do is sandwich your script in between the following commands:

Start-SPAssignment –Global

#PowerShell script goes here

Stop-SPAssignment –Global

Any objects defined between the Start-SPAssignment –Global and Stop-SPAssignment –Global commands will be automatically disposed of by PowerShell as it will now know when the script has finished running. So, even if you are not sure whether objects should be disposed of or not, as long as you always top and tail your script with these cmdlets, you can’t go wrong.

Changing the master page on SharePoint sites with PowerShell

On SharePoint Server 2010 publishing sites, you can change the name of the master page from the browser UI by going to Site Settings > Master page, under the “Look and Feel” section. However, as this option does not exist in the administration UI on team sites, each site in the site collection will retain a copy of its own master page by default, and it is not possible to change the name or location of it from the browser. For example, if you had one top level team site and five sub-sites, that is six separate master pages to manage for a relatively small structure.

One approach you may want to take is to store a central master page on the root site of the site collection and use PowerShell to change the master page setting for all sites in the site collection, pointing to the new name and location. To do this, first upload your new master page to the master page gallery on the root site of the site collection by going to Site Settings > Master pages under the “Galleries” section, clicking on the Documents tab, and uploading the new custom master page to the library:

 

if you want to change the master page setting for just one site, then use the script below:

$web = Get-SPWeb siteurl
$web.MasterUrl = "masterpagerelativeurl"
$web.Update()
$web.Dispose()

For the example site pictured above, the script would be as follows:

$web = Get-SPWeb http://portal/sites/collaboration
$web.MasterUrl = "/sites/collaboration/_catalogs/masterpage/custom.master"
$web.Update()
$web.Dispose()

Refreshing the root site in the browser will now show the new master page design, but all sub-sites will still show the old master page design – this is because they are each using a master page stored on their respective site. To change the master page setting for all sites in a site collection, use the following script:

$site = Get-SPSite http://portal/sites/collaboration
$site | Get-SPWeb -limit all | ForEach-Object { $_.MasterUrl = "/sites/collaboration/_catalogs/masterpage/custom.master";$_.Update() }
$site.Dispose()

Note that this will only change existing sites and will not set a custom master page for any new sites created. To update new sites, you will need to either run the script every time a new site is created, or use an alternative approach such as a feature receiver/stapler or Web provisioning event receiver to automatically set the new master page on site creation.

If you want to revert all team sites in the site collection back to using their own default master page again, then use the following script:

$site = Get-SPSite http://portal/sites/collaboration
$site | Get-SPWeb -limit all | ForEach-Object { $_.MasterUrl = $_.ServerRelativeUrl + "/_catalogs/masterpage/v4.master";$_.Update() }
$site.Dispose()

Check in and approve all publishing pages using PowerShell

This PowerShell script performs the following tasks on a SharePoint Server 2010 site collection containing publishing sites:

• Checks each site in the site collection to test if it is a publishing site
• If it is a publishing site, grab all the pages in the Pages library
• Check in any page currently checked out by you
• On sites requiring approval for publishing pages, approve any page that has been checked in but still has a Draft approval status

To use, you must first run the following script:

function PublishAllPages ($url)
{
    $site = Get-SPSite -Identity $url
    #Walk through each Publishing Web and check in every page
    $site | Get-SPWeb -limit all | ForEach-Object {
        #Check to see if site is a publishing site
        if ([Microsoft.SharePoint.Publishing.PublishingWeb]::IsPublishingWeb($_)) {
            write-host "Reviewing pages in"$_.Title"site...."
            #Get the Publishing Web and pages within it
            $publishingWeb = [Microsoft.SharePoint.Publishing.PublishingWeb]::GetPublishingWeb($_)
            $publishingPages = $publishingWeb.GetPublishingPages()
            foreach ($publishingPage in $publishingPages)
            {
                #Check to ensure the page is checked out, and if so, check it in
                if ($publishingPage.ListItem.File.CheckOutStatus -ne "None")
                {
                    CheckInPage -page $publishingPage -web $_
                }
                else
                {
                    #Notify administrator that the page is already checked in
                    write-host $publishingPage.Title"("$publishingPage.Name") has already been checked in"
                }
                #Check to ensure page is checked in, and is so, approve it
                if ($publishingPage.ListItem.File.CheckOutStatus -eq "None")
                {
                    ApprovePage -page $publishingPage
                }
            }
        }
        else
        {
            #Notify administrator that the site is not a publishing site
            write-host $_.Title"is not a publishing site"
        }
    }
    #Dispose of the site object
    $site.Dispose()
}

function CheckInPage ($page, $web)
{
    #Check to ensure the page is checked out by you, and if so, check it in
    if ($page.ListItem.File.CheckedOutBy.UserLogin -eq $web.CurrentUser.UserLogin)
    {
        $page.CheckIn("Page checked in automatically by PowerShell script")
        write-host $page.Title"("$page.Name") has been checked in"
    }
    else
    {
        write-host $page.Title"("$page.Name") has not been checked in as it is currently checked out to"$page.ListItem.File.CheckedOutBy
    }
}

function ApprovePage ($page)
{
    if($page.ListItem.ListItems.List.EnableModeration)
    {
        #Check to ensure page requires approval, and if so, approve it
        if ($page.ListItem["Approval Status"] -eq 0)
        {
            write-host $page.Title"("$page.Name") is already approved"
        }
        else
        {
            $page.ListItem.File.Approve("Page approved automatically by PowerShell script")
            write-host $page.Title"("$page.Name") has been approved"
        }
    }
    else
    {
        write-host $page.Title"("$page.Name") does not require approval in this site"
    }
}

Once the script has been run, you can call it using the following command:

PublishAllPages -url sitecollectionurl

For example, for a site collection with the address http://portal, type the following command:

PublishAllPages -url http://portal

When you run this command, a progress report will be shown in the PowerShell console – an example of which is shown below:

PS C:\> PublishAllPages -url http://portal
Reviewing pages in PAC Portal site....
Home ( default.aspx ) has not been checked in as it is currently checked out to domain\aansell
Contact Us ( contactus.aspx ) has already been checked in
Contact Us ( contactus.aspx ) is already approved
Products ( products.aspx ) has already been checked in
Products ( products.aspx ) has been approved
Test ( test.aspx ) has been checked in
Test ( test.aspx ) has been approved

Note that I decided not to check in ALL pages on each site - i.e., those not checked out by you. Doing this could potentially cause lots of issues as users will lose any changes made since they checked the page out. However, if you wanted to do this, you could remove the if ($page.ListItem.File.CheckedOutBy.UserLogin -eq $web.CurrentUser.UserLogin) check performed in the CheckInPage function.

Restrict site collection users to a specific OU using PowerShell

There may be a scenario where you might want to restrict a site collection to only permit users from a specific Organizational Unit (OU) hierarchy in Active Directory for assigning permissions. For example, I have two OUs in AD called IT Team and Accounts. IT Team contains a user called “Shaun Young” and all other users are present in the Accounts OU, as shown below:

In SharePoint, I have a site collection called “IT Team” which by default allows me to add any user from Active Directory:

For this example, I want to restrict the site collection so that only members of the IT Team OU are able to be given permissions to the site collection. I can do this in PowerShell by simply typing one line:

Set-SPSite -Identity "http://portal/sites/IT Team" -UserAccountDirectoryPath "ou=IT Team,dc=pacdomain2,dc=local"

Now when I search for an account, it shows me an error if I try any account from the Accounts OU:

Note that specifying an OU using this command will still allow the principal picker to search for users below the OU that you have restricted – in other words, you are not restricting the ability to add users from just one OU, as you can still find users from the entire OU hierarchy below it.

To restore the site collection back to the default setting, type the command again with a double quote for the UserAccountDirectoryPath setting, as follows:

Set-SPSite -Identity "http://portal/sites/IT Team" -UserAccountDirectoryPath ""

The functionality provided by this cmdlet mirrors that provided by the setsiteuseraccountdirectorypath stsadm command introduced in Office SharePoint Server 2007 SP1, which has an article on TechNet here. There is some important information in this article that describes the behaviour of this command should you use it on new or existing site collections:

“If a site collection is new and an administrator uses the setsiteuseraccountdirectorypath operation to specify a target OU, only users under the specified path can be added to the site collection and no one else can be added to the site collection.”

“If users have already been added to a site collection and the setsiteuseraccountdirectorypath operation is run, only users under the specified path will be able to be added going forward.”

“Unlike the Peoplepicker-serviceaccountdirectorypaths property where multiple OUs can be specified, only a single OU can be set at a time when the setsiteuseraccountdirectorypath operation is used. As a result, this operation should only be run once per site collection.”

Create and manage SharePoint site collection quotas using PowerShell

Whilst you can enforce disk quotas on any site collection in SharePoint, I have seen them used most commonly to cap the amount of disk space used in My Sites. Providing a default site quota template from which all My Sites inherit during creation is easy enough, but it can be a pain for administrators to change the template on existing My Sites as the interface used to modify quota templates on site collections is fine for the odd one or two, but there are no options in the SharePoint UI to bulk modify the quota template used on multiple sites. In this article I will explain how to use PowerShell with SharePoint 2010 to create a new quota template, apply it to a single site collection, and then multiple site collections in one hit.

First, run this script below to set up a function for creating new quota templates:

function CreateQuotaTemplate ($Name, $MaxLevelMB, $WarnLevelMB)
{
    $quotaTemplate = New-Object Microsoft.SharePoint.Administration.SPQuotaTemplate
    $quotaTemplate.Name = $Name
    $quotaTemplate.StorageMaximumLevel = ($MaxLevelMB*1024)*1024
    $quotaTemplate.StorageWarningLevel = ($WarnLevelMB*1024)*1024
    $contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
    $contentService.QuotaTemplates.Add($quotaTemplate)
    $contentService.Update()
}

Once you have run the script, type the following command to create a new quota template in your SharePoint farm:

CreateQuotaTemplate –Name “Template Name” –MaxLevelMB MaximumLevelInMB –WarnLevelMB WarningLevelInMB

For example, if we wanted to create a new quota template called “Media Users” with a maximum quota size of 500 MB and warning level of 400 MB, then we can type this command:

CreateQuotaTemplate –Name “Media Users” –MaxLevelMB 500 –WarnLevelMB 400

Now run this script to set up a function for assigning a quota template to a single site collection:

function AssignQuotaTemplate ($Name, $Url)
{
    $contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
    $quotaTemplate = $contentService.QuotaTemplates[$Name]
    Set-SPSite -Identity $Url -QuotaTemplate $quotaTemplate
}

Once this script has been run, you can now use the following command to assign our “Media Users” quota template to a single site collection:

AssignQuotaTemplate –Name “Media Users” –Url http://mysite/personal/phil.childs

Finally, run this script to set up a function for replacing a quota template across all site collections in a web application:

function BulkReplaceQuotaTemplates ($WebApp, $OldTemplate, $NewTemplate)
{
    $contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
    $oldQuotaTemplate = $contentService.QuotaTemplates[$OldTemplate]
    $newQuotaTemplate = $contentService.QuotaTemplates[$NewTemplate]
    $webApplication = Get-SPWebApplication $WebApp
    $webApplication | Get-SPSite -limit all | ForEach-Object {
        if ($_.Quota.QuotaID -eq $oldQuotaTemplate.QuotaID) {   
            Set-SPSite -Identity $_.Url -QuotaTemplate $newQuotaTemplate
        }
    }
}

This function allows you to specify the web application, old quota template you want to replace, and the new quota template you want to replace it with. For example, lets assume that all My Sites in your SharePoint implementation have been created with the default “Personal Site” quota template. We could run a script to simply bulk change all of these site collections to a new quota template, but there may be the odd one or two that have been configured with individual quotas. Therefore, the command below will walk through each site collection in the web application and change the quota template to “Media Users”, but only on site collections that are currently configured with the “Personal Site” quota template – leaving alone any site collections that have been configured with individual quota templates:

BulkReplaceQuotaTemplates -WebApp http://mysite -OldTemplate "Personal Site" -NewTemplate "Media Users”

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()