SharePoint 2013 – Call Search REST API from PowerShell Script

Add-Type -AssemblyName System.Web

$sServerPath = “http://<Site URL>”
$sQueryOptions = “selectproperties=’ManagedPropertyName’&clienttype=’ContentSearchRegular’&QueryTemplatePropertiesUrl=’spfile://webroot/queryparametertemplate.xml'”
$sUserName = “user name”
$sPassword =”password”
$sDomain=”domain name”
$WebRMethod=[Microsoft.PowerShell.Commands.WebRequestMethod]::Get

function Get-SPSearchResults
{
param ($keyword,$sUserName,$sPassword, $sDomain, $WebRMethod)
try
{
$spCredentials = New-Object System.Net.NetworkCredential($sUserName,$sPassword,$sDomain)
$enckeyword = [System.Web.HttpUtility]::UrlEncode(“‘$keyword'”)
$url = $sServerPath+”/_api/search/query?querytext=$enckeyword”+”&$sQueryOptions”

$spWebRequest = [System.Net.WebRequest]::Create($url)
$spWebRequest.Credentials = $spCredentials
$spWebRequest.Accept = “application/json;odata=verbose”
$spWebRequest.Method=$WebRMethod
$spWebResponse = $spWebRequest.GetResponse()
$spRequestStream = $spWebResponse.GetResponseStream()
$spReadStream = New-Object System.IO.StreamReader $spRequestStream
$spData=$spReadStream.ReadToEnd()
$results = $spData | ConvertFrom-Json
$results = $results.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results

for($i=0; $i -le $results.length-1; $i++)
{

$row = $results[$i]

for ($j=0; $j -le $row.Cells.results.length-1; $j++)
{

if ($row.Cells.results[$j].Key -eq ‘Title’)
{
Write-Host $row.Cells.results[$j].Value -ForegroundColor Green
}
}
}
}
catch
{
Write-Host $_.Exception.Message -ForegroundColor Red
}
}

try
{
Get-SPSearchResults -keyword “ca” -sUserName $sUserName -sPassword $sPassword -sDomain $sDomain -WebRMethod $WebRMethod
}
catch
{
Write-Host “Problem in running the REST API. Failed with error : ” + $_.Exception.Message -ForegroundColor Red
}

Advertisements

SharePoint 2010 not showing results – Files with big size

Problem Statement:

Our current search does not seem to include older Office versions (e.g. ppt) in SharePoint 2010 Search results.  Why did we limit this?

Issue:

When I received the above mentioned problem statement I started exploring the below:

  • Is there a problem in crawl / any restrictions configured to specific file types – Test passed
  • Is Browser Locale setting in the Search Result Core webpart trimming the results specific to some location – Test passed
  • Is Remove Duplicate Results settings  in the Search Result Core webpart causing any issue– Test passed
  • Compared the file size between the files which are appearing the search results and the issue causing file – Test partially failed, the file of the disappearing files are always bigger than the files which are appearing in the search results.  

 I stopped here and started exploring is there a limitation in the crawler settings to crawl the file contents with big size and found that SharePoint is by default limited to crawl file contents which are less than 16 MB.

So SharePoint when it crawls the files from the document library or list, if the file size exceeds this 16 MB limit then it will only crawls the basic meta data which are associate to that list /library such as Title, Created By, Modified By. The contents inside the file will not be crawled.  

To increase this limit we can run the following PowerShell script. But the impact will be on the crawl time. So we have to analyze the environment and perform this action.

$ssa = Get-SPEnterpriseSearchServiceApplication

$ssa.SetProperty(“MaxDownloadSize”, 25)

$ssa.Update()

We can set this limit to specific file type as shown below:  

$ssa.SetProperty(“MaxDownloadSizeExcel”, 25) 

 

Manage SharePoint incremental crawl index deletion

Problem

In SharePoint 2013, we have a content source that connects via BCS. The total items in the External table are around 7.7 million records. We have a full crawl scheduled to crawl the BCS items every month and an incremental crawl to run every 4 hrs.

In our environment the full crawl takes 18 hrs to crawl 7.7 million records and incremental crawl takes 2-3 hrs based on the number of items modified in the external table.

Things were moving fine until the incremental crawl deleted all the indexes from the content source when it failed (due to permission issue) to crawl BCS items. When we checked the crawl log we found that the incremental crawl only triggered the delete operation in the SharePoint Search Service.

Finally we came to know that Microsoft enforced some policies in SharePoint Search Service to delete the index based on some conditions. I am not sure we can disable this setting but we can increase the thresholds through PowerShell.

If somebody knows how to disable this setting / policy please let me know….

Solution / Workaround

Please find the below article which explains about this issue:

http://technet.microsoft.com/en-us/library/hh127009.aspx

But few intervals are changed in 2013 version of SharePoint

Comparison of Default Values between 2010 and 2013:

Thanks to Simon’s blog for sharing this information in his blog.  

Property SP 2010 SP 2013
ErrorDeleteCountAllowed 30 times 10 times
ErrorDeleteIntervalAllowed 720 hours 240 hours
ErrorCountAllowed 100 times 15 times
ErrorIntervalAllowed 1440 hours 360 hours
RecrawlErrorCount 10 times 5 times
RecrawlErrorInterval 360 120 hours
DeleteUnvisitedMethod 1 1

 

 

SharePoint 2013 Search with Refiner Webpart Out of memory error Issue

I have been waiting for an answer for the following issue. I felt it’s a MS bug since I can reproduce this in the OOB SharePoint 2013 page without any customization. Before I conclude this as bug I wanted to confirm with Microsoft.

Finally I came to know from a Microsoft field engineer that it’s a MS bug which they are going  to fix in the April 2014 CU.

To Reproduce please follow the below steps:

  • Create new site collection
  • Create new a Page
  • Add search results &  search refiner WebParts in the new page created
  • Search for something
  • Click on any refiner column and filter the result
  • Click browser back button. You will find the following message.

bug

FYI: The same page will work without any error if you remove the refinement panel WebPart from the page.

So let us wait for the April 2014 CU.

SharePoint Search Suggestions

Where the Search Suggestions are saved in SharePoint?

When we start typing the keywords in the SharePoint search box control it internally performs an AJAX call to
/_vti_bin/client.svc/ProcessQuery and that calls the stored procedure proc_MSS_GetQuerySuggestions.
The proc_MSS_GetQuerySuggestions stored procedure gets the suggestions from the MSSQLogSearchCounts table.
This table exist in the <Search Service Application Name>__DB_<GUID> Database.

How Suggestions are saved?

  1. Manual ( using code / PowerShell / csv file import from central admin)
  2. Automatically by prepare query suggestions timer job.

What determines a query suggestion?

  • Query suggestions are based on frequently used search terms and click through to results. Listed below are the steps needed for SharePoint to promote a term to a query suggestion.
  • The associated Search Service application must have query logging enabled.
  • The user must type in a valid word or set of words in the search box.
  • The user must click on 6 or more search result links and open or save the file. Some types of pages containing the term can qualify as click-throughs.
  • The Query Logging timer job must be run.
  • The Prepare Query Suggestions time job must be run.

Some Powershell Operations related to Search Suggestion

#Add Single Suggestion

Add-PSSnapin “Microsoft.SharePoint.Powershell” -ErrorAction SilentlyContinue
$searchapp = Get-SPEnterpriseSearchServiceApplication -Identity “Search Service App 2”
$owner = Get-SPEnterpriseSearchOwner -Level SSA
Get-SPEnterpriseSearchQuerySuggestionCandidates -SearchApplication $searchapp -Owner $owner
New-SPEnterpriseSearchLanguageResourcePhrase -SearchApplication $searchapp -Language En-Us -Type QuerySuggestionAlwaysSuggest -Name
“Honda” -Owner $owner
Start-SPTimerJob -Identity “prepare query suggestions”

#Add More than one Suggestion

Add-PSSnapin “Microsoft.SharePoint.Powershell” -ErrorAction SilentlyContinue
$SearchSuggestionsList = @(“Car”, “Honda”, “F1”)
$searchapp = Get-SPEnterpriseSearchServiceApplication -Identity “Search Service App 2”
$owner = Get-SPEnterpriseSearchOwner -Level SSA
Get-SPEnterpriseSearchQuerySuggestionCandidates -SearchApplication $searchapp -Owner $owner

foreach ($Suggestion in $SearchSuggestionsList)
{
New-SPEnterpriseSearchLanguageResourcePhrase -SearchApplication $searchapp -Language En-Us -Type QuerySuggestionAlwaysSuggest –
Name $Suggestion -Owner $owner
}

Start-SPTimerJob -Identity “prepare query suggestions”

#Get All Search Suggestions

Add-PSSnapin “Microsoft.SharePoint.Powershell” -ErrorAction SilentlyContinue

$searchapp = Get-SPEnterpriseSearchServiceApplication -Identity “Search Service App 2”
$owner = Get-SPEnterpriseSearchOwner -Level SSA
Get-SPEnterpriseSearchQuerySuggestionCandidates -SearchApplication $searchapp -Owner $owner

#Remove Single Search Suggestion

Add-PSSnapin “Microsoft.SharePoint.Powershell” -ErrorAction SilentlyContinue

$searchapp = Get-SPEnterpriseSearchServiceApplication -Identity “Search Service App 2”
$owner = Get-SPEnterpriseSearchOwner -Level SSA
Get-SPEnterpriseSearchQuerySuggestionCandidates -SearchApplication $searchapp -Owner $owner

Remove-SPEnterpriseSearchLanguageResourcePhrase -SearchApplication $searchapp -Language En-Us -Type QuerySuggestionAlwaysSuggest –
Identity “Honda” -Owner $owner

Start-SPTimerJob -Identity “prepare query suggestions”

Suggestions Table

Searching SharePoint Results from Windows Explorer using OSDX and Custom RSS File

Environment:

SharePoint 2013 Enterprise Search with Windows 7 Operating Systems

Introduction:

To understand what is a OSDX file and how to add an OSDX file as a search provider in Windows Explorer please refer the below links:

https://www.nothingbutsharepoint.com/sites/eusp/Pages/sharepoint-2010-search-locations-in-windows-7.aspx

http://technet.microsoft.com/en-us/library/ff899315(v=office.14).aspx

Problem:

When I use the OOB SharePoint RSS file to show the results in the windows explorer I was not able to map the custom managed properties to the windows attributes such as Summary,  thumbnail, etc.,

The OOB SharePoint RSS file located in the following location:

http://<Site>/_layouts/srchrss.aspx

Regarding this issue, when I approached some MVPs and Microsoft Windows Development team they replied that it is not possible to map the SharePoint managed properties with the windows explorer search result attributes.

Some of the articles are also explaining the same

http://msdn.microsoft.com/en-us/library/dd742951(v=VS.85).aspx

https://communities.netapp.com/blogs/TheSharePointGuy/2011/09/22/windows-7-sharepoint-search-connector

So what is the solution for this?

Solution:

Windows 7 is the first OS with the capability to use external search providers as federated search locations – and yes, this federated location can also be SharePoint!  In general, all search providers can be federated if it’s OpenSearch 1.0/1.1 compatible or, in other words, can provide the search results in RSS/Atom format.

So that means if I have to create my own RSS feeding file then my custom code should return the SharePoint search results in RSS/Atom format. So I started exploring what is the best way to get the search results. Then I found a interesting feature in SharePoint 2013 called “SharePoint 2013 Search REST API”. This new REST service is the best way to go in a variety of application scenarios. External Applications to SharePoint that require search functionality can also leverage this service as the endpoint for communication into the Search platform. The old search.asmx SOAP web service is marked as deprecated in SP2013, and the new Search REST service is the replacement.

Location of the Search Rest service

The Search REST service is located at the following URI: http://host/site/_api/search

See more about this from the below link

http://blogs.msdn.com/b/nadeemis/archive/2012/08/24/sharepoint-2013-search-rest-api.aspx

Using the above mentioned API I am supplying the keyword to the API and getting the XML output. From the XML output I am extracting the necessary XML Node values and populating the output in the format of RSS/Atom using the below code:

Front end Code

<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”SearchAppliedRSS.aspx.cs” Inherits=”SearchResultsRSS.Layouts.SearchResultsRSS.SearchAppliedRSS” ContentType=”text\xml” %>

<asp:Repeater ID=”ResultRSSRepeater” runat=”server”>

<HeaderTemplate>

<rss version=”2.0″>

<channel>

<title>Nucleus</title>

<link>Configure you site URL</link>

<description>

Nucleus Search Center Site.

</description>

</HeaderTemplate>

<ItemTemplate>

<item>

<title><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “Title”)) %></title>

<link>Item URL</link>

<description><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “Description”))%></description>

<category><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “Category”)) %></category>

<pubDate><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “LastModifiedTime”))%></pubDate>

<author><%# RemoveIllegalCharacters(DataBinder.Eval(Container.DataItem, “Author”))%></author>

</item>

</ItemTemplate>

<FooterTemplate>

</channel>

</rss>

</FooterTemplate>

</asp:Repeater>

Back end Code

protected void Page_Load(object sender, EventArgs e)

{

string keyword = Request.QueryString[“keyword”];

string query = “http://<Site Name>/_api/search/query?querytext='” + keyword + “‘&sourceid=’c9a51276-5b58-423c-88cb-a64297974cb4’&selectproperties=’Author,TCItemType,TCItemName,Description,LastModifiedTime'”;

WebRequest req = WebRequest.Create(query);

NetworkCredential myCred = new NetworkCredential(“<Admin Account User ID>”,”Password”,”Domain Name”);

req.Credentials = myCred;

WebResponse resp = req.GetResponse();

System.IO.StreamReader sr = new System.IO.StreamReader(resp.GetResponseStream());

// process response..

XDocument oDataXML = XDocument.Load(sr, LoadOptions.None);

XNamespace atom = “http://www.w3.org/2005/Atom&#8221;;

XNamespace d = “http://schemas.microsoft.com/ado/2007/08/dataservices&#8221;;

XNamespace m = “http://schemas.microsoft.com/ado/2007/08/dataservices/metadata&#8221;;

List<XElement> items = oDataXML.Descendants(d + “query”)

.Elements(d + “PrimaryQueryResult”)

.Elements(d + “RelevantResults”)

.Elements(d + “Table”)

.Elements(d + “Rows”)

.Elements(d + “element”)

.ToList();

// Extracting the values from XML..

DataTable dt = new DataTable();

dt.Columns.Add(“Title”);

dt.Columns.Add(“Author”);

dt.Columns.Add(“Category”);

dt.Columns.Add(“Description”);

dt.Columns.Add(“LastModifiedTime”);

foreach (XElement item in items)

{

DataRow dr = dt.NewRow();

//XElement xItem = item.Element(d + “Cells”).Descendants(d + “Key”).First();

foreach (XElement xItem in item.Element(d + “Cells”).Descendants(d + “Key”))

{

if (xItem.Value == “TCItemName”)

{

dr[“Title”] = xItem.Parent.Element(d + “Value”).Value;

}

else if (xItem.Value == “Author”)

{

dr[“Author”] = xItem.Parent.Element(d + “Value”).Value;

}

else if (xItem.Value == “Description”)

{

dr[“Description”] = xItem.Parent.Element(d + “Value”).Value;

}

else if (xItem.Value == “LastModifiedTime”)

{

dr[“LastModifiedTime”] = xItem.Parent.Element(d + “Value”).Value;

}

dr[“Category”] = “Teamcenter Item”;

}

dt.Rows.Add(dr);

}

// data-bind to ListView..

ResultRSSRepeater.DataSource = dt;

ResultRSSRepeater.DataBind();

}

protected string RemoveIllegalCharacters(object input)

{

// cast the input to a string

string data = input.ToString();

// replace illegal characters in XML documents with their entity references

data = data.Replace(“&”, “&amp;”);

data = data.Replace(“\””, “&quot;”);

data = data.Replace(“‘”, “&apos;”);

data = data.Replace(“<“, “&lt;”);

data = data.Replace(“>”, “&gt;”);

return data;

}

Finally instead of configuring the SharePoint OOB search RSS file, I have configured the customized RSS Feed file which I have created using the above code in the OSDX file.

How to create RSS?

http://net.tutsplus.com/tutorials/asp-net/how-to-build-an-rss-feed-with-asp-net/

Now I am able to see the results with the format I have specified in the code. Please see the below screen:

search-result-in-we

Search Database Related Tables using BCS

The following solution is designed and developed with the help of my friend (Sreeharsha) and Scott Hilier

Environment:

SharePoint 2013 Enterprise with all the Services enabled on the same server.

SQL Server 2008 R2 Standard also on the same SharePoint standalone server.

Requirement:

Configure BCS to search a Database, where the Tables have a parent-child relationship. When searched for any term within the child table, the item linked in the parent item has to be displayed on the result page than the searched term or item from the child table.

The requirement makes sense in a way, that the child tables are considered as paragraphs within a document. When searched for a term within the paragraph, you would expect the link to the document to be displayed. After all the child tables will always be additional information about the parent item.

Design:

While designing for this requirement the data to be retrieved from the parent table and the child table has to be from two different entities, however they need be within the same LOBInstance.

By which, we have control to define the association between these entities within the Model.

Here is how the Method and the method instance need to be defined.

<Method Name=”PartItemToRevisionItem”>

<Properties>

<Property Name=”HideOnProfilePage” Type=”System.Boolean”>true</Property>

</Properties>

<Parameters>

<Parameter Name=”partID” Direction=”In”>

<TypeDescriptor Name=”PartID” TypeName=”System.String” IdentifierEntityName=”PartItem” IdentifierEntityNamespace=”MultiBCS.MultiEntity” IdentifierName=”PartID” ForeignIdentifierAssociationName=”PartItemToRevisionItem” />

</Parameter>

<Parameter Name=”revisionAssociationItemList” Direction=”Return”>

<TypeDescriptor Name=”RevisionItemList” TypeName=”MultiBCS.MultiEntity.RevisionItem[], MultiEntity” IsCollection=”true”>

<TypeDescriptors>

<TypeDescriptor Name=”RevisionItem” TypeName=”MultiBCS.MultiEntity.RevisionItem, MultiEntity”>

<TypeDescriptors>

<TypeDescriptor Name=”RevisionID” TypeName=”System.String” IdentifierEntityName=”RevisionItem” IdentifierEntityNamespace=”MultiBCS.MultiEntity” IdentifierName=”RevisionID” />

<TypeDescriptor Name=”Revision_Name” TypeName=”System.String” />

<TypeDescriptor Name=”Revision_Cost” TypeName=”System.Int32″ />

<TypeDescriptor Name=”PartID” TypeName=”System.String” />

<TypeDescriptor Name=”Revision_Type” TypeName=”System.String” />

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<Association Name=”PartItemToRevisionItem” Type=”AssociationNavigator” ReturnParameterName=”revisionAssociationItemList” IsCached=”false”>

<Properties>

<Property Name=”AttachmentAccessor” Type=”System.String”></Property>

</Properties>

<SourceEntity Name=”PartItem” Namespace=”MultiBCS.MultiEntity” />

<DestinationEntity Name=”RevisionItem” Namespace=”MultiBCS.MultiEntity” />

</Association>

</MethodInstances>

</Method>

The association method can be written in any entity (parent or child). But the tag that defines the source and the destination can be seen within the MethodInstance.

Silver Bullet:

Of all this requirement the most important part and the one that defines that the child tables need to be crawled as attachments by which I mean that, “when searched for a term in the child table, the item linked in the parent table to be displayed”, the below Tag needs to be added within the method instance, otherwise which multiple results will be displayed for a single record which is unwanted.

<Property Name=”AttachmentAccessor” Type=”System.String”></Property>

Thanks to Scott Hilier to the right pointer on the “Attachment Accessor”  which is significant for this requirement.

Resources:

How to: Crawl associated external content types in SharePoint 2013

Modeling Associations in External Data