Friday, March 2, 2018

How to implement "GetItemsWithUniquePermissions" through PowerShell and CSOM

In C# or JavaScript, it's easy. As this link shows, we can do it through the script below:

var items = list.GetItems(CamlQuery.CreateAllItemsQuery());
ctx.Load(items, col => col.Include(i => i.HasUniqueRoleAssignments));
int itemCount = items.Where(i => i.HasUniqueRoleAssignments).Count;

However, can we do similar thing in PowerShell by ONE "ctx.ExecuteQuery()" submit?

The answer is YES.

Below is the script.

$query = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery()
$items = $list.GetItems($query)

$items | %{

foreach($item in $items){
if ($item.HasUniqueRoleAssignments){
# your code here

If there are too many items in the list, we may see the error message:

"The request message is too big. The server does not allow messages larger than 2097152 bytes"

Based on my test, 1000 items is fine. In that case, we need to do it in batches. Below is the script.

$Global:_BatchRowLimit = 1000
$caml = ""
$viewFields = ""
$position = $null
$allItems = @()

$camlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
$camlQuery.ViewXml = "$caml$viewFields$Global:_BatchRowLimit"
$camlQuery.ListItemCollectionPosition = $position

$listItems = $list.getItems($camlQuery)

$listItems | %{

$position = $listItems.ListItemCollectionPosition
$allItems += $listItems
Until($position -eq $null) 

Friday, February 16, 2018

SharePoint 2016 patch installation failure caused by Custom Tiles

During the installation of the latest patch, The Configuration Wizard throw out an error as below:


Failed to upgrade SharePoint Products.
An exception of type Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException was thrown.  Additional exception information: 
Feature upgrade action 'CustomUpgradeAction.AddSwitchField' threw an exception upgrading Feature 'CustomTiles' (Id: 15/'68642d38-a556-4384-888c-082844fbf224') in WebApplication 'SharePoint - 80': List |0

Feature upgrade incomplete for Feature 'CustomTiles' (Id: 15/'68642d38-a556-4384-888c-082844fbf224') in WebApplication 'SharePoint - 80'. Exception: List |0

Feature upgrade action 'CustomUpgradeAction.AddSwitchField' threw an exception upgrading Feature 'CustomTiles' (Id: 15/'68642d38-a556-4384-888c-082844fbf224') in WebApplication 'SharePoint - SPTest': List |0

Feature upgrade incomplete for Feature 'CustomTiles' (Id: 15/'68642d38-a556-4384-888c-082844fbf224') in WebApplication 'SharePoint - SPTest'. Exception: List |0

Upgrade completed with errors.  Review the upgrade log file located in C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\LOGS\Upgrade-20180216-083525-624-c026758ad0924bb8ae1431288b75f172.log.  The number of errors and warnings is listed

Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException: Exception of type 'Microsoft.SharePoint.PostSetupConfiguration.PostSetupConfigurationTaskException' was thrown.
   at Microsoft.SharePoint.PostSetupConfiguration.UpgradeTask.Run()
   at Microsoft.SharePoint.PostSetupConfiguration.TaskThread.ExecuteTask()


Google quickly leads me to this link, which says:

"CustomTiles is a standard SharePoint Feature. It's neither missing nor faulty. It seems that the feature upgrade code has a bug though. The upgrade doesn't work if the hidden CustomTiles lists have never been created. These lists get created when you enable the feature. So what you have to do is enabling the CustomTiles feature on every web application in your farm.
You can do so using Powershell: Enable-SPFeature -Identity CustomTiles -Url UrlOfYourWebApplication -Force 
After enabling the feature (which creates the hidden list) the upgrade worked for us without any errors. 
If you want to know more about CustomTiles before enabling the feature see this TechNet article: "

Now things are easy to handle. I wrote some PowerShell script to resolve it:

# resolve the "Custom Tiles" error
$WebApplicationUrlObjects = @(Get-SPWebApplication -IncludeCentralAdministration | Select Url)
foreach ($url in $WebApplicationUrlObjects){
    Enable-SPFeature -Identity CustomTiles -Url $url.Url -Force

# upgrade content database schema
Get-SPWebApplication -IncludeCentralAdministration | Get-SPContentDatabase | ?{$_.NeedsUpgrade –eq $true} | Upgrade-SPContentDatabase -Confirm:$false

This script needs to be run between the installation of the new patch and "SharePoint 2016 Products Configuration Wizard".

Thursday, February 8, 2018

How to handle "429" error in PowerShell

Sometimes we got error "The remote server returned an error: (429) Too Many Requests", when accessing SharePoint Online through PowerShell script.

Below is how I handle it:

$Global:_retryCount = 1000
$Global:_retryInterval = 10

for($retryAttempts=0; $retryAttempts -lt $Global:_retryCount; $retryAttempts++){
Catch [system.exception]{
Start-Sleep -s $Global:_retryInterval

Friday, January 19, 2018

Simple way to get absolute URL of a list object through PowerShell and CSOM

There is no absolute URL property in list object.

Below is the relevant attribute values:

$oList.RootFolder.ServerRelativeUrl: /sites/SPAdmin/Lists/testList1
$oList.ParentWebUrl: /sites/SPAdmin

So, we can get the url here:

$url = $oWeb.Url + $oList.RootFolder.ServerRelativeUrl.Replace($oList.ParentWebUrl, "")

The result is:

Hope this script saves you a few minutes.

[update 20180123]

If the user account has SharePoint admin rights, we can do it through tenant "RootSiteUrl" property.

$oTenant = New-Object Microsoft.Online.SharePoint.TenantAdministration.Tenant($ctx)
$Global:_RootSiteUrl = $oTenant.RootSiteUrl
$url = $Global:_RootSiteUrl + $oList.RootFolder.ServerRelativeUrl

Friday, November 3, 2017

How to check available properties of CSOM client object in PowerShell?

The script is quite simple, but it took me quite a while to figure it out.

The variable "$obj" could be any client object, such as "web", "content type", etc.

$ | ?{$obj.IsPropertyAvailable($_.Name)} | %{
 Write-Host "$($_.Name): $($_.Value)"

Wednesday, October 18, 2017

Change DocumentID prefix through PowerShell script

Four and a half years ago, I submitted a post about how to change DocumentID prefix manually for a single document.

Eventually I realised it's convenient to use site collection path name as the DocumentID prefix. However, if users want to change the site collection name, then we have to refresh the DocumentID for all documents.

Here is about how to do that through PowerShell for multiple site collections.

$ver = $host | select version
if ($ver.Version.Major -gt 1)  {$Host.Runspace.ThreadOptions = "ReuseThread"}
Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
Add-PSSnapin Microsoft.Office.DocumentManagement -ErrorAction SilentlyContinue

Set-StrictMode -Version Latest

# Write-Log -Message 'Log message'
# Write-Log -Message 'Restarting Server.'
# Write-Log -Message 'Folder does not exist.' -Level Error
$Global:LogFile = "E:\DailyBackup\Log\ResetDocumentID." + (Get-Date).ToString("yyyyMMdd-HHmmss") + ".txt"

function Write-Log{
        [Parameter(Mandatory=$true, ValueFromPipelineByPropertyName=$true)]


        $VerbosePreference = 'Continue'
        #if (!(Test-Path $LogFile)) {
        #    Write-Verbose "Creating $LogFile."
        #    $NewLogFile = New-Item $LogFile -Force -ItemType File

        $FormattedDate = Get-Date -Format "yyyy-MM-dd HH:mm:ss"

        switch ($Level) {
            'Error' {
                $LevelText = 'ERROR:'
                $MessageColor = [System.ConsoleColor]::Red
            'Warn' {
                $LevelText = 'WARNING:'
                $MessageColor = [System.ConsoleColor]::Yellow
            'Info' {
                $LevelText = 'INFO:'
                $MessageColor = [System.ConsoleColor]::DarkGreen
            'HighLight' {
                $LevelText = 'HIGHLIGHT:'
                $MessageColor = [System.ConsoleColor]::Green
        Write-Host $Message -f $MessageColor

        $MessageContent = "$FormattedDate $LevelText $Message"
        $MessageContent | Out-File -FilePath $Global:LogFile -Append
        #$opts = @{ForegroundColor=$MessageColor; BackgroundColor="black"; object=$MessageContent}
        #Write-Log $opts

function GetWebAppUrlFromSiteUrl([string]$SiteUrl){
#Write-Log -Message "GetWebAppUrlFromSiteUrl(), start......SiteUrl=$SiteUrl" -Level HighLight
    $site = Get-SPSite -Identity $SiteUrl
    $WebAppUrl = $site.WebApplication.GetResponseUri([Microsoft.SharePoint.Administration.SPUrlZone]::Default).AbsoluteUri
    if ($WebAppUrl.EndsWith("/","CurrentCultureIgnoreCase")){
        $WebAppUrl = $WebAppUrl.Substring(0, $WebAppUrl.Length - 1)

#Write-Log -Message "GetWebAppUrlFromSiteUrl(), complete. WebAppUrl=$WebAppUrl" -Level HighLight
    return $WebAppUrl

function GetSiteNameFromSiteUrl([string]$SiteUrl){
# Write-Log -Message "GetSiteNameFromSiteUrl(), start......SiteUrl=$SiteUrl"
    if ($SiteUrl.EndsWith("/","CurrentCultureIgnoreCase")){
        $SiteUrl = $SiteUrl.Substring(0, $SiteUrl.Length - 1)
$iPos = $SiteUrl.LastIndexOf('/')
$SiteUrl = $SiteUrl.Substring($iPos + 1)

# Write-Log -Message "GetSiteNameFromSiteUrl(), complete. SiteUrl=$SiteUrl"
    return $SiteUrl

function StartTimerJob([string]$WebAppUrl, [string]$JobName){
Write-Log -Message "StartTimerJob(), start......WebAppUrl=$WebAppUrl, JobName=$JobName"
$job = Get-SPTimerJob -WebApplication $WebAppUrl $JobName
if (!$job){
Write-Log -Message "StartTimerJob(), No valid timer job found, WebAppUrl=$WebAppUrl, JobName=$JobName" -Level Error
$startTime = $job.LastRunTime

Start-SPTimerJob $job
while (($startTime) -eq $job.LastRunTime)
Write-Host -NoNewLine "."
Start-Sleep -Seconds 2

Write-Log "Timer Job '$JobName' has completed on $WebAppUrl."

# Write-Log -Message "StartTimerJob(), complete. SiteUrl=$SiteUrl"

function ResetDocumentID([string]$startSPSiteUrl){
    Write-Log -Message "ResetDocumentID(), startSPSiteUrl=$startSPSiteUrl"
    $SiteUrlPrevious = ""
    $SiteUrl = ""
    $WebAppUrl = ""
    $WebAppUrlPrevious = ""

$rootweb = $null
    $SiteCount = 0
    $i = 0

$sites = @(Get-SPSite -Limit ALL | ?{$_.ServerRelativeUrl -notmatch "Office_Viewing_Service_Cache" `
-and $_.Url.Startswith($startSPSiteUrl, "CurrentCultureIgnoreCase") `
-and $_.Url -notmatch "SearchCenter" `
-and $_.Url -notmatch "IPForm " `
-and $_.Url -notmatch "SPTest" `
-and $_.Url -notmatch "mysite"})
$SiteCount = $sites.count
if ($SiteCount -eq 0){
Write-Log -Message "No valid SPSite found, startSPSiteUrl=$startSPSiteUrl" -Level Error
Write-Log -Message "sites.count=$SiteCount"

$progressBarTitle = "ResetDocumentID(), Scan SPSites, SiteCount=$SiteCount, startSPSiteUrl=$startSPSiteUrl"
foreach ($site in $sites){
Write-Progress -Activity $progressBarTitle -PercentComplete (($i/$SiteCount)*100) -Status "Working"

$SiteUrl = $site.Url
$WebApplicationUrl =

Write-Log "ResetDocumentID(), SiteUrl=$SiteUrl"
if ($site.ReadOnly){
Write-Log "ResetDocumentID(), Site($SiteUrl) is read-only. Skip." -Level Warn

$WebAppUrl = GetWebAppUrlFromSiteUrl $SiteUrl
if ($WebAppUrl.EndsWith(".local","CurrentCultureIgnoreCase") -eq $false){
Write-Log -Message "ResetDocumentID(), skip web application: WebAppUrl=$WebAppUrl"

$SiteName = GetSiteNameFromSiteUrl $SiteUrl
Write-Log "ResetDocumentID(), DocumentID=$SiteName"

[Microsoft.Office.DocumentManagement.DocumentID]::EnableAssignment($site,$false)   #First disable, then enable DocID assignment
$["docid_msft_hier_siteprefix"]= $SiteName  # This is the property holding the Document ID Prefix which we use to ensure uniqueness
[Microsoft.Office.DocumentManagement.DocumentID]::EnableAssignment($site,$true,$true,$true)  # now we can force all Document IDs to be reissued
Catch [system.exception]{
$strTmp = [string]::Format("ResetDocumentID(), startSPSiteUrl={0}, SiteUrl={1}, ex.Message={2}", $startSPSiteUrl, $SiteUrl, $Error[0].Exception.Message)
Write-Log $strTmp -Level Error
Write-Log $_.Exception -Level Error
if ($rootweb){
if ($site){
if ([string]::IsNullOrEmpty($SiteUrlPrevious)){
$SiteUrlPrevious = $SiteUrl
$WebAppUrlPrevious = $WebAppUrl
if ($WebAppUrl.Equals($WebAppUrlPrevious, [StringComparison]::InvariantCultureIgnoreCase) -eq $false){
StartTimerJob $WebAppUrl "DocIdEnable"
StartTimerJob $WebAppUrl "DocIdAssignment"

$WebAppUrlPrevious = $WebAppUrl

Write-Log -Message "ResetDocumentID(), completed"

StartTimerJob $WebAppUrl "DocIdEnable"
StartTimerJob $WebAppUrl "DocIdAssignment"


# $_SiteNameSuffix = '2016DEV'
# $_SiteNameSuffix = '2013DEV'
$_SiteNameSuffix = ''

# $_SiteUrl = ""
$_SiteUrl = "http://team$_SiteNameSuffix.SharePointServer.local/sites/SiteCollectionName"

ResetDocumentID $_SiteUrl

Write-Log -Message "Finished! Press enter key to exit."

Tuesday, October 17, 2017

Shocking change of Office 365 and AAD licensing

Based on this post , for a company with 1000 users, to prevent users from creating Groups, Microsoft will charge AUD 91700 ( around USD 72000) per year!

So, is this the extra cost of "cloud platform"? How many of this kind of licensing changes are there waiting for us?!

I am speechless now.  :-(


Azure Active Directory pricing

The Price of Office 365 Groups

"I honestly cannot come up with a justification for charging extra for the ability to prevent Groups from being created by every user in your organization."