Adding custom columns in Search results SharePoint 2013

Search Results with custom columns

We created a Custom Commenting System in SharePoint (I’ll right another blog for it). The commenting system is based on SP Lists.
But now the requirements was to link the comments in search results as image shown below. The red box is the output of the solution.

Search Results with custom columns
Search Results with custom columns

First we need to add managed properties that will used in Search Results.

Steps for adding managed property:

  • Open Central Admin
    • Click General Application Settings
    • Click Farm Search Administration under Search
    • Click Search Service Application
  • Under Search Service Application: Search Administration
    • Click Search Schema under Queries and Results
    • Click New Managed Property as shown below and create each of the Managed property items in the follow table/steps. These outline the Property and the values that need to be set for each property
      • Managed Property Name
        pagecommentcount
        pagefavscount
        ***pageid

*** Under ‘Mappings to crawled properties’ for pageid ONLY

  • Label Value
    Property Name pagecommentcount
    Type Integer
    Searchable TRUE
    Queryable TRUE
    Retrievable TRUE
    Allow multiple values FALSE
    Refinable Yes-Active
    Sortable Yes-active
    Token Normalization TRUE
    Complete Matching FALSE
    Company name extraction FALSE
    Custom entity extraction FALSE
  • Make sure ‘Include content from all crawled properties’ is selected.

  • Click Add a Mapping
  • Search ID.
  • Select ows_ID and click ok.
  •  Click OK.

Repeat Step for all the managed properties

Create a WCF service

Reference to the blog Content Enrichment Web Service in SharePoint 2013 for creating Content Enrichment Service.

My code looks like this:



' NOTE: You can use the "Rename" command on the context menu to change the class name "Service1" in code, svc and config file together.
' NOTE: In order to launch WCF Test Client for testing this service, please select Service1.svc or Service1.svc.vb at the Solution Explorer and start debugging.
Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Runtime.Serialization
Imports System.ServiceModel
Imports System.Text
Imports Microsoft.SharePoint
Imports Microsoft.Office.Server.Search.ContentProcessingEnrichment
Imports Microsoft.Office.Server.Search.ContentProcessingEnrichment.PropertyTypes
Imports Microsoft.SharePoint.Administration
Imports System.Security.Cryptography
Imports System.Data.SqlClient

Public Class ContentEnrichmentService
    Implements IContentProcessingEnrichmentService

#Region "[Global variables]"

    Dim commentCount As Integer = 0
    Dim pageID As Integer = 0
    Dim pageLikes As Integer = 0
    Dim pageFavs As Integer = 0
    Dim pagePathHost As String = String.Empty

    Private Const Passphrase As String = "XXXX-XXXX"

    Private ReadOnly processedItemHolder As New ProcessedItem() With {
         .ItemProperties = New List(Of AbstractProperty)()
    }

#End Region

    ''' <summary>
    ''' Process Item
    ''' </summary>
    ''' <param name="item">Item</param>
    ''' <returns>ProcessedItem</returns>
    ''' <remarks></remarks>
    Public Function ProcessItem(item As Item) As ProcessedItem Implements IContentProcessingEnrichmentService.ProcessItem

        processedItemHolder.ErrorCode = 0
        processedItemHolder.ItemProperties.Clear()

        'Get pageid from input properties
        Dim pageIdProperty = item.ItemProperties.Where(Function(p) p.Name = "pageid").FirstOrDefault()

        Dim pathProperty = item.ItemProperties.Where(Function(p) p.Name = "Path").FirstOrDefault()

        If pageIdProperty IsNot Nothing AndAlso pathProperty IsNot Nothing Then

            pageID = Convert.ToInt32(pageIdProperty.ObjectValue)

            Dim oUri As String() = pathProperty.ObjectValue.ToString().Split("/")

            If oUri.Length > 2 Then
                pagePathHost = oUri(2)

                'Get total comments from SP List
                SPSecurity.RunWithElevatedPrivileges(AddressOf GetCommentCount)

                'Get total page like and favs from logger database
                GetPageDetails()

            End If

        End If

        'Add pagecommentcount managed property
        Dim oCommentCount = New [Property](Of Integer)()
        oCommentCount.Name = "pagecommentcount"
        oCommentCount.Value = commentCount
        processedItemHolder.ItemProperties.Add(oCommentCount)

        'Add pagelikescount managed property
        Dim oPageLikes = New [Property](Of Integer)()
        oPageLikes.Name = "pagelikescount"
        oPageLikes.Value = pageLikes
        processedItemHolder.ItemProperties.Add(oPageLikes)

        'Add pagefavscount managed property
        Dim oPageFavs = New [Property](Of Integer)()
        oPageFavs.Name = "pagefavscount"
        oPageFavs.Value = pageFavs
        processedItemHolder.ItemProperties.Add(oPageFavs)

        Return processedItemHolder

    End Function

    ''' <summary>
    ''' Get Comment Count
    ''' </summary>
    ''' <remarks>Get comments from SP comment list from helper site</remarks>
    Public Sub GetCommentCount()

        Try
            Dim helperSiteUrl As String = ConfigurationManager.AppSettings("HelperSiteRelativeUrl").ToString

            helperSiteUrl = String.Format("http://{0}{1}", pagePathHost, helperSiteUrl)

            Using oSpSite As New SPSite(helperSiteUrl)

                Using oSpWeb As SPWeb = oSpSite.OpenWeb()

                    Try
                        Dim oSpList As SPList = oSpWeb.Lists.TryGetList("KnetCommentCount")

                        If oSpList IsNot Nothing Then
                            Dim oSpQuery As New SPQuery()
                            oSpQuery.ViewFields = "<FieldRef Name='PageId'/>" + _
                                                      "<FieldRef Name='PageTitle'/>" + _
                                                      "<FieldRef Name='PageUrl'/>" + _
                                                      "<FieldRef Name='TotalComments' />"

                            oSpQuery.Query = "<Where><Eq><FieldRef Name='PageId' /><Value Type='Integer'>" + pageID.ToString + "</Value></Eq></Where>" + _
                                             "<OrderBy><FieldRef Name='ID' Ascending='TRUE' /></OrderBy>"

                            oSpQuery.RowLimit = 1

                            Dim listItems As SPListItemCollection = oSpList.GetItems(oSpQuery)

                            If listItems IsNot Nothing AndAlso listItems.Count > 0 Then
                                commentCount = Convert.ToInt32(listItems.Item(0)("TotalComments"))
                            End If

                        End If

                    Catch ex As Exception
                        LogException(ex, "GetCommentCount")
                    End Try

                End Using

            End Using

        Catch ex1 As Exception
            LogException(ex1, "GetCommentCount")
        End Try

    End Sub

    ''' <summary>
    ''' Get Page Details
    ''' </summary>
    ''' <remarks>Get total page likes and favourites from loger db</remarks>
    Public Sub GetPageDetails()

        Try

            Dim connectionString As String = ConfigurationManager.ConnectionStrings("LoggerDBConnection").ToString

            Dim isBOL As Boolean = False
            Dim isWeb As Boolean = False

            Dim pageLikeID As Integer = 0
            Dim pageFavsID As Integer = 0
            Dim strPageIdKey As String = "KnetStagePageLikeID"
            Dim strPageFavKey As String = "KnetStagePageFavID"

            Dim pagePath As String() = pagePathHost.Split("-")

             strPageIdKey = "PageLikeID"
             strPageFavKey = "PageFavID"

            pageLikeID = Convert.ToInt32(ConfigurationManager.AppSettings(strPageIdKey))

            pageFavsID = Convert.ToInt32(ConfigurationManager.AppSettings(strPageFavKey))

            connectionString = DecryptString(connectionString)

            Using connection = New SqlConnection(connectionString)

                Using command = New SqlCommand("myProcedureName", connection)

                    Try
                        Dim dsResult As New DataSet
                        command.CommandType = CommandType.StoredProcedure

                        command.Parameters.Add("@PageID", SqlDbType.Int).Value = pageID
                        command.Parameters.Add("@LikeActID", SqlDbType.Int).Value = pageLikeID
                        command.Parameters.Add("@FavActID", SqlDbType.Int).Value = pageFavsID

                        Dim dataAdaptor As SqlDataAdapter = New SqlDataAdapter()
                        dataAdaptor.SelectCommand = command
                        dataAdaptor.Fill(dsResult)

                        If dsResult.Tables.Count > 0 Then

                            Dim dtTable As DataTable = dsResult.Tables(0)

                            If dtTable IsNot Nothing AndAlso dtTable.Rows.Count > 0 Then
                                pageLikes = Convert.ToInt32(dtTable.Rows(0)("PG_LKS"))
                                pageFavs = Convert.ToInt32(dtTable.Rows(0)("PG_FAV"))
                            End If

                        End If

                    Catch ex As Exception
                        LogException(ex, "GetPageDetails")
                    Finally
                        connection.Close()
                    End Try

                End Using

            End Using

        Catch ex As Exception
            LogException(ex, "GetPageDetails")
        End Try

    End Sub

    ''' <summary>
    ''' Decrypt connection string
    ''' </summary>
    ''' <param name="Message">Phrase to encrypt</param>
    ''' <returns>Encrypted message</returns>
    ''' <remarks></remarks>
    Public Function DecryptString(ByVal Message As String) As String
        Dim Results As Byte()
        Dim UTF8 As New System.Text.UTF8Encoding()

        ' Step 1. We hash the passphrase using MD5
        ' We use the MD5 hash generator as the result is a 128 bit byte array
        ' which is a valid length for the TripleDES encoder we use below

        Dim HashProvider As New System.Security.Cryptography.MD5CryptoServiceProvider()
        Dim TDESKey As Byte() = HashProvider.ComputeHash(UTF8.GetBytes(Passphrase))

        ' Step 2. Create a new TripleDESCryptoServiceProvider object
        Dim TDESAlgorithm As New System.Security.Cryptography.TripleDESCryptoServiceProvider()

        ' Step 3. Setup the decoder
        TDESAlgorithm.Key = TDESKey
        TDESAlgorithm.Mode = CipherMode.ECB
        TDESAlgorithm.Padding = PaddingMode.PKCS7

        ' Step 4. Convert the input string to a byte[]
        Dim DataToDecrypt As Byte() = Convert.FromBase64String(Message)

        ' Step 5. Attempt to decrypt the string
        Try
            Dim Decryptor As System.Security.Cryptography.ICryptoTransform = TDESAlgorithm.CreateDecryptor()
            Results = Decryptor.TransformFinalBlock(DataToDecrypt, 0, DataToDecrypt.Length)
        Finally
            ' Clear the TripleDes and Hashprovider services of any sensitive information
            TDESAlgorithm.Clear()
            HashProvider.Clear()
        End Try

        ' Step 6. Return the decrypted string in UTF8 format
        Return UTF8.GetString(Results)

    End Function

    ''' <summary>
    ''' Log exception
    ''' </summary>
    ''' <param name="ex">Exception</param>
    ''' <param name="functionName">Error generator function name</param>
    ''' <remarks></remarks>
    Public Sub LogException(ByVal ex As Exception, ByVal functionName As String)
        Try
            Dim userId As String = SPContext.Current.Web.CurrentUser.LoginName
            Dim output As String = String.Format("Function: {0} :: UserID: {1} :: Error : {2} :: StackTrace: {3}", functionName, userId, ex.Message, ex.StackTrace)
            SPDiagnosticsService.Local.WriteTrace(0, New SPDiagnosticsCategory("Content Enrichment Service", _
                            TraceSeverity.Unexpected, EventSeverity.Error), TraceSeverity.Unexpected, output, functionName + " : " + ex.StackTrace)

        Catch ex1 As Exception

        End Try
    End Sub

End Class

Host this service to our App server

Instructions for hosting Search Enrichment Service:

  1. Copy the SearchEnrichService folder with contents under path: C:\inetpub\wwwroot\wcf\. If wcf folder doesn’t exist, create it.
  2. Open IIS manager
    1. Create a new website with following properties:
      1. Site Name: SearchEnrichService
      2. Application pool: SharePoint Central Administration v4
  • Physical path: C:\inetpub\wwwroot\wcf\SearchEnrichService
  • Port: 5454 (you can use any free port number)

Test the service by opening link: http://localhost:5454/ContentEnrichmentService.svc in browser.

The following message will confirm website is running fine.

Hosted message

3. Run the powershell script ps1 via SharePoint 2013 Management shell as Administrator.

Add-PSSnapin Microsoft.SharePoint.PowerShell

Write-Host &amp;amp;quot;Removing existing Search Enrichment Service ...&amp;amp;quot; -ForegroundColor Green

$result = $false
$ssa = Get-SPEnterpriseSearchServiceApplication
$serviceUrl = &amp;amp;quot;http://localhost:5454/ContentEnrichmentService.svc&amp;amp;quot;

try
{
    Remove-SPEnterpriseSearchContentEnrichmentConfiguration –SearchApplication $ssa
    Write-Host &amp;amp;quot;- Search Enrichment Service removed&amp;amp;quot; -ForegroundColor Green
    $result = $true
}
catch [Exception]
{
    Write-Host "Failed with error" -ForegroundColor Red
    Write-Host $_.Exception.Message -ForegroundColor Gray
}

if ($result){
Write-Host &amp;amp;quot;Adding Search Enrichment Service ...&amp;amp;quot; -ForegroundColor Green


    $config = New-SPEnterpriseSearchContentEnrichmentConfiguration 
    $config.Endpoint = $serviceUrl 
    $config.InputProperties = "pageid","Path";
    $config.OutputProperties = "pagecommentcount","pagelikescount","pagefavscount";
    $config.SendRawData = $True
    $config.Trigger = "IndexOf(ToLower(Path),"/pages/") > -1)";
    $config.MaxRawDataSize = 8192
    $config.Timeout = 30000 

    Set-SPEnterpriseSearchContentEnrichmentConfiguration –SearchApplication $ssa –ContentEnrichmentConfiguration $config
    
    Write-Host &amp;amp;quot; - Search Enrichment Service Added with following parameters&amp;amp;quot; -ForegroundColor Green

    Get-SPEnterpriseSearchContentEnrichmentConfiguration –SearchApplication $ssa 

}


$config.InputProperties = “pageid”,”Path”
$config.OutputProperties = “pagecommentcount”,”pagelikescount”,”pagefavscount”

Create a Search Result display template to show these properties.

Follow the blog SharePoint 2013 Customize Display Template for Content By Search Web Part (CSWP) Part-1

My display template looks like this:


<html xmlns:mso="urn:schemas-microsoft-com:office:office" xmlns:msdt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882">
<head>
<title>Kn Web Page Item</title>

<!--[if gte mso 9]><xml>
<mso:CustomDocumentProperties>
<mso:TemplateHidden msdt:dt="string">0</mso:TemplateHidden>
<mso:MasterPageDescription msdt:dt="string">Displays a result tailored for a web page.</mso:MasterPageDescription>
<mso:ContentTypeId msdt:dt="string">0x0101002039C03B61C64EC4A04F5361F385106603006F603C951FD471419804968CB8A20BBD</mso:ContentTypeId>
<mso:TargetControlType msdt:dt="string">;#SearchResults;#</mso:TargetControlType>
<mso:HtmlDesignAssociated msdt:dt="string">1</mso:HtmlDesignAssociated>
<mso:ManagedPropertyMapping msdt:dt="string">'Title':'Title','Path':'Path','Description':'Description','EditorOWSUSER':'EditorOWSUSER','LastModifiedTime':'LastModifiedTime','CollapsingStatus':'CollapsingStatus','DocId':'DocId','HitHighlightedSummary':'HitHighlightedSummary','HitHighlightedProperties':'HitHighlightedProperties','FileExtension':'FileExtension','ViewsLifeTime':'ViewsLifeTime','ParentLink':'ParentLink','FileType':'FileType','IsContainer':'IsContainer','SecondaryFileExtension':'SecondaryFileExtension','DisplayAuthor':'DisplayAuthor','pageid':'pageid','pagecommentcount':'pagecommentcount','pagelikescount':'pagelikescount','pagefavscount':'pagefavscount'</mso:ManagedPropertyMapping>
<mso:HtmlDesignConversionSucceeded msdt:dt="string">True</mso:HtmlDesignConversionSucceeded>
<mso:HtmlDesignStatusAndPreview msdt:dt="string">http://mpi-uat-kn-stage/_catalogs/masterpage/Display Templates/Search/kn_Item_WebPage.html, Conversion successful.</mso:HtmlDesignStatusAndPreview>
<mso:ContentType msdt:dt="string">Item Display Template</mso:ContentType>
</mso:CustomDocumentProperties>
</xml><![endif]-->
</head>
<body>
    <div id="Item_WebPage">
<!--#_
        if(!$isNull(ctx.CurrentItem) && !$isNull(ctx.ClientControl)){
            var id = ctx.ClientControl.get_nextUniqueId();
            var itemId = id + Srch.U.Ids.item;
            var hoverId = id + Srch.U.Ids.hover;
            var itemDate = new Date(ctx.CurrentItem.LastModifiedTime);
            var monthNames = ["Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"];
            var modifiedDate = monthNames[itemDate.getMonth()] + " " + itemDate.getUTCDate() + ", " + itemDate.getUTCFullYear() ;
            var hoverUrl = "~sitecollection/_catalogs/masterpage/Display Templates/Search/Item_WebPage_HoverPanel.js";
            $setResultItem(itemId, ctx.CurrentItem);
            ctx.currentItem_ShowHoverPanelCallback = Srch.U.getShowHoverPanelCallback(itemId, hoverId, hoverUrl);
            ctx.currentItem_HideHoverPanelCallback = Srch.U.getHideHoverPanelCallback();
_#-->
            <div id="_#= $htmlEncode(itemId) =#_" name="Item" data-displaytemplate="WebPageItem" class="ms-srch-item">
				_#=ctx.RenderBody(ctx)=#_
                <div id="_#= $htmlEncode(hoverId) =#_" class="ms-srch-hover-outerContainer"></div>
				<div class="kn-search-result-meta">
					<div class="kn-search-meta">
						<a href="_#= ctx.CurrentItem.Path =#_">_#= ctx.CurrentItem.pagecommentcount =#_ comments</a> | Favorited _#= ctx.CurrentItem.pagefavscount =#_ times | _#= ctx.CurrentItem.pagelikescount =#_ people found this useful | Updated: _#= modifiedDate =#_
					</div>
				</div>
            </div>
<!--#_
        }
_#-->
    </div>
</body>
</html>


This is important line. We need to add mapping here with managed properties.


<mso:ManagedPropertyMapping msdt:dt="string">'Title':'Title','Path':'Path','Description':'Description','EditorOWSUSER':'EditorOWSUSER','LastModifiedTime':'LastModifiedTime','CollapsingStatus':'CollapsingStatus','DocId':'DocId','HitHighlightedSummary':'HitHighlightedSummary','HitHighlightedProperties':'HitHighlightedProperties','FileExtension':'FileExtension','ViewsLifeTime':'ViewsLifeTime','ParentLink':'ParentLink','FileType':'FileType','IsContainer':'IsContainer','SecondaryFileExtension':'SecondaryFileExtension','DisplayAuthor':'DisplayAuthor','pageid':'pageid','pagecommentcount':'pagecommentcount','pagelikescount':'pagelikescount','pagefavscount':'pagefavscount'</mso:ManagedPropertyMapping>

Now change the result Type to your newly created display template, follow this blog:

http://blogs.technet.com/b/tothesharepoint/archive/2013/09/10/how-to-create-a-new-result-type-in-sharepoint-server-2013.aspx

Troubleshooting:

If any of the above crawled properties are not available, reset the index of pages library.

  • List Setting -> Advance settings -> Reindex document library
  • Run full crawl again.
  • If still doesn’t work then Under “Search Service Application: Search Administration” do the ‘Index Reset’ under crawling tab and run full crawl again.

Leave a Reply

Your email address will not be published. Required fields are marked *