Publishing and Consuming SharePoint Service Application with PowerShell

Below are my PowerShell scripts for publishing and consuming service applications between SharePoint farms (Service Application Federation). Before I introduce the scripts let be briefly explain the process involved.

The are several reasons why you might want to share service applications between farms and I’m not planning on going into those details here but the overall process of setting up service application federation is illustrated below:

image

The SharePoint farm on the left is the consuming farm. It will use the service applications from the SharePoint farm on the right – the publishing farm. Practically this means that the publishing farm contains your service instances, service applications and service application proxies and your consuming farm contains just service application proxies that ‘point’ to the publishing farm. Loads more details on service application federation is available on technet including which service applications can be federated: http://technet.microsoft.com/en-us/library/ff621100.aspx

The scripts I have follow the pattern shown in the diagram above. First on the consuming farm, I export the farm root certificate, the security token service certificate and I write the farm ID to a text file:

Contents of 1_Consumer_ExportCerts.ps1 – run this on the CONSUMING farm

Add-PSSnapIn "Microsoft.SharePoint.PowerShell" -EA 0

# export consumer root certificate
Write-Host "Exporting Consumer Root Certificate…" -nonewline
$rootCert = (Get-SPCertificateAuthority).RootCertificate
$rootCert.Export("Cert") | Set-Content ConsumingFarmRoot.cer -Encoding byte
Write-Host "Done" -Foreground Green

# export consumer sts certificate
Write-Host "Exporting Consumer STS Certificate…" -nonewline
$stsCert = (Get-SPSecurityTokenServiceConfig).LocalLoginProvider.SigningCertificate
$stsCert.Export("Cert") | Set-Content ConsumingFarmSTS.cer -Encoding byte
Write-Host "Done" -Foreground Green

# export consumer farm id
Write-Host "Exporting Consumer Farm Id…" -nonewline
$farmID = Get-SPFarm | select Id
set-content -path ConsumerFarmID.txt $farmID
Write-Host "Done" -Foreground Green

Write-Host "Now copy ConsumingFarmRoot.cer, ConsumingFarmSTS.cer & ConsumerFarmID.txt to the publishing farm." -Foreground Yellow

You’ll notice this script ends with a prompt to guide you to the next step – copy the consuming farm certificate, consuming sts certificate and the consuming farm id txt file we’ve just created to the publishing farm. Next, switch over to the publishing farm.

Contents of 2_Publisher_ExportCerts.ps1 – run this on the PUBLISHING farm

Add-PSSnapIn "Microsoft.SharePoint.PowerShell" -EA 0

# export publisher root certificate
Write-Host "Exporting Publisher Root Certificate…" -nonewline
$rootCert = (Get-SPCertificateAuthority).RootCertificate
$rootCert.Export("Cert") | Set-Content PublishingFarmRoot.cer -Encoding byte
Write-Host "Done" -Foreground Green

Write-Host "Exporting Publisher Topology Url…" -nonewline
$topologyUrl = Get-SPTopologyServiceApplication | Select LoadBalancerUrl
$url = $topologyUrl.LoadBalancerUrl.OriginalString
Set-Content -path PublishingFarm.Url.txt -Value $url
Write-Host "Done" -Foreground Green

write-host
Write-Host "Now copy PublishingFarmRoot.cer & PublishingFarm.Url.txt to the consuming farm." -Foreground Yellow
write-host

Now copy the publishing farm certificate and publishing farm url txt file that have just been generated to the consuming farm. Now switch back to the consuming farm.

Contents of 3_Consumer_ImportCerts.ps1 – run this on the CONSUMING farm

Add-PSSnapIn "Microsoft.SharePoint.PowerShell" -EA 0

# import publisher root certificate
Write-Host "Importing Publisher Root Certificate…" -nonewline
$trustCert = Get-PfxCertificate PublishingFarmRoot.cer
New-SPTrustedRootAuthority PublishingFarm -Certificate $trustCert
Write-Host "Done" -Foreground Green

Write-Host "Now import certificates on the publishing farm." -Foreground Yellow

You’ve now imported the publishing farm root certificate into the consuming farm, Next switch back to the publishing farm and import the consuming farm certificate and consuming sts certificate into the publishing farm with the following PowerShell:

Contents of 4_Publisher_ImportCerts.ps1 – run this on the PUBLISHING farm

Add-PSSnapIn "Microsoft.SharePoint.PowerShell" -EA 0

# import consumer root certificate
Write-Host "Importing Consumer Root Certificate…" -nonewline
$trustCert = Get-PfxCertificate ConsumingFarmRoot.cer
New-SPTrustedRootAuthority ConsumingFarm -Certificate $trustCert
Write-Host "Done" -Foreground Green

# import consumer sts certificate
Write-Host "Importing Consumer STS Certificate…" -nonewline
$stsCert = Get-PfxCertificate ConsumingFarmSTS.cer
New-SPTrustedServiceTokenIssuer ConsumingFarm -Certificate $stsCert
Write-Host "Done" -Foreground Green

Write-Host "Now set permissions for application discovery on the publishing farm." -Foreground Yellow

Note: At this point I would recommend you access Central Admin on both the CONSUMING and PUBLISHING farms and verify that the trusts are in place as expected. To do this, from central admin select Security > Manage Trust.

Once you have verified your trusts are in place you’re ready to start sharing the service applications between farms. Now switch to the publishing farm.

Contents of 5_Publisher_SetPermissions.ps1– run this on the PUBLISHING farm

Add-PSSnapIn "Microsoft.SharePoint.PowerShell" -EA 0

# get consumer farm id
Write-Host "Reading Consumer Farm ID…" -nonewline
$consumerId = Get-Content -path ConsumerFarmID.txt
$consumerId = $consumerId.Replace("@{Id=","").Replace("}","")
Write-Host "Done" -Foreground Green

# set application discovery permissions
Write-Host "Set Application Discovery Permissions…" -nonewline
$security=Get-SPTopologyServiceApplication | Get-SPServiceApplicationSecurity
$claimprovider=(Get-SPClaimProvider System).ClaimProvider
$principal=New-SPClaimsPrincipal -ClaimType "http://schemas.microsoft.com/sharepoint/2009/08/claims/farmid" -ClaimProvider $claimprovider -ClaimValue $consumerId
Grant-SPObjectSecurity -Identity $security -Principal $principal -Rights "Full Control"
Get-SPTopologyServiceApplication | Set-SPServiceApplicationSecurity -ObjectSecurity $security
Write-Host "Done" -Foreground Green

# list the available service applications and prompt for one to be selected
$serviceAppList = @{"0"="DummyServiceApp"}
$serviceApps = Get-SPServiceApplication
$count = 1
$serviceWarning = ""
Write-Host
Write-Host "The following service applications are available for publishing:"
foreach ($serviceApp in $serviceApps)
{
    # ensure only service applications that can be shared are listed
    $type = $serviceApp.TypeName
    $serviceSharable = 0

    Switch ($type)
    {
        ("Business Data Connectivity Service Application") {$serviceSharable = 1}
           ("Managed Metadata Service")                       {$serviceSharable = 1}
           ("User Profile Service Application")               {$serviceSharable = 1}
           ("Search Service Application")                     {$serviceSharable = 1}
           ("Secure Store Service Application")               {$serviceSharable = 1}
           ("Web Analytics Service Application")              {$serviceSharable = 1}
           ("Microsoft SharePoint Foundation Subscription Settings Service Application") {$serviceSharable = 1}
    }
    if ($serviceSharable -gt 0)
    {
        $serviceAppList.Add("$count",$serviceApp.Id)
        Write-host "$count. " -nonewline -foregroundcolor White
        Write-host $serviceApp.DisplayName -foregroundcolor gray
        $count++
    }

}
Write-Host
$serviceAppNum = Read-Host -Prompt " – Please enter the id of the service application to be shared"
Write-Host
Write-Host "Getting Service Application…" -nonewline
$serviceAppId = $serviceAppList.Get_Item($serviceAppNum)
$serviceApp = Get-SPServiceApplication $serviceAppId
Write-Host "Done" -Foreground Green

# warn about domain trusts
$serviceWarning = ""
$type = $serviceApp.TypeName
Switch ($type)
{
    ("Business Data Connectivity Service Application") {Write-Host; Write-Host "Note: Publishing domain must trust Consuming domain." -Foreground Yellow; Write-Host;}
       ("User Profile Service Application") {Write-Host; Write-Host "Note: A two-way trust must exist between the Publishing and Consuming domains." -Foreground Yellow; Write-Host;}
       ("Secure Store Service Application") {Write-Host; Write-Host "Note: Publishing domain must trust Consuming domain." -Foreground Yellow; Write-Host;}
}

# list the service rights for the specified service application
write-host
$rightsList = @{"0"="DummyServiceApp"}
$count = 1
$serviceAppSecurity = Get-SPServiceApplicationSecurity $serviceApp
foreach ($right in $serviceAppSecurity.NamedAccessRights)
{
        $rightsList.Add("$count",$right.Name)
        Write-host "$count. " -nonewline -foregroundcolor White
        Write-host $right.Name -foregroundcolor gray
        $count++
}
write-host
$serviceAppRight = Read-Host -Prompt " – Please enter the right to be granted"
$serviceAppRight = $rightsList.Get_Item($serviceAppRight)

Write-Host "Granting ‘$serviceAppright’ to service application…" -nonewline
$security=Get-SPServiceApplication $serviceApp| Get-SPServiceApplicationSecurity
$claimprovider=(Get-SPClaimProvider System).ClaimProvider

if ($type -eq "User Profile Service Application")
{
    $consumFarmAcc= Read-Host -Prompt " – Please enter the consuming farm account e.g. DOMAIN\account"
    $principal=New-SPClaimsPrincipal -Identity $consumFarmAcc -IdentityType WindowsSamAccountName   
}
else
{
    $principal=New-SPClaimsPrincipal -ClaimType "http://schemas.microsoft.com/sharepoint/2009/08/claims/farmid" -ClaimProvider $claimprovider -ClaimValue $consumerId
}
Grant-SPObjectSecurity -Identity $security -Principal $principal -Rights $serviceAppRight
Set-SPServiceApplicationSecurity $serviceApp -ObjectSecurity $security
Write-Host "Done" -Foreground Green

Write-Host "Publishing service application…" -nonewline
Publish-SPServiceApplication -Identity $serviceApp
Write-Host "Done" -Foreground Green

$lbUrl = Get-SPserviceApplication $serviceApp | Select Uri
Set-Content -path $serviceAppId -Value $lbUrl
$cleanUrl = Get-Content -path $serviceAppId
del $serviceAppId
$cleanUrl = $cleanUrl.Replace("@{Uri=","").Replace("}","")

write-Host
Write-Host "Now connect to the service application from the consumer farm with the following url:" -Foreground Yellow
write-Host
Write-Host $cleanUrl -Foreground Yellow

It’s a whopper but basically this script lists all the available service applications on the PUBLISHING farm and allows you to publish these to the CONSUMING farm whilst choosing the service application specific permissions to grant to the consuming farm:

image

Simply enter the the number for the service application you wish to publish and then the number associated with the permissions you wish to grant to the consuming farm.

Now, switch to the consuming farm.

Contents of 6_Consumer_ConnectService.ps1 – run this on the CONSUMING farm

Add-PSSnapIn "Microsoft.SharePoint.PowerShell" -EA 0

# get url from user
Write-Host
Write-Host "Reading topology service url…" -nonewline
$topologyUrlShort = get-content -path PublishingFarm.Url.txt
Write-Host "Done" -Foreground Green
Write-Host

#get available published services:
Write-Host "Connecting to topology service $topologyUrlShort…" -nonewline
$publishedServices = Receive-SPServiceApplicationConnectionInfo -FarmUrl $topologyUrlShort
Write-Host "Done" -Foreground Green
Write-Host

# list the published services
Write-Host "The following service applications are available for consumption:"
$serviceAppList = @{"0"="DummyServiceApp"}
$count = 1
foreach ($publishedService in $publishedServices)
{
    Write-host "$count. " -nonewline -foregroundcolor White
    Write-host $publishedService.DisplayName -foregroundcolor gray
        $serviceAppList.Add("$count",$publishedService.Uri)
    $count++

}

Write-Host
$serviceAppNum = Read-Host -Prompt " – Please enter the id of the service application to be consumed"

Write-Host
$serviceAppProxyName= Read-Host -Prompt " – Please enter the service application proxy name"
Write-Host

#get the selected published service app
$count = 1
foreach ($publishedService in $publishedServices)
{
    if ($count.ToString() -eq $serviceAppNum )
    {

        #we’ve found our service application – let go create it based on the type
        $type = $publishedService.SupportingProxy
        $serviceUrl =  $serviceAppList.Get_Item($serviceAppNum)
       
        Switch ($type)
        {
            ("BdcServiceApplicationProxy"){
                    Write-Host "Creating new Business Data Connectivity Service Application Proxy…" -nonewline
                    New-SPBusinessDataCatalogServiceApplicationProxy -Uri "$serviceUrl" -Name "$serviceAppProxyName"
                }
            ("MetadataWebServiceApplicationProxy"){
                    Write-Host "Creating new Managed Metadata Service Proxy…" -nonewline
                    New-SPMetadataServiceApplicationProxy -Uri "$serviceUrl" -Name "$serviceAppProxyName"
                }
            ("UserProfileApplicationProxy"){
                    Write-Host "User Profile Service Application Proxy…" -nonewline
                    New-SPProfileServiceApplicationProxy -Uri "$serviceUrl" -Name "$serviceAppProxyName"
                }
            ("SearchServiceApplicationProxy"){
                    Write-Host "Search Service Application Proxy…" -nonewline
                    New-SPEnterpriseSearchServiceApplicationProxy -Uri "$serviceUrl" -Name "$serviceAppProxyName"
                }
            ("SecureStoreServiceApplicationProxy"){
                    Write-Host "Secure Store Service Application Proxy…" -nonewline
                    New-SPSecureStoreServiceApplicationProxy -Uri "$serviceUrl" -Name "$serviceAppProxyName"
                }
            ("WebAnalyticsServiceApplicationProxy"){
                    Write-Host "Web Analytics Service Application Proxy…" -nonewline
                    New-SPWebAnalyticsServiceApplicationProxy -Uri "$serviceUrl" -Name "$serviceAppProxyName"
                }
        }
        Write-Host "Complete." -Foreground Yellow

    }
    $count++

}

This final script connects to the topology service of the publishing farm and lists all the published services applications. Simply select the number of the service application you wish to consume

image

This has saved me loads of time in the past and has proved to be very reliable. However, please note the following points:

  • The script assumes the files copied between the farms are copied to the same location as the PowerShell scripts.
  • If you want to consume partitioned service applications you’ll need to update the final script to include the –PartitionMode switch (or the –Partitioned switch in the case of the New-SPEnterpriseSearchServiceApplicationProxy cmdlet)

I hope this helps…

Adding additional claims to a Trusted Identity Token Issuer

In my first blog post about setting up claims based authentication between the Thinktecture identity server and SharePoint I showed how to create a basic token that contains a single claim – emailaddress.

Here is how you can extend the claims that SharePoint will accept in a token. I’m assuming you’ve setup claims based authentication as per by previous article.

First, we get a reference to the trusted identity token issuer we created:

$ap = Get-SPTrustedIdentityTokenIssuer | where {$_.Name -eq "idp SAML Provider"  }

Next we extend this to include our new claim – role:

$ap.ClaimTypes.Add("http://schemas.microsoft.com/ws/2008/06/identity/claims/role")
$ap.Update()

Next we create our claim mapping:

$map1 = New-SPClaimTypeMapping -IncomingClaimType "http://schemas.microsoft.com/ws/2008/06/identity/claims/role" -IncomingClaimTypeDisplayName "Role" –SameAsIncoming

Finally we add this mapping to our trusted identity provider:

Add-SPClaimTypeMapping -Identity $map1 -TrustedIdentityTokenIssuer $ap

If we query our trusted identity token issuer again we should see the additional claim:

image

Finally, logging onto our claims based authenticated site we should see our new claim courtesy of  the claims viewer web part I installed from the codeplex project http://claimsid.codeplex.com/:image

Enjoy!

Warm up a SharePoint Web Application with PowerShell

Here’s my script for warming up a SharePoint Web Application with PowerShell. It should be fairly self explanatory but note the following:

  • It uses System.Net.HttpWebRequest so does not require access to the SharePoint PowerShell cmdlets or object model – in fact this could work with most web based applications.
  • It uses the credentials from the current context so the process running the script must be able to access the url and authenticate automatically – typically via the IE Local Intranet zone.

Script:

function Get-HTTPRequest {
   param ( $URL = (Read-Host "Enter the URL of the page to request") )
  
   ## Create our request object
   $Request = [System.Net.HttpWebRequest]::Create($URL);
  
   ## Use the default (current) credentials
   $Request.UseDefaultCredentials = $true
  
   try
   {

        ## Make the request
        $Response = $Request.GetResponse();
       
        ## Process the response
        if($Response.StatusCode -eq 200) {
       
            ## Get the length of the response
            [int]$Length = $Response.ContentLength
           
            ## Get the response stream
            $Stream = $Response.GetResponseStream()

            ## Create a byte array so we can read our buffer in chunks
            [byte[]]$Chunk = new-object byte[] 1024
           
            ## Create a running total of our progress
            [int]$TotalBytesRead = 0
            [int]$BytesRead = 0
           
            ## Loop through the stream reading a Chunk (1024 bytes) at a time
            do {
                ## Read a Chunk
                 $BytesRead = $Stream.Read($Chunk, 0, $Chunk.Length)
               
                ## Accumilate the total bytes read
                $TotalBytesRead += $BytesRead
               
                ## Determine if there is anything left on the response stream to read
                if($Length -gt 0)
                {
                    ## Update progress
                       Write-Progress "GET $url" "Bytes read: $TotalBytesRead of $Length" -percentComplete (($TotalBytesRead/$Length)*100)
                }
                else
                {
                    ## Nothing left to read – we are all done.
                       Write-Progress "GET $url" "Total bytes read: $TotalBytesRead bytes"
                }
            }
            ## Loop until there are no more bytes to read
            while ($BytesRead -gt 0)

            ## Close the response stream
            $Stream.Close()
        }

        ## Close the response
        $Response.Close();
   }
   catch
   {
           ## Ooops
        Write-Host "ERROR: $_.Message" -ForegroundColor Red
        return
   }
 
}

Get-HTTPRequest "http://bc01/SitePages/Home.aspx"
Get-HTTPRequest "http://bc01/Search/Pages/default.aspx"
Get-HTTPRequest "http://bc01/_layouts/viewlsts.aspx"
Get-HTTPRequest "http://bc01/my/default.aspx"
Get-HTTPRequest "http://bc01/my/person.aspx"
Get-HTTPRequest "http://bc01/_vti_bin/socialdataservice.asmx"
Get-HTTPRequest "http://bc01/_vti_bin/ExcelRest.aspx/Documents/Book1.xlsx/Model"
Get-HTTPRequest "http://bc01/_vti_bin/ExcelRest.aspx/Documents/Book1.xlsx/Model/Charts(‘Chart%201′)?$format=image"
Get-HTTPRequest "http://bc01/Search/Pages/results.aspx?k=SharePoint"
Get-HTTPRequest http://bc01/Pages/Mashup-Demo1.aspx

You’ll notice the script contains a single function called Get-HTTPRequest that reads the response from each URL passed into it and displays progress via the Write-Progress command like this:

image

Feel free to re-use this as you see fit. Enjoy!

Upload Multiple Documents to SharePoint Library

Here’s my script to upload all the documents found in a particular file system folder to a SharePoint document library. You can specify the destination URL and local directory path as parameters to the script or simple enter them at runtime.

WARN16 WARNING: This script first deletes the library if it already exists – please remove this step if this is not what you want. It’s there in my script so I can rebuild my environment rapidly after ever demo…

param ([String]$spPath, [String]$localPath)

## Load the SharePoint Snapin so the script can be executed from PowerShell editor
Add-PSSnapin Microsoft.SharePoint.PowerShell –erroraction SilentlyContinue

# get the SharePoint path if not specified
if ($spPath -eq "")
{
    Write-Host
    $spPath = Read-Host -Prompt " – Please enter the fully qualified url to the destination document library"
}
# get the local path if not specified
if ($localPath -eq "")
{
    Write-Host
    $localPath = Read-Host -Prompt " – Please enter the full path to the local directory that contains the documents to be uploaded"
}
Write-Host

## Define our library name and web url
$LibraryName = $spPath.Substring($spPath.LastIndexOf("/")+1)
$webUrl = $spPath.Substring(0,$spPath.LastIndexOf("/"))

## Define a function to return the destination file name
Function BuildFileName($filename)
{
    "$webUrl/$LibraryName/" + $(split-path -leaf $filename)
}

## Delete our library – by name
$OpenWeb = Get-SPWeb $webUrl
$OpenList = $OpenWeb.Lists[$LibraryName]
if ($OpenList -ne $null)
{
    Write-Host "Deleting $LibraryName Library…."
    $OpenList.Delete()
    $OpenWeb.Update()
}

## Create our library
Write-Host "Creating new $LibraryName Library…."
$OpenWeb.Lists.Add($LibraryName, "A SharePoint document library created via PowerShell", 101) | Out-Null
$NewList = $OpenWeb.Lists[$LibraryName]
$NewList.OnQuickLaunch = 1
$NewList.Update()
$OpenWeb.Update()

## Tidy up
$OpenWeb.Dispose()

## Upload all the documents found in the specified location
Write-Host "Uploading all documents from $sourceDocs"
$webClient = New-Object System.Net.WebClient
$webClient.Credentials = [System.Net.CredentialCache]::DefaultCredentials
dir $localPath | % {
    Write-Host "Uploading: $_" -nonewline
    $fName = BuildFileName $_
    $webClient.UploadFile($fName,"PUT", $_.FullName)
    Write-Host " done" -ForegroundColor Green
    }

Create SharePoint list view sparklines from version history

Here’s my simple process to create dynamic spaklines in a SharePoint list view.

image

Unlike some of the other methods out there, this approach uses a single value column for the source of the spark line and the list item version history to plot the changes to the value over time.

Here’s how you do it.

Step1 – Create a custom list, in my example I’ve called this ‘SparkLines’

Step 2 – Add a column to contain the numeric value you wish to plot. In my example I’ve called this ‘Value’

Step 3 – Add a column to host the sparkline image. This needs to be created as a ‘Hyperlink or Picture’ column and ensure the display option for the column is set to ‘Format URL as: Picture’.

Step 4 – This step is vital – enable versioning for the list

Now that the list is set up is should look something like this:

image

Now we need to add an Event Receiver to the list to general the sparkline. The code below uses the Google Chart API to process and render the sparkline as an image that is then displayed in the browser. This technique has a few advantages and a few disadvantages. Firstly, processing for rendering the sparkline is performed by Google and not SharePoint which is good news for performance, however, it does rely on the client browser have access to the internet. Secondly, to use this technique you should note the Google Chart API terms of service: http://code.google.com/apis/chart/image/terms.html

Event Receiver Code:

/// <summary>
/// An item was updated.
/// </summary>
public override void ItemUpdated(SPItemEventProperties properties)
{
    base.ItemUpdated(properties);
    if (properties.ListTitle == "SparkLines")
    {
        string url = "https://chart.googleapis.com/chart?chs=100×25&cht=ls&chco=0077CC&chd=t:{0} SparkLine from Google Charts API";
        string values = "";

        // get the item
        SPListItem i = properties.ListItem;

        // get the ‘Value’ history
        SPListItemVersionCollection history = i.Versions;
        foreach (SPListItemVersion version in history)
        {
            if (version["Value"] != null)
            values = version["Value"].ToString() + "," + values;
        }

        // update the item
        EventFiringEnabled = false;
        i["SparkLine"] = String.Format(url, values);
        i.SystemUpdate(false); // false = do not create a new version of the item
        EventFiringEnabled = true;
    }
}

Once the event receiver is built, deployed and attached to the SparkLines list you are all set to plot sparklines via the Google Chart API.

To test the plotting I’ve used the following Powershell script that updates the first few list items with random numbers:

## Load the SharePoint Snapin so the script can be executed from PowerShell editor
Add-PSSnapin Microsoft.SharePoint.PowerShell –erroraction SilentlyContinue

## Create a random number
$random = New-Object system.random

## get the default web app
$web = Get-SPWeb http://bc01

## get the list
$l = $web.Lists | where { $_.Title -eq "SparkLines" }

$i = $l.items[0]; $i["Value"]
$i["SparkLine"].ToString()

## update the item
$i = $l.items[0]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[1]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[2]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[3]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[4]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[5]; $i["Value"] = $random.next(0,100);$i.Update();
$i = $l.items[6]; $i["Value"] = $random.next(0,100);$i.Update();

Run the script or manually update your list items a few times to build up some version history. The resultant list view should look something like this:

image

The Google Chart API is very flexible and powerful and this technique can be used to render all sorts of different effect. Examples of the types of other charting you can create can be found at http://code.google.com/apis/chart/index.html

Enjoy!

Fun with PowerPivot Previews

DISCLAIMER: Clearly this is just done for fun and not supported by Microsoft at any level !

I’m sure we all know how great the PowerPivot views are that allow us to preview the contents of Excel files without having to open each one. But have you ever wondered how they are stored and how you might be able to manipulate the previews for a bit of Friday afternoon fun?

OOB PowerPivot Gallery View:

image

Well here’s how you can modify the preview images shown in the PowerPivot views to include any images you might want to display to the PowerPivot users. Firstly, all the images are stored in the vti_Snapshots property of the SPListItem for each file in the library. This property can be inspected with the following PowerShell:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$w = Get-SPweb http://bc01/BI
$l = $w.Lists["PowerPivot"]
$i = $l.items[0] ##Get the first item in the library
$ss = $i.Properties["vti_Snapshots"]
$ss

What you’ll see from the output of this is all the preview images have been base64 encoded and concatenated into a single property. To output this property to a file and to allow us to start manipulating it, use the following PowerShell:

Out-File -FilePath c:\users\brian\snapshot.txt -InputObject $ss -Encoding ascii

This will write the contents of the vti_Snapshots property to a text file we can then edit. If you open the text file you’ll notice it is a comma separated file. I’ve split the contents of the file over several lines to make the content easier to read:

image

You’ll notice there is a reference to each of the worksheets contained in this Excel file. Additionally there is a base64 encoded string that represents each preview image for each sheet. This has been highlighted in red in the screen shot.

Now it’s time to customise the preview image. Firstly, copy on of these base64 encoded strings (a preview image), ensuring you don’t copy any trailing commas, onto the clipboard. Then using a publicly available web site, convert the string into an image file. There are many such sites that allow you to do this but I’ve been using http://www.motobit.com/util/base64-decoder-encoder.asp

If you’re using the above site, paste the base64 string into the form (1), select decode (2), and select export to binary file(3). Finally change the filename to preview.jpg (4) and press ‘Convert the data source’ (5)

image

This will convert the base64 encoded image back into binary file and download it to your browser. Save the downloaded file and you’ll then have:

image

Now edit this image file in the software of your choosing, I’ve simply inserted my head shot over the top of the existing preview image:

image

Now we are going to reverse the whole process. Using the same website we used to decode the base64 string, we’ll encode the binary file (preview.jpg). The options to take should be fairly obvious:

image

This will result in the manipulated image being base64 encoded and returned to you as a very long string. If the returned string contains any carriage returns, remove these with your favourite text editor and then copy the new base64 encoded string into the snapshot.txt file created earlier ensuring you only overwrite the original base64 encoded string we copied from this file previously, i.e. overwrite the first preview image.. Once you’ve overwrite the original image in the snapshot.txt (and removed any carriage returns you might have inserted as I have done) save this.

New base64 encoded string inserted back into snapshot.txt and carriage returns removed:

 image

Now the final step.

Use the following PowerShell to update the vti_Snapshots property of the SPListItem we originally took the preview images from:

Add-PSSnapin Microsoft.SharePoint.PowerShell -EA SilentlyContinue
$w = Get-SPweb http://bc01/BI
$l = $w.Lists["PowerPivot"]
$i = $l.items[0] ## Get the first item in the library
$ss = Get-Content -Path c:\users\brian\snapshot.txt
$i.Properties["vti_Snapshots"] = $ss.ToString()
$i.systemupdate(0)

The final result:

imageimage

I can’t think of any practical use for this but open to suggestions.

Enjoy!

Backup SharePoint web.config file with PowerShell

Just a quick post to share the technique I use to back up the web.config file of a web application. As you may know, the out of the box backup commands do not backup the web.config file for any web apps. Microsoft recommend that these are backed up separately, see http://technet.microsoft.com/en-us/library/cc261687.aspx.

So unless ALL your web.config modifications are made via Central Admin, or the API (SPWebConfigModification) your web.config file changes or those made by third party applications or solutions are not captured during a backup and are not available for restore.

To work around this issue, I’ve extended the scripts I use to perform routine scheduled Web Application backups (via Backup-SPFarm) to include a file system copy of the web.config file. The following script should be self explanatory:

$webappName = “http://bc01”
$backupDir = “C:\backups”

Write-Host "Backing up $webappName web.config file…" -foreground Gray –nonewline

## Get the web application – by display name
$w = Get-SPWebApplication | where { $_.DisplayName -eq "$webappName"}

## Get the default (first) zone for the web app…
## You may wish to iterate through all the available zones
$zone = $w.AlternateUrls[0].UrlZone

## Get the collection of IIS settings for the zone
$iisSettings = $w.IisSettings[$zone]

## Get the path from the settings
$path = $iisSettings.Path.ToString() + "\web.config"

## copy the web.config file from the path
copy-item $path -destination $backupDir\$webappName

Write-Host "done" -foreground Green

I hope this helps…

Create Random or Demo SharePoint Content with PowerShell

One of the things we need to a lot whilst creating SharePoint solutions or demonstrating its capabilities is to work with example data. However, creating reams of sample data can be quite laborious to say the least!

That’s where PowerShell comes in… We can rapidly create dozens of sample items from a very simple script. Best of all – the scripts can be used to create the document libraries or lists that hold the sample data. With a little bit of effort, the scripts can be used to prepopulate Libraries and Lists throughout an entire site collection.

The script example below is used to create two SharePoint Calendars and populate each with a dozen or so events. As we use this script in demonstrations, we’ve set it up to create the events within a 30 day window of the current date. That way, when the calendar is viewed, you’ll always see some events in the current month and some in the preceding and subsequent months.

Here’s what the script creates, two calendars populated with events:

image

Note the two calendars in the Quick Launch – Team Calendar and My Calendar – this is what we’ll be creating.

The Script

If you’re not familiar with PowerShell a great article on getting started can be found here: http://www.somewhere.com

The script example used in this article can be downloaded here: Create Calendars Script

First of all, the script is started with the Add-PSSnapin Microsoft.SharePoint.PowerShell command. This loads the SharePoint PowerShell cmdlets and allows the script to be executed directly from the PowerShell Editor.

image

Without this the PowerShell editor with throw the following error as it tries to execute SharePoint commands:

The term ‘Get-SPWeb’ is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.
At C:\Documents\SP2010\Create Calendars.ps1:42 char:21
+ $OpenWeb = Get-SPWeb <<<< $WebUrl
+ CategoryInfo : ObjectNotFound: (Get-SPWeb:String) [], CommandNotFoundException
+ FullyQualifiedErrorId : CommandNotFoundException

Now we’ve loaded the SharePoint cmdlets, the next thing we do is create a random number object; this will be used latter when we insert items into the list.

image

Next, the script sets up a list of titles to be used for the events, add as many as you want; I’ve added 26 separate items:

image

Finally, having created all our event titles, we connect to SharePoint (line 42) and delete the list defined by the $LibraryName variable if it already exists (line 43). You may not wish to do this in your scripts, but for us in a demonstration environment it’s nice to be able to drop and recreate the required lists simply by executing the same script again. Additionally, by leaving the list delete in place (line 43) you can see the speed that PowerShell runs at but simple re-running the script over and over again. ;-)

image

Having deleted any existing ‘Team Calendar’ list we simply recreate it with the following;

image

Line 47 adds the new list to the opened web and uses list template number 106 (calendar list). For a complete list of available template numbers, see: http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.splisttemplatetype.aspx. Once added, line 48 opens the list we’ve just added and line 49 ensures the list is displayed on the Quick Launch bar.

Now we’ve successfully added our new list, it’s time to insert some random items. This is done very simply by the following code:

image

Line 53 sets up our loop, and in our example loads the first 13 events only (the other 13 are loaded into a second calendar list).

The title for our event is taken from the list of event titles we defined at the start of the script. The start date for the event (EventDate) is created by taking the current date from the Get-Date function and then adding a random number of days to it (line 57). In this example, we add events anywhere from 30 days prior to 30 days subsequent from the current date. The end date for the event is calculated by adding a random number of minutes to the start date (line 59). Here we are assuming each event we add in anywhere between 30 and 90 minutes in duration. Finally, line 60 saves our new item to the calendar list.

Because our script is setup to create two calendars so we can demonstrate Calendar Overlays, I’ve skipped over lines 62 to 87 as this is just a repeat of what we’ve seen above. The only difference with the second iteration of list operations is that when we run the same commands again we change the $LibraryName to ‘My Calendar’ and there is no need to re-open the web again:

image

Finally, we need to dispose of the SPWeb object we opened earlier (line 42) to release its resources; this is done simply by calling its Dispose method:

image

The complete script is available for download here: Create Calendar Script. Feel free to modify and use it to meet you own requirements and enjoy not having to manually create sample data ever again!

I hope this helps…

Update ‘System Account’ user information on a stand alone SharePoint installation

If you’ve ever installed SharePoint in a stand alone SharePoint configuration, e.g. on a development machine on Windows 7, you’ll have noticed the user properties drop down by default shows ‘System Account’ as the current logged on user:

image

Although this does not cause any problems with the operation of SharePoint in a stand-alone install, when demonstrating SharePoint this can sometimes require explanation to the audience why System Account is displayed. It is actually displayed because the current user is logged on with the account used by the SharePoint app pool.

To change the display name show on the user properties drop down to something more friendly is easy with PowerShell:

## Load the SharePoint Snapin so the script can be executed from PowerShell editor
Add-PSSnapin Microsoft.SharePoint.PowerShell –erroraction SilentlyContinue

## get the default web app
$web = Get-SPWeb http://bc01

## get the user info list
$l = $web.Lists | where { $_.Title -eq "User Information List" }

## get the system account item
$i = $l.Items | where { $_["Account"] -eq "SHAREPOINT\system" }

## update to a friendly name
$i["Title"] = "Brian Cartmel"

## update e-mail address
$i["Work e-mail"] = "your.email@your.domain.com"

## update the item
$i.Update()

This results in the above being shown…

image

…and the following being updated in the into the (hidden) User Information list:

image

I hope this helps.

Follow

Get every new post delivered to your Inbox.