Home » SharePoint » SharePoint 2013

SharePoint Reporting Services Subscription Management using PS Scripting

One of the more tedious things to manage in SharePoint are the SQL Reporting Services reports. Going through item by item to configure the data sources, parameters and shared schedule processing can take a bit of time. Fortunately, there is a PowerShell option for doing some management of reports in SharePoint, although it is not very well documented.

Let’s say you have a batch of SSRS reports that you have just added new subscriptions for, and you want to clear out the old subscriptions. Rather than go through each report individually, you can just run a bit of PS and remove all the old entries.

First hop on the SharePoint management shell, and then set up a web proxy to the reports server:

$websvc = New-WebServiceProxy -Uri "http://spreportssite-local.contoso.com/_vti_bin/ReportServer/ReportService2010.asmx" -Namespace SSRS.ReportingService2010 -UseDefaultCredential;

Next, pull the subscriptions for the site you want to manage:

$siteSubs = $websvc.ListSubscriptions("https://spreportssite.contoso.com/NYCOffice");

$siteSubs | select report, owner, status, lastexecuted, subscriptionid | format-table -auto

Then you will want to filter this down futher, to just get the subscriptions you want to remove:

$clearSubs = $siteSubs | select report, owner, status, lastexecuted, subscriptionid | where {$_.Status -ne "New Subscription"}

$clearSubs | select report, owner, status, lastexecuted, subscriptionid | format-table -auto

If the list looks good, then run the following to clear out all the old subscriptions:

foreach ($sub in $clearSubs) {$websvc.DeleteSubscription($sub.SubscriptionID)}

Display Metadata Tags in SharePoint Search Core Results

SharePoint 2010 and onwards has the excellent feature of being able to tag content with customizable metadata. This great for using the refining options that are built into the framework. For whatever reason however, Microsoft does not display this metadata in the actual results, so if you want too refine the search results by tag/metadata, it is really just a matter of guesswork.

Fortunately, there is a solution that is pretty easy to plug in which resolves this problem, provided you do not mind getting your hands dirty with a little XSL / XSLT.

The first thing you are going to need to do is identify the “Mapped To Property” that you want to display results for in the search results. This will begin with “ows_taxId_” in the “Search Schema” section for your Search Application (in Central Admin).

My metadata is just called “Tags”, so the “Mapped to Property” is “owstaxidtags”. The critical piece is that you remember this.

Next up, navigate to your search results page you want to show the tags on. Click the “Page” tab, and then edit the page. Scroll down to the “Search Core Results” web part, and click “Edit Web Part”.

From here, there are two text areas you are going to need to edit. I STRONGLY RECOMMEND MAKING A BACKUP OF EACH OF THESE BEFORE YOU START EDITING. Open the “Display Properties”.

The first thing you will do is add a reference to the property for the “Fetched Properties”. To do so add the following before “</Columns>”

<Column Name="owstaxidtags"/>

Next, you will need to update the XSL for the search core results. Click the button that says “XSL Editor…” Copy this text and paste it into a more user friendly editor like Notepad++

There area few areas you will need to make edits for. First is to set up a parameter for the property. Find the area at the beginning where there are a bunch of “param name” declarations, and add one for your property:

<xsl:param name="IsSearchServer" />
<xsl:param name="Period" />
<xsl:param name="SearchHelp" />
<xsl:param name="owstaxidtags" /> 
<xsl:param name="Date" />
<xsl:param name="Size" />
<xsl:param name="ViewInBrowser" />

Next, scroll down until you find the “<xsl:template match=”Result”>” area. Add a local variable for your property:

<xsl:variable name="id" select="id"/>
<xsl:variable name="currentId" select="concat($IdPrefix,$id)"/>
<xsl:variable name="url" select="url"/>
<xsl:variable name="owstaxidtags" select="$owstaxidtags"/>

Next you need to find the area you want to output the tags. For my purposes, I am going to put it in “<div class=”srch-Metadata2″>”. You can use the developer tools in your browser to locate the div name that you want to render the text to.

So right underneath the “DisplayDate”, I am going to just make the metadata tags an extra piece of content to display. Here I am adding two things. First the label, if it sees that there are any tags, and the second is a reference to the display template, which formats the metadata tags:

<div class="srch-Metadata2">

    <xsl:call-template name="DisplayDate">
        <xsl:with-param name="write" select="write" />
    </xsl:call-template> 

    <xsl:if test="string-length(owstaxidtags) &gt; 0"> 
        <xsl:text> Tags: </xsl:text>
    </xsl:if> 
    <xsl:call-template name="FormatMetadata">
        <xsl:with-param name="owstaxidtags" select="owstaxidtags" />
    </xsl:call-template>

Then all that is left of the XSL is to add in the display template. Your mileage may vary on this. Depending on how SharePoint outputs your tags, you may need to modify these if statements until you get the desired formatting. Scroll down until you get the area with the other display templates (example: <xsl:template name=”DisplayDate”>)

Then add in the display template:

<xsl:template name="FormatMetadata">
    <xsl:param name="owstaxidtags"/>
    <xsl:if test="string-length($owstaxidtags) > 0">
         <xsl:variable name="nextItem" select= "substring-before(concat($owstaxidtags, '|'), '|')"/>
         <xsl:variable name="remaining" select= "substring-after(concat($owstaxidtags, '|'), '|')"/>
         <xsl:if test="(not(contains($nextItem, '#'))) and (contains($nextItem, ';'))">
             <xsl:variable name="validItem" select= "substring-before(concat($nextItem, ';'), ';')"/>
             <xsl:value-of select="$validItem"/>
             <xsl:if test="contains($remaining, ';')">
                 <xsl:text>, </xsl:text>
             </xsl:if>
        </xsl:if>
        <xsl:if test="(not(contains($nextItem, '#'))) and (not(contains($nextItem, 'GP0'))) and (not(contains($nextItem, ';')))">
            <xsl:value-of select="$nextItem"/>
        </xsl:if> 
        <xsl:call-template name="FormatMetadata">
            <xsl:with-param name="owstaxidtags" select="substring-after($owstaxidtags, '|')"/>
        </xsl:call-template>
    </xsl:if>
</xsl:template>

Once you have made all your edits (be sure to keep that backup handy), paste the new edited XSL changes into the “XSL Editor” for the web part, and click “OK”. If you get an error, recheck your steps. If you are not seeing what you expect, you can always output just the raw metadata tag info without using the template by calling it directly in your template:

<xsl:value-of select="$owstaxidtags" />

So that’s it! You should now be seeing your metadata tags in your search results.

Locking Down Search for a Site (Part 2 – SP2013, SP2016, SP2019)

If you want to achieve the functionality I outlined in “Part 1” using SharePoint 2013 UI, or higher (SP 2016, 2019), you are going to need to go through a different process, as Microsoft deprecated Search Scopes in the 2013 UI. Fortunately, the work around is actually a bit less work, as you only need to modify your search results page.

The first step is to create a new results page. This can be done by navigating to the “Pages” area of your search site. (ie. click the gear icon, and then “Site Contents”). From there, click the Documents drop down, and choose a new “Welcome Page”.

It should default to a “Search Results” page template, but if it does not, be sure you choose that type. Give it a title relating to the search you want to have the page limited to.

Once you have the page created, click “Edit” from the toolbar, and scroll down to the search results area. Choose “Edit Web Part” from the Search Results web part.

From there, click the “Change Query” button from the web part editor’s “Properties for Search Results” section.

Here is where you are going to restrict the results for the page. Under the “Basics” tab, you will have several property filters to choose from. If you do not see the one you want, you can load all the properties by clicking the “Show all managed properties” option at the bottom. For this example, I want to restrict the results to a particular site, so I chose the “Site” property, and then filled in the URL for the site. You can use the “Test Query” button to confirm there are results coming through, provided your site already has some content.

Click OK to save the updated settings.

Next you will likely want to do some tidying up, and possibly even include a new tab for your new search results page.

Choose “Edit Web Part” for the Search Box further up at the top of the page, and click the checkbox for “Use this site’s Search Settings”.

Also click the link where it is hyperlinked to “Search Settings”. This is where you can add a new tab to the search navigation for the page. Give it a title, and then put in the link to the custom results page you just created. Click OK for the Search Settings, and also OK for the Search Box settings.

Save and publish the page. Run a few queries to make sure you didn’t miss anything.

That’s it! You are done.

You can also reference search boxes on your other sites to use this custom results page. Just point them there.

Locking Down Search for a Site (Part 1 – SP2010, SP2013*)

One of the SharePoint features that does not always work as expected is the “Search This Site” component. If one is doing a search using this option, the results page can sometimes get confusing very quickly for unexperienced SharePoint users. Results are typically presented for the site in question, however the user is then presented with too many options for making ongoing searches. What is worse, is that if the user alters their search, they may lose the context of searching just that one site, and in turn get results for the entire enterprise instead.

Fortunately, SharePoint has some quite simple options for remedying this. For SharePoint 2010 & 2013 (in the 2010 UI), there is the Search Scopes option, and in 2013 (UI) onwards there is an option for the customization of the results page, which I will be documenting in a follow-up post to this one.

The first step is it to set up a new scope for the site you wish to limit the results to. To do so, navigate to the root of the site collection, and click “Site Actions”, then “Site Settings”. Find the link labeled “Search Scopes”.

Once on the Scopes page, click the “New Scope” link.

Give the new scope a title. Preferably something short, and with no spaces. The reason for this is you will need to use this exact title later on. Unless you need the other options changed for some reason, you can feel free to leave them as is. The scope will still be available for use, even if the check boxes are not selected.

The next step is to set up some rules for the scope you just created, which will tell the scope which content it should hold. Look through the scopes on the View Scopes page, until you locate the scope you just created. Click the “Add Rules” link (you can also click “Edit Properties and Rules” from the drop down if you are going back to change or add more rules).

From here, you will want to set the scope to be just to the site. Set the URL to the domain, and click OK.

From here, you will want to set up the search results page that you want to use for the site where you will have the limited results. You have a couple of options here for placement. You can either add the results page to the root search site, or create a new Search Center and add it as a subsite under the site you are working on. The reason you may want to set up a new Search Center is for when you want to maintain the same branding presence as the site you are providing results for.

You will want to create a new “results page” in the “Pages” area of the search site. For example, you could add the page to https://company.com/search/Pages/Forms/AllItems.aspx (Click the “Documents” tab, Click the New Documents drop down, and select “Welcome Page”, then “Search Results Page”)

If using a new search center as a subsite (Site Actions -> Site Settings -> Sites and Workspaces -> Create -> Under the Enterprise tab choose Enterprise Search Center), you could just modify the existing results page: https://company.com/SpringDemo/search/Pages/results.aspx

Once on the results page, click the “Page” tab, and then “Edit”

Now you are ready to plug in the custom scope you created in the prior step. Scroll down to the Search Core Results web part, and click the “Edit Web Part” option from the drop down.

In the settings area, expand the “Location Properties”, and in the textbox labeled “Scope”, add in the scope name of the scope you created earlier.

Click OK, and then check in and publish the updated results page.

The last step is to wire up a search box to the updated results page.

For this example, we will be wiring up a search box on the home page of the site.

The first thing you may want to do is hide the search bar in the masterpage.

The reason for this is that this search bar is not directly customizable. The only way around that is to use some custom JavaScript to override the default search destination. If you are using the default SharePoint masterpage, you can hide it by using a bit of custom CSS ( #s4-searcharea {display:none} ). You can put that in a .css file, and if you have publishing turned on, add it as a css file to be used for the site.

After completing that optional step, you are finally ready to complete the final step. Add a “Search Box” web part to where you would like it placed. Then choose “Edit Web Part”

If you want to keep searches limited to just this site, first hide the scopes drop down (default to target results page).

Under the Miscellaneous section, add put in the link to the results page you modified earlier (ie. /SpringDemo/search/Pages/results.aspx)

Last you will want to change the chrome type and title so that people will know what it is for.

Click OK, and stop editing (publish if necessary) the page. You should be ready to test/go now.

As a last optional step, if you want to put a link on the search results page so that the user can get easily back to the home page, you do that by adding a content editor web part to add a link.

SharePoint Migration Tool Error – 0x01710009

When using the SharePoint Migration Tool to move to 365, you may encounter some cryptic errors in the tool’s error reports, such as the following:

(In this example I was trying to move a discussion board from SP 2013 on-prem)

Source Destination Item name Extension Item size Content type Status Result category Message Error code

https://sp.contoso.com/Lists/Discussions/TestThread https://contoso.sharepoint.com/Lists/Discussions/TestThread TestThread 0 Folder Failed SERVER FAILURE Errors or timeout for Server Processing the file:Not all the items in the package have been migrated 0x01710009…………

https://sp.contoso.com/Lists/Discussions/TestThread/2_.000 https://contoso.sharepoint.com/Lists/Discussions/TestThread/2_.000 2_.000 0 0 Item Failed SERVER FAILURE Errors or timeout for Server Processing the file:Not all the items in the package have been migrated 0x01710009………………

In my case, the solution was to look into the “Details” folder of the Migration Tool report, and look at the UserNotMapped_R1.csv file. This contained a list of SIDs for users for our on-prem installation, that while their accounts where in SharePoint, they did not exist in our AD. Basically the Migration Tool errors out when it encounters a problem mapping an account to 365 SharePoint.

The solution was to look them up from the on-prem installation, and then delete them:

PS C:\Users\SPFarm> $u = Get-SPUser -Web https://sp.contoso.com -Limit ALL |where {$_.Sid -eq "s-1-5....."}
PS C:\Users\SPFarm> $u

UserLogin DisplayName
--------- -----------
CONTOSO\jdoe1  John Doe

Then, once the user account was known, visit https://sp.contoso.com/_catalogs/users and create a custom view to filter on the “UserLogin” listed above, with an option to Edit. If you click on the “Edit” next to their account, you will have an option to delete them from the site collection. I did this for all the SIDs provided in the file, and it resolved the issue.

Disable Install Silverlight Prompt in SharePoint 2013

If you have users that are still using IE to visit your SharePoint 2013 sites, they may encounter an IE message telling them they should install the latest version of Silverlight (which is quite dead). All this is really used for is the gallery for installing new apps/webparts. This can easily be disabled by running a bit of PowerShell (by disabling this, users will still get the fallback functionality as you would in Chrome, etc.)

$webapp = Get-SPWebApplication http://sitename
//check to see if the Silverlight prompt is enabled
$webapp.allowsilverlightprompt
//if it returns as true, then set it to false and update
$webapp.AllowSilverlightPrompt = $false
$webapp.Update()

Remove a SharePoint Search Server from the Search Service Application

Occasionally there will be instances where you need to remove a server from the Search Service Application, if you have multiple search servers set up in the farm. To do so, especially if the server is unavailable, or offline, follow the steps below:

$ssa = Get-SPEnterpriseSearchServiceApplication
$active = Get-SPEnterpriseSearchTopology -SearchApplication $ssa -Active
$clone = New-SPEnterpriseSearchTopology -SearchApplication $ssa -Clone -SearchTopology $active
$clone.GetComponents()

//use the output from above to grab the names of each component from the server you wish to remove.  remove each component as necessary.
Remove-SPEnterpriseSearchComponent -SearchTopology $clone -Identity ($clone.GetComponents() | Where-Object {$_.Name -eq "ContentProcessingComponent3"}) -Confirm:$false

//check the new components topology
$clone.GetComponents()

//set the topology
Set-SPEnterpriseSearchTopology -Identity $clone

SharePoint Search Box Not Firing When Enter Pressed in IE

Ran into an issue in SP2013/SP2010 where the Enter/Return key would default to other page elements instead of taking the keywords and conducting the search if the Enter key was pressed. So for example, if there was a button on the page, it would fire that element, instead of the search box. Here is a simple bit of jQuery which will ensure that the user’s search gets completed:

//fix for IE 11 Enter/Return key not firing for Search box
jQuery("input[id^=ctl00_PlaceHolderSearchArea]").keypress(function(event){
  if(event.which == 13) 
  {
    window.location = jQuery("a[id^=ctl00_PlaceHolderSearchArea]").attr('href');
   }		        
});

Filtering SharePoint Search by Calendar Dates

SharePoint, out of the box does not support filtering calendar dates. The reason for this is that to search, they are set up as strings, thus running a comparision is not achievable. Example: Let’s say you are using the Search API, and only want to show events that have not taken place yet. There is nothing within search that will allow you to do this, without doing some tweaks.

In order to accomplish this, you will first need to set up a custom mapping for search to pick up the calendar date as a DateTime datatype.

1. In Central Admin, click on the Search Service Application

2. Click on Search Schema

3. Click “New Managed Property” and fill it out like the following. Be sure to set the type as “Date and Time”. Then click OK.

Be sure to map the “ows_q_Date_EndDate” and “ows_q_Date__EndDate” properties.

4. Once the property has been mapped, you will need to run a Full Crawl in order for search to pick up the new property.

5. Then after the crawl has finished, and the new mapped datatype is available, you can run a query for new training events that have not yet taken place:

https://sharepoint.company.com/_api/search/query?querytext='*training* -path:"https://sharepoint.company.com/management/"'&refinementfilters='and(ContentTypeID:0x0102*,CalendarEndDate:range([Today],max,from="ge"))'&clienttype='AllResultsQuery'&rowlimit=10&sortlist='CalendarEndDate:descending'

A few things to note in the query:

ContentTypeID:0x0102* is the SharePoint content type for calendar events
[Today] will need to be replaced in your code with today’s actual date and time in string format, so: ‘2017-01-01’ (or DateTime.Now.ToString(“yyyy-MM-dd”))
This query also includes an optional exclusion (-path:”https://sharepoint.company.com/management/”), if you do not want a particular calendar or site included in the results.

This will return you calendar events matching the keyword, that have also not taken place yet.

Setting up Office Web Apps Server for SharePoint & Publishing via ADFS WAP

1. Create a new vanity DNS entry for Office Web Apps that points to the ADFS WAP for public consumption (ie. officewebapps.contoso.com -> 10.92.92.100)

2. Download and run install software [i.e. en_office_web_apps_server_2013_with_sp1_x64_dvd_3833121] on server that is not in SharePoint Farm. Office Web Apps Server cannot be installed on server hosting SharePoint. There are several prerequisites that it may prompt you to install (IIS, .NET, etc.). You may also need to uninstall this hotfix for .NET 4.6.1: Update for Microsoft Windows (KB3102467)

3. Ensure there is a cert installed on the Office Web Apps Server for use (i.e. wildcard), if none is available, one must be installed. Set the friendly name if none exists. Install certificate under Personal Certificates for the local computer.

To set friendly name, run MMC as an admin. Go to “File”, “Add/Remove Snap In”, choose “Certificates”, manage Certificates for “Computer account”, then “Next”, keep option for “Local Computer” selected, click “Finish”, then “OK”. Navigate to Personal Certificates, then right click on the certificate you just installed and click “Properties.” Set the friendly name to a name of your choice.

4. After install, run the following PowerShell on the Office Web Apps Server:

 New-OfficeWebAppsFarm -Verbose -InternalUrl https://officewebapps.contoso.com -ExternalUrl https://officewebapps.contoso.com 
-CertificateName CertFriendlyName -ClipartEnabled –TranslationEnable

5. On SharePoint Server, run the following under the SharePoint Management Shell:

 New-SPWOPIBinding -ServerName webappsservername.contoso.com

(must be fully qualified server name)

(Optional depending on your internal network configuration):

If you are seeing error messages (such as Event ID 3005, “We couldn’t find the file you wanted”, “Server Error in ‘/x’ Application”, etc.) You may also need to add a hosts entry on your Office Web Apps Server that points portal.contoso.com directly to your SharePoint WFE. The reason being is that OWAS cannot read the Office files from SharePoint through ADFS, if portal.contoso.com resolves to your ADFS.

7. Log onto ADFS WAP server. Add hosts file entry that points officewebapps.contoso.com to actual Office Web Apps Server IP to override the DNS entry. (ie. 10.92.92.101 officewebapps.contoso.com)

8. Publish Office Web Apps Server with public URL (https://officewebapps.contoso.com). Set to “Pass Through” instead of “ADFS”.

9. Your done. To test, try opening an Office document in SharePoint, or try navigating to: https://officewebapps.contoso.com/hosting/discovery