SharePoint Online: Approve Pages in Pending Status using PnP PowerShell

When publishing is enabled in SharePoint Online, all the edits of the documents/pages will have the minor versions (or drafts). All changes to any document isn’t available to users with read only permissions, until its Published. Published documents becomes major versions such as 1.0, 2.0, 3.0, etc.

Document will have multiple statuses: Scheduled, Draft, Pending, and Approved.

You can publish the individual document by right click on the document to publish –> In the context menu, choose More –> and then click on Publish.

You can use PowerShell to Publish multiple documents at ones:

 
#################################################################################################### 
# 
# Author.......: David Shvartsman 
# Date.........: 05/07/2021 
# Description..: SharePoint Online: Approve Pages in Pending Status using PnP PowerShell 
# 
####################################################################################################
CLS
$SiteURL = "https://contoso.sharepoint.com/sites/site1"
$LibraryName ="Library1"

Write-host "Processing $($SiteURL) ..."
Connect-PnPOnline -Url $SiteURL -Credentials $credential -IgnoreSslErrors

#Get All Files from the document library - In batches of 500 and _ModerationStatus equel 2 (Approval Status: Pending)
$ListItems = Get-PnPListItem -List $LibraryName -PageSize 500 | Where {$_["_ModerationStatus"] -eq 2} 
$DocumentsData=@()
Write-host "Number of items found in '$($LibraryName)' $($ListItems.count)"
ForEach($Item in $ListItems)
{
    #Collect Documents Data
    $DocumentsData += New-Object PSObject -Property @{
        FileName = $Item.FieldValues['FileLeafRef']
        FileURL = $Item.FieldValues['FileRef']
        Status = $Item.FieldValues._ModerationStatus
    }
    Set-PnPFileCheckedIn -Url $Item.FieldValues['FileRef'] -CheckinType OverwriteCheckIn -Comment "Automated Approval" -Approve
}
$DocumentsData | Export-csv "C:\temp\PendingPages.csv"


Write-host "Processing $($SiteURL) completed."
Write-host ""
Write-host ""
Write-host ""
Write-host ""

###################################################################################################

The values of ‘_ModerationStatus’ property are:

0 – Approved

2 – Pending

3 – Draft

4 – Scheduled

You can use the script to select pages in different Approval Status and Approve and/or Publish those using Set-PnPFileCheckedIn PnP command.

That was another page in the Chronicles of SharePoint Bits, happy scripting!

Resetting Office 365 Cloud Search Index

From time to time we need to remove all the items out of the Office 365 search index. There are 3 options:

1. Re-index everything on premises and after the indexing completes delete the content sources to trigger a delete crawl to run. Of course re-indexing everything is not efficient, it takes time and if items have been deleted from the on premises content you still run the risk of missing orphans in the Office 365 search index.

2.  Call Microsoft Office 365 support and raise a ticket to ask for an index purge, something that takes time and again is inefficient for the task at hand.

3. Use DeleteAllCloudHybridSearchContent method of PushTenantManager.

More information on DeleteAllCloudHybridSearchContent method can be found here and here.

That was another page in the Chronicles of SharePoint Bits, happy scripting!

Error: PowerShell ISE stopped working

PowerShell Integrated Scripting Environment is a very powerful and useful tool to create and debug PowerShell scripts. After installing some security patching and IRM component it stopped working and would crash:

The issue is related to the corrupt GlobalUserInterface.CompositeFont file. This causes PowerShell ISE. It also effects other WPF applications to fail as well. See this article: https://github.com/dotnet/announcements/issues/53.

We used the Solution 3:

Replace GlobalUserInterface.CompositeFont Manually

Manually replace corrupted font file with correct version.

  1. Download GlobalUserInterface.CompositeFont (default download for Windows 7 is %USERPROFILE%\Downloads).
  2. Launch cmd as Administrator and navigate to %windir%\Microsoft.NET\Framework\v4.0.30319\WPF\Fonts and run:
    xcopy /y %USERPROFILE%\Downloads\GlobalUserInterface.CompositeFont .
    (or copy and paste the GlobalUserInterface.Composite file through Windows Explorer to %windir%\Microsoft.NET\Framework\v4.0.30319\WPF\Fonts)
  3. Launch cmd as Administrator and navigate to %windir%\Microsoft.NET\Framework64\v4.0.30319\WPF\Fonts and run:
    xcopy /y %USERPROFILE%\Downloads\GlobalUserInterface.CompositeFont .
    (or copy and paste the GlobalUserInterface.Composite file through Windows Explorer to %windir%\Microsoft.NET\Framework64\v4.0.30319\WPF\Fonts).
  4. Re-launch the application.
  5. Reboot machine and re-launch application if you still have trouble.
##
##
xcopy /y GlobalUserInterface.CompositeFont %windir%\Microsoft.NET\Framework\v4.0.30319\WPF\Fonts
xcopy /y GlobalUserInterface.CompositeFont %windir%\Microsoft.NET\Framework64\v4.0.30319\WPF\Fonts
##
##

 

That was another page in the Chronicles of SharePoint Bits, happy scripting!

SharePoint Online: Using PowerShell to delete alerts in site/sub sites using CSOM

Metalogix Content Matrix is a great tool to migrate from SharePoint on-premises to the the Cloud. As part of migration to SharePoint Online we had to re-migrate some of the content multiple times. If users had alerts set up on the migrated items, those alerts will trigger as content gets re-migrated. Th prevent flood of the emails to the users we needed to deleted the migrated alerts.
Here is a PowerShell script to delete alerts in SharePoint Online. This PowerShell script will delete all alerts in root web and all sub webs in SharePoint Online site.

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 09/14/2018
#  Description..: SharePoint Online: Delete alerts in site/sub sites using CSOM
#
####################################################################################################
CLS
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll" -ErrorAction Stop
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" -ErrorAction Stop

#use the $AlertsExportFile to store existing Alers
$AlertsExportFile = "C:\temp\Alerts.csv"
if (Test-Path $AlertsExportFile) {
    Remove-Item $AlertsExportFile -Confirm:$false
}

$UserName = Read-Host "Please enter User Name"
$Password = Read-Host "Enter a Password" -AsSecureString
$SiteUrl = Read-Host "Please enter SharePoint Online Site URL"

$credentials= New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,$Password)

$ctx= New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$ctx.Credentials = $credentials

$web = $ctx.Web
$ctx.Load($web)
$ctx.Load($web.Webs)
$siteUsers=$web.SiteUsers
$ctx.Load($siteUsers)
$siteUsers=$web.SiteUsers
$ctx.Load($siteUsers)
$alerts=$web.Alerts
$ctx.Load($alerts)
$ctx.ExecuteQuery()
Write-Host "Processing $($web.Url)"
#List all Alerts for RootWeb site
if ($alerts.Count -gt 0) {
    $alerts |SELECT @{Label="SiteUrl";Expression={"$($web.Url)"}},AlertFrequency, AlertTime, AlertType, AlwaysNotify, DeliveryChannels, EventType, Filter, ID, UserID, Status | Export-Csv $AlertsExportFile -Append -NoTypeInformation
}
foreach($alert in $alerts) {
	$web.Alerts.DeleteAlert($alert.ID)
	$web.Update()
	$ctx.ExecuteQuery()
}
#enumerate through seub webs
foreach ($webSub in $web.Webs) {
    Write-Host "Processing $($webSub.Url)"
    $alertsSub = $Null
    $ctxSub= New-Object Microsoft.SharePoint.Client.ClientContext($webSub.Url)
    $ctxSub.Credentials = $credentials
    $webSub = $ctxSub.Web
    $ctxSub.Load($webSub)
    $alertsSub=$webSub.Alerts
    $ctxSub.Load($webSub)
    $ctxSub.Load($alertsSub)
    $ctxSub.ExecuteQuery()
    #List all Alerts for each sub web
    if ($alertsSub.Count -gt 0) {
        $alertsSub |SELECT @{Label="SiteUrl";Expression={"$($webSub.Url)"}},AlertFrequency, AlertTime, AlertType, AlwaysNotify, DeliveryChannels, EventType, Filter, ID, UserID, Status | Export-Csv $AlertsExportFile -Append -NoTypeInformation
    }
    foreach($alert in $alertsSub){
	    $webSub.Alerts.DeleteAlert($alert.ID)
	    $webSub.Update()
	    $ctxSub.ExecuteQuery()
    }
}

###################################################################################################

All you will need is re-migrate the alerts and you all set!

That was another page in the Chronicles of SharePoint Bits, happy scripting!

Howto: Check all the sites in the particular Content DB

From time to time, there is a need to check the SharePoint sites is a particular content database are accessible. There is a simple script for it:  enumerate all the sites in the DB and Invoke a Web Request for the site URL. The returned Web request code will indicate if site is accessible or not:

####################################################################################################
#
# Author.......: David Shvartsman
# Date.........: 07/05/2018
# Description..: Check all the sites in the particular Content DB
#
####################################################################################################
#Load SharePoint PowerShell Snapin
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
$db = "Content_DBName"
$Sites = Get-SPSite -ContentDatabase $db -Limit All
foreach ($site in $sites) {
    $SiteStatus = Invoke-WebRequest -Uri $site.url -UseDefaultCredentials | Select-Object StatusCode

    If ($siteStatus.StatusCode -eq 200) {
        Write-host "$($site.url) is OK." -ForegroundColor Green
    }
    Else {
        Write-Host "Failed check for $($site.url)" -ForegroundColor Red
    }
}

####################################################################################################

That was another page in the Chronicles of SharePoint Bits, happy scripting!

Troubleshooting: Collecting Verbose ULS logs

Working with Microsoft on the one of the issues we were asked to provide verbose ULS logs. Having the ULS logs in verbose mode might take a lot of disk space. Here is a simple solution: enable the verbose logging only for the period of time to reproduce the issue and switch it back to default level when done. PowerShell to the rescue: the following script will change the ULS log level to verbose and pause while you can reproduce the issues that is being troubleshooted. Just press the enter button after that and it will switch the log level to default (Information). If your default log level is different from Information modify the script accordingly. The script will generate Merged ULS log for the period between the start of the script and the time you pressed the Enter button to continue.

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 06/27/2018
#  Description..: Troubleshooting: Collecting Verbose ULS logs
#
####################################################################################################
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
CLS
$startDate = (Get-Date -format "MM/dd/yyyy HH:mm:ss")
set-sploglevel -traceSeverity verbose
pause

$endDate = (Get-Date -format "MM/dd/yyyy HH:mm:ss")
Clear-SPLogLevel
$logfile = "d:\temp\ULSLogFileName.log"
if (test-path $logfile) {
          Remove-Item $logfile
}

Merge-SPLogFile –Path $logfile –StartTime $startDate –EndTime $endDate

####################################################################################################

That was another page in the Chronicles of SharePoint Bits, happy scripting!

PowerShell: working with JSON files

With introduction of Office 365 and SharePoint online, Microsoft increasingly using JSON file format to transfer data to and from the new platform. For example, Bulk UPA Custom Profile Properties Update API for SharePoint Online. For SharePoint administrator is natural to fall back to PowerShell to perform those tasks and automate the integration between the on-Premises environment and SharePoint Online.  Windows PowerShell 3.0 higher includes couple of commandlets (ConvertFrom-Json and ConvertTo-Json) to convert PowerShell objects to JSON format and back. One of the used cases is a fast and easy way to interact with web services.

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 05/21/2018
#  Description..: Using PowerShell with JSON
#
#################################################################################################### 
#Save PowerShell object to the file in JSON format
$users | Where {$_name -like 'test'} | Select-Object $selectObject -skip 10 | 
            Select-Object -First 5 | ConvertTo-Json > C:\temp\users.json


#Read File in JSON format into PowerShell object
$users = Get-Content 'C:\temp\users.json' | Out-String | 
            ConvertFrom-Json | SELECT -expand Value | SELECT UserPrincipalName 

#Read Web Request response:
$WebRequestURL = "http://www.nactem.ac.uk/software/acromine/dictionary.py?sf=BMI"
Invoke-WebRequest $WebRequestURL | ConvertFrom-Json  | select *


#################################################################################################### 

That was another page in the Chronicles of SharePoint Bits, happy scripting!

Audience Compilation Status is ‘Compiling’ for a long time

Looking at Central Administration console under Manage Profile Service we have noticed that Audience Compilation Status shows ‘Compiling’ for a long time. There is no errors in the event logs. Attempts to stop compilation or compile an individual audience did not produce any results.

There is AudienceJob.exe executable that you can use to manipulate audiences:

AudienceJob.exe <Application Id> [Crawl Type] [Audience Name]
Application Id: Guid corresponding to UserProfile application
Command: 1 = Start, 0 = Stop
Crawl Type: 1 = Full, 0 = Incremental (default = 1)
Audience Name: Specific audience to compile (default = all)

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 05/18/2018
#  Description..: Restart Audience Compilation
#
#################################################################################################### 
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
$ServiceType = "User Profile Service Application"
$spService = Get-SPServiceApplication  | where {$_.TypeName -like $ServiceType }
$id = $spService.id
Audiencejob.exe $id 0 
Audiencejob.exe $id 1

You can use AudienceJob.exe to compile a specific audience.

It works in SharePoint 2010, SharePoint 2013 and SharePoint 2016.

That was another page in the Chronicles of SharePoint Bits, happy scripting!

Application error when access /_layouts/15/EditProperty.aspx, Error=AccessKey too long

After installing March 2018 update for SharePoint 2013 you may get the following error when trying to configure sync in User Profile Service Application:

Application error when access /_layouts/15/EditProperty.aspx, Error=AccessKey too long, cannot be more than one character. Parameter name: value
at System.Web.UI.WebControls.WebControl.set_AccessKey(String value)
at Microsoft.SharePoint.Portal.WebControls.InputFormCheckBox.set_AccessKeyLocId(LocStringId value)
at ASP._layouts_15_editproperty_aspx.__BuildControlchkboxIsVisibleOnViewer()
at ASP._layouts_15_editproperty_aspx.__BuildControlInputFormSectionFieldArea4()
at ASP._layouts_15_editproperty_aspx.__BuildControlSection4()
at ASP._layouts_15_editproperty_aspx.__BuildControlInputForm1()
at ASP._layouts_15_editproperty_aspx.__BuildControl__control7(Control __ctrl)
at System.Web.UI.MasterPage.InstantiateInContentPlaceHolder(Control contentPlaceHolder, ITemplate template)
at ASP._admin_admin_master.__BuildControlPlaceHolderMain()
at ASP._admin_admin_master.__BuildControlDeltaPlaceHolderMain()
at ASP._admin_admin_master.__BuildControl__control27()
at ASP._admin_admin_master.__BuildControlSPHtmlTag()
at ASP._admin_admin_master.__BuildControlTree(_admin_admin_master __ctrl)
at System.Web.UI.MasterPage.CreateMaster(TemplateControl owner, HttpContext context, VirtualPath masterPageFile, IDictionary contentTemplateCollection)
at System.Web.UI.Page.get_Master()
at System.Web.UI.Page.ApplyMasterPage()
at System.Web.UI.Page.PerformPreInit()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)

You also might have experienced the slight disfigurement of UI Search/Content Source configuration screen:
The Ok and Cancel button label changed.

The problem is related to ASP.net temporary files. The workaround is to delete ASP.net temporary files on CA server and let .NET framework rebuild the executable. To fix the issue follow the steps below:
1. Stop IIS: iisreset /stop
2. Delete the content of the following folder:
C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files
3.Start IIS: iisreset /start

Working with Communication sites Using PowerShell

A SharePoint communication site is a great modern design site that can be used to share news, reports, and other information in a visually compelling format and uses adoptive layout. More information can be found in this Microsoft article: What is a SharePoint communication site?

The template used to create a Communication SharePoint site is SITEPAGEPUBLISHING#0.

The following code will create a Communication Sites using the SharePoint Online Management Shell. A connection to SPO tenant have to be established first using Connect-SPOService command.

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 05/04/2018
#  Description..: Create a Communication Site 
#
#################################################################################################### 
CLS
# Tenant Configuration
$tenantName = "Tenant Name" 
$AdminURI = "https://$($tenantName)-admin.sharepoint.com"
$PersonalURI = "https://$($tenantName)-my.sharepoint.com"
$TenantURI = "https://$($tenantName).sharepoint.com"

#Site Configuration Parameters
$SiteTitle = "Test Communication Site"
$SiteUrl = "CommunicationTestSite" # Note this URL must be available (check with "/_api/GroupSiteManager/GetValidSiteUrlFromAlias")
$SiteTemplate = "6142d2a0-63a5-4ba0-aede-d9fefca2c767" #"Showcase" = "6142d2a0-63a5-4ba0-aede-d9fefca2c767" and "Blank" = "f6cc5403-0d63-442e-96c0-285923709ffc"
$SiteDescription = "Test Communication Site"
$siteClassification = "LBI"
$siteLCID = 1033

$SiteFullURL = "$($TenantURI)/sites/$($SiteUrl)"

# Communication site creation request
$RequestBody = @{
    request = @{__metadata = @{type ="SP.Publishing.CommunicationSiteCreationRequest"}
    AllowFileSharingForGuestUsers = 'false'
    Classification = $siteClassification
    Description = $SiteDescription
    SiteDesignId = $SiteTemplate
    Title = $SiteTitle
    Url = $SiteFullURL
    lcid = $siteLCID
    }
}
$jsonBody = ConvertTo-Json $RequestBody

$contentType = 'application/json;odata=verbose'

# Get a user based context for SharePoint (app credentials not supported for this approach)
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($TenantURI)
$Context.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username, $SecurePWD)
$Context.ExecuteQuery()

# Get url, cookie and forms digest for authentication
$RequestUrl = "$($TenantURI)/_api/sitepages/communicationsite/create"
$AuthenticationCookie = $Context.Credentials.GetAuthenticationCookie($TenantURI, $true)
$FormsDigest = $Context.GetFormDigestDirect()

$WebSession = New-Object Microsoft.PowerShell.Commands.WebRequestSession
$WebSession.Credentials = $Context.Credentials
$WebSession.Cookies.SetCookies($TenantURI, $AuthenticationCookie)

$Headers = @{ 
    'X-RequestDigest' = $FormsDigest.DigestValue;
    'accept' = 'application/json;odata=verbose';
    'content-type' = 'application/json;odata=verbose' }

# Call REST API to create new site
try {
    $Result = Invoke-RestMethod -Method Post -WebSession $WebSession -Headers $headers -Body $jsonBody -Uri $RequestUrl -UseDefaultCredentials -ContentType $contentType  
    # Site has been created
    Write-Output "New site created at: $($Result.d.Create.SiteUrl), Status: $($Result.d.Create.SiteStatus)"
}
catch {
    Write-Host "Error creating a site"
}
finally {
    $Context.Dispose()
}

Currently SharePoint Admin Portal does not show Communication Sites. To show a list of all Communication Sites using the SharePoint Online Management Shell use the following code:

####################################################################################################
#
#  Author.......: David Shvartsman
#  Date.........: 05/04/2018
#  Description..: List all Communication Site 
#
#################################################################################################### 
Get-SPOSite -Template SITEPAGEPUBLISHING#0 -Limit ALL

To use CSOM or PnP libraries check out How to list all Communication sites in your tenant internet article.

That was another page in the Chronicles of SharePoint Bits, happy scripting!