PowerShell script snippets for common SharePoint 2010 operations

Introduction

Below are PowerShell script snippets that are frequently used and frequently forgotten.   Note that PowerShell scripts for interacting with SharePoint must be either done through the SharePoint Management Shell or by first running the command, Add-PSSnapin Microsoft.SharePoint.PowerShell, in the PowerShell window.

Export a document library

# The URL to the document library is
# http:/myweb.com/site/subsite1/Shared%20Documents/forms/AllItems.aspx
Export-SPweb -Identity http:/myweb.com/site/subsite1/
-ItemURL "Shared%20Documents/forms/AllItems.aspx" -Path C:\test.cmp

NOTE: to force the export to create a single file, use the -Force parameter.
 
Get a site list

$sourceWebURL = http://spdev12/northwind
$sourceListName = "Products"
$SourceWeb = Get-SPWeb $sourceWebURL
$SourceList = $SourceWeb.Lists[$sourceListName]

Count the rows in a list freshly retrieved from a site

$sourceWebURL = http://spdev12/northwind
$sourceListName = "Products"
$SourceWeb = Get-SPWeb $sourceWebURL
$SourceList = $SourceWeb.Lists[$sourceListName]
$Items = $SourceList.Items
$Count = $Items.Count

Get a site list then filter it based upon a column value

$sourceWebURL = http://spdev12/northwind
$sourceListName = "Products"
$SourceWeb = Get-SPWeb $sourceWebURL
$SourceList = $SourceWeb.Lists[$sourceListName]
$SourceItems = $SourceList.Items | Where {$_['Category'] -Like '*Beverages*'}

Count the rows in a site list that has been filtered by some column value

$sourceWebURL = http://spdev12/northwind
$sourceListName = "Products"
$SourceWeb = Get-SPWeb $sourceWebURL
$SourceList = $SourceWeb.Lists[$sourceListName]
$SourceItems = $SourceList.Items | Where {$_['Category'] -Like '*Beverages*'}
$SourceItems.Items.Count

List the fields in a list

$SourceWeb = Get-SPWeb http://spdev12/northwind
$Sourcelist = $SourceWeb .Lists["mylist"]
$Sourcelist.Fields | sort Title | FT Title,StaticName,ID -AutoSize

Iterate through all items (rows) in a list

$sourceListName = "Products"
$SourceWeb = Get-SPWeb $sourceWebURL
$SourceList = $SourceWeb.Lists[$sourceListName]
$SourceItems = $SourceList.Items
$SourceItems | ForEach-Object {
     Write-Host $_['ID']
     Write-Host $_['Title']
}

Get an item in a list by its ID value

$SourceItems = $SourceList.GetItemById("1")
or
$SourceItems = $SourceList.Items | where {$_['ID'] -eq 1}

Cast a variable as a string

$var = 1,000
[string]$MyString = $var.ToString()

List the Properties and Methods of a Command or Object

[Object] | Get-Member

Iterate through each item in a site list and revise it

$sourceListName = "Products"
$SourceWeb = Get-SPWeb $sourceWebURL
$SourceList = $SourceWeb.Lists[$sourceListName]
$SourceItems = $SourceList.items
foreach($item in $SourceItems)
 {
  if($item["Title"] -eq "My first item!")
 {
  $item["Title"] = "My first edited item!"
  $item.Update()
 }
}

Extract list A totals by category and then store these totals in list B

$SiteURL = "http://spdev12/northwind"
$ListName = "Products"
$Site = Get-SPWeb $SiteURL
$SiteList = $Site.Lists[$ListName]

$TotalValueBeverages = 0
$TotalValueCondiments = 0
$TotalValueConfections = 0
$TotalValueDairyProducts = 0
$TotalValueGrainsCereals = 0
$TotalValueMeatPoultry = 0
$TotalValueProduce = 0
$TotalValueSeafood = 0
$BadResult = $FALSE
[int]$Count = 0
$SiteList.Items | ForEach-Object {
    $Count = $Count + 1
    if ($_['Category'] -Like '*Beverages*')
    {
        $TotalValueBeverages = $TotalValueBeverages + $_['UnitsInStock'] * $_['UnitPrice']
    }
    elseif ($_['Category'] -Like '*Condiments*')
    {
        $TotalValueCondiments = $TotalValueCondiments + $_['UnitsInStock'] * $_['UnitPrice']
    }
    elseif ($_['Category'] -Like '*Confections*')
    {
        $TotalValueConfections = $TotalValueConfections + $_['UnitsInStock'] * $_['UnitPrice']
    }
    elseif ($_['Category'] -Like '*Dairy Products*')
    {
        $TotalValueDairyProducts = $TotalValueDairyProducts + $_['UnitsInStock'] * $_['UnitPrice']
    }
    elseif ($_['Category'] -Like '*Grains/Cereals*')
    {
        $TotalValueGrainsCereals = $TotalValueGrainsCereals + $_['UnitsInStock'] * $_['UnitPrice']
    }
    elseif ($_['Category'] -Like '*Meat/Poultry*')
    {
        $TotalValueMeatPoultry = $TotalValueMeatPoultry + $_['UnitsInStock'] * $_['UnitPrice']
    }
    elseif ($_['Category'] -Like '*Produce*')
    {
        $TotalValueProduce = $TotalValueProduce + $_['UnitsInStock'] * $_['UnitPrice']
    }
    elseif ($_['Category'] -Like '*Seafood*')
    {
        $TotalValueSeafood = $TotalValueSeafood + $_['UnitsInStock'] * $_['UnitPrice']
    }
    else
    {
    $BadResult = $TRUE
    }
}
#Some convenient error checking to the console...

Write-Host ("TotalValueBeverages: " + $TotalValueBeverages.ToString())
Write-Host ("TotalValueCondiments: " + $TotalValueCondiments.ToString())
Write-Host ("TotalValueConfections: " + $TotalValueConfections.ToString())
Write-Host ("TotalValueDairyProducts: " + $TotalValueDairyProducts.ToString())
Write-Host ("TotalValueGrainsCereals: " + $TotalValueGrainsCereals.ToString())
Write-Host ("TotalValueMeatPoultry: " + $TotalValueMeatPoultry.ToString())
Write-Host ("TotalValueProduce: " + $TotalValueProduce.ToString())
Write-Host ("TotalValueSeafood: " + $TotalValueSeafood.ToString())
Write-Host ("BadResult: " + $BadResult.ToString())
Write-Host ("Count: " + $Count.ToString())

$SiteURL = "http://spdev12/northwind"
$ListName = "TotalInvByCat"
$Site = Get-SPWeb $SiteURL
$SiteList = $Site.Lists[$ListName]

$ListItem = $SiteList.Items | Where {$_['Category'] -Like "*Beverages*"}
$ListItem["TotalInvValue"] = $TotalValueBeverages
$ListItem.Update()

$ListItem = $SiteList.Items | Where {$_['Category'] -Like "*Condiments*"}
$ListItem["TotalInvValue"] = $TotalValueCondiments
$ListItem.Update()

$ListItem = $SiteList.Items | Where {$_['Category'] -Like "*Confections*"}
$ListItem["TotalInvValue"] = $TotalValueConfections
$ListItem.Update()

$ListItem = $SiteList.Items | Where {$_['Category'] -Like "*Dairy Products*"}
$ListItem["TotalInvValue"] = $TotalValueDairyProducts
$ListItem.Update()

$ListItem = $SiteList.Items | Where {$_['Category'] -Like "*Grains/Cereals*"}
$ListItem["TotalInvValue"] = $TotalValueGrainsCereals
$ListItem.Update()

$ListItem = $SiteList.Items | Where {$_['Category'] -Like "*Meat/Poultry*"}
$ListItem["TotalInvValue"] = $TotalValueMeatPoultry
$ListItem.Update()

$ListItem = $SiteList.Items | Where {$_['Category'] -Like "*Produce*"}
$ListItem["TotalInvValue"] = $TotalValueProduce
$ListItem.Update()

$ListItem = $SiteList.Items | Where {$_['Category'] -Like "*Seafood*"}
$ListItem["TotalInvValue"] = $TotalValueSeafood
$ListItem.Update()

Add User Account or Group to Farm Configuration Database

Add-SPShellAdmin -UserName [domain]\[AccountName]

Get the SharePoint 2010 Product Key

function Get-SP2010ProductKey {    
    $map="BCDFGHJKMPQRTVWXY2346789"
    $value = (get-itemproperty "HKLM:\SOFTWARE\Microsoft\Office\14.0\Registration\{90140000-110D-0000-1000-0000000FF1CE}").digitalproductid[0x34..0x42]  
    $ProductKey = "" 
    for ($i = 24; $i -ge 0; $i--) { 
      $r = 0 
      for ($j = 14; $j -ge 0; $j--) { 
        $r = ($r * 256) -bxor $value[$j] 
        $value[$j] = [math]::Floor([double]($r/24)) 
        $r = $r % 24 
      } 
      $ProductKey = $map[$r] + $ProductKey
      if (($i % 5) -eq 0 -and $i -ne 0) { 
        $ProductKey = "-" + $ProductKey
      } 
    } 
    $ProductKey
} 
#Call the function
Get-SP2010ProductKey

Get the farm build version

(Get-SPFarm).BuildVersion

List all software updates

Get-WmiObject -Class "win32_Quickfixengineering"

List all Websites for a site collection

function ListAllWebsites() {
write-host "Please enter the site url"
$url = read-host
$Site = Get-SPWeb($url)
ForEach ($Web in $Site.Site.AllWebs) 
 {
   Write ($Web.Title+","+$Web.Name+","+$Web.Created+","+$Web.Author+","+$Web.Url)
 }
$site.Dispose()
} 
ListAllWebsites

List all Checked Out Documents in a Site Collection

[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
 
function CheckedOutItems() {
 
  write-host "Please enter the site url"
  $url = read-host
  write ("SiteURL`t" + "FileName`t" + "CheckedOutTo`t" + "ModifiedDate`t"+"Version")
  $site = New-Object Microsoft.SharePoint.SPSite($url)
  $webs = $site.AllWebs
  foreach($web in $webs)
  {
    $listCollections = $web.Lists
    foreach($list in $listCollections)
    {
      if ($list.BaseType.ToString() -eq "DocumentLibrary")
      {
        $dList = [Microsoft.Sharepoint.SPDocumentLibrary]$list
        $items = $dList.Items
        $files = $dList.CheckedOutFiles
        foreach($file in $files)
        {
          $wuse = $file.DirName.Substring($web.ServerRelativeUrl.Length)
          Write($web.Url+"`t"+$wuse+"`/"+$file.LeafName+"`t"+$file.CheckedOutBy.Name+"`t"+$file.TimeLastModified.ToString()+"`t"+"No Checked In Version" )
        }
        foreach($item in $items)
        {
          if ($item["Checked Out To"] -ne $null)
            {
              $splitStrings = $item["Checked Out To"].ToString().Split('#')
              Write ($web.Url+ "`t" + $item.Url + "`t" + $splitStrings[1].ToString() + "`t" + $item["Modified"].ToString() +"`t" + $item["Version"].ToString())
            }
        }
      }
    }
    $web.Dispose()
  }
  $site.Dispose()
 
}
 
CheckedOutItems

List Installed Features on Windows Server 2012

Get-WindowsFeature | ? {$_.Installed}

Disable Loopback Check

New-ItemProperty HKLM:\System\CurrentControlSet\Control\Lsa -Name "DisableLoopbackCheck" -value "1" -PropertyType dword

Get a list of application pools and their service accounts

Get-SPServiceApplicationPool

Get ordered list of all files in a folder

Get-ChildItem [path]\*.* | Sort-Object CreationTime

References

• TechNet Library
• Windows PowerShell Profiles
• Using the Export-Csv Cmdlet
• Overview of Cmdlets Available in Windows PowerShell
• Using the Get-Member Cmdlet
• How to List All of the Windows and Software Updates Applied to a Computer
• Get-SPServiceApplicationPool
• Windows PowerShell Tip of the Week: Get-ChildItem
• Using the Get-ChildItem Cmdlet
• TechNet magazine
• Windows PowerShell
• MSDN Blogs
• Add Microsoft.SharePoint.PowerShell Snap-In to All PowerShell Windows
• SharePoint 2010 with Windows PowerShell Remoting Step by Step
• Add, Modify, Verify, and Sort Your PowerShell Array
• Use PowerShell to Work with CSV Formatted Text
• Stack Overflow
• Append an Array to an Array of Arrays in PowerShell
• SharePoint Blogs
• PowerShell Basics #1: Reading and parsing CSV
• SP 2010: Find error messages with a Correlation ID token in SharePoint 2010
• Get items from SharePoint List using PowerShell script
• Get all SharePoint sites (webs) recursively in PowerShell
• Clone Windows Installed Roles and Features
• MSDN Wiki
• SharePoint 2010: Interacting with Site Columns using PowerShell
• MSDN Dev Center
• SPFarm.BuildVersion property
• SPWeb members