Tuesday, 16 December 2014

Provisioning a new Nintex Workflow Content Database using PowerShell

Scenario:

I need to create a new Nintex Workflow Content database and associate it with a new SharePoint Site Collection as part of a PowerShell based solution provisioning process.

Problem:

There is no obvious way to do this; there are no methods in the web api, and nwadmin doesn't have any operations that remotely resemble adding a new content database.

Approach:

There are two ASPX pages in SharePoint Central Admin that allow administrators to create new Nintex Workflow Content databases and associate those databases to Site Collections. Since these pages must have code behind them, I thought I'd open up the Nintex dll's (using ILSpy) and see if I could find the code responsible for the functionality on these pages.

This approach worked perfectly, and it turned out that I only needed a few lines of PowerShell to create my new database and associated it with a Site Collection.

The caveat is, the PowerShell needs to be run from a PowerShell command prompt on the Server, so it won't work if you're solution is being built for Office 365, or if you need to use all client side (PowerShell) code.

Solution / Code:

#Load all the assemblies that we need to use            
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SharePoint.Administration') | Out-Null            
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow') | Out-Null            
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.Administration') | Out-Null            
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.Common') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.ContentDbMappingCollection') | Out-Null            
            
#Add the SharePoint PowerShell snapin (in case it's not already loaded)            
if(-not(Get-PSSnapin | Where-Object {$_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
 Add-PSSnapin Microsoft.SharePoint.PowerShell;            
}            
            
$NintextDatabaseName = "Nintex_Flintstones"            
$Url = "http://portaldev.bi.local/sites/flintstones";            
#Get the SharePoint Site that you want to create a separate Nintex Content database for.            
$site = Get-SPSite $Url            
#Get the content database for the SharePoint Site            
$siteContentDb = Get-SPContentDatabase -Site $site            
#Get the top level farm object. We'll use this to get access to the farms config database server             
#Note: The Microsoft.SharePoint.Administration.SPGlobalAdmin class is deprecated. 
#I'm using it here, only becuase I'm trying to keep my PowerShell code as close 
#as possible to the code used in the Nintex admin pages            
$globalAdmin = New-Object Microsoft.SharePoint.Administration.SPGlobalAdmin            
#Get the Nintext Configuration Database            
$configDatabase = [Nintex.Workflow.Administration.ConfigurationDatabase]::GetConfigurationDatabase();            
#Check if there is an existing Nintex Content database with the name we want to use            
$contentDatabase = $configDatabase.ContentDatabases.FindByDatabaseAndServerName($globalAdmin.ConfigDatabaseServer,$NintextDatabaseName);            
#If the an existing database with the same name we want to use wasn't found, then we'll add it.             
if($contentDatabase -eq $null)            
{            
    Write-Host "The Nintex Content Database $NintextDatabaseName does not exist." -f Yellow;            
    Write-Host "Creating a new Nintex Content Database with the following name: $NintextDatabaseName" -f Yellow;            
    #Create a SQL connections string            
    $connectionString = ([String]::Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", $globalAdmin.ConfigDatabaseServer,$NintextDatabaseName))            
    #Initialise a new DatabaseAttacher object using the connection string            
    $dbAttacher = New-Object Nintex.Workflow.Administration.DatabaseAttacher($connectionString, 0)            
    #Set properties on the DatabaseAttacher object.             
    $dbAttacher.AttachOptions.CreateNewDatabase = $true;            
    $dbAttacher.AttachOptions.ProvideAllWebApplicationsAccess = $true;            
    $dbAttacher.AttachOptions.IncludeStorageRecordStep = $false;            
    #Finally, call the Attach() method to create and attach the 
    #database to the SharePoint farm.             
    $attachResult = $dbAttacher.Attach();            
    #Handle the success and failure scenarios            
    if($attachResult.CanContinue)            
    {            
        Write-Host "Successfully created a new Nintex Content Database with the following name: $NintextDatabaseName" -f Green;            
        if($attachResult.Warnings)            
        {            
            Write-Host "The following warnings were logged via creating the Nintex Content Database:" -f DarkYellow            
            Write-Host $($attachResult.Warnings) -f DarkYellow            
        }            
        #Get the new database we just created             
        $contentDatabase = $configDatabase.ContentDatabases.FindByDatabaseAndServerName($globalAdmin.ConfigDatabaseServer,$NintextDatabaseName);                    
    }            
    else            
    {            
        Write-Host "Error creating the Nintex Content Database." -f Red            
        Write-Host $($attachResult.Errors) -f Red            
        return;            
    }            
}            
else            
{            
    Write-Host "The Nintex Content Database $NintextDatabaseName already exists." -f Green;            
}            
            
#If the database was successfully created (or already existed), 
#update the mappings to associate the Nintex Content database 
#with the SharePoint Site Collection.            
if($contentDatabase -ne $null)            
{            
    Write-Host "Updating the Nintex Content Database mappings for site: $($site.Url)" -f Yellow;            
    #Create a new ContentDbMapping object, and get the current content 
    #database mappings for the Site Collection            
    [Nintex.Workflow.ContentDbMapping]$contentDbMapping;            
    $contentDbMapping = [Nintex.Workflow.ContentDbMappingCollection]::ContentDbMappings.GetContentDbMappingForSPContentDb($siteContentDb.Id)            
    #if there are no content database mappings found, initialise a new 
    #ContentDatabaseMapping object            
    if($contentDbMapping -eq $null)            
    {            
        $contentDbMapping = New-Object Nintex.Workflow.ContentDbMapping            
    }            
    #Set the properties of the ContentDatabaseMapping object to associate
    #the Nintex Content Database with the Site Collection            
    $contentDbMapping.SPContentDbId = $siteContentDb.Id;            
    $contentDbMapping.NWContentDbId = $contentDatabase.DatabaseId;            
    #Call the CreateOrUpdate() method to save the changes            
    $contentDbMapping.CreateOrUpdate();                
    Write-Host "Successfully added a Nintex Content Database mapping for site: $($site.Url)" -f Green;            
}            




Sunday, 23 November 2014

Quick and Dirty: Parsing logs with PowerShell

Recently I needed to parse a large number of SharePoint log files looking for particular strings. The strings I was looking for indicted errors that required further investigation.

There are a number of tools for doing this sort of activity. In this case, I choose to use PowerShell, because of the speed with which I could accomplish this task. It took me less than 5 minutes to write and test the script. And it saved me a lot of time parsing the log files!

Using the PowerShell script, I can quickly change my queries, re-run my queries, export results, and most importantly, query large numbers of log files quickly.

Here's the code I used.

# Set the path to the log files            
$path = "C:\Temp\Logs"            
# Get a collection of all the log files (anything ending in .log)            
$files = Get-ChildItem -Path $path -Filter "*.log"            
# Pipe the collection of log files to the ForEach-Object cmdlet             
# (the alias of ForEach-Object is %)            
$files | %{            
    # Call the OpenText method, to return a System.IO.StreamReader object            
    $file = $_.OpenText();            
    # Record the current line number (to use in the console output)            
    $lineNum = 1;            
    Write-Host "Checking file"$_.Name -f Yellow;            
    # Use the EndOfStream method (which returns true when you have reach the end            
    # of the file), read each line of the file.              
    while($file.EndOfStream -ne $true)            
    {            
        # Read the next line in the file            
        $line = $file.ReadLine();                    
        if($line -ne $null)            
        {            
            # Use the String ToLower and Contains methods to check for occurances            
            # of the strings (or values) you need to check the file for            
            # In this example, I'm looking for any instances of the text "error" or "exception"            
            if($line.ToLower().Contains("error") -or $line.ToLower().Contains("exception"))            
            {            
                # If the current lines contains a match, write the line number            
                # and line text out to the console            
                Write-Host "Line: $lineNum " -NoNewline -ForegroundColor Green;            
                Write-Host $line -f Red;            
            }            
        }             
        # Increment the line number            
        $lineNum++;                   
    }            
}            



Wednesday, 29 October 2014

Unexpected Token when Parsing a JSON String Retrieved from a SharePoint Listitem

I'm working on a SharePoint based solution at moment, which uses AngularJS and REST calls to build the interface (UI).

In certain scenarios I need to save JSON strings into list item properties. This is easy enough. I can call JSON.stringify(myobject) to create a string representation of the JavaScript object, and then save the value to SharePoint using a REST call.

The problem is, when I retrieve the value back from SharePoint, certain characters are encoded using ISO/IEC 8859-1 encoding. For example, "{" becomes {

Example: see the eBriefRecommendationsForApproval property below



When I make a call to JSON.parse(myjsonstring), I get the error: "Error retrieving noting data  SyntaxError: Unexpected token & at Object.parse (native)"

A colleague suggested a method he'd used before to get around a similar issue, simply replacing the characters.

I'm not sure if this is the "correct" approach or not, but it works! I ended out with the following function to parse JSON strings that are returned from SharePoint,.

function parseJSONString(value) {
try{
var convertedValue = value.replace(/"/g,'"')
.replace(/{/g,'{')
.replace(/}/g,'}')
.replace(/:/g,':');
return JSON.parse(convertedValue);
}catch(e){
return null;
}
}


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!