Searching and Auditing SharePoint with PowerShell


Last week I gave a presentation to the Dayton SharePoint user group on using PowerShell to search and audit SharePoint. In this article, and one or two follow ups I’ll expand on that presentation.

So what’s here?

  • An overview of permissions needed to use PowerShell with SharePoint
  • How to use PowerShell with both SharePoint 2007 and 2010
  • The SPFarm object and a few of its properties
  • The SPService object



  • Find info that cannot be found with the out of the box tools
  • Find info that can not be conveniently found with the out of the box tools
  • Collect data for reports
  • Do nothing that is not undoable
  • Do nothing (or as little as possible) that impacts server performance

In this article we are not:

  • Doing administration
  • Doing installs
  • Doing Backups and Restores

PowerShell can do all of this, but those are for other sessions (and other presentors)



Not everyone who can start PowerShell can access SharePoint data using the SharePoint 2010 PowerShell cmdlets! PowerShell does not avoid SharePoint security, and actually adds an additional requirement or two.

  • You can use the “setup account” that was used to install SharePoint as it has the rights listed below (but this is not the best practice!)
  • You must be a member of the SQL SharePoint_Shell_Access role
  • You must be a member of the WSS_Admin_WPG local security group on each server
  • You can configure both of the above using one SharePoint PowerShell cmdlet:
        Add-SPShellAdmin –Username “domain/user” -Database “databasename

For SharePoint 2007, just think about which permissions would be needed to otherwise get to the desired content. You will need the same permissions when using PowerShell. Access to the Farm object will require admin permissions. Access to a Site Collection will require Site Collection Administrator, Site Owner, or for some data, Visitor permissions.


Memory Management (and possible “manglement!)


First the bad news: You have to manage memory. If you don’t, you can crash your SharePoint server by creating objects that are not immediately disposed of. As you drill down into the SharePoint hierarchy you can potentially create thousands of objects.

Different PowerShells, different memory defaults

If running a PowerShell other than the SharePoint 2010 Management Shell, such as the Integrated Scripting Environment (ISE), each command line (each press of the Enter key) runs on a different thread.

         $site = Get-SPSite http://intranet/sites.training

When the above is run in the ISE the $site object is not been immediately disposed as the request was made on a different thread.

Starting the SharePoint 2010 Management Shell runs this:
      $ver = $host | select version
      if ($ver.Version.Major -gt 1)
          { $Host.Runspace.ThreadOptions = "ReuseThread“ }
      Add-PsSnapin Microsoft.SharePoint.PowerShell
      Set-location $home"

“ReuseThread” causes all commands to now run in the same thread.


Memory tools


If you would like to track the memory being used by PowerShell you can check the running processes on your PC. PowerShell includes a cmdlet, Get-Process (gps for short) just for this, and it also keeps track of its own process ID in a variable named $PID. You can check the current memory being used with this command:

      gps -id $pid


Here’s a sample test:

      `create 100 subsites
      gps -id $pid; 
      for ($i=0; $i -lt 100; $i++) 
        $s = new-spweb http://intranet/sites/training/PS$i ; 
      gps -id $pid;


Here’s a sample test with dispose:

      `create 100 subsites 
      gps -id $pid; 
      for ($i=0; $i -lt 100; $i++) 
        $s = new-spweb http://intranet/sites/training/PS$i ;
      gps -id $pid;


Sometimes you need to force the garbage collection process if you need to immediately free up RAM. Do not do this excessively as it will be very slow. That said, the following is not best practice!

      `create 100 subsites 
      gps -id $pid; 
      for ($i=0; $i -lt 100; $i++) 
        $s = new-spweb http://intranet/sites/training/PS$i ;

      gps -id $pid;


Memory - helper functions

If you plan to do much memory usage testing then you may want to create a function or two to save some typing:

PowerShell Process memory:
      function psm
        (Get-Process $pid).PrivateMemorySize/1024/1024


Run the garbage collector:
      function cg


Is PowerShell just for SharePoint 2010? Or will it work in SharePoint 2007?


SharePoint 2010 includes nearly 600 PowerShell cmdlets. With these you can quickly get a hold of common SharePoint objects with a simple command. Here is how you can get a Site Collection object in one line:

      $site = Get-SPSite http://intranet/sites/training


There are no SharePoint PowerShell cmdlets for SharePoint 2007! But that is not too much of a problem as PowerShell can directly access DLLs, including the SharePoint DLLs.

First you will need to load the SharePoint DLL:


Then you can use the SharePoint API to create SharePoint objects such as a Site Collection object. The following creates the exact same object as the cmdlet above, and it will work in both 2007 and 2010.

$site = New-Object Microsoft.SharePoint.SPSite(“http://intranet/sites/training”)


The SharePoint Hierarchy


When looking for a single object in SharePoint (site, list, item, etc) you will often be able to use a single PowerShell cmdlet or API object. But when you want to find all Word documents in all libraries in all site collections in all sites in all web applications, then you will need to drill down through the SharePoint object Hierarchy. Here are the primary objects we will be working with in these articles:


SPFarm (the config database)

  SPWebService (Excel services, etc)

    SPWebApplication (IIS app plus a SQL DB)

      SPSite (Site collection)

        SPWeb (a single site)

          SPList & SPLibrary (a single list)

            SPListItem (a single list item)

              SPField (a single property)


Each of these objects have their own collections of properties that we can browse through using PowerShell.


The Farm


As a start, let’s take a look at the top most object in the SharePoint hierarchy, the SPFarm object. Note that the Farm is the Config database, not the collection of physical servers. In fact, the collection of server objects is just one of many properties of the Farm.

For SP 2010:
      Get-SPFarm returns the farm object    (actually the SharePoint Config database)

Or for SP 2007:
      $farm = [Microsoft.SharePoint.Administration.SPFarm]::Local

Get the farm:
      $farm = Get-SPFarm

Get the version:
      $farm.BuildVersion              or (Get-SPFarm).BuildVersion

Get the list of servers:
      $farm.Servers                       or Get-SPServer

Get a list of installed Features
      $farm.FeatureDefinitions | sort scope                  or Get-SPFeature

Get a list of installed Solutions
      $farm.Solutions                     or Get-SPSolution


Get a list of installed products (or SKUs)


This returns a list of GUIDs of the installed products.

Value Product
84902853-59F6-4B20-BC7C-DE4F419FEFAD Project Server 2010 Trial
ED21638F-97FF-4A65-AD9B-6889B93065E2 Project Server 2010
BC4C1C97-9013-4033-A0DD-9DC9E6D6C887 Search Server 2010 Trial
08460AA2-A176-442C-BDCA-26928704D80B Search Server 2010
BEED1F75-C398-4447-AEF1-E66E1F0DF91E SharePoint Foundation 2010
1328E89E-7EC8-4F7E-809E-7E945796E511 Search Server Express 2010
B2C0B444-3914-4ACB-A0B8-7CF50A8F7AA0 SharePoint Server 2010 Standard Trial
3FDFBCC8-B3E4-4482-91FA-122C6432805C SharePoint Server 2010 Standard
88BED06D-8C6B-4E62-AB01-546D6005FE97 SharePoint Server 2010 Enterprise Trial
D5595F62-449B-4061-B0B2-0CBAD410BB51 SharePoint Server 2010 Enterprise
926E4E17-087B-47D1-8BD7-91A394BC6196 Office Web Applications 2010

More info here:




The Services


While looking at the SPFarm object let’s take a look at the SPServices object. SharePoint 2010 seems to have an endless list of services. Which are installed on your farm?

SharePoint 2010 does not have a “Get-SPServices” cmdlet, so we will have to get the services object SPFarm object.

      $farm = Get-SPFarm    (or   $farm = [Microsoft.SharePoint.Administration.SPFarm]::Local )

      $farm.Services | select TypeName

Note that the web applications (SPWebApplication) are a special kind “service” (Microsoft SharePoint Foundation Web Application) and can be retrieved using Get-SPWebApplication or by looping through all of the SPService objects and checking the type of service.




In the next installment we will start looking at, and start exploring, the “everyday” objects such as Site Collections, Webs, Items and Users.

  • Get a list of all site collections
  • Get a list of all sites of type “abc” (blog, Team Site, etc)
  • Get a list of all groups and their users
  • Get a list of all users
  • Get a list of all permissions
  • Get a list of all lists that use Content Type “abc”
  • and a few more...


No comments:

Note to spammers!

Spammers, don't waste your time... all posts are moderated. If your comment includes unrelated links, is advertising, or just pure spam, it will never be seen.