Thursday, 16 October 2014

Using the SharePoint Client-Side People Picker with an AngularJS SPA

If you're building a client-side SharePoint app using AngularJS (or any other JavaScript framework), and need a people picker, then using Microsoft's Client-Side People Picker is great option.

Resolving a user with the People Picker

A selected user in the people picker


The ClientSidePeoplePicker is Microsoft client side people picker control. It's built using HTML and JavaScript, is pretty easy to get working, and is generally well documented. E.g. How to: Use the client-side People Picker control in SharePoint-hosted apps

Microsoft: "The picker and its functionality are defined in the clientforms.js, clientpeoplepicker.js, and autofill.js script files, which are located in the %ProgramFiles%\Common Files\Microsoft Shared\web server extensions\15\TEMPLATE\LAYOUTS folder on the server."

In this post I want to demonstrate how to resolve the SharePoint user ID for a selected user and use it to update the data model in an AngularJS spa using a REST call.

To update a people field in a SharePoint list using a REST call, you need the SharePoint SPUser.ID value for the selected user. Unfortunately, the clientside people picker doesn't contain the userid of a resolved user (I guess for good reason; if it did, it would have to call EnsureUser on the web for every user in the people picker that is resolved).

So the challenge is, how to get the SPUser.ID for the selected user?

When a user is resolve by the client side people picker, the claims identity of the user is returned. The people picker also fires an event, OnUserResolvedClientScript, after each user in the picker is resolved. This event is the key to getting that user ID!

Using the OnUserResolvedClientScript event, all you need to do is call a function that will take the claims identity of the user, and use it to perform an EnsureUser on the web. Calling EnsureUser on the SPWeb will add the user to the SharePoint User Information List (of the site collection), and return the SPUser object.

The sequence of actions is:

1. Client selects a user in the people picker
2. The people picker resolves each selected user, and fires the OnUserResolvedClientScript event each time a user is resolved
3. Each time the OnUserResolvedClientScript event is raised, the custom function (that you need to write) to ensure the user is called.
4. This function disables saving the model, and then does a quick REST call to EnsureUser.
5. When the REST call returns, you get the selected users ID back, and can use this to update the data model.
6. Saving the model is re-enabled.

The first step is to create a function that calls EnsureUser on the web. In this example, I'm using the AngularJS Resource module to make a REST call to EnsureUser:

// Define the ensureuser model
var ebc = ebc || {};
ebc.models = ebc.models || {};
ebc.models.ensureUser = function () {
    this.logonName = null;
}
// This example uses AngularJS resource service for iteracting with RESTful services
// This function defines the REST endpoint for EnsureUser.
function getUserIdResource() {
    return $resource('../../../_api/web/ensureuser',
        {}, {
            post: {
                method: 'POST',
                params: {
                },
                headers: {
                    'Accept': 'application/json;odata=verbose;',
                    'Content-Type': 'application/json;odata=verbose;',
                    'X-RequestDigest': service.securityValidation
                }
            }
        });
}
  
// the getUser function takes a claims formatted user string
// and makes a POST to ensureUser to check whether the specified login name
// belongs to a valid user in the site.
// If the user doesn't exist, adds the user to the site.
// The specified SPUser object is returned, containing the users SharePoint user ID.
// The user ID can then be used with the data model to update the list item
function getUser(claimsUserName) {    
    var userModel = new ebc.models.ensureUser();
    userModel.logonName = claimsUserName;
    var resource = getUserIdResource();
    var deferred = $q.defer();
    resource.post(userModel, function (data) {
        // successful callback 
        // data.d contains the JSON representation of the SPUser
        deferred.resolve(data.d);
    }, function (error) {
        // error callback
        deferred.reject(error);
    });
    return deferred.promise;
}

The second step is to create a function that can be called by the people picker each time a user is resolved. This function will be responsible for calling the function to ensure the user, and updating the data model when that function returns the selected users user id.

// This function is called by the people picker each time a user is resolved.
// The property parameter contains a string value that represents the property
// on the data model that needs to be updated with value of the SPUser ID on
function setUserIdFromPickerChoice(userKey, property, isMultiValued) {
    if (userKey && userKey[0] && property) {
        //disable saving the "rec" data model
        rec.saveDisabled = true;
        //call the getUser function
        datacontext.getUser(userKey[0].Key)
            .then(function (data) {
                if (data.Id) {
                    // "item" represents the SharePoint list item model property of the data model.
                    // check that the property "property" exists on the data model.
                    if (rec.item.hasOwnProperty(property)) {
                        // set the value of the property
                        rec.item[property] = data.Id;
                    }
                }
                // re-enable saving
                rec.saveDisabled = false;
                // log
                common.logger.logSuccess('Set user id.', null, controllerId);
            })
            .catch(function (error) {
                // log the error
                common.logger.logError('error retrieving request details data', error, controllerId);
            });
    }
    else if (property && rec.item) {
        //If the userKey doesn't contain a value, clear the value from the data model
        if (rec.item.hasOwnProperty(property)) {
            // "item" represents the SharePoint list item model property of the data model.
            // Use the the "property" value
            rec.item[property] = null;
        }
    }
}

You can see the POST and RESPONSE returned from the call to EnsureUser using Fiddler;

POST to /_api/web/ensureuser


RESPONSE from /_api/web/ensureuser


The third step is to initialise the people picker, and attach our function to the OnUserResolvedClientScript function

function initializePeoplePicker(peoplePickerElementId, displayName, userName, modelProperty, isMultiValued) {
    var schema = {};
    schema['PrincipalAccountType'] = 'User';
    schema['SearchPrincipalSource'] = 15;
    schema['ResolvePrincipalSource'] = 15;
    schema['AllowMultipleValues'] = isMultiValued;
    schema['MaximumEntitySuggestions'] = 50;
    schema['Width'] = '270px';
    schema['OnUserResolvedClientScript'] = function (elementId, userKey) {
        //Check if the people picker is intialised
        if (peoplePickerInitalised) {
            // If the picker is initialised, then call the 
            // setUserIdFromPickerChoice method.
            // This method will ensure the user on the web, 
            // and then update the data model 
            // Note that the modelProperty variable, is passed into this 
            // function. This variable is passed into the initalizePeoplePicker
            // function, and then used with the setUserIdFromPickerChoice function
            // to update the data model property with the selected users userid  
            setUserIdFromPickerChoice(userKey, modelProperty, isMultiValued);
        }
    }
    // If the userName property contains a value,
    //then populate the people picker with the user passed to the function
    var users = null;
    if (userName != null) {
        users = new Array(1);
        var user = new Object();
        user.AutoFillDisplayText = displayName;
        user.AutoFillKey = userName;
        user.AutoFillSubDisplayText = "";
        user.DisplayText = displayName;
        user.EntityType = "User";
        user.IsResolved = true;
        user.Key = userName;
        user.ProviderDisplayName = "Tenant";
        user.ProviderName = "Tenant";
        user.Resolved = true;
        users[0] = user;
    }
    // Call SPClientPeoplePicker_InitStandaloneControlWrapper to initialise the control
    SPClientPeoplePicker_InitStandaloneControlWrapper(peoplePickerElementId, users, schema);
}

Once you have this done, you're ready to go!

The screen shot below (from Fiddler) shows the people pickers request and the JSON returned, containing the users claims identity. This is followed by the call to EnsureUser, from the method we've attached to the people pickers OnUserResolvedClientScript event.



I'll be posting a full working example on the MSDN Gallery soon!


Monday, 6 October 2014

PowerShell: Array to a Comma Separated String in One LIne

Today I got asked how to take the output of a list of servers, append the domain name to each server, and then output the list as a single comma separated string.

After trying to achieve it with one single short line of PowerShell, I settled on three lines of code. I then got chastised by My Crazy Brazilian Colleague (MCBC) for always writing too many one liners in my scripts! Apparently too many condensed, piped one liners, make a PowerShell script hard to read. After all, a PowerShell script isn't meant to be a minimised JavaScript file!

Now while I agree with MCBC in principal, failing to pipe (and append text to) all the strings in the array, into a single comma seperated string object, bugged me for the rest of the day!

Determined to prove the point, I came up with this. My array to string one liner, that goes against readability!

First, my formula!

$a | %{$v += ($(if($v){", "}) + $_ + $t)}

That definitely isn't readable, so here's an example (the single line of code, obviously being the middle line!)

#Define an array of text values            
$a = @("orange","apple","kiwi","banana")
#One line of PowerShell to output the values into a single comma separated string, appending "'s are delicious" to each string            
$a | %{$v += ($(if($v){", "}) + $_ + "'s are delicious")}
#Print the value            
$v



In our scenario today, we took a list of servers from a SharePoint farm, and then piped the server names into some other command (which I don't care to recall!). Had I thought of this during the day, instead of on the train during the commute home, the PowerShell would have looked this:

#Get the list of servers from SharePoint            
$sl = (Get-SPFarm).Servers | Select Name            
#For each server, add the domain name            
$sl | %{$v += ($(if($v){","}) + $_.Name + "." + $env:USERDNSDOMAIN)}            
#Print the array            
$v



And finally, for those unclear on what is happening, here's a pict-o-gram!



Sorry MCBC, I couldn't resist!



Tuesday, 23 September 2014

Update the IIS bindings on all Servers in a SharePoint Farm using PowerShell Remoting

I blogged the other day about using PowerShell Remoting to add a SQL alias to multiple servers in a SharePoint Farm. Adding a SQL Alias to all the Servers in a SharePoint Farm using PowerShell and Remoting.

This post follows that one in theme. The migration project I'm working on at the moment required a change to the IIS bindings for one of the web applications. We needed to a add a default binding that would catch all requests other-wise not resolved, on port 443.

The binding in IIS looks like this: *:443:

I used the PowerShell Remoting technique described in the blog above to run a PowerShell script block on all of the 15 SharePoint servers in the farm. This is fast, and ensures the settings get applied consistently!

The key to this task, is to ensure you load the IIS PowerShell module in each script block. Each "remote session" is like openning a new PowerShell console; you must remember to load any additional modules or add-ins that are need by your script.

To the code.

Tasks:
1. Stop the default website (if it's running)
2. Add the default (catch-all) binding for SSL (port 443). In the example, we're adding that binding to the Yarletto IIS web application (not to be confused a SharePoint web application).
3. Remove the existing bindings on the IIS web application.


The first part of the script takes care of credentials, stopping the default website (if it's started) and adding the new binding.

# Add the SharePoint Snap-in            
Add-PSSnapin Microsoft.SharePoint.PowerShell            
            
# Create a credential object - this is needed to authenticate to the             
# remote server of each PowerShell Remote session.            
$account = Read-Host -Prompt "Enter the farm account";            
$password =  Read-Host -Prompt "Enter the farm account password" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($account,$password);
            
# Get a list of servers in the SharePoint farm. Then filter the list to servers
# that actually have the SharePoint binaries installed (omit DB and email servers)
$farm = Get-SPFarm            
$servers = $farm.Servers | ?{$_.Role -eq "Application"} | Select Name,Role            
            
# Send the list of servers to a for-each cmdlet - the alias is "%".             
$servers | %{             
 # For each loop, create a new PowerShell Remote Session            
 # Pass in the credential object to authenticate the remote session            
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Updating bindings on"$_.Name -f green;            
             
 # Invoke a PowerShell Script block in the remote session,             
 # using the Invoke-Command cmdlet.            
 # Invoke-Command will run the script block in the session passed             
 # into the Session parameter.            
 # At the end of the script block, notice that parameters have been             
 # passed into the script block            
 # using the -ArgumentList paramater.            
    Invoke-Command -Session $rs -Script {             
        param($hostname = "", $iisWebAppname = "")            
        Write-Host "Working on"$env:COMPUTERNAME;             
        Write-Host "Hostname:"$hostname                    
        Write-Host "IIS APP name"$iisWebAppname;            
        # Load the IIS Web Administration module, to get access to             
        # the IIS PowerShell cmdlet's            
        Import-Module "WebAdministration"             
        # Get the default IIS web site            
        $ws =  Get-Website "Default Web Site"            
        # If the website is running, stop it.            
        if($ws.state -eq "Started"){            
            Write-Host "Stopping default website." -f DarkMagenta            
            $ws.Stop();            
            Sleep 1;            
        }            
         # Use the Get-WebBinding cmdlet to search for an existing             
         # instance of the web binding we want to add            
        $b = $null            
        $b = Get-WebBinding | ?{$_.bindingInformation -eq "*:443:"}            
        # If the web binding doesn't already exist, then create it!            
        if($b -eq $null)            
        {            
            Write-host "Adding binding" -f DarkYellow            
            New-WebBinding -Name $iisWebAppname -Protocol https -Port 443 -IPAddress "*" -HostHeader $hostname                    
        }                    
    } -ArgumentList "","Yarletto"              
    # Finally, make sure you close the Remote session.            
    Remove-PSSession -Session $rs;                
}

The second part of the script removes the old bindings. This could be scripted more efficiently (as a function), but sometimes you just need to create a script in the shortest possible amount of time!

# Send the list of servers to a for-each cmdlet - the alias is "%".             
$servers  | %{            
    # For each loop, create a new PowerShell Remote Session            
    # Pass in the credential object to authenticate the remote session                
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Updating bindings on"$_.Name -f green;            
            
    # Invoke a script block using the remote session (as above)            
    Invoke-Command -Session $rs -Script {             
        param($hostname = "", $iisWebAppname = "")            
        Write-Host "Working on"$env:COMPUTERNAME;             
        Write-Host "Hostname:"$hostname                    
        Write-Host "IIS APP name"$iisWebAppname;            
        # Remember to load the IIS Web Administration module            
        Import-Module "WebAdministration"            
        # Check the IIS Binding exists            
        $b = $null            
        $b = Get-WebBinding -HostHeader $hostname -Port 443            
        # If the binding exists, delete it!            
        if($b -ne $null)            
        {            
            Write-host "Removing binding" -f DarkYellow            
            Remove-WebBinding -BindingInformation $b.bindingInformation                    
        }                    
    } -ArgumentList "yarletto.com.au","Yarletto"              
    # Finally, remember to close the session once you're finished with it!            
    Remove-PSSession -Session $rs;                
}



Wednesday, 17 September 2014

Adding a SQL Alias to all the Servers in a SharePoint Farm using PowerShell and Remoting

Today I needed to add a new SQL alias to all the servers in a large SharePoint Farm.

There are 15 servers in this farm (not including the SQL clusters). So I didn't want to logon to each server in the farm to add the alias manually.

I thought it would be great if I could just run a single PowerShell script on one of the SharePoint servers that added the alias to all the servers in the farm.

That's where PowerShell Remoting comes to the rescue! I can execute a script on multiple servers from a single server!

Here's how.

Cooking Time:
5 mins

Ingredients:
1 x Script to execute
1 x Credential (requires administrative permissions)
A handful of Servers

Method:
1. Create a credential object

$account = Read-Host -Prompt "Enter the farm account";            
$password =  Read-Host -Prompt "Enter the farm account password" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($account,$password);

2. Get all the "SharePoint" servers in the farm (a.k.a any server that has the SharePoint binaries installed on it).

I'm filtering the list of servers in the farm based on the Role = Application. This ensures we don't get SQL Servers and email servers.

$farm = Get-SPFarm            
$servers = $farm.Servers | ?{$_.Role -eq "Application"} | Select Name

3. Store the SQL Alias information in some variables

$aliasname = "HR"             
$sqlserver = "sqlserver\hrinstancename"            
$tcpalias = "DBMSSOCN," + $sqlserver

4. Pipe the list of servers to the Foreach-Object (%) cmdlet, and let the magic begin!

In each loop of the for-each block, create a new PSSession, using the server name and the credential object created earlier, to connect to the remote server.

Once you have the new remote PSSession, use the Invoke-Command cmdlet to run the PowerShell script in the remote session.  Pass the $aliasname and $tcpalias variables to Invoke-Command, so that they can be used in the script block.

The PowerShell for actually adding the aliases is a slightly modified version of a script from the guys at Habanero Consulting

Finally, remember to close the PSSession at the end of the block

$servers | %{                
    $rs = New-PSSession -ComputerName $_.Name -Credential $credentials;            
    Write-Host "Adding SQL Aliases to"$_.Name -f green;            
    Invoke-Command -Session $rs -Script {             
  param($AliasName = "", $TCPAlias = "")            
        Write-Host "Working on"$env:COMPUTERNAME;
        $x86 = "HKLM:\Software\Microsoft\MSSQLServer\Client\ConnectTo"            
        $x64 = "HKLM:\Software\Wow6432Node\Microsoft\MSSQLServer\Client\ConnectTo"
        if ((test-path -path $x86) -ne $True){write-host "$x86 doesn't exist";New-Item $x86}            
        if ((test-path -path $x64) -ne $True){write-host "$x64 doesn't exist";New-Item $x64}           
        $p = $null;            
        $p = Get-ItemProperty -Path $x86 -Name $AliasName -ErrorAction:SilentlyContinue
        if($p -eq $null){Write-Host "creating x86 alias" -f Yellow; New-ItemProperty -Path $x86 -Name $AliasName -PropertyType String -Value $TCPAlias}            
                
        $p = $null;            
        $p = Get-ItemProperty -Path $x64 -Name $AliasName -ErrorAction:SilentlyContinue            
        if($p -eq $null){Write-Host "creating x64 alias" -f Yellow;New-ItemProperty -Path $x64 -Name $AliasName -PropertyType String -Value $TCPAlias}            
    }                
    Remove-PSSession -Session $rs;                
} -ArgumentList $aliasname,$tcpalias

And that's it! It's as easy as that!
Kaaaaa PoW!

Monday, 15 September 2014

Deleting Orphaned SharePoint Databases

When you delete a Service Application, but not the data, databases are left behind. Should you want to clean up references to these databases later, here's how.

Use the Get-SPDatabase cmdlet to list all of the databases the Farm knows about. The output of this command is verbose, so pipe it to Format-List and select a subset of the properties.

Get-SPDatabase | FT Name,Exists


Notice how the IsAttachedToFarm and ExistsInFarm properties don't report true or false for most of the service applications?

It's not a problem! You can use the Exists property (on each database) to filter that list to just databases that SharePoint "thinks" don't exist.

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false} | %{Write-Host "DB"$_.Name"does not exist." -f red}

Or

$dbs | %{Write-Host "Database"$_.Name;if($_.Exists){Write-Host "DB Exists." -f Green}else{Write-Host "DB does not exist." -f red}}



To remove these database references, call the Delete() method, and then the Unprovision() method, on each database.

For Example:

$dbs = Get-SPDatabase            
$dbs | ?{$_.Exists -eq $false}  | %{Write-Host "DB"$_.Name"does not exist. Deleting and cleaning up references." -f red; $_.Delete();$_.Unprovision()}


Thursday, 11 September 2014

Filtering a SharePoint List View by Document Approval status

Just a quick one on creating SharePoint list views that filter results based on a workflow status column.

Scenario: SharePoint 2013, Nintex Workflow 2013, Document Library with a workflow attached (that runs on the documents).

The workflow status is recorded in the Document Approval column (static name, Document). 

The values of this column can be retrieved using PowerShell. In this example, I'm using CSOM to access the field values, by getting the field, and looking at the SchemaXml property:

$SourceWebUrl = "http://some.site.com/sites/fud"            
$SourceListName = "Project Documents";            
$account = Read-Host -Prompt "Enter the account to use to query pages";            
$password =  Read-Host -Prompt "Enter the password to use to query pages" -AsSecureString
$credentials = New-Object System.Management.Automation.PsCredential($Account,$Password);

Add-Type -Path "C:\Temp\Microsoft.SharePoint.Client.dll";            
Add-Type -Path "C:\Temp\Microsoft.SharePoint.Client.Runtime.dll";            
            
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SourceWebUrl)            
$ctx.Credentials = $credentials            
$w = $ctx.Web            
$ctx.Load($w)            
$l = $w.Lists.GetByTitle($SourceListName)            
$ctx.Load($l)            
$fields = $l.Fields            
$ctx.Load($fields)            
$ctx.ExecuteQuery()            
            
#Get the document approval field and check the SchemaXxml property            
$da = $fields.GetByInternalNameOrTitle("Document Approval")            
$ctx.Load($da)            
$ctx.ExecuteQuery()            
$da.SchemaXml

The values are:

<Field DisplayName="Document Approval" Type="WorkflowStatus" Required="FALSE" ID="{e7cfcdf7-6990-4a20-835c-83d64fbaf87a}" SourceID="{a777c58e-b89b-4f82-8c08-36721dd8ceeb}" StaticName="Document" Name="Document" ColName="nvarchar16" RowOrdinal="0" Version="154" WorkflowStatusURL="_layouts/15/WrkStat.aspx" ReadOnly="TRUE">
    <CHOICES>
        <CHOICE>Starting</CHOICE>
        <CHOICE>Failed on Start</CHOICE>
        <CHOICE>In Progress</CHOICE>
        <CHOICE>Error Occurred</CHOICE>
        <CHOICE>Canceled</CHOICE>
        <CHOICE>Completed</CHOICE>
        <CHOICE>Failed on Start (retrying)</CHOICE>
        <CHOICE>Error Occurred (retrying)</CHOICE>     
    </CHOICES>
</Field>

To use these values in a List View, open your list view (or create a new list view) in SharePoint Designer. Then create a CAML query that filters on this field, using the (zero based) index of the field values to specify the field value to filter on. The field type needs to be Integer. 

For example, the following CAML query filters all documents that the current user has authored, that have a workflow status of Starting or In Progress;


<Query>
    <Where>
        <And>
            <Eq>
                <FieldRef Name="Author"/>
                <Value Type="Integer">
                    <UserID Type="Integer"/>
                </Value>
            </Eq>
            <Or>
                <Eq>
                    <FieldRef Name="Document"/>
                    <Value Type="Integer">0</Value>
                </Eq>
                <Eq>
                    <FieldRef Name="Document"/>
                    <Value Type="Integer">2</Value>
                </Eq>
            </Or>
        </And>
    </Where>
</Query>