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…

Follow

Get every new post delivered to your Inbox.