February 15, 2017

SharePoint: Batch update CSWP query using PowerShell

Task

Needed to update CSWP query on a lots of sites programmatically. URL of the content was changed so needed to change the Path rule in CSWP.

Solution

Script below recursively uses Office PnP PowerShell, so first install it using

Install-Module SharePointPnPPowerShellOnline -AllowClobber

Script goes through all subsites, publishing pages on the subsites, and web parts on the publishing pages and updates the path search rule, but obviously you can just modify the first two parameters to make any change you wish.

NOTE! It determines if web part is CSWP by looking at the DataProviderJSON property of the web part.

Flip $saveChanges to $true to actually persist changes.

$stringToFind = @('path:\"https://xyz.sharepoint.com/oldsite/')
$stringToReplace = @('path:\"https://xyz.sharepoint.com/newsite/')
$saveChanges = $false
$credentials = Get-Credential

function UpdateSearchWebParts($webUrl)
{
    Connect-PnPOnline -Url $webUrl -Credentials $credentials

    Write-Host "Updating CSWP source string:"$stringToFind " with Target string " $stringToReplace
   
    foreach($subweb in Get-PnPSubWebs -Recurse)
    {
        Write-Host "Subweb '$($subweb.Title)'"
       
        # Get pages
        foreach($page in Get-PnPListItem -Web $subweb -List "pages" -Fields "FileRef" | Where { $_.FieldValues.FSObjType -eq 0})
        {  
            Write-Host "Page '$($page["FileRef"])'"
         
            if($saveChanges) {
                Set-PnPFileCheckedOut -Url $page["FileRef"] -Web $subweb
            }
           
            #Get CSWP
            $webparts = Get-PnPWebPart -Web $subweb -ServerRelativePageUrl $page["FileRef"]
            foreach($webpart in $webparts)
            {
                if($webpart.WebPart.Properties.FieldValues.DataProviderJSON -eq $null -or !$webpart.WebPart.Properties.FieldValues.DataProviderJSON.Contains($stringToFind))
                {
                    continue
                }

                Write-Host "Web part: " $webpart.WebPart.Title               
               
                Write-Host "Old: '"$webpart.WebPart.Properties.FieldValues.DataProviderJSON"'"
                $newJson = $webpart.WebPart.Properties.FieldValues.DataProviderJSON.Replace($stringToFind, $stringToReplace)
                Write-Host "New: '"$newJson"'"

                if($saveChanges) {
                    Set-PnPWebPartProperty -ServerRelativePageUrl $page["FileRef"] -Identity $webpart.Id -Key DataProviderJSON -Value $newJson
                }
            }

            if($saveChanges) {
                Set-PnPFileCheckedIn -Url $page["FileRef"] -Web $subweb -CheckinType MajorCheckIn
            }           
        }       
    }
}

UpdateSearchWebParts https://xyz.sharepoint.com

January 17, 2017

SharePoint: Yet another way to remove orphan SSRS Content Types

Problem

In a SharePoint Web Application, SQL Server Reporting Services was installed when farm was on SharePoint 2010 level. Since then, the farm had been upgraded to SharePoint 2013 using content database upgrade method. New servers were set up for the new farm.

History is history, but for some reason, orphan SSRS Content Types were left on the site collection breaking when attempting to create team sites based on a template. In the saved site template there was references to Content types with names such as

“$Resources:ReportServerResources,DataSourceContentTypeName;”,
“$Resources:ReportServerResources,ReportBuilderContentTypeName;” and “$Resources:ReportServerResources,ReportBuilderModelName;”,

but subsite creation failed as it couldn’t find such content types. On the content type list of the site collection, you could see three content types with those exact names, meaning something was missing as valid names of the content types couldn’t be shown.

Solution

  1. Installed SSRS components on the new farm using (SKIPCA skips the custom action phase, as it was not succeeding in our case):

    Msiexec.exe /i rsSharePoint.msi SKIPCA=1
  2. This makes the content type names behave correctly.
  3. Now you can install the ReportServer feature

    Install-SPFeature -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\TEMPLATE\FEATURES\ReportServer"
  4. Followed by uninstalling that same feature

    Uninstall-SPFeature -Identity e8389ec7-70fd-4179-a1c4-6fcb4342d7a0
  5. And finally you can just uninstall the rsSharePoint.msi for good

    msiexec.exe /uninstall rsSharePoint.msi

January 3, 2017

Azure VPN install hangs on Windows 10

Problem

Tried to install Azure VPN client (x64) on Windows 10, but after double-clicking on the installer, Windows Explorer always froze and I had to restart explorer.exe to wake it up again.


Solution

Smart Screen is failing due to some reason. When double clicking on the VPN client (AMD64).exe, you can see CHXSmartScreen.exe process being starter. Kill that process and VPN client installation starts normally.

smartscreen

October 21, 2016

SharePoint Search TrimDuplicates trims too much

Problem

Simple SharePoint search <MANAGER_PROPERTY>:<VALUE> returns only subset of items.

E.g., you have 100 unique customers on a list with same managed term defined in a field, and you try to return all active customers with query “Status:active”, but you only get few hits from search.

I’m using on-prem SharePoint 2013.

Solution

Disable TrimDuplicates in your Search Results Web Part settings or in your Query XML by setting <TrimDuplicates>false</TrimDuplicates> ALTHOUGH the items are not duplicate and only thing in common is this one individual value.

Works? Yes. Makes sense? No, this is SharePoint.

October 4, 2016

Log Parser issue with SharePoint claims user name special characters

Problem

I'm using Log Parser 2.2 to read IIS logs and import them to SQL Server. Everything works fine except the cs-username field: if it contains special characters, those get messed up during database import. As this is SharePoint web application and it uses external trusted provider, I cannot control nor get rid of the special characters in user names before they are stored in IIS logs.

So, the IIS logs contain hits with user names such as:

    #Software: Microsoft Internet Information Services 8.5
    #Version: 1.0
    #Date: 2016-10-01 07:27:28
    #Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) cs(Referer) cs-host sc-status
    2016-10-01 07:28:15 10.0.2.4 GET /_layouts/images/advadd.png - 443 0ǵ.t|claims+idp|12345678 111.222.223.224 Mozilla/5.0
https://my.site.com/page.aspx my.site.com 200

And after Log Parser imports those to DB, user name has changed to 0ǵ.t|claims+idp|12345678

Same applies if I test outputting it to Data Grid with

    LogParser.exe "SELECT cs-username FROM f:\iislogs\dev3\*.log" -i:IISW3C -o:datagrid

--> I see 0ǵ.t|claims+idp|12345678 instead of 0ǵ.t|claims+idp|12345678.

 

Solution

As IIS Log files are stored using UTF-8, you need to add -iCodepage 65001 to the Log Parser command, like so:

logparser.exe file:AnalyticsQuery.sql -i:IISW3C -o:SQL -database:AnalyticsDB -server:sqlserver -iCodepage:65001

And of course remember to set your table column as nvarchar or other n-type.

August 30, 2016

SharePoint SP.UserProfiles.PeopleManager GetMyProperties Unknown Error

Problem

When attempting to query SharePoint User Profile using REST at _api/SP.UserProfiles.PeopleManager/GetMyProperties, you get error

<m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
<m:code>-1, Microsoft.SharePoint.Client.UnknownError</m:code>
<m:message xml:lang="en-US">Unknown Error</m:message>
</m:error>

Solution

Go to Central Admin –> User Profile Service Application –> Setup My Sites

mysite1

Fill in the MySite Host Location field. It will also work if you have centralized MySite host located on another farm.

mysite2

Done!

June 29, 2016

SQL Server: Log Shipping backup job fails

Problem

After configuring Log Shipping, the first job that runs is the transaction log backup. However, it keeps on failing with very generic error:

Executed as user: DOMAIN\sqlagent1. The step failed.

No matter what you do, it just won’t log anything else, e.g., configuring the Job step to log to file just ends up with empty log file. Also the log table contains no rows for this specific step.

Solution

I finally tried executing the sqllogship.exe command from the failing Job step on the SQL server:

"s:\Program Files\Microsoft SQL Server\130\Tools\Binn\sqllogship.exe" –Backup <GUID> -server SQL-1

and it displayed me dialog that .NET Framework 3.5 was required on the server. After installing that from “Add Roles and Features”, the Job finally executed without issues.