Powershell Archives » Paul Ryan

User photos in Office 365

The user photo story in Office 365 is not so straight forward. Photos are stored in Active Directory (AD) on-premises, Azure Active Directory (AAD), Exchange Online (EXO), SharePoint Online (SPO), and at first appearances possibly elsewhere as well (where does my Delve profile picture live, what about my Skype for Business (SfB) avatar?).

I have put together a flow diagram to represent how this actually works. It aims to demonstrate where user photos are stored and where different applications fetch user photos from (if they don’t store the images), and leads to some recommendations about user photo synchronisation.

Please note the date of this article (August 2016) and be conscious that Office 365 is changing rapidly and the following recommendations may have changed (e.g. Prior to the Delve user profile page, the SharePoint user profile page referenced images stored in SharePoint rather than Exchange. Changes such as these will continue to evolve).

User photos: the diagram

Where applications store and fetch user photos

Photo Location	Comments	Size	Is source?
On-premises AD DS in the thumbnailPhoto attribute	100Kb maximum	Recommended to be 96×96 or 48×48	Yes
Azure AD in the thumbnailPhoto attribute	100Kb maximum Usually synced from AD DS via Azure AD Connect	Recommended to be 96×96 or 48×48	No. Sync from AD
Exchange Online as property of the mailbox	500Kb Provided manually by users or a bulk import can be scripted if source photos can be located and named appropriately. If not provided, Exchange will reference the AAD thumbnailPhoto in some instances but only if the thumbnailPhoto is less than 10Kb. Does not sync back to AD	Recommended to be 648×648	Yes
SharePoint Online ‘User Photos’ library	Three renditions of the EXO photo are automatically created in SharePoint after upload to EXO. It generally takes up to 72 hours to see changes to EXO photo here. Sometimes we see that a user must ‘touch’ their profile before the sync will be performed. NOTE: Updating user profile photo via Delve profile is actually updating EXO profile photo and not performing any actions directly in SharePoint Online.	Small is 48 x 48, Medium is 72 x 72, Large changes depending on the source image but is always square. I have seen as small as 120 x 120 and as large as 300 x 300. PnP image upload solution uploads these as 200 x 200.	No. Sync from EXO
Skype for Business	Does not store any images Uses the high resolution Exchange image if available, otherwise uses the AD thumbnailPhoto	EXO image or AD thumbnailPhoto	No. Read from EXO
Delve user profile	Does not store any images Uses the high resolution Exchange image if available, otherwise uses the AD thumbnailPhoto	EXO image or AD thumbnailPhoto	No. Read from EXO
Yammer	Also stores its own photo. Out of scope of this discussion for now.		Yes

Likely issues and resolutions

Issue

Resolution

Exchange Online user photo is low quality (and in turn so is the SPO photo and SfB photo)

The source image coming from AD was/is low quality.

EXO user photos can be updated by users individually or if high res source photos are available this import can be scripted.

Source images should be jpg of 648×648 (resizing and compression can also be scripted)

Exchange Online user photo is high quality but SfB photo is low quality

High resolution photos from Exchange will be used as long as both Exchange and Sfb/Lync are of new enough versions (2013 or greater) and SfB is configured to allow all photos (not just those from AD).

NB. If a user doesn’t have a mailbox (e.g. not licenced) then they will be displayed using the AD photo

There is no Exchange Online user photo (and in turn there is no SPO photo or SfB photo)

A photo has not been imported to the user’s EXO mailbox and the AAD thumbnailPhoto either doesn’t not contain an image or that image is greater than 10Kb.

Import of photos up to 500Kb to EXO mailbox can be scripted (the source images could be on a file share, or AAD).

Changes to user photos are reflected quickly in Exchange and Skype but take days to replicate to SPO

Exchange to SPO synchronisation is a periodic process and can take up to 72 hours.

A custom solution can perform this replication on demand (e.g. at the same time EXO user photos are set)

User photos changed in other systems which update AD are not reflected in EXO, SPO, SfB.

E.g. A user in an on-premises SharePoint farm updates their user photo

When AD is updated, it is synchronised with AAD but that is as far as it gets as the “sync” from AAD to EXO is one-off import rather than a Sync.

Unlikely to be desirable to create a custom sync relationship here as users will want to be able to update EXO directly and won’t want their photo’s overwritten

User photos updated in EXO aren’t replicated to other systems which share an AD.

E.g. An on-premises SharePoint farm

The user photo in EXO is not synched back to AD – it can’t be consistently as the AD thumbnailPhoto attribute only supports photos up to 100Kb where EXO supports larger images.

Potential for a custom solution to sync images back to AD after having resized/compressed them to <100kb – However general recommendation is that AD thumbnailPhoto optimal size is 10Kb and 96×96.

Recommendations

Use Exchange user photos as the master. Allow users to update their user photos but pre-populate their user photo if possible and before end users are provided any access to the system.

If high resolution photos are available, script import of high resolution photos (648×648) to Exchange Online (see Set-UserPhoto and this and sample script below). These will then be visible in Exchange, in Skype, and, once processed, in SharePoint Online. In a dispersed environment this may have to be managed by many teams rather than trying to compile a single list of all user photos.

Users may then update their user profile photo directly via Outlook or indirectly via their Delve profile.

If synchronisation back to AD is required in order serve other applications (e.g. an on-premises SharePoint farm) then a custom solution could provide synchronisation from EXO to AD but this process should compress and shrink images as the recommended size of thumbnailPhoto images is only 96×96 and 10Kb.

Sample usage of the Set-UserPhoto cmdlet

# UPDATE THESE VARIABLES $userDomain = "@tenant.onmicrosoft.com" $photoFolderURL = "C:\Users\paul.ryan1\Desktop\people\" $data = @( @{ User = "alex.law" + $userDomain; PictureURL = "cast-aaron-j-albano.jpg"; }, @{ User = "david.bowman" + $userDomain; PictureURL = "cast-richard-todd-adams.jpg"; }, @{ User = "matthew.holden" + $userDomain; PictureURL = "cast-quentin-earl-darrington.jpg"; }, @{ User = "alex.dorrian" + $userDomain; PictureURL = "cast-christopher-gurr.jpg"; }, @{ User = "catherine.conroy" + $userDomain; PictureURL = "cast-emily-tate.jpg"; }, @{ User = "sandy.truong" + $userDomain; PictureURL = "cast-samantha-sturm.jpg"; }, @{ User = "claire.rowe" + $userDomain; PictureURL = "cast-lili-elizabeth-froehlich.jpg"; }, @{ User = "christian.ramirez" + $userDomain; PictureURL = "cast-zachary-downer.jpg"; }, @{ User = "paul.ryan" + $userDomain; PictureURL = "cast-tyler-hanes.jpg"; }, @{ User = "tim.wallis" + $userDomain; PictureURL = "cast-colin-cunliffe.jpg"; }, @{ User = "chris.obrien" + $userDomain; PictureURL = "cast-andy-huntington-jones.jpg"; }, @{ User = "sarah.johnson" + $userDomain; PictureURL = "cast-claire-rathbun.jpg"; }, @{ User = "ben.athawes" + $userDomain; PictureURL = "cast-marc-heitzman.jpg"; } ) # SCRIPT STARTS HERE $MSOLCred = Get-Credential $ExSession = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/?proxymethod=rps -Credential $MSOLCred -Authentication Basic -AllowRedirection $Import = Import-PSSession $ExSession -CommandName Set-UserPhoto -AllowClobber Write-Host "Uploading user photos:" $count = 0 try { $data | ForEach { Write-Host "$($_.User)..." -nonewline try { Set-UserPhoto -Identity $_.User -PictureData ([System.IO.File]::ReadAllBytes($photoFolderURL + $_.PictureURL)) -Confirm:$false Write-Host " done" -f green $count++ } catch [System.Exception] { Write-Host " failed!" -f red } } } finally { Remove-PSSession $ExSession } Write-Host "Successfully uploaded $count photos"

Paul.

Convert an existing plain text note field/column to rich text

If you create a SharePoint site column (a note field in this case), associate it with a site content type, and then associate that content type with a list in a sub site, the site column will be available on that library. Obviously right?

However, when you update the site column (and push all changes to lists and libraries) not *all* of the changes you make are in fact pushed down. An example of this is the setting that dictates whether a note field should allow rich text or enforce plain text. If you change this setting at the site column level it will *not* propagate to libraries which already exist. New instances of the column (say if you associated the content type with a list for the first time) will be configured correctly, but existing list-level instances are not updated. NOTE: This is only true for properties specific to particular column type; common properties such as ‘required’ will be pushed down to existing instances of the column at the list level.

Configuring a SharePoint note field to support rich text — Configuring a SharePoint note field

So you want to change a list-level instance of a plain text note column to a rich text note column (or vice-versa, or otherwise change column specific properties or another field type)? You need to do it for every list where the column is in use. That would be very tedious to do via the SharePoint UI, but you can’t anyway. The UI only supports changing the set of common field properties (type, required, hidden, etc).

In comes PowerShell. Below you will find a script which updates a plain text note column to be a rich text note column. It is important to note that this script only updates the list-level columns and not the site column. This means that after running the script, new instances will continue to inherit the site column configuration.

The script takes advantage of recursion using delegate functions which is an approach I blogged about here: PowerShell Recursion with Delegate Functions

Credit also to Chris O’Brien’s topofscript.ps1 for the CSOM integration bit: Using CSOM in PowerShell scripts with Office 365

The script is written for SharePoint Online (and assumes that the SharePoint Online Client Components SDK is installed) but for this to work on-premises you would only need to update the referenced assemblies (v15 for 2013) and modify the code which passes the credentials.

# Parameters $siteCollectionUrl = "https://<tenant>.sharepoint.com/sites/<site collection>" # UPDATE $username = "<username>" # UPDATE $listTitle = "Pages" # UPDATE $fieldName = "Comments" # UPDATE # From here on, errors should stop the script $ErrorActionPreference = "stop" # Utility function function foreachDecendentWeb( [Parameter(Mandatory=$true)] [Microsoft.SharePoint.Client.Web]$web, [Parameter(Mandatory=$true)] [Action[Microsoft.SharePoint.Client.Web]]$webAction) { # Load child webs $context = $web.Context $childWebs = $web.Webs $context.Load($childWebs) $context.ExecuteQuery() # Iterate child webs foreach($childWeb in $childWebs) { # Perform action on child web $webAction.Invoke($childWeb) # Iterate child-child webs foreachDecendentWeb $childWeb $webAction } } function Prompt-Password ($username) { [string]$password = read-host "Provide password for $username" if($password.length -lt 1) { LogError "You must provide a password" Exit } return $password } # Work to be done at each web [Action[Microsoft.SharePoint.Client.Web]]$updateListField = { param([Parameter(Mandatory=$true)]$web) write-host "" write-host "Found web: " -nonewline write-host "$($web.Title)" -f green # Fetch list fields write-host "Fetching list fields..." $list = $web.Lists.GetByTitle($listTitle) $fields = $list.Fields $ctx.Load($fields) $ctx.ExecuteQuery() # Find specific field write-host "Finding field..." $field = $fields | ?{$_.InternalName -eq $fieldName} # Update field properties write-host "Updating field properties..." $field.set_RichText($true) $field.set_RestrictedMode($false) $field.set_AllowHyperlink($true) $field.Update() $ctx.Load($field) $ctx.ExecuteQuery() write-host "Done!" } write-host "*Script: Update site description field to support rich text*" write-host "Site collection: $siteCollectionUrl" -f green [string]$password = Prompt-Password $username # Import CSOM assemblies write-host "Importing CSOM assemblies..." Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll" Add-Type -Path "c:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" # Authenticate write-host "Authenticating to SharePoint Online..." $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($siteCollectionUrl) $securePassword = ConvertTo-SecureString $password -AsPlainText -Force $credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword) $ctx.Credentials = $credentials # Perform action on root web $rootWeb = $ctx.Web $ctx.Load($rootWeb) $ctx.ExecuteQuery() $updateListField.Invoke($rootWeb) # Perform action on every descendent web foreachDecendentWeb $rootWeb $updateListField write-host "" write-host "All done!" -f green

Paul.

PowerShell recursion with delegate functions, iterate all lists in all webs

There are many ways to iterate a collection in PowerShell. I just really like using delegate functions. This approach is not native PowerShell but utilises the .NET Action class as a function parameter. Using a delegate function approach, it is possible to create a recursive loop that can be very easily reused in the future just by providing an alternative Action.

The example code I provide below demonstrates how to create a delegate function in PowerShell, how to write a function that accepts one as a parameter, and provides some ready made samples for iterating SharePoint objects, specifically all webs or all lists. I am using some specific SharePoint objects in these samples, however the fundamental pattern can be used to effectively iterate any recursive structure.

foreachDecendentWeb : perform an action on every web below the provided web
foreachListInWeb : perform an action on every list in the provided web
foreachListInWebAndAllDecendentWebs : perform an action on every list in the current and all decendent webs

Some notes

The below script references ‘TopOfScript.ps1’, it is specifically related to calling SharePoint CSOM from PowerShell. Read about it here on sharepointnutsandbolts.

Making the call, providing the delegate

# See Chris O'Brien's blog on using CSOM in PowerShell for # more information on 'TopOfScript'. In essence this just # defines the $clientContext object. # http://www.sharepointnutsandbolts.com/2013/12/Using-CSOM-in-PowerShell-scripts-with-Office365.html . .\TopOfScript.ps1 # Load root web $rootWeb = $clientContext.Site.RootWeb $clientContext.Load($rootWeb) $clientContext.ExecuteQuery() # Define the work that you want done here [Action[Microsoft.SharePoint.Client.List]]$printListName = { param([Parameter(Mandatory=$true)]$list) Write-Host "$($list.Title)" } # Kick it off! foreachListInWebAndAllDecendentWebs $rootWeb $printListName

The utility scipts, recursive functions accepting delegate parameters

function foreachDecendentWeb( [Parameter(Mandatory=$true)] [Microsoft.SharePoint.Client.Web]$web, [Parameter(Mandatory=$true)] [Action[Microsoft.SharePoint.Client.Web]]$webAction) { # Load child webs $context = $web.Context $childWebs = $web.Webs $context.Load($childWebs) $context.ExecuteQuery() # Iterate child webs foreach($childWeb in $childWebs) { # Perform action on child web $webAction.Invoke($childWeb) # Iterate child-child webs foreachDecendentWeb $childWeb $webAction } } function foreachListInWeb( [Parameter(Mandatory=$true)] [Microsoft.SharePoint.Client.Web]$web, [Parameter(Mandatory=$true)] [Action[Microsoft.SharePoint.Client.List]]$listAction) { # Load lists $context = $web.Context $lists = $web.Lists $context.Load($lists) $context.ExecuteQuery() # Iterate lists $listCount = 0 foreach($list in $lists) { # Perform action on child web $listAction.Invoke($list) $listCount++ } return $listCount } function foreachListInWebAndAllDecendentWebs( [Parameter(Mandatory=$true)] [Microsoft.SharePoint.Client.Web]$web, [Parameter(Mandatory=$true)] [Action[Microsoft.SharePoint.Client.List]]$action) { # Define per-list action [Action[Microsoft.SharePoint.Client.List]]$perListAction = { param([Parameter(Mandatory=$true)]$list) $action.Invoke($list) } # Define per-web action [Action[Microsoft.SharePoint.Client.Web]]$perWebAction = { param([Parameter(Mandatory=$true)]$web) foreachListInWeb $web $perListAction } # Perform action on root web $perWebAction.Invoke($rootWeb) # Perform action on every decendent web foreachDecendentWeb $rootWeb $perWebAction }

Paul.

PowerShell Add-Type Error: Could not load file or assembly XXX or one of its dependencies.

If you are attempting to run the PowerShell command Add-Type with a Path parameter that references an assembly which has been emailed or downloaded from the internet (e.g. Add-Type -Path "C:\Microsoft.SharePoint.Client.dll") then it is likely that you will encounter the following error:

Could not load file or assembly ‘file:///’ or one of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515)

To confirm that the following steps are appropriate. Check the inner exception:

PS> $error[0].Exception.InnerException

“An attempt was made to load an assembly from a network location which would have caused the assembly to be
sandboxed in previous versions of the .NET Framework. This release of the .NET Framework does not enable
CAS policy by default, so this load may be dangerous. If this load is not intended to sandbox the assembly,
please enable the loadFromRemoteSources switch. See http://go.microsoft.com/fwlink/?LinkId=155569 for more
information.”

Not using SharePoint Client Components?

First check the execution policy under which PowerShell is running. This can be done by running the Get-ExecutionPolicy cmdlet. If you are not running under an Unrestricted policy, try running the Add-type cmdlet after setting the execution policy to Unrestricted (Set-ExecutionPolicy “Unrestricted”). If this resolves the issue then you’ll need to look into getting the assembly signed by a trusted publisher if you can’t continue to run PowerShell in this lowered state of security.

Next ensure that the assembly hasn’t been blocked. The easiest way to achieve this is by right-clicking on the assembly and checking on the General tab that there isn’t the option to unblock the assembly at bottom of the dialog. There is also the Unblock-File cmldlet to achieve this action via PowerShell.

Finally, PowerShell supports a config file which by default is not present. It allows configuration of a few things like the .NET versions which are supported and, importantly for us, whether or not PowerShell is allowed to load from remote sources. In order to add/update this file, ensure that you are logged in as a local administrator. The config file needs to reside adjacent to the PowerShell exe file. So, by default, it ought to be created here: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe.config

Note: Do not confuse this with powershell_ise.exe.config although you may want to update this as well if you use ISE.

The content of the powershell.exe.config should be as follows. If you already have a file present, ensure that you merge the XML rather than just replacing it.

Using SharePoint Client Components?

If you are adding the SharePoint Client assemblies required for the execution of CSOM requests then you should take a different approach. Rather than bundling these assemblies with your script, instead insure that the correct SharePoint Client Components package is installed on the executing machine and reference the assemblies from their default location:

Download Client Components:
SharePoint Online Client Components
SharePoint 2013 Client Components

Reference Accordingly:
C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\assembly.dll
C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\assembly.dll

Paul.

SPO CSOM Error: For security reasons DTD is prohibited in this XML document

I found myself encountering the following error when authenticating to SharePoint Online using CSOM from PowerShell:

Exception calling “ExecuteQuery” with “0” argument(s): “For security reasons DTD is prohibited in this XML document. To enable DTD processing set the DtdProcessing property on XmlReaderSettings to Parse and pass the settings into XmlReader.Create method.”

I believe that there a number of causes for this issue some of which are firewall and ISP related. This may only resolve a subset of the cases where this issue has been arising, even under the same circumstances.

In my scenario, I found that this issue was only arising when the credentials I was passing were being federated. That is, when the username was *not* in form <me>@<domain>.onmicrosoft.com but rather something like <me>@<domain>.co.uk. It is also possible that this issue resolves itself after a single successful authentication has occurred. Try providing credentials for a *.onmicrosoft.com account, and if that works try again with a federated account. This is discussed more later.

I used Fiddler to compare the request/response trace from a successful authentication and one where this error occurs. It turns out that somewhere internally a request is made to msoid.<full-domain> where <full-domain> is the bit after the @ symbol from the username provided. In the case where this value is of the *.onmicrosoft.com variety, a 502 error (no DNS entry) is returned with no request body and the authentication proceeds successfully. In the other case, the ‘msoid’ URL is resolved and a response with a request body is returned.
In my case the response was a 301 error (permanent relocation), however I read of cases where a 200 (success) has been received. Importantly to note, is that the response, success or otherwise, returns an HTML body containing a DTD (Document Type Declaration), and in turn produces the rather unhelpful error message.

So how do you fix it? Well one way is to provide an entry in your hosts file which ensure that the msoid URL will be invalid. I found that providing a local host entry for it worked. Your hosts file can be found here:
C:\Windows\System32\drivers\etc

I added a line which looks like the following:

127.0.0.1 msoid.<domain>.co.uk

And it worked! Intriguingly I found that if I then removed this line from my hosts file, SharePoint Online authentication from PowerShell continued to work. It is for this reason that I suggested trying to use a *.onmicrosoft.com account first at the begging of this post – just in case it resolves the issue for you without touching the hosts file. Please comment if you have any success (or otherwise) with that approach.

Hope this helps! Good luck.

Web Event Receivers: Attach using PowerShell

The following is a simple PowerShell script for attaching SharePoint web event receivers. It is clever enough to check if they have already been attached and hence to avoid duplication.

There are no “gotchas” or anything really worth noting here. This example is pretty much the same as the dozens of list item event receiver examples out there, just targeting a different event receiver type. I am only posting it because I was so surprised that I had to write it myself after failing to find an example to ‘steal’ from the internet – maybe I wasn’t looking hard enough.

The first half of the script is the bit you’ll be interested in (if you are interested in any of it!), the second half is just a usage example.

Cancel all workflows in site collection efficiently

Here you will find a script to cancel all list workflows (not site workflows) running in a given site collection in the most efficient manner possible (that I can think of) while still only using the SharePoint API. A better approach still would be to query the SharePoint workflows SQL table to identify the running workflows so as to avoid iterating all site collection webs and lists. Unfortunately there is no ‘GetAllRunningWorkflows(SPSite)’ method available via the API. I imagine that this approach should be satisfactory in the majority of cases though.

There are a number of posts on the web with code somewhat similar to this, at least with code that aims to achieve the same outcome. Of all the posts which I found they all performed this function in a very inefficiency manner, iterating the SPListItemCollection for every list in the site collection. This may be fine in many circumstances but I wanted something that would run faster with less strain on the server.

I have achieved this by checking for workflow associations on a list before iterating the items as well querying the list items and where a workflow column exists checking to see if the list item needs to be returned at all. When returning the list items, I am querying with ViewFieldsOnly so that less data is returned.

This script also accepts an optional parameter that specifies which workflow associations should be canceled if you are not looking to cancel all of the workflow associations but just those of a specific name.

NB: The script contains a reference to a help function GetNestedCaml which I have defined in a separate post which can be found here.

As the script sample is quite large I suggest clicking the ‘view raw’ link at the bottom of the sample to view it.

param( [string]$siteCollectionUrl = $null, #"http://hostname", [array]$workflowAssociationNames = @(), # pass $null to cancel all [bool]$verbose = $true, [array]$workflowStatesToCancel = @(2,6,7) ) #Status Value #Not Started 0 #Failed on Start 1 #In Progress 2 #Error Occurred 3 #Canceled 4 #Completed 5 #Failed on Start (retrying) 6 #Error Occurred (retrying) 7 #Canceled 15 #Approved 16 #Rejected 17 Write-Host "Canceling workflows in the site collection..." # Verify parameters if([string]::IsNullOrEmpty($siteCollectionUrl)) { Write-Host "-siteCollectionUrl parameter is required" -F Red return } if($workflowAssociationNames -ne $null ` -and $workflowAssociationNames.length -lt 1) { $errMsg = "-workflowAssociationNames parameter must be provided. " Write-Host "$errMsg" -F Red return } # Iterate all webs $site = Get-SPSite $siteCollectionUrl $allWebs = $site.AllWebs foreach($web in $allWebs) { try { # Iterate all lists $lists = $web.Lists for($listIndex = 0; $listIndex -lt $lists.Count; $listIndex++) { $list = $lists[$listIndex] # Get the collection of workflow associations with running instances $was = @() foreach ($wa in $list.WorkflowAssociations) { if($wa.RunningInstances -gt 0) { $was += $wa } } # Check if the list has the workflow we want $checkList = $false $tempWorkflowAssociationNames = @() if($workflowAssociationNames -ne $null) { foreach($wa in $was) { if($workflowAssociationNames -contains $wa.Name) { $tempWorkflowAssociationNames += $wa.Name } } $checkList = $true } else { foreach($wa in $was) { $tempWorkflowAssociationNames += $wa.Name $checkList = $true } } # Only investigate list if it has workflow associations if($checkList) { # Query for items that have a column for the workflow # where the column value is not null or empty not is # in the $workflowStatesToSkipCancel array. $spQuery = New-Object Microsoft.SharePoint.SPQuery $spQuery.ViewAttributes = "Scope='Recursive'" $spQuery.RowLimit = 2000 $spQuery.ViewFieldsOnly = $true $spQuery.ViewFields = "<FieldRef Name='FileLeafRef'/>" # Map workflow association name to column name $failedToMapToColumns = $false $fieldInternalNames = @() foreach($wan in $tempWorkflowAssociationNames) { try { $field = $list.Fields.GetField($wan) $fieldInternalNames += $field.InternalName } catch [Exception] { #Write-Host "Failed to find field $wan on list $($list.Title)" -F Red $failedToMapToColumns = $true } } # Only attempt to query if we have a field to query against if($fieldInternalNames.Length -gt 0 -or $failedToMapToColumns) { # Build caml query to get items with running workflows if(!$failedToMapToColumns) { $camlFrags = @() foreach($fin in $fieldInternalNames) { foreach($state in $workflowStatesToCancel) { $camlFrags += "<Eq><FieldRef Name='$fin' />" $camlFrags += "<Value Type='WorkflowStatus'>$state</Value></Eq>" } } $caml = GetNestedCaml $camlFrags "Or" $caml = "<Where>$caml</Where>" $spQuery.Query = $caml } else { $spQuery.Query = "" } do { # Iterate all items with workflows that we wish to cancel $listItems = $list.GetItems($spQuery) $spQuery.ListItemCollectionPosition = $listItems.ListItemCollectionPosition for($itemIndex = 0; $itemIndex -lt $listItems.Count; $itemIndex++) { try { $item = $listItems[$itemIndex] $wfGuids = @() foreach ($workflow in $item.Workflows) { $wfpan = $workflow.ParentAssociation.Name if($tempWorkflowAssociationNames -contains $wfpan) { $wfGuids += $workflow.InstanceId } } foreach ($wfGuid in $wfGuids) { # Ensure that the workflow is in a 'running' state $workflow = $item.Workflows[$wfGuid] $wfState = [Microsoft.SharePoint.Workflow.SPWorkflowState]::Running $isWfInState = ($workflow.InternalState -band $wfState) -eq $wfState if($isWfInState) { #Cancel Workflows [Microsoft.SharePoint.Workflow.SPWorkflowManager]::CancelWorkflow($workflow) if($verbose) { Write-Host "Workflow canceled: $($workflow.ParentAssociation.Name) on $($item.Name)" } } } } catch [Exception] { Write-Host "Error: $($item.ServerRelativeUrl) $($Error[0])" -F Red } } } while ($spQuery.ListItemCollectionPosition -ne $null) } } } } catch [Exception] { Write-Host "Error: $($web.Url) $($Error[0])" -F Red } finally { $web.Dispose() } } $site.Dispose() Write-Host "DONE" -F Green

Dynamically nesting CAML query statements

Here is a short PowerShell function that can be used when you need to dynamically generate CAML queries with many logically joined statements. I actually adapted it from a C# implementation I wrote (which is probably more useful…) but as you can rewrite it in C# very easily I won’t bother bother posting it twice.

As CAML logical join operators (And, Or) can only compare two statements, when many statements need to be compared you must nest them which is what this function achieves. The $join parameter should be passed as “And” or “Or” and the $fragments parameter should be passed as an array of CAML statement strings such as:
@("<Eq><FieldRef Name='Title' /><Value Type='Text'>title</Value></Eq>", "<Eq><FieldRef Name='FileLeafRef' /><Value Type='Text'>name.docx</Value></Eq>")

Deployment failure causing Get-SPSite to fail

I run a scripted deployment process each time new (SharePoint) solutions are ready for QA, or any environment for that matter. We script a series of commands, specifically:
Uninstall-SPSolution, Remove-SPSolution, Add-SPSolution and finally Install-SPSolution.

Before running these commands, along with a number of other checks, we perform Get-SPSite to ensure the site is available and fail early if need be. If our custom solution has not been deployed then viewing any page under any of the web application’s site collections fails with an exception due to a failure to find one of the solution assemblies. This is because we have custom membership and claims providers which are defined in the solution assembly and are referenced in the web.config for the web application. Despite this, any SPSite and SPWeb objects can be obtained safely via PowerShell as forms authentication is not taking place.

So I was surprised to find that the Get-SPSite check failing during deployment today with a failure to locate the assembly containing the membership and claims providers. I did not discover the root cause as to why this suddenly occurred but will outline what it took to fix it.

In the end I was able to ” Install-SPSolution -force ” to recover from this situation but not before stopping and starting the SharePoint Administration Service across all Web servers in the farm. The service was not stopped on any of the machines however the Install job was never completing despite the timer job history in Central Administration stating that the job had been successful. Upon restarting all this service the Install-SPSolution command would then complete.

Credit to Andreas’ blog for putting me in the right direction.

Scripting WSP deployment

I won’t take this opportunity to evangelise the benefits of scripted deployments, I’m going to assume that you already do it (as you should be!) and provide a tiny bit of script that will identify when your deployment doesn’t run quite so smoothly. Before I do, I’ll briefly mention my experience as to why a deployment may fail.

I have found that the by far the primary reason that a SharePoint full-trust deployment fails is that one or more of the assemblies being deployed is locked in the GAC and cannot be removed/replaced. An ISSRESET fixes this in the majority of cases (consider performing a remote ISSRESET across all the farm SharePoint servers as part of your deployment process. Note to self: future blog topic…) and in the remainder of cases stopping related services (v4 Timer, SSRS etc) on the affected server will release the assembly. The easiest way to identify the servers at which the deployment failed is via CA:

WSPs are deployed using a timer job. When performing deployment actions we need to wait on the timer job and upon it completing then verify the deployment status of the solution is as we expect. If we don’t wait for the action and ensure that it has run successfully we run this risk of not detecting a failed to deployment until we attempt to access the site. This can be a real time sink if we run lengthy scripts after deployment or are scripting the deployment a number of solutions in succession. The following snippet shows how easy it is to achieve this in PowerShell.

# Define function to wait for solution job to end function WaitOnSPSolutionJob($solution) { while ($solution.JobExists) { write-host "." -nonewline sleep 4 $solution = Get-SPSolution $solution.Name -ea SilentlyContinue } return $solution } # Uninstall solution $solution = Get-SPSolution $solutionName -ea SilentlyContinue if($solution -ne $null) { $solution | Uninstall-SPSolution -AllWebApplications -Confirm:$false Remove-SPSolution -Identity $solutionName -Confirm:$false $solution = WaitOnSPSolutionJob($solution) if($solution.Deployed) { throw "Failed to remove solution" } } # Deploy Solution $solution = Add-SPSolution "$solutionPath\$solutionName" Install-SPSolution $solutionName ` -WebApplication $webApplication ` -GACDeployment:$($solution.ContainsGlobalAssembly) ` -CASPolicies:$($solution.ContainsCasPolicy) -force $solution = WaitOnSPSolutionJob($solution) if(!$solution.Deployed) { throw "Failed to deploy solution" }

May your deployment failures be immediately detected.