Recently I turned on usage analysis on my blog. One of the first things I notice when looking at the site usage report is that there are incoming links from seventeen different Google domains, but no indication of what people are searching for.

   

A quick search doesn’t turn up anything useful - just recommendations to use Google Analytics and references to features that require MOSS rather than WSS. However, I do find some code for parsing SharePoint usage log files, which should make it reasonably easy to extract the necessary data .

   

Since I want the data to be easily accessible in SharePoint, I will be putting the data into a SharePoint list. I create a custom list called Searches, with the following columns

  • Title - used for the query - I didn’t bother renaming the default column.

  • Count - number of times the query is used

LastSearch - when the query was last used

   

The code added on top of the existing parsing code is reasonably simple - loop through all the log files, which are stored in folders named with the web application guid and the date of the search, and record any search queries found. After processing all the new log entries, add the queries to the SharePoint list.

   

Download the source code or the exe and list template

   

The command line to use when running the program is

   

WSSLogParser “http://www.tqcblog.com/”

   

Replace the url with that of the appropriate site collection in the same format. There are other parameters, used if log files are in a non-standard location or the list has a different name or structure from the one given above. Look at the code for the full details.

   

After setting up the program to run regularly using schtasks.exe, I have all the information I need in the list, easily manipulated with the standard SharePoi

nt view tools. I can now take it a step further, making the recent searches visible to readers of my blog.

   

Since my blog is using CKS EBE, all the changes are to files in the themes folder.

   

First I create an XSL file that will be used to display the searches.

<**xsl**:**stylesheet** version="1.0"  
xmlns:date="http:**//**exslt.org/dates-and-times"  
xmlns:xsl="http:**//**www.w3.org/1999/XSL/Transform" exclude-result-prefixes="date">  
  
<**xsl**:**param** name="IsBlogOwner" />  
<**xsl**:**param** name="RelativeUrl" />  

<**xsl**:**output** omit-xml-declaration="yes" />  
  
 <**xsl**:**template** match="/">  
<ul>  
<**xsl**:**apply-templates** select="rows/row[position() < 10]"/>  
</ul>  
</**xsl**:**template**>  
<**xsl**:**template** match="row">  
<li>  
<a href="[http://www.google.com/search?q=](http://www.google.com/search?q={Title}){Title}">  
<**xsl**:**value-of** select="Title" />  
</a>  
</li>  
</**xsl**:**template**>   
</**xsl**:**stylesheet**>

 

This is based on a copy of one of the existing XSL files - the key changes are the position in the row selector, which is used to limit the list to ten rows because there is a bug in the current version of CKS EBE that prevents the simpler RowLimit attribute from working with SingleListQuery, and the link back to the Google search page.

   

With the XSL created, I can add the list to theme.master:

<EBE:SingleListQuery 
    runat="server" 
    ListName="Searches"
    QueryXML="<OrderBy><FieldRef Ascending="FALSE" Name="LastSearch" /></OrderBy>"
    Transform-XslName="Searches.xsl" />

If using the out of box blog template, you could get most of the same functionality with a standard list web part, setting up the view to show the ten most recent searches. The link back to the search page would be a little more complicated, requiring either a column with a custom RenderPattern or adding the full link as a column when processing the logs.