Getting a little more from SharePoints Web Analytics

I did this ages ago, but I thought I blog about it now, in case it helps someone.

SharePoint has a built-in web analytics webpart. It provides some interesting info out of the box, but I wanted a tailored version that would display the top pages based on the users department, and cache it, without the user having to select any options.

I developed a webpart, that uses the same SQL function SharePoint does to query SharePoint's analytics database, returning the most popular pages being accessed by people in the same department (I used SQL Analyzer to find out the function SharePoint was using).

The function, fn_WA_GetClickthroughChanges, takes 8 inputs, of which I'm supplying 6, and using the default for the other two, indicated by *:

The current date as an Int (todays date minus the number of days you want to trend over. I.e. -30)
The previous date as an Int (the "current date", as defined above, minus the number of days you want to trend over)
The number of days to trend across
The site id
A bit value that indicates whether to include sub sites
The content type (default is null)*
The users title (default is null)*
The users department

The webpart queries the database for the most accessed pages based on the current users department, gets the page title (and does some optional formating depending on the page name), and caches the output.

You can download the wsp/source code here:

Source code and WSP file

It looks like this:

The code looks like this:

private const string GetRecentTrend = "SELECT TOP (200) * FROM [dbo].[fn_WA_GetClickthroughChanges]({0}, {1}, {2}, '{3}', {4}, default, default, {5})  ORDER BY [CurrentFrequency] DESC";
private string GetMostPopularLinks()
{
 var cacheObjectName = String.Format("inceMostPopular{0}", GetUsersDepartment());
 var cachedOutput = HttpRuntime.Cache[cacheObjectName];
 if (cachedOutput != null)
 {
  return (String)cachedOutput;
 }

 SqlConnectionStringBuilder cs = new SqlConnectionStringBuilder();
 cs.UserID = SpAnalyticsSqlUser;
 cs.Password = SpAnalyticsSqlUserPassword;
 cs.DataSource = SpAnalyticsSqlServer;
 cs.InitialCatalog = SpAnalyticsSqlServerDatabase;
 SqlConnection conn = new SqlConnection(cs.ConnectionString);
 StringBuilder im = new StringBuilder();
 im.Append("<ul id=\"inceMPList\">");
 im.Append(String.Format("<li id=\"inceMPListFirstItem\">{0}</li>", Title));

 try
 {
  conn.Open();
  Int32 lastNumberOfDays = NumberOfDaysToLookBack;
  String siteId = SubstituteWebId == String.Empty ? SPContext.Current.Site.ID.ToString() : SubstituteWebId;
  String department = GetUsersDepartment();
  DateTime currentDate = DateTime.Now.AddDays(-lastNumberOfDays);
  DateTime previousDate = currentDate.AddDays(-lastNumberOfDays);
  Int32 currentDateAsInt = Int32.Parse(String.Format("{0}{1}{2}", currentDate.Year, currentDate.Month.ToString().PadLeft(2, '0'), currentDate.Day.ToString().PadLeft(2, '0')));
  Int32 previousDateAsInt = Int32.Parse(String.Format("{0}{1}{2}", previousDate.Year, previousDate.Month.ToString().PadLeft(2, '0'), previousDate.Day.ToString().PadLeft(2, '0')));

  SqlCommand command = new SqlCommand();
  command = department != String.Empty ? new SqlCommand(String.Format(GetRecentTrend, currentDateAsInt, previousDateAsInt, lastNumberOfDays, siteId, 1, String.Format("'{0}'", department)), conn) : new SqlCommand(String.Format(GetRecentTrend, currentDateAsInt, previousDateAsInt, lastNumberOfDays, siteId, 1, "default"), conn);

  command.CommandType = CommandType.Text;
  var dr = command.ExecuteReader();
  if (dr != null)
  {
   if (dr.HasRows)
   {
    String trendup = "<img src=\"/_layouts/images/ince/isqtrendup.png\" alt=\"trend up\"/>";
    String trenddown = "<img src=\"/_layouts/images/ince/isqtrenddown.png\" alt=\"trend up\"/>";
    Int32 currentRank = 1;
    Int32 rowCount = 0;
    Int32 maxCout = MaximumItemsToDisplay;

    while (dr.Read())
    {
     String pageId = dr["PageId"] == DBNull.Value ? String.Empty : (String)dr["PageId"];
     //|| pageId.ToLower().Contains("/lists/") || pageId.ToLower().Contains("/forms/")
     if (pageId.ToLower().EndsWith("home.aspx") || pageId.ToLower().EndsWith("default.aspx") || pageId.ToLower().Contains("_layouts") || pageId.ToLower().Contains("/searchcenter/"))
     {
      currentRank++;
      continue;
     }

     var oCurrentFrequency = dr["CurrentFrequency"];
     var oPreviousRank = dr["PreviousRank"];

     Int64 currentFrequency = oCurrentFrequency == DBNull.Value ? 0 : (Int64)oCurrentFrequency;
     Int64 previousRank = oPreviousRank == DBNull.Value ? 0 : (Int64)oPreviousRank;
     String pageName = GetPageTitle(pageId);

     if(currentRank > previousRank)
     {
      im.Append(String.Format("<li class=\"inceMPItem\"><a href=\"{0}\" alt=\"{1}\">{1}{2}</a></li>", pageId, String.Format("{0} ({1})", pageName, currentFrequency), trendup));  
     }
     if (currentRank == previousRank)
     {
      im.Append(String.Format("<li class=\"inceMPItem\"><a href=\"{0}\" alt=\"{1}\">{1}</a></li>", pageId, String.Format("{0} ({1})", pageName, currentFrequency)));
     }
     if (currentRank < previousRank)
     {
      im.Append(String.Format("<li class=\"inceMPItem\"><a href=\"{0}\" alt=\"{1}\">{1}{2}</a></li>", pageId, String.Format("{0} ({1})", pageName, currentFrequency), trenddown));
     }
     currentRank++;
     rowCount++;
     if(rowCount == maxCout)
     {
      break;
     }
    }
   }
   else
   {
    im.Append(String.Format("<li class=\"inceMPItem\"><span>No Results</span></li>"));
   }
   dr.Close();
  }
 }
 catch (Exception exception)
 {
  return String.Format("[GetMostPopularLinks] Unhandled Exception retrieving most popular links. Error: {0}",exception.Message);
 }
 finally
 {
  conn.Close();
 }
 im.Append("</ul>");
 HttpRuntime.Cache.Insert(cacheObjectName, im.ToString(), null, DateTime.UtcNow.AddMinutes(MinutesToCacheLookup), Cache.NoSlidingExpiration);
 return im.ToString();
}

private string GetPageTitle(string pageId)
{
 bool originalCatchValue = SPSecurity.CatchAccessDeniedException;
 SPSecurity.CatchAccessDeniedException = false;
 try
 {
  if(SubstituteWebHostHeader != String.Empty)
  {
   var currentWebHh = pageId.Substring(pageId.IndexOf("//")+2);
   currentWebHh = currentWebHh.Substring(0, currentWebHh.IndexOf("/"));
   pageId = pageId.Replace(currentWebHh, SubstituteWebHostHeader);  
  }
  using (SPSite spSite = new SPSite(SPContext.Current.Site.Url))
  {
   using (SPWeb spWeb = spSite.OpenWeb(pageId))
   {
    if (pageId.ToLower().Contains("/forms/") || pageId.ToLower().Contains("/lists/"))
    {
     if(pageId.ToLower().Contains("/lists/"))
     {
      var firstPart = pageId.Substring(0, pageId.ToLower().LastIndexOf("/"));
      firstPart = firstPart.ToLower().Replace("/lists/", "/");
      firstPart = firstPart.Substring(spSite.Url.Length);
      return firstPart;
     }
     if (pageId.ToLower().Contains("/forms/"))
     {
      var firstPart = pageId.Substring(0, pageId.ToLower().LastIndexOf("/"));
      firstPart = firstPart.ToLower().Replace("/forms", "/");
      firstPart = firstPart.Substring(spSite.Url.Length);
      return firstPart;
     }
    }
    var item = spWeb.GetListItem(pageId);
    if (item != null)
    {
     return item.Title == String.Empty ? pageId.Substring(pageId.LastIndexOf('/')) : item.Title;
    }
   }
  }
  return pageId.Substring(pageId.LastIndexOf('/'));
 }
 catch (Exception)
 {
  return pageId.Substring(pageId.LastIndexOf('/'));
 }
 finally
 {
  SPSecurity.CatchAccessDeniedException = originalCatchValue;
 }
}