March 21, 2017

SharePoint: Getting list items with SPFx and CamlQuery

Problem

When fetching SPList items using SPFx and REST combined with CAMLQuery, you need to use spHttpClient.post. However, the following code wasn’t working:

const options: ISPHttpClientOptions = {
	body: `{'query': {
		'__metadata': {'type': 'SP.CamlQuery'},
		'ViewXml': '<View><Query><OrderBy><FieldRef Name="ID"" /></OrderBy></Query></View>'
	}}`
};

return this.context.spHttpClient.post(window.location.protocol + '//' + window.location.hostname + (this.properties.webUrl +
	`/_api/web/lists/GetByTitle('Pages')/items?$select=Title`),
	SPHttpClient.configurations.v1,
	options)
	.then((response: SPHttpClientResponse) => {
		return response.json();
});

But instead it was throwing errors such as:

The property 'query' does not exist on type 'SP.Data.PagesItem'. Make sure to only use property names that are defined by the type.

or

An entry without a type name was found, but no expected type was specified. To allow entries without type information, the expected type must also be specified when the model is specified.

Solution

First of all make sure you use the “odata-version: 3.0”, but more importantly, as you’re using POST, change the REST URL from …/items to …/GetItems.

Final working code:

const options: ISPHttpClientOptions = {
	headers: {'odata-version':'3.0'},
	body: `{'query': {
		'__metadata': {'type': 'SP.CamlQuery'},
		'ViewXml': '<View><Query><OrderBy><FieldRef Name="ID"" /></OrderBy></Query></View>'
	}}`
};

return this.context.spHttpClient.post(window.location.protocol + '//' + window.location.hostname + (this.properties.webUrl +
	`/_api/web/lists/GetByTitle('Pages')/GetItems?$select=Title`),
	SPHttpClient.configurations.v1,
	options)
	.then((response: SPHttpClientResponse) => {
		return response.json();
});

Oh, and no need to use JSON.stringify() when building the ISPHttpClientOptions body query, just use ` around the code and it will already be a string.

March 17, 2017

SharePoint: Make form Cancel button to not redirect to Source URL

Problem

On a feedback form implementation, you can make the SAVE button redirect to any page you wish using the Source Query String parameter. But how to make the CANCEL button to not redirect to this same Source page, which is commonly a “Thank you” page?

Solution

  1. Create new “New” form for the list.
  2. Find SharePoint:GoBackButton control
  3. Add new parameter RedirectUrl with the URL where Cancel button should redirect to.

In my case it is just redirecting to the front page of the site.

form1

March 9, 2017

Sonera IPTV on Ubiquiti EdgeRouter

Network details

eth0 = internet
eth1 = LAN
eth0.252 = IPTV VLAN
LAN subnet = 192.168.1.0/24

 

Steps

1. Add new VLAN interface

clip_image001

2. For interface MAC, set MAC of your Inteno router (not sure if this is needed)

clip_image002

3. For interface DHCP / route settings set the values

clip_image003

4. For firewall create address group IPTV for multicast traffic

address-group IPTV 
{
address 239.16.116.0/24
address 239.16.117.0/24
description ""
}

5. For firewall, create two rules: IPTV_IN and IPTV_LOCAL, assign to eth0.252/in and eth0.252/local

name IPTV_IN 
{ 
default-action drop 
description "" 

rule 10 
{ 
action accept 
description "Allow established/related" 
log disable 
protocol all 
state 
{ 
established enable 
invalid disable 
new disable 
related enable 
}
} 

rule 20 
{ 
action accept 
description "Allow multicast" 
destination { 
group { 
address-group IPTV 
} 
} 
log disable 
protocol udp
} 

rule 30 
{ 
action drop 
description "Drop invalid state" 
log disable 
protocol all 
state { 
established disable 
invalid enable 
new disable 
related disable 
}
} 
} 
name IPTV_LOCAL { 
default-action drop 
description "WAN to router" 

rule 10 
{ 
action accept 
description "Allow established/related" 
log disable 
protocol all 
state { 
established enable 
related enable 
}
} 

rule 20 
{ 
action accept 
description "Allow multicast" 
destination { 
group { 
address-group IPTV
} 
} 
log disable 
protocol udp 
source { 
} 
} 

rule 30 
{ 
action accept 
description "Allow IGMP" 
log disable 
protocol igmp
} 

rule 40 
{ 
action drop 
description "Drop invalid state" 
log disable 
protocol all 
state { 
invalid enable
} 
} 
} 

6. Done!

Keywords: Sonera Viihde, Fibre, Fiber, sonera viihde iptv omalla reitittimellä

SharePoint: Batch delete access requests

Problem

On on-premises SharePoint 2013 there was a web site where there were hundreds of pending access requests that needed to be removed. Approving or Declining requests was not desired, they just needed to be removed without any email being sent to the users.
There is no deletion option in browser UI, and after adding traditional AllItems.aspx view using SharePoint Designer, deleting requests one by one would’ve taken very long time, as while the All Items view allowed me to select multiple rows, there was no Delete button in any toolbar nor ribbon.

Solution

Simple PowerShell script did the trick.

NOTE! This also removes the history, so only run it if you do not wish to persist the access request history for the site.

$web = Get-SPWeb https://teamsites.company.com/teams/teamsiteA
$items = $web.Lists["Access Requests"].Items
while($items.Count -gt 0)
{
    Write-Host "Deleting: $($items[0].Title)"
    $items[0].Delete()
}

March 6, 2017

SharePoint: Web applications slow loading when using DigiCert certificates when no access to internet

Problem

On SharePoint farm with no access to internet, if you configure IIS sites to use DigiCert SSL certificates, page loading (especially the first) are slow. You’ve disabled CRL checking and configured SharePoint to trust it’s own Root Certificate. Still you see 10-15 sec think times when loading pages.

In Windows Event Log –> Applications and Service Logs –> Microsoft –> Windows –> CAPI2 –> Operational log you see Event ID 53 errors like this:

ocsp

Solution

This is due to Kerberos Client PCSP Stapling Requests that fail due to now having connection to http:/ocsp.digicert.com and can be disabled

On SharePoint servers, In registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters\ add RequestOCSP of type DWORD and set it to 0 after which the computer will not request a stapled response. Restart/reboot not required.

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