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;                
}

Quick Analytics from SharePoint (kind of...)

SharePoints builtin analytics, interesting as they are, don't always provide you with what you need.

This week I had a request to provide a report that summarised access to a particular pdf document on a SharePoint site (accessed via a URL in an email), broken down by department, location and employee type. At first I thought I'd design a simple SharePoint solution that allowed site admins to upload a file to a library, then track the access to the document via an application page that provided a dynamic graph where they could slide and dice the information.

As cool as that would have been, since I was short on time, and this was a one off request, I decided to use Log Parser and PowerShell to create the report (which took a fraction of the time).

Here's what I did:

1. Copy the IIS log files (for the SharePoint web application in question) from the WFE servers into a single directory on my computer

2. Open PowerShell, change to the directory with the log files, run logparser against the files, and extract the data I want into a CSV file.

Obviously this is where you would customise Log Parser to output the data you're interested in, but the command I ran pulls out the number of times a user has opened the document (DocumentInQuestion.pdf) per day.

Here's the command:

logparser.exe -i:W3C "select cs-username as User, Count(*) as ReadCount, date into report.csv from .\* where cs-uri-stem = '/marketing/documents/DocumentInQuestion.pdf' and User Is Not Null and date > Timestamp('2012-04-01','yyyy-MM-dd') group by user,date" -o:CSV

3. Next, the fun bit, is using PowerShell to bring it all together. What I'm going to do is create an object to store each record in, then add the records to an array, and use the array to create the report.... Oh, and I'm going to use the Active Directory PowerShell module so that I can get some extra data about each user from Active Directory (department, office location and employee type).

First. create the object for storing each record in:

$request = New-Object psobject
$request | Add-Member -MemberType NoteProperty -Name "Name" -value ""
$request | Add-Member -MemberType NoteProperty -Name "Location" -value ""
$request | Add-Member -MemberType NoteProperty -Name "Department" -value ""
$request | Add-Member -MemberType NoteProperty -Name "EmployeeType" -value ""
$request | Add-Member -MemberType NoteProperty -Name "Reads" -value ""

Copy the data from the CSV file to a PowerShell object we can use:

$r  = Import-Csv .\report.csv

$r now contains each line of the CSV file (as a collection of objects), and we can access individual columns by their original name in the CSV file. I.e. $r[0].user

Next, create a new array, loop through each object in $r (aka each line of the imported CSV file), format the 'user' column (I need to trim of the domain name), passing the 'user' string to the Get-AdUser command (returning a user object containing the extra properties (displayName, Office and employeeType) I need), parse the object returned from Active Directory, extracting the contents into a new "request" object and merge it with the details from the current object in $r, and finally add it to the array. Phew... that was a long sentence, but really quite straight forward.

To break it down a little;

1. For each object (aka line of the CSV file) in $r

foreach($i in $r)

2. Getting the "user" property, trimming off the first 6 characters (which is our domain name and forward slash)

$i.User.Substring(6)

3. Pass the trimmed user name to the Get-AdUser commandlet, and request the additional properties, displayName, department, office and employeeType

$u = get-aduser $i.User.Substring(6) -Properties displayName,department,office,employeeType 

4. If the object returned from Active Directory is not empty, then create a new "request" object, and populate it with information from the current object in $r (aka current line in the CSV file), and add it to the array

if($u -ne ""){
$b = $request | Select-Object *; $b.Name = $u.displayName; $b.Location = $u.office; $b.Department = $u.department; $b.EmployeeType = $u.employeeType; $b.Reads = $i.ReadCount; $a += $b;
;}

Here's the full command:

$a = $null
$a = @()
foreach($i in $r){$u="";$u = get-aduser $i.User.Substring(6) -Properties displayName,department,office,employeeType -ErrorAction:SilentlyContinue; if($u -ne ""){
$b = $request | Select-Object *; $b.Name = $u.displayName; $b.Location = $u.office; $b.Department = $u.department; $b.EmployeeType = $u.employeeType; $b.Reads = $i.ReadCount; $a += $b;
;}}

Once we have done this, we have all the information we need in a new array of objects, and it's simply a case of formatting the data the way we want it and creating the report. Because we have an array of "request" objects, this is pretty easy, as we can iterate over them, group them, sort them, etc. I needed to display the following information:

Total amount of people who opened the document
Total amount of people by Office
Total Partners
Total Fee Earners
Total Business Services

My report looked like this:

$nr = @();
$nr += "Usage for: "+$doc
$nr += ""
$nr += "Total Count: "+$a.Count
$nr += ""
$nr += "Count per office:"
$b = $a | group location; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name}
$nr += ""
$nr += "Total Partners"
$b = $a | ?{$_.Department -like "Partners"} | measure; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name}
$nr += ""
$nr += "Partners / Office"
$b = $a | ?{$_.Department -like "Partners"} | group location; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name}
$nr += ""
$nr += "Total Support Staff"
$b = $a | ?{$_.EmployeeType -like "SupportStaff"} | measure; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name}
$nr += ""
$nr += "Support Staff / Office"
$b = $a | ?{$_.EmployeeType -like "SupportStaff"} | group location; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name}
$nr += ""
$nr += "Total Fee Earning Staff"
$b = $a | ?{$_.EmployeeType -like "FeeEarningStaff"} | measure; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name}
$nr += ""
$nr += "Fee Earning Staff / Office"
$b = $a | ?{$_.EmployeeType -like "FeeEarningStaff"} | group location; foreach($d in $b){$nr += $d.Count.ToString()+"`t"+$d.Name}

And when displayed, it looks like this...

Usage for: /marketing/documents/DocumentInQuestion.pdf

Total Count: 477

Count per office:
52      Hong Kong
42      Hamburg
8        Monaco
191    London
12      Le Havre
19      Paris
33      Dubai
36      Greece
29      Singapore
33      Shanghai
20      Singapore Local
2        Beijing

Total Partners
92

Partners / Office
58      London
1        Hamburg
5        Singapore
11      Greece
2        Shanghai
8        Dubai
3        Singapore Local
4        Paris

Total Support Staff
195

Support Staff / Office
23      Hong Kong
35      Hamburg
58      London
12      Le Havre
15      Paris
11      Greece
7        Dubai
15      Singapore Local
5        Shanghai
13      Singapore
1        Beijing

Total Fee Earning Staff
269

Fee Earning Staff / Office
6         Monaco
7         Hamburg
133     London
26       Dubai
29       Hong Kong
15       Greece
21       Singapore
28       Shanghai
4         Paris