How to get Search Query API calls to be logged in search usage analysis reports
I was recently working on a customer requirement to log Search Query API calls. OOB this is easy to achieve where we have specific UI option in central admin to get this information.
On discussion with the PG, we found that by design this is not supported. The underlying Query API architecture doesn’t allow this to be logged till the Dec 2008 CU. After a lot of requests some changes were made to the architecture and a way was introduced to achieve this. Yes the way isn’t very flexible but something is better than nothing. J
To get this functionality to work below are the perquisites to achieve this:
1. Enable Usage Analysis Processing in Operations [Logging and Reporting] in Central Admin
2. Enable Usage reporting under Office SharePoint Usage Reporting in Shared Services home page
3. Enable Reporting Feature in the Site collection Feature for which you want to turn on query reporting.
4. You will have to be on April CU or SP2. As per PG the fix is in builds > 6415.1000 [make sure that stswel.dll or Tquery.dll version > 6415.1000]
5. The custom search code should be implemented as a web part. Then only it will log the query usage.
And below is the code to achieve this. Yes this code:
<Code>
using System;
using System.Runtime.InteropServices;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Serialization;
using Microsoft.SharePoint;
using Microsoft.SharePoint.WebControls;
using Microsoft.SharePoint.WebPartPages;
using Microsoft.Office.Server;
using Microsoft.Office.Server.Search;
using Microsoft.Office.Server.Search.Query;
using System.Data;
using Microsoft.SharePoint.Administration;
using Microsoft.SharePoint.Utilities;
using System.Collections.Generic;
using System.Text;
using System.IO;
using System.Xml;
using System.Drawing;
namespace MSearchWebpart
{
[Guid("252d61f3-bc01-44c8-a4d1-91342ac71ca0")]
public class MySearch : System.Web.UI.WebControls.WebParts.WebPart
{
private DataTable dt;
private DataGrid mygrid;
private TextBox MySearchTextbox;
private Button MySearchBtn;
private string SSPName = "SharedServices1";
string kwd = "MOSS";
private QueryInfo myQueryInfo;
private string myQueryString;
public MySearch()
{
}
protected override void CreateChildControls()
{
base.CreateChildControls();
MySearchTextbox = new TextBox();
MySearchTextbox.ID = "mysearchtxt";
MySearchTextbox.Text = "Enter Text to Search";
Controls.Add(MySearchTextbox);
MySearchBtn = new Button();
MySearchBtn.ID = "btnsearch";
MySearchBtn.Text = "Search";
MySearchBtn.Click += new EventHandler(MySearchBtn_Click);
Controls.Add(MySearchBtn);
mygrid = new DataGrid();
Controls.Add(mygrid);
}
private void MySearchBtn_Click(object obj, EventArgs evg)
{
dt = ExecuteFullTextSqlQuery();
mygrid.DataSource = dt;
mygrid.DataBind();
}
private void RegisterQueryLoggingScripts()
{
// Create a new queryInfo object. This object stores the information about the item we've clicked
myQueryInfo = new QueryInfo();
myQueryInfo.AdvancedSearch = true;
myQueryInfo.SiteGuid = SPContext.Current.Site.ID.ToString(); // required value
myQueryInfo.ResultsUrl = "https://must1667186:39178/Pages/default.aspx"; // required value
myQueryInfo.SearchTime = DateTime.Now; // required value
myQueryInfo.QueryString = MySearchTextbox.Text;
// need to create our script key and register it
string scriptKey = "MySearch" + this.ID;
if (this.Page.ClientScript.IsStartupScriptRegistered(this.GetType(), scriptKey))
{
return;
}
// We need to encode our ClickLogPostdata and place it into soapEnv
StringBuilder soapEnv = new StringBuilder(SPHttpUtility.EcmaScriptStringLiteralEncode(ClickLogPostdata()));
soapEnv.Replace("\\u002f", "/").Replace("\\u0022", "\"").Replace("\\u003c", "<").Replace("\\u003e", ">");
// Once we have generated everything we need to, we need to register it in order for it to be displayed in the HTML
this.Page.ClientScript.RegisterStartupScript(this.GetType(), scriptKey, myQueryInfo.GetQueryLoggingScript(this.Page, "1", this.ID, false));
}
/// <summary>
/// Generates the XML necessary to log
/// </summary>
/// <returns></returns>
protected string ClickLogPostdata()
{
string ns = "urn:Microsoft.Search";
StringWriter str = new StringWriter();
XmlTextWriter xml = new XmlTextWriter(str);
xml.WriteStartElement("soap", "Envelope", "https://schemas.xmlsoap.org/soap/envelope/");
xml.WriteStartElement("soap:Body");
xml.WriteStartElement("RecordClick", ns);
xml.WriteStartElement("clickInfoXml");
// so we get the xmlns again, but not another <? xml>
StringWriter innerWriter = new StringWriter();
XmlTextWriter xmlInner = new XmlTextWriter(innerWriter);
xmlInner.WriteRaw("<![CDATA[");
XmlSerializer mySerializer = new XmlSerializer(typeof(QueryInfo), ns);
mySerializer.Serialize(xmlInner, myQueryInfo);
xmlInner.WriteRaw("]]>");
xml.WriteRaw(innerWriter.ToString());
xml.WriteEndElement();
xml.WriteEndElement();
xml.WriteEndElement();
xml.WriteEndElement();
return str.ToString();
}
/// <summary>
/// The OnPreRender method provides the necesary pre-requistics in order to query log
/// </summary>
/// <param name="e"></param>
protected override void OnPreRender(EventArgs e)
{
// We need to register search.js on the page if we are using a new page, as this is where the RecordClick method is stored
Microsoft.SharePoint.WebControls.ScriptLink.Register(this.Page, "search.js", true, true);
// We need to register the query logging scripts and setup it
//this.RegisterQueryLoggingScripts();
base.OnPreRender(e);
}
#region :: Execute Full Text SqlQuery ::
/// <summary>
/// Execute Full Text SqlQuery
/// </summary>
/// <param name="resultTableCollection"></param>
/// <param name="kwd"></param>
/// <param name="siteName"></param>
private DataTable ExecuteFullTextSqlQuery()
{
kwd = MySearchTextbox.Text;
myQueryString = "SELECT Title,Filename, Rank, Keywords, Path FROM portal..scope() WHERE FREETEXT ('" + kwd + "') ORDER BY \"Rank\" DESC";
// We need to register the query logging scripts and setup it
this.RegisterQueryLoggingScripts();
ServerContext context = ServerContext.GetContext(SSPName);
using (FullTextSqlQuery _FullTextSqlQuery = new FullTextSqlQuery(context))
{
_FullTextSqlQuery.StartRow = 0;
_FullTextSqlQuery.RowLimit = 1000000;
_FullTextSqlQuery.HighlightedSentenceCount = 3;
_FullTextSqlQuery.TrimDuplicates = true;
_FullTextSqlQuery.ResultTypes = ResultType.RelevantResults;
_FullTextSqlQuery.QueryText = myQueryString;
ResultTableCollection resultTableCollection = _FullTextSqlQuery.Execute();
ResultTable myresulttable = resultTableCollection[ResultType.RelevantResults];
DataTable dt1 = new DataTable();
dt1.Load(myresulttable);
return dt1;
}
}
#endregion
}
}
</Code>