A Quick Guide to Getting, Setting and Copying User Profile Properties using PowerShell

Whether you're a SharePoint Administrator or SharePoint Developer, being able to quickly read, update or copy User Profile properties is a handy skill to have. Using PowerShell to get and set User Profile properties is both quick and easy. This post outlines how to do it!

Getting the User Profile

The basic PowerShell code for getting a user profile, using a users UPN (User Principal Name):

[void][reflection.assembly]::Loadwithpartialname("Microsoft.Office.Server") | out-null;            
$site=new-object Microsoft.SharePoint.SPSite("https://c05470sp10:7443");            
$serviceContext = Get-SPServiceContext $site;            
$site.Dispose();            
$upm = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($serviceContext);            
$userProfile = $upm.GetUserProfile("myarlett@company.com");

The basic PowerShell code for getting a user profile, using the user's login name:

[void][reflection.assembly]::Loadwithpartialname("Microsoft.Office.Server") | out-null;            
$site=new-object Microsoft.SharePoint.SPSite("https://c05470sp10:7443");            
$serviceContext = Get-SPServiceContext $site;            
$site.Dispose();            
$upm = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($serviceContext);
$userProfile = $upm.GetUserProfile("company\myarlett");

Listing all the Profile Fields (Properties) and their Types

List the user profile properties (including the field type). This is handy, because we'll need to know what the field type is before trying to set it's value:

$userProfile.Properties | sort DisplayName | FT DisplayName,Name,@{Label="Type";Expression={$_.CoreProperty.Type}}

Getting the Value of a Property

Get the users About Me property (HTML):

$userProfile["AboutMe"].Value

Setting the Values of Properties

Update the users Location (String field):

$userProfile["SPS-Location"].Value = "London";            
$userProfile.Commit();

Update the users Manager (Person field):

$userProfile["Manager"].Value = (Get-SPWeb https://c05470sp10:7443).EnsureUser("company\fred");
$userProfile.Commit();

Note that in the above example, we have retrieved an SPUser object (for the manager) from the Central Admin site, using the EnsureUser method.

Copying User Profile Properties between Profiles

Copy fields from one user profile to another:

$userProfile2 = $upm.GetUserProfile("company\matthewette");            
$userProfile2["AboutMe"].Value = $userProfile["AboutMe"];            
$userProfile2.Commit();

See Also

• UserProfileManager class
• SPWeb.EnsureUser method

Using the SharePoint Secure Store Application for Database Connection Settings

Introduction

A few weeks back I blogged about accessing the SharePoint Secure Store using C# to retrieve user credentials. Credentials in the Secure Store are stored securely and can be managed via the Central Administration site. That post is here: Retrieving Credentials from the SharePoint Secure Store using C#

In this post, I want to expand on that concept, and demonstrate how the SharePoint Secure Store can be used for storing database connection settings (username, password, database, server).

Often SharePoint solutions are required to access external databases. The SharePoint Secure Store solves the problem of securely storing and managing credentials, but what about managing the database server and database name?

The example below extends the class designed in the first post, to demonstrate how credential and database information can be retrieved by code to connect to and authenticate with external database systems, such as Microsoft SQL Server.

This article assumes you have the SharePoint Secure Store Application configured. To complete the example, you will need access to the Central Administration site, and will need permissions to create new Target Applications and deploy solutions.

The source code for this project can be downloaded from the Microsoft TechNet Gallery, here: Retrieving Credentials from the SharePoint Secure Store using C#

Creating a Target Application in the Secure Store

Before looking at the code, we are going to step through creating a Target Application in the Secure Store.

The new Target Application we create will store a generic user credential (username and password), a database name, and a database server name. We will then use this information in the code example to connect and authenticate with a SQL Server used to store HR information.. The name of the target application will be a description of the target SQL server and database we are connecting with, in this example, HRPro.

1. Browse to the Central Administration site
2. Click on Application Management
3. Click on Manage Service Applications
4. Click the Secure Store Application
5. Create a new Target Application
5.1 On the ribbon, in the Manage Target Applications, click New

5.2 In the Create New Secure Store Target Application page, enter the following information;

Target Application ID: HrPro
Display Name: HR Pro
Contact E-mail: (enter your email address)
Target Application Type: Group

5.3 Click Next. On the next page, Specify the credential fields for the Secure Store Target Application, configure the fields that are used to store the credential and database information.

Special attention needs to be paid here, as a standard for all applications needs to be set. In this example, I'm specifying that the SqlServer must use the field type Key, and the database must use the field type Generic. The code that is used to access this information doesn't have access to field names, only the FieldType enumeration. The standard is: [FieldType]Key = the SQL Server, and [FieldType]Generic = the Database name. This will become clearer in the code example.

Configure the following fields:

Field Name	Field Type	Masked
UserName	Username	No
Password	Password	Yes
SqlServer	Key	No
Database	Generic	No

5.4 Click Next. On the next page, Specify the membership Settings, you need to enter administrators and members. Administrators are people who will be able to manage this target application, while members are people who will have permissions to retrieve (read) the user credentials.

Since the user credentials and database information will be accessed via code under the context of a standard site user, we are adding Domain Users as Members. The SharePoint farm account (and any other administrators) should be added as administrators.

5.5 Click OK to save the new Target Application.
6. Set the credentials of the new Target Application
6.1 Select the new Target Application, and click Set (in the Credentials section of the ribbon)

6.2 Enter the username, password, SQL Server and database information. The user and password need to be a SQL Server user (unless you plan on using impersonation to open the connection to SQL - which is a blog for another day).

6.3 Click OK to save the credential information

You have finished creating the new Target Application. The next step is to write some code that will access and use the credentials and database settings.

Building a Class to Access the Credentials

This part of the example requires creating a some classes and methods for accessing the secure store, retrieving a credential object, and returning it to the caller.

1. Create a new empty SharePoint Project (deploy as a farm solution)
2. Add the following references to the project:

Microsoft.Office.SecureStore.dll (see the reference below about finding the Microsoft.Office.SecureStore.dll in the GAC (Global Assembly Cache))
Microsoft.BusinessData.dll

3. Add a new class to the project, called SecureStoreProxy
4. Make the class as public and static

namespace SecureStoreCredentialsExample
{
 public static class SecureStoreProxy
 {
 
 }
}

5. Add the following using statements

using System;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security;
using Microsoft.BusinessData.Infrastructure.SecureStore;
using Microsoft.Office.SecureStoreService.Server;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint;

4. Add the following code to the SecureStoreProxy class
4.1. Add CredentialType enum. This will be used by the GetDatabaseConnectionSettingsFromSecureStoreService method.

public enum CredentialType
{
 Domain,
 Generic
}

4.2. Add a new class, BaseCredentials, to store credential information. The base class implements IDisposable to ensure the classes SecureString properties are correctly disposed of. It also contains a method for returning a SecureString as a String.

Add an additional two classes, that inherit BaseCredentials, for storing Windows credentials, and Database credentials.

The class that stores the Windows credentials contains an extra property to hold the domain name, and an updated constructor (this class isn't used in this example, but is included for completeness).

The class that stores the database credentials contains extra properties to hold the Sql Server and Database values, a method to create a default connection string, and an updated constructor.

public class BaseCredentials : IDisposable
{
 private readonly SecureString _userName;
 public String UserName
 {
  get { return ConvertToUnsecuredString(_userName); }
 }
 private readonly SecureString _password;
 public String Password
 {
  get { return ConvertToUnsecuredString(_password); }
 }
 public BaseCredentials(SecureString username, SecureString password)
 {
  _userName = username.Copy();
  _password = password.Copy();
 }
 protected string ConvertToUnsecuredString(SecureString securedString)
 {
  if (securedString == null) return String.Empty;
  IntPtr uString = IntPtr.Zero;
  try
  {
   uString = Marshal.SecureStringToGlobalAllocUnicode(securedString);
   return Marshal.PtrToStringUni(uString);
  }
  finally
  {
   Marshal.ZeroFreeGlobalAllocUnicode(uString);
  }
 }

 private Boolean _isDisposed;
 public void Dispose()
 {
  if (_isDisposed) return;
  _userName.Dispose();
  _password.Dispose();
  _isDisposed = true;
 }
}

public class UserCredentials : BaseCredentials
{
 public String DomainName;
 public UserCredentials(SecureString username, SecureString password, SecureString domainName) : base(username, password)
 {
  DomainName = base.ConvertToUnsecuredString(domainName);
 }
 public UserCredentials(SecureString username, SecureString password): base(username, password)
 {
 }
}

public class DatabaseCredentials : BaseCredentials
{
 public readonly String Database;
 public readonly String SqlServer;
 public readonly Boolean UseWindowsAuthentication;
 public String DefaultSqlConnectionString
 {
  get
  {
   var connectionString = new SqlConnectionStringBuilder() { DataSource = SqlServer, InitialCatalog = Database};
   if (UseWindowsAuthentication)
   {
    connectionString.IntegratedSecurity = true;
   }
   else
   {
    connectionString.UserID = UserName;
    connectionString.Password = Password;
   }
   return connectionString.ToString();
  }
 }
 
 public DatabaseCredentials(SecureString username, SecureString password, SecureString sqlServer, SecureString database, Boolean useWindowsAuthentication) : base(username, password)
 {
  Database = ConvertToUnsecuredString(database);
  SqlServer = ConvertToUnsecuredString(sqlServer);
  UseWindowsAuthentication = useWindowsAuthentication;
 }
}

4.3. Add a new public static method used to retrieve database credential information from the Secure Store. This method takes an Application ID (a target application id), and the CredentialType enumeration as inputs, and returns a DatabaseCredentials object.

public static DatabaseCredentials GetDatabaseConnectionSettingsFromSecureStoreService(string applicationId, CredentialType credentialType)
{
 ISecureStoreProvider provider = SecureStoreProviderFactory.Create();
 if (provider == null)
 {
  throw new InvalidOperationException("Unable to get an ISecureStoreProvider");
 }
 using (SecureStoreCredentialCollection credentials = provider.GetCredentials(applicationId))
 {
  var un = from c in credentials
     where c.CredentialType == (credentialType == CredentialType.Domain ? SecureStoreCredentialType.WindowsUserName : SecureStoreCredentialType.UserName)
     select c.Credential;

  var pd = from c in credentials
     where c.CredentialType == (credentialType == CredentialType.Domain ? SecureStoreCredentialType.WindowsPassword : SecureStoreCredentialType.Password)
     select c.Credential;

  var s = from c in credentials
     where c.CredentialType == SecureStoreCredentialType.Key
     select c.Credential;

  var db = from c in credentials
    where c.CredentialType == SecureStoreCredentialType.Generic
    select c.Credential;

  SecureString userName = un.First(d => d.Length > 0);
  SecureString password = pd.First(d => d.Length > 0);
  SecureString sqlServer = s.First(d => d.Length > 0);
  SecureString database = db.First(d => d.Length > 0);
  var databaseConnectionSettings = new DatabaseCredentials(userName, password, sqlServer, database, credentialType == CredentialType.Domain);
  return databaseConnectionSettings;
 }
}

The method connects to the Secure Store, and retrieves the credentials of the Target Application. If the user context that the code is running isn't in the membership of the Target Application, an exception will be thrown.

Once the method has connected to the Secure Store and retrieved the Target Application's credentials (returned as a SecureStoreCredentialCollection), it parses the collection, extracting the username, password, SQL Server and database into a new DatabaseCredentials object.

From this method, you can see the importance of deciding on a standard for which FieldType contains the SQL server, and which FieldType contains the database. There is no method to get the name of a Credential from the provide. The only values that get returned are the CredentialType (Generic, Pin, Key, User, Password, Windows User, Windows Password), and the StringString value itself.

5. Build the solution.

Building a Webpart that uses the Credentials to Connect to a SQL Server Database

The final step in our example is to build a webpart the retrieves the information about the current user from the HR SQL Server database, augments it with data from the SharePoint User Profile Service, and displays it to the user.

1. Add a new standard webpart to the project called GetInformationFromSql
2. Add a the following using statements to the webparts code file

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.Office.Server.UserProfiles;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.WebControls;

3. Copy the following code into the webpart file.

The webpart contains a label and some code that runs when OnPreRender event. During the OnPreRender event, the webpart will retrieve information about the currently logged on user from the HR SQL server using the credentials retrieved from the Secure Store. It will augment this information with additional information from the User Profile Application.

Notice that the call to GetDatabaseSettingsFromSecureStore is within a Using block to ensure the object is disposed of.

namespace SecureStoreCredentialsExample.GetInformationFromSql
{
 [ToolboxItemAttribute(false)]
 public class GetInformationFromSql : WebPart
 {
  private Label _results;
  private const String SqlCommandText = "select t1.ID,t1.Full_Name, t1.Initials, t1.Job_Title, t1.Ince_Reference, t1.Location, t1.Start_Date, t1.department from HRPro.dbo.personnel_Records t1 where t1.leaving_date is null and t1.phi = 0 and ince_reference = '{0}'";

  private String _applicationId = "HrPro";
  [WebBrowsable(true), WebDisplayName("Application Id"), WebDescription("Secure Store Target Application ID"),
   Personalizable(PersonalizationScope.Shared), Category("Webpart Settings")]
  public String ApplicationId
  {
   get { return _applicationId; }
   set { _applicationId = value; }
  }


  protected override void CreateChildControls()
  {
   _results = new Label();
   Controls.Add(_results);
  }

  protected override void OnLoad(EventArgs e)
  {
   base.OnLoad(e);
   if (!Page.ClientScript.IsClientScriptIncludeRegistered(this.GetType(), "ssce"))
   {
    Page.ClientScript.RegisterClientScriptInclude(this.GetType(), "ssce", "/_layouts/ssce.js?v1");
   }
  }

  protected override void OnPreRender(EventArgs e)
  {
   base.OnPreRender(e);
   _results.Text = String.Empty;
   try
   {
    //Get Database settings from the Secure Store
    using (var databaseSettings = SecureStoreProxy.GetDatabaseConnectionSettingsFromSecureStoreService(ApplicationId,SecureStoreProxy.CredentialType.Generic))
    {
     using (var connection = new SqlConnection(databaseSettings.DefaultSqlConnectionString))
     {
      var userId = SPContext.Current.Web.CurrentUser.LoginName.Contains(@"\")
       ? SPContext.Current.Web.CurrentUser.LoginName.Substring(SPContext.Current.Web.CurrentUser.LoginName.IndexOf(@"\", StringComparison.InvariantCultureIgnoreCase) + 1)
       : SPContext.Current.Web.CurrentUser.LoginName;
      var sqlCommand = new SqlCommand(String.Format(SqlCommandText, userId), connection) { CommandType = CommandType.Text };
      connection.Open();
      var reader = sqlCommand.ExecuteReader();
      if (reader.HasRows)
      {
       reader.Read();
       String fullname = reader["Full_Name"] == DBNull.Value ? String.Empty : (String)reader["Full_Name"];
       String initials = reader["Initials"] == DBNull.Value ? String.Empty : String.Format("({0})", reader["Initials"]);
       String inceRef = reader["Ince_Reference"] == DBNull.Value ? String.Empty : (String)reader["Ince_Reference"];
       String location = reader["Location"] == DBNull.Value ? String.Empty : String.Format("Office: {0}", reader["Location"]);
       String jobTitle = reader["Job_Title"] == DBNull.Value ? String.Empty : (String)reader["Job_Title"];
       String department = reader["Department"] == DBNull.Value ? String.Empty : String.Format("({0})", reader["Department"]);
       DateTime startdate = reader["Start_Date"] == null ? DateTime.MinValue : (DateTime)reader["Start_Date"];
       Uri imageUrl;
       String aboutMe;
       GetProfileInformation(inceRef, out imageUrl, out aboutMe);
       var sb = new StringBuilder();
       aboutMe = aboutMe == String.Empty ? String.Empty : String.Format("<div><span onclick=\"javascript:displayElementInPopup('{0}', 'About Me')\">About Me</span><div><div id=\"{0}\">{1}</div></div></div>", String.Format("incePInstance{0}", inceRef.Trim()), aboutMe);
       String photoWrapper = String.Format("<div><img src=\"{0}\" alt=\"{1}\" style=\"max-width:48px;\"/></div>", imageUrl, fullname);
       String infoWrapper = String.Format("<div><div>{0} {1}</div><div>{2} {3}</div><div>{4}</div><div>{5}</div></div>", fullname, initials, jobTitle, department, location, aboutMe);
       sb.Append(String.Format("<div><table><tr><td>{0}</td><td>{1}</td></tr></table></div>", photoWrapper, infoWrapper));
       _results.Text = sb.ToString();
      }
      connection.Close();
     }
    }
    
   }
   catch (Exception exception)
   {
    _results.Text = String.Format("Something went wrong accessing information from the HR system. Error: {0}",exception.Message);
   }
  }

  private static void GetProfileInformation(string userName, out Uri imageUrl, out string aboutMe)
  {
   try
   {
    SPServiceContext serviceContext = SPServiceContext.GetContext(GetCentralAdministrationSite());
    var upm = new UserProfileManager(serviceContext);
    var domain = Environment.UserDomainName;
    String samAccount = String.Format("{0}\\{1}", domain, userName);
    if (!upm.UserExists(samAccount))
    {
     aboutMe = String.Empty;
     imageUrl = new Uri("/_layouts/images/person.gif", UriKind.Relative);
     return;
    }
    UserProfile up = upm.GetUserProfile(samAccount);
    UserProfileValueCollection pictureUrl = up["PictureURL"];
    UserProfileValueCollection aboutMeField = up["AboutMe"];
    aboutMe = aboutMeField.Value == null ? String.Empty : aboutMeField.Value.ToString();
    imageUrl = new Uri(pictureUrl.Value != null ? pictureUrl.Value.ToString() : "/_layouts/images/person.gif", UriKind.RelativeOrAbsolute);
   }
   catch (Exception)
   {
    aboutMe = String.Empty;
    imageUrl = new Uri("/_layouts/images/ince/anon.png", UriKind.Relative);
   }
  }

  private static SPSite GetCentralAdministrationSite()
  {
   var webApplication = SPAdministrationWebApplication.Local;
   if (webApplication == null)
   {
    throw new NullReferenceException("Unable to get the Central Administration Site.");
   }
   var caWebUrl = webApplication.GetResponseUri(SPUrlZone.Default);
   if (caWebUrl == null)
   {
    throw new NullReferenceException("Unable to get the Central Administration Site. Could get the URL of the Default Zone.");
   }
   return webApplication.Sites[caWebUrl.AbsoluteUri];
  }
 }
 
}

4. Build and deploy the project
5. Add the webpart to a page
6. The webpart connects to the HR System (using the credentials, SQL Server and database information provided from the Secure Store) and displays information.

See Also

Retrieving Credentials from the SharePoint Secure Store using C#

References

How to use the DirectoryServices Namespace in ASP (Double-Hop Authentication Issue)
Microsoft.Office.SecureStoreService.dll
Getting credients from the Secure Store Provider
Visual Studio Project Sample: Retrieving Credentials from the SharePoint Secure Store using C#

Customising the SharePoint Advanced Search Page

The SharePoint Enterprise Search site contains a number of pages when it's created. One of those pages is an Advanced Search page that can be used to create advanced search queries.

I answered a question in a forum recently about a specific problem customising this page. The forum user wanted to set the Result Type to "All Results", hide the Result Type label and drop-down box, but keep the property selector in the "Add property restrictions..." section. You can use the Advanced Search Box webparts toolpane properties to remove the Results Type selector, however, it also removes the "Add property restrictions..." section.

There are two solutions to achieve this; use JavaScript, or use CSS. The answer I posed was using CSS with specific selectors to hide the elements used to display the "Result Type" label and drop-down box.

Solution

1. Edit the Advanced Search page
2. Edit the Advanced Search Box webpart
3. In the webparts toolbox, expand the Scopes section
4. Un-check Show the languages picker
5. In the webparts toolbox, expand the Properties section

6. Copy the text in the Properties textbox to a notepad editor
7. Search for the string "<ResultTypes>"
8. Remove all the <ResultType> elements, except for the "All Results" element.

9. Copy the XML back into the Properties textbox in the webparts toolbox pane
10. Apply the changes to the Advanced Search Box webpart.
11. Add an HTML Form webpart to the page.
12. Add the following markup to the HTML form webpart

<style type="text/css">
td.ms-advsrchText-v2 > select[title='Result Type']{display:none}
td.ms-advsrchText-v1 > label[for*='_ASB_SS_rtlb']{display:none}
</style>

13. Save the HTML Form webparts properties
14. Save the page page.

The Advanced Search page now includes the property restrictions for the All Results result type, without displaying the Result Type picker.

CSS Explanation

To remove the Result Type label and drop-down box, we need two CSS rules to hide those elements.

The first CSS rule, "td.ms-advsrchText-v2 > select[title='Result Type']{display:none}", applies display:none to all select elements that have a title equal to "Result Type", appearing directly after a <td> element that contains the ms-advsrchText-v2 class (<td class="ms-advsrchText-v2>)

The second rule is slightly trickier. It looks as though there isn't something specific enough to hide the "Result Type" label with a CSS rule (without hiding other rows). However, the "for" attribute of the label contains a randomly generated unique string. This string, is only partially random. The last part of the string is constant, and we can use an attribute selector to select all labels that have a "for" element that ends in "_ABS_SS_rtlb". This does the job perfectly, selecting only the "Results Type" label.

This can be seen using the Internet Explorer Developer Tools (F12 in your Internet Explorer browser).

References

Forum content (advanced-search-web-part)

Retrieving Credentials from the SharePoint Secure Store using C#

Introduction

There are times when you need to connect SharePoint to an system external. If the external system requires authentication, unless you have Kerberos authentication (and delegation) configured in your environment, you will suffer from the "Double Hop" authentication issue (explained here). Because of the double hop issue, you will need to reference a username and password within your to connect to the external system (unless anonymous access is allowed). However, hard coding a username and password is not only a bad practice, it's also insecure, unmanageable and lacks portability.

This article focuses on using the SharePoint Secure Store Application to store user credentials for use in code based solutions. Credential information can be retrieved by code to authenticate against other systems, such as Active Directory. Credentials in the Secure Store are stored securely and can be managed via the Central Administration site.

This article assumes you have the SharePoint Secure Store Application configured. To complete the example, you will need access to the Central Administration site, and will need permissions to create new Target Applications and deploy solutions.

The source code for this project can be downloaded from the Microsoft TechNet Gallery, here: Retrieving Credentials from the SharePoint Secure Store using C#

Creating a Target Application in the Secure Store

Before looking at the code, we are going to step through creating a Target Application in the Secure Store.

The new Target Application we create will store a Windows Domain user credential, which we will then use in the code example to authenticate against Active Directory.

1. Browse to the Central Administration site
2. Click on Application Management
3. Click on Manage Service Applications
4. Click the Secure Store Application
5. Create a new Target Application
5.1 On the ribbon, in the Manage Target Applications, click New

5.2 In the Create New Secure Store Target Application page, enter the following information;

Target Application ID: ActiveDirectoryConnection
Display Name: Active Directory Connection
Contact E-mail: (enter your email address)
Target Application Type: Group

5.3 Click Next. On the next page, Specify the credential fields for the Secure Store Target Application, configure the fields that are used to store the credential information.

Configure the following fields:

Field Name	Field Type	Masked
UserName	Windows Username	No
Password	Windows Password	Yes
Domain	Key	No

5.4 Click Next. On the next page, Specify the membership Settings, you need to enter administrators and members. Administrators are people who will be able to manage this target application, while members are people who will have permissions to retrieve (read) the user credentials.

Since the user credentials will be accessed via code under the context of a standard site user, we are adding Domain Users as Members. The SharePoint farm account (and any other administrators) should be added as administrators.

5.5 Click OK to save the new Target Application.
6. Set the credentials of the new Target Application
6.1 Select the new Target Application, and click Set (in the Credentials section of the ribbon)

6.2 Enter the username, password and domain of the Windows Domain user account that you want to use. This account will be used to connect to, and query, Active Directory.

6.3 Click OK to save the credential information

You have finished creating the new Target Application. The next step is to write some code that will access and use the credentials.

Building a Class to Access the Credentials

This part of the example requires creating a some classes and methods for accessing the secure store, retrieving a credential object, and returning it to the caller.

1. Create a new empty SharePoint Project (deploy as a farm solution)
2. Add the following references to the project:

Microsoft.Office.SecureStore.dll (see the reference below about finding the Microsoft.Office.SecureStore.dll in the GAC (Global Assembly Cache))
Microsoft.BusinessData.dll

3. Add a new class to the project, called SecureStoreProxy
4. Make the class as public and static

namespace SecureStoreCredentialsExample
{
    public static class SecureStoreProxy
    {
    
    }
}

5. Add the following using statements

using System;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security;
using Microsoft.BusinessData.Infrastructure.SecureStore;
using Microsoft.Office.SecureStoreService.Server;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint;

4. Add the following code to the SecureStoreProxy class
4.1. Add CredentialType enum. This will be used by the GetCredentialsFromSecureStoreService method.

public enum CredentialType
{
    Domain,
    Generic
}

4.2. Add a new class to store credential information. This class implements IDisposable to ensure the classes SecureString properties are correctly disposed of. It also contains a method for returning a SecureString as a String.

public class UserCredentials : IDisposable
{

    private readonly SecureString _userName;
    public String UserName
    {
        get { return ConvertToUnsecuredString(_userName); }
    }

    public String DomainName;

    private readonly SecureString _password;
    public String Password
    {
        get { return ConvertToUnsecuredString(_password); }
    }
    public UserCredentials(SecureString username, SecureString password)
    {
        _userName = username.Copy();
        _password = password.Copy();
    }

    public UserCredentials(SecureString username, SecureString password, SecureString domain)
    {
        _userName = username.Copy();
        _password = password.Copy();
        DomainName = ConvertToUnsecuredString(domain);
    }

    private static string ConvertToUnsecuredString(SecureString securedString)
    {
        if (securedString == null) return String.Empty;
        IntPtr uString = IntPtr.Zero;
        try
        {
            uString = Marshal.SecureStringToGlobalAllocUnicode(securedString);
            return Marshal.PtrToStringUni(uString);
        }
        finally
        {
            Marshal.ZeroFreeGlobalAllocUnicode(uString);
        }
    }

    private Boolean _isDisposed;
    public void Dispose()
    {
        if (_isDisposed) return;
        _userName.Dispose();
        _password.Dispose();
        _isDisposed = true;
    }
}

4.3. Add a new public static method used to retrieve credential information from the Secure Store. This method takes an Application ID (a target application id), and the CredentialType enum as inputs, and returns a UserCredentials object.

public static UserCredentials GetCredentialsFromSecureStoreService(string applicationId, CredentialType credentialType)
{
    ISecureStoreProvider provider = SecureStoreProviderFactory.Create();
    if (provider == null)
    {
        throw new InvalidOperationException("Unable to get an ISecureStoreProvider");
    }

    using (SecureStoreCredentialCollection credentials = provider.GetCredentials(applicationId))
    {
        var un = from c in credentials
                    where c.CredentialType == (credentialType == CredentialType.Domain ? SecureStoreCredentialType.WindowsUserName : SecureStoreCredentialType.UserName)
                    select c.Credential;

        var pd = from c in credentials
                    where c.CredentialType == (credentialType == CredentialType.Domain ? SecureStoreCredentialType.WindowsPassword : SecureStoreCredentialType.Password)
                    select c.Credential;

        var dm = from c in credentials
                    where c.CredentialType == SecureStoreCredentialType.Key
                    select c.Credential;


        SecureString userName = un.First(d => d.Length > 0);
        SecureString password = pd.First(d => d.Length > 0);
        SecureString domain = dm.First(d => d.Length > 0);
        var userCredientals = new UserCredentials(userName, password, domain);
        return userCredientals;
    }
}

The method connects to the Secure Store, and retrieves the credentials of the Target Application. If the user context that the code is running isn't in the membership of the Target Application, an exception will be thrown.

Once the method has connected to the Secure Store and retrieved the Target Application's credentials (returned as a SecureStoreCredentialCollection), it parses the collection, extracting the username, password and domainname into a new UserCredential object.

5. Build the solution.

Building a Webpart that uses the Credentials to Connect to Active Directory

The final step in our example is to build a webpart the retrieves the membership of an Active Directory group.

1. Add a new standard webpart to the project called GetGroupMembership
2. Add a new reference to the project

System.DirectoryServices.AccountManagement

3. Add a the following using statements to the webparts code file

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.SharePoint;
using System.DirectoryServices.AccountManagement;
using System.Text;
using IdentityType = System.DirectoryServices.AccountManagement.IdentityType;

4. Copy the following code into the webpart file.

The webpart contains a textbox, button and label. When an enduser enters a group name and clicks submit, the webpart will connect to Active Directory, using the credentials retrieved from the Secure Store. It will search for the group, using methods in the System.DirectoryServices.AccountManagement namespace. Finally it will return a list of the groups members, and message indicating if the current user is a member of the group.

Notice how the GetPrinipalContext method retrieves the UserCredients (used to authenticated against Active Directory) from the SharePoint Secure Store, via the Proxy class we created early in this article.

For more information on using the System.DirectoryServices.AccountManagement namespace in a SharePoint project, see this article: SharePoint: Querying Active Directory from a Farm Based Solution using C#.Net

namespace SecureStoreCredentialsExample.GetGroupMembership
{
    [ToolboxItemAttribute(false)]
    public class GetGroupMembership : WebPart
    {
        private Label _results;
        private TextBox _groupName;
        private Button _submit;

        protected override void OnInit(EventArgs e)
        {
            base.OnInit(e);
            _results = new Label();
            _groupName = new TextBox();
            _submit = new Button {Text = "Submit"};
            _submit.Click += SubmitOnClick;
        }

        protected override void CreateChildControls()
        {
            Controls.Add(_groupName);
            Controls.Add(_submit);
            Controls.Add(new LiteralControl("<br/>"));
            Controls.Add(_results);
        }

        private void SubmitOnClick(object sender, EventArgs eventArgs)
        {
            try
            {
                StringBuilder output = new StringBuilder();
                GroupPrincipal group = GetGroup(_groupName.Text);
                if (group == null)
                {
                    _results.Text = "Group not found.";
                    return;
                }
                output.Append(String.Format("Current user, {0}, {1} a member of {2}", SPContext.Current.Web.CurrentUser.Name, IsUserMemberOfGroup(group, SPContext.Current.Web.CurrentUser.Sid, IdentityType.Sid) ? "is" : "is not", group.DisplayName));
                output.Append("<br/>");
                var groupMembers = GetAllUsersInGroup(group, true);
                String members = String.Empty;
                foreach (UserPrincipal userPrincipal in groupMembers)
                {
                    members = String.Format("{0}{1}", String.IsNullOrEmpty(members) ? "" : String.Format("{0}, ", members), userPrincipal.DisplayName);
                }
                output.Append(String.Format("The current list of users in the {0} group are: {1}", group.DisplayName, members));
                _results.Text = output.ToString();
            }
            catch (Exception e)
            {
                _results.Text = e.Message;
            }
        }

        private UserPrincipal GetUser(String identity, IdentityType identityType)
        {
            PrincipalContext principalContext = GetPrincipalContext;
            return UserPrincipal.FindByIdentity(principalContext, identityType, identity);
        }

        private IEnumerable<UserPrincipal> GetAllUsersInGroup(GroupPrincipal groupPrincipal, Boolean recurse)
        {
            PrincipalSearchResult<Principal> members = groupPrincipal.GetMembers(recurse);
            return members.OfType<UserPrincipal>().ToList();
        }

        private GroupPrincipal GetGroup(String groupName)
        {
            PrincipalContext principalContext = GetPrincipalContext;
            return GroupPrincipal.FindByIdentity(principalContext, IdentityType.Name, groupName);
        }

        private Boolean IsUserMemberOfGroup(GroupPrincipal groupPrincipal, String identity, IdentityType identityType)
        {
            UserPrincipal userPrincipal = GetUser(identity, identityType);
            if (userPrincipal == null) return false;
            return userPrincipal.IsMemberOf(groupPrincipal);
        }

        private static PrincipalContext GetPrincipalContext
        {
            get
            {
                using (var userCredientals = SecureStoreProxy.GetCredentialsFromSecureStoreService("ActiveDirectoryConnection", SecureStoreProxy.CredentialType.Domain))
                {
                    var principalContext = new PrincipalContext(ContextType.Domain, userCredientals.DomainName, userCredientals.UserName, userCredientals.Password);
                    return principalContext;
                }
            }
        }
    }
}

5. Build and deploy the project
6. Add the webpart to a page
7. Enter an Active Directory group into the text box and click Submit. The webpart will search Active Directory for the group, display a message about the current users membership status, and finally enumerate and list the groups members.

See Also

SharePoint: Querying Active Directory from a Farm Based Solution using C#.Net

References

How to use the DirectoryServices Namespace in ASP
Microsoft.Office.SecureStoreService.dll
Getting credients from the Secure Store Provider
Retrieving Credentials from the SharePoint Secure Store using C#

Search vs. Recursive Looping: Getting a List of Sites (SPWeb's) a User Has Access to in a SharePoint Site Collection

Introduction

A question about returning all the sites (SPWeb's) "the current user" has access to in a given site collection comes up regularly in the TechNet SharePoint forums. The question usually asked is, "is there a method that returns all the sub-webs" of a site collection that a user has access to, or do we need to recursively loop through each web in the site collection, checking if the user has a specific permission to view the web?

The answer is regularly that you need to loop through the collection of webs (recursively), to determine the list of webs the user has access to.

Depending on the size of a site collection, this can be a very expensive and time consuming operation.

There is another way to achieve this requirement, using Search. This article explores using Search to generate a list of webs a user has access to, examines the performance differences between Search and Looping through collections, as well as some potential pros and cons.

Creating a Webpart to Test the Performance of Both Methods.

To compare the difference in performance and the results produced from each method, we are going to create a test webpart. The webpart is very simple, containing two main methods. One method is used for generating the list of webs by looping (calling SPWeb.GetSubwebsForCurrentUser() on each web), and the other method is using the SharePoint Search infrastructure, via the KeywordSearch class. Each of these methods is wrapped in an SPMonitoredScope block, enabling the performance of the each method to be tracked. The results can be seen in the Developer Dashboard.

The method that uses SPWeb.GetSubwebsForCurrentUser() starts at the root web for the site collection, an traverse down, calling GetSubwebsForCurrentUser() on each child web of the current web, until it finishes enumerating all the webs the current user has access to.

The search query used in the search method, queries the search engine for "ALL sites AND webs WHERE the webapplication hostname STARTS WITH the current sites hostname". You can test out the results of this search query using the SharePoint UI, via a standard Enterprise Search site. The search command would look something similar to this, if you were searching for all sub-sites you had access to on the http://corporate site collection:

(contentclass:STS_SITE OR contentclass:STS_Web) AND sitename:http://corporate

WebPart Code for Testing the Performance of Both Methods

using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Text;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using Microsoft.Office.Server.Search.Administration;
using Microsoft.Office.Server.Search.Query;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Utilities;

namespace SearchVerseLoop.GetTheSitesIHavePermissionsToSee
{
    [ToolboxItemAttribute(false)]
    public class GetTheSitesIHavePermissionsToSee : WebPart
    {
        private Label _sitesFromSearch;
        private Label _sitesFromLooping;

        protected override void CreateChildControls()
        {
            _sitesFromSearch = new Label();
            _sitesFromLooping = new Label();
            Controls.Add(_sitesFromSearch);
            Controls.Add(_sitesFromLooping);
        }

        protected override void OnPreRender(EventArgs e)
        {
            base.OnPreRender(e);
            _sitesFromLooping.Text = GetAllWebs();
        }

        private String GetAllWebs()
        {
            try
            {
                var output = new StringBuilder();
                var websFromLooping = new ArrayList();
                using (new SPMonitoredScope("using a loop"))
                {
                    GetListOfWebs(SPContext.Current.Site.RootWeb, SPContext.Current.Site.RootWeb.GetSubwebsForCurrentUser(), websFromLooping);
                    output.Append(String.Format("<p>There are {0} webs I have access to (retrieved from looping through the rootwebs sub-webs)</p>", websFromLooping.Count));
                    foreach (var web in websFromLooping)
                    {
                        output.Append(String.Format("<span>{0}</span><br/>", web));
                    }
                }

                var websFromSearching = new ArrayList();
                using (new SPMonitoredScope("using search"))
                {
                    GetListOfWebsFromSearch(websFromSearching);
                    output.Append(String.Format("<p>There are {0} webs I have access to (retrieved from search, filtering on the current site)</p>", websFromSearching.Count));
                    foreach (var web in websFromSearching)
                    {
                        output.Append(String.Format("<span>{0}</span><br/>", web));
                    }
                }

                return output.ToString();
            }
            catch (Exception e)
            {
                return e.Message;
            }
        }

        private void GetListOfWebs(SPWeb currentWeb, IEnumerable<SPWeb> webCollection, ArrayList webs)
        {
            webs.Add(currentWeb.Url);
            foreach (SPWeb web in webCollection)
            {
                if (web.GetSubwebsForCurrentUser().Count > 0)
                {
                    GetListOfWebs(web, web.GetSubwebsForCurrentUser(), webs);
                }
                else
                {
                    webs.Add(web.Url);
                }
            }
        }

        private void GetListOfWebsFromSearch(ArrayList webs)
        {
            var ssaProxy = (SearchServiceApplicationProxy)SearchServiceApplicationProxy.GetProxy(SPServiceContext.GetContext(SPContext.Current.Site));
            var keywordQuery = new KeywordQuery(ssaProxy)
                {
                    RowLimit = 500,
                    TrimDuplicates = true,
                    ResultsProvider = SearchProvider.Default
                };
            keywordQuery.SelectProperties.Clear();
            keywordQuery.SelectProperties.Add("Path");
            keywordQuery.ResultTypes |= ResultType.RelevantResults;
            keywordQuery.QueryText = String.Format("(contentclass:STS_SITE OR contentclass:STS_Web) AND sitename:{0}", SPContext.Current.Site.HostName);
            ResultTableCollection searchResults;
            try
            {
                searchResults = keywordQuery.Execute();
            }
            catch (Exception)
            {
                //"Your query is malformed. Please rephrase your query."
                return;
            }

            if (!searchResults.Exists(ResultType.RelevantResults)) return;
            var searchResult = searchResults[ResultType.RelevantResults];
            var results = new DataTable { TableName = "SearchResults" };
            results.Load(searchResult, LoadOption.OverwriteChanges);
            foreach (DataRow dataRow in results.Rows)
            {
                webs.Add(dataRow["Path"]);
            }
        }
    }
}

Using SPMonitoredScope

In the code above we have two main functions that get called during the PreRender event. Both of these functions are wrapped in an SPMonitoredScope, which will enable us to track performance information about each method, namely the time each method takes to produce the list webs the current user has access to.

Using SPMonitoredScope also allows us to see other valuable information, such as the number and type of SQL calls, and expensive object allocations, like SPRequest allocations.

Turning on the Developer Dashboard with PowerShell

To see the results, we need to enable the Developer Dashboard. There is no user interface in SharePoint for enabling the Developer Dashboard, but thankfully, it's easily enabled using PowerShell.

To enable the Developer Dashboard, logon to your (test) SharePoint server, and open the SharePoint Management Shell.

Execute the following commands to enable the Developer Dashboard.

$ds = [Microsoft.SharePoint.Administration.SPWebService]::ContentService.DeveloperDashboardSettings;
$ds.DisplayLevel = 'On';
$ds.Update();

Testing the Example Webpart

To test the performance differences, we will run the following tests.
1. User A, with the webpart on a site collection with 9 sites (all webs are indexed)
2. User A, with the webpart on a site collection with 54 sites (some webs are NOT indexed)
3. User B, who has more restricted permissions than User A, with the webpart on a site collection with 54 sites (some webs are NOT indexed)

Each test will be run three times (by refreshing the page), the results (time taken for each method) will be aggregate to produce an average time.

Test 1 User A, on a site collection with 9 sites (all webs are indexed):

The output from the webpart shows both methods return the same number of sites.

This image shows part of the Developer Dashboard output. Using it, we can see the time taken for various parts of the page to load. The highlighted section shows the time taken to execute the two methods we wrapped in the SPMonitoredScope blocks.

You can see immediately that the search method is much faster, even on a small site collection.

The results from the first page refresh.

The results from the second page refresh.

The results from the third page refresh.

From the five screen shots above, we can see that both methods returned the same number of sites, and the differences in the time taken by each method.

Average time for the Looping Method to generate the result set: 84.3ms (93.68, 90.96, 68.20)
Average time for the Search Method to generate the result set: 23.37ms (25.62, 22.04, 22.45)

From this test, we can already see that using search is considerably faster, even though we are dealing with a small site collection.

Test 2 User A, with the Webpart on a Site Collection with 54 Sites (some Webs are not Indexed):

From the output of the webpart you can see, there is a difference in the number of sites returned. The loop method returns 57 webs, but the search method only returns 54 webs.

The difference in the search results is mainly down to a setting on an SPWeb that controls if the site is included in the search index. That setting, "Allow this site to appear in search results?", is set via the site settings page of a site (SPWeb). For example: The "search center" (http://sneakpreview/searchcenter) is not returned in the result set, as this site is excluded from appearing in search results.

This is one of the caveats of using the search method, and could be seen as either a dis-benefit, or a benefit.

Looking at the Developer Dashboard for this test, we can see that the Search method clearly out-performs the loop based method. Test 2 has approximately 6 times the number of sites to retrieve. Using search, the query takes about 3 times longer than it did in the first example. Using looping, the query takes nearly 10 times longer than it did in the first test. Ouch! 

The results from the first page refresh.

 The results from the second page refresh.

The results from the third page refresh.

From the five screen shots above, we can see that both methods returned approximately the same number of sites, and the different time taken by each method. Looping returns all 57 sites the user has access to, while the Search method returns 54 sites (because some sites are excluded from the Search Index).

Average time for the Looping Method to generate the result set: 803.8ms (806.64, 806.96, 786.25, 815.11)
Average time for the Search Method to generate the result set: 64ms (69.83, 67.64, 60.80, 57.91)

In this case, where we are searching a slightly larger site collection, the differences in performance are very noticeable!

Test 3 User B, who has more Restricted Permissions than User A, with the Webpart on a Site Collection with 54 sites (some Webs are not Indexed):

In this test, we focus on some other performance statistics that are highlighted by the Developer Dashboard.

We can see that this user has access to fewer sites than the user used in the previous test (47 sites, as compared with 57 sites for the user in Test 2). While the results are similar to Test 2 (the Search method returns 3 fewer results than the looping method, and the performance time statistics are similar), we want to look at what else is going on behind the scenes.

If we have a closer look at the Developer Dashboard's output, under the Database Queries, we can that the Looping method (calling SPWeb.GetSubwebsForCurrentUser()) makes two calls to the SQL (proc_ListChildWebsFiltered, and proc_GetTpWebMetaDataAndListMetaData) database for each Web that is checked.

Further down the Developer Dashboard page, we have the SPRequest Allocations listed. Here we can see that the Looping method (which calls SPWeb.GetSubwebsForCurrentUser()) creates an SPRequest allocation for each web that is checked.

Test Summary

From the three tests above, it's clear that the Search method out performs the Looping method, and uses less resources in doing so. This makes the search method more scale-able, both in terms of simultaneous users loading the page, and in terms of how large the site collection can be.

The caveat to the Searching method is that the result set might not include all of the sites a user has access to, if one or more sites has been excluded from the Search Index. This may or may not be a problem, depending on why the sites have been excluded from the search index.

The looping method puts more load on the SharePoint infrastructure, and performance issues are bound to occur as the number of users using the code (or webpart) increases and/or the number of sites in a site collection increases.

Quick Summary of Pros and Cons

Search Pros

• It's fast
• It can handle a very large site collection, returning results very quickly

Search Cons

• If a site has the "Allow this site to appear in search results?" set to No, then true to form, the site won't be returned in the search results. This could be a pro (in some scenarios) or a con.
• There are limited properties that can returned about an SPWeb object using Search. If you need to query additional properties, for example a property from the SPWeb.Properties collection, you would need to use the looping method.

Loop (iteratively calling GetSubwebsForCurrentUser) Pros

• You can query additional properties of each SPWeb object as you parse the  collection of webs the user has access to. For example, you could query a custom property from the SPWeb.Properties collection.

Loop (iteratively calling GetSubwebsForCurrentUser) Cons

• As the number of webs in a site collection increases, the performance becomes a big issue, causing the page to load slower.
• Make calls to SPWeb.GetSubwebsForCurrentUser() increases load on the SQL server. This could cause a performance problem (albeit, depending on the size of your environment, number of webs in the site collection and the frequency in which the code is called).
• Creates a lot of SPRequest allocations.

See Also

KeywordQuery
SPWeb.GetSubwebsForCurrentUser()
Using the Developer Dashboard
Using SPMonitoredScope
SPMonitoredScope

Using PowerShell to Group and Filter SharePoint ListItems by Metadata Fields

You can use PowerShell to group documents (or list items) based on metadata. You might want to do this to find documents that are duplicates, to find the most active authors for a certain metadata combination (i.e. group by author, then by document type), or to create a report on metadata combinations.

Though some of this functionality can be produced using ListViews, there are scenarios where a ListView can't be used. One such example is grouping documents/listitems by certain metadata (e.g. customer id, document type), then displaying a list of  groups that only contain more than one document/listitem for a given combination of customer id/document type.

The PowerShell functions that are required to perform this sort of task are all standard PowerShell cmdlets (out of the box). Most of the following script examples make use of three PowerShell cmdlets, Group-Object, Where-Object, Sort-Object.

In the examples below, we are querying a marketing list that contains hundreds of marketing publications and submissions. The document library has a number of metadata fields used to describe those documents, including the following fields:

* Marketing Document Type (the type of document. E.g. Publication, Submission, etc)
* Year (the year the publication or submission relates to)
* Group (the business group)

The examples below demonstrate a number of business cases, an example of the script used and the report that gets created.

The basic structure of the script command is piping (passing) collections between cmdlets, grouping, filtering sorting, and counting the items along the way.

Getting Started

All of the examples below use a DataTable as the source list of objects that get passed to the Group-Object cmdlet, which we can get from the following lines of code:

Get the collection of items from the document library and store it in a variable.

$w = Get-SPWeb "http://corporation/marketing"
$l = $w.Lists["Legal Directories"]
$items = $l.Items;

Get all of the items in the listitem collection, return it as a DataTable and store it in the $dt variable.

$dt = $items.GetDataTable();

Store the static names of the fields we'll be using to group and sort by, into variables (to keep the scripts manageable and make the script commands shorter).

$fAuthor = "Author";
$fYear = "iYear";
$fDocType = "iMarketingDocumentClassification"
$fGroup = "iStrand";

Before we hop into the examples, let's take a quick look at each object returned along the pipeline, what it's type is, what properties are exposed, and most importantly, how we use them.

The example we are going to use is grouping by the Author, then Document Type fields. Using the object returned by the Group-Object cmdlet, we are sorting on the Document Type property, then the Count property. The final collection of grouped and sorted objects is passed into the $ad variable. We then pass the $ad variable to Format-Table, displaying the following columns; Count, Authors Name, Document Type.

$ad = $dt | Group-Object $fAuthor,$fDocType  | Sort-Object -Property {$_.Values[1]},{$_.Count} -Descending

$ad | Format-Table Count,@{Label="Name";Expression={$_.Values[0]}},@{Label="Document Type";Expression={$_.Values[1]}} -Autosize

The first part of the example is piping the DataTable object to the Group-Object cmdlet, and grouping it by the Author field, then by the Document Type field.

$go = $dt | Group-Object $fAuthor,$fDocType

If we have a look at the type of object returned, we see that the Group-Object cmdlet has given us a System.Array full of Objects.

If we list out the contents of $go, we see the three properties; Count (the number of items in the group), Name (the values of each set of grouped fields) and Group (containing the datarows in the group).

When we look at the first object in the collection of objects in $go, we see the type is GroupInfo (Microsoft.PowerShell.Commands.GroupInfo). The documentation for the GroupInfo class can be seen here, GroupInfo. The GroupInfo class actually has a forth property, Values, which contains the values of the elements in the group (seen in the screen shot below, as well as in the MSDN Documentation).

Now that we have grouped our list items, and have them in an Array full of GroupInfo objects, we need to sort them into an order ready for displaying. In this example, we want to sort them by Document Type, and then by the Count of each group.

$ad = $go  | Sort-Object -Property {$_.Values[1]},{$_.Count} -Descending

We do this by piping the results from Group-Object to Sort-Object, and specifying the properties to sort on.

The first property we sort on is $_.Values[1]. $_ represents the curent GroupInfo object in the pipeline. We know the GroupInfo object has the Value property, which contains the values of the elements in the group (from the screen, we can see is the Author and Document Type field values).

The second field we are sorting on, is $_.Count. $_, as just discussed, is a GroupInfo object, so we know it has a Count property that contains the number of elements in the group.

Finally, we add the -Descending switch to Sort-Object, to order the results in descending order. The results from Sort-Object (an Array of GroupInfo objects) are then returned and stored in the $ad variable.

Now that we have our results grouped and sorted the way we want them, we can display the results in a table format, using the Format-Table cmdlet. However, to display the columns we want, we need to use formatting instructions to get the values of Document Type and Author, because these values are not direct properties of the GroupInfo object (the GroupInfo object's properties are Count, Group, Name, and Values). If you're interested in learning more about formatting instructions, there's a great article on the Microsoft TechNet Scripting site that is worth reading, Creating Custom Tables.

$ad | FT Count,@{Label="Name";Expression={$_.Values[0]}},@{Label="Document Type";Expression={$_.Values[1]}} -Autosize

The full script can be condensed a little more, piping the DataTable to Group-Object, piping the Array of GroupInfo (returned from Group-Object) to Sort-Object, and finally piping the results to Format-Table for displaying.

$dt | Group-Object $fAuthor,$fDocType  | Sort-Object -Property {$_.Values[1]},{$_.Count} -Descending | FT Count,@{Label="Name";Expression={$_.Values[0]}},@{Label="Document Type";Expression={$_.Values[1]}} -Autosize

Examples

The following examples build on the script above, using the same DataTable ($dt) variable and fields.

Example One: Listing authors who frequently create documents

Group documents by Author, Then by Document Type. Then get all the groups that contain five or more documents with the same author and document type value, and write them out to screen, including each documents ListItemId.

This report will list the people who regularly add documents of certain types (e.g. publications) to the marketing document library

$ad = $dt | Group-Object $fAuthor,$fDocType  | Where-Object{$_.Count -gt 5}

foreach($d in $ad){$d.Group | Format-Table @{Label="Name";Expression={$_["FileLeafRef"]}},@{Label="Item Id";Expression={$_["ID"]}},@{Label="Document Type";Expression={$_["iMarketingDocumentClassification"]}},@{Label="Author";Expression={$_["Author"]}}}

Example Two: Looking for potential duplicates by author

Group documents by Author, Then by Document Type, then by Business Group. Then get all the groups with the same author, document type value and business group, where the group contains more than one item (e.g. a potential duplicate) and write them out to screen, including each documents ListItemId.

This is an example of something a SharePoint List View can't achieve. A list view can do the grouping, but can't filter out groups that only contain one document.

$ad = $dt | Group-Object $fAuthor,$fDocType,$fGroup | Where-Object{$_.Count -gt 1}

foreach($d in $ad){$d.Group | Format-Table @{Label="Name";Expression={$_["FileLeafRef"]}},@{Label="Item Id";Expression={$_["ID"]}},@{Label="Document Type";Expression={$_["iMarketingDocumentClassification"]}},@{Label="Group";Expression={$_["iStrand"]}},@{Label="Author";Expression={$_["Author"]}}}

Example Three: Looking for duplicate submissions

Filter the documents by the document type, then Group the documents by Year, Then by Group. Then get all the groups that contain more than 1  (submission) documents with the same year for the same business group, and write them out to screen, include each documents ListItemId.

$ad = $dt | ?{$_[$fDocType] -eq "Directory Submission"} | Group-Object $fYear,$fGroup  | Where-Object{$_.Count -gt 1 }

foreach($d in $ad){$d.Group | Format-Table @{Label="Name";Expression={$_["FileLeafRef"]}},@{Label="Item Id";Expression={$_["ID"]}},@{Label="Business Group";Expression={$_["iStrand"]}},@{Label="Year";Expression={$_["iYear"]}}}

Example Four: Looking for potential duplicates.

Group documents by Year, Then by Document Type, Then by Group. Then get all the groups that contain two or more documents with the same year, document type and business group values (identifying the potential duplicates), and write them out to screen, include each documents ListItemId.

$ad = $dt | Group-Object $fYear,$fDocType,$fGroup  | Where-Object{$_.Count -gt 1}

foreach($d in $ad){$d.Group | Format-Table @{Label="Name";Expression={$_["FileLeafRef"]}},@{Label="Item Id";Expression={$_["ID"]}},@{Label="Document Type";Expression={$_["iMarketingDocumentClassification"]}},@{Label="Year";Expression={$_["iYear"]}},@{Label="Group";Expression={$_["iStrand"]}},@{Label="Author";Expression={$_["Author"]}}}

Continuing to build on the previous example where we have identified potential duplicate documents, we will add a new column for tracking the potential duplicate documents, and set the value of that column to true for all the identified documents. This field can then be used to create a List View in the SharePoint UI, that a person from the Marketing team can use to review the suspect documents and take an appropriate action.

First, we create the new field, then refresh the list reference and datatable

$l.Fields.Add("PotentialDuplicate", [Microsoft.SharePoint.SPFieldType]::Boolean , $false)
$l.Update();
$items = $l.Items;
$dt = $items.GetDataTable();

Next, using the items we grouped and sorted into the $ad variable in example four, we loop through the collection of group objects, looping through each group object, finally updating the PotentialDuplicate field of each item.

foreach($group in $ad){foreach($item in $group.Group){$li = $l.Items.GetItemById($item.ID); $li["PotentialDuplicate"] = $true;$li.Update();}}

Well, that's it! There's lots of PowerShell in there, but once you get a feel for the GroupInfo object collection that the Group-Object cmdlet returns, and how to use it with Sort-Object, Where-Object and Format-Table, it's really easy to group, filter, sort and count item collections!