SharePoint 2010 and the Usage BLOB

I had a client who wanted to show a list of all site collections and sort them by most popular using site hits.  I was able to use Martin Kearn’s ShrarePoint 2010 solution on Codeplex (here) to return the aggregated site collection list however Martin’s solution wasn’t grabbing site hits even though there is a method within his code that appears to do that – I confirmed that it doesn’t work.

I then came across the solution in the SharePoint Diaries Blog, in the post where Diego Bruno Galeota documents Parsing the SharePoint Usage BLOB and provides the code.  Site hits are collected if you have SharePoint’s Web Analytics enabled where the analytics is stored as a BLOB in the database – so to get site hits out of that BLOB, you have to parse it – which is what Diego’s code does.  I deployed Diego’s code and it works for SharePoint 2010.  However, one update I made to Diego’s code was to change the GetUsageBlob function in the UsageBlob.cs class to remove the NetworkCredential property (snippet below).  Martin’s SharePoint directory solution is running at the farm level using elevated credentials so it works without having to hard code credentials.

UsageBlob.cs Snippet

1. //public static UsageBlob GetUsageBlob(string url, bool currentBlob, NetworkCredential credential)
2. public static UsageBlob GetUsageBlob(string url, bool currentBlob)
3. {
4.     var uri = new Uri(url);            
5.     
6.     ....

Then, using Martin’s Site Directory Solution I was able to add the Site Hits to the Site Directory list with the following function:

ScanJobHelperMethods.cs Snip

1. public int GetHitsCount(SPWeb webInSite)
2. {
3.     int intHits = 0;
4.     GlobalMethods globalMethods = new GlobalMethods();
5.     System.Net.ServicePointManager.ServerCertificateValidationCallback = ((sender, certificate, chain, sslPolicyErrors) => true);
6.  
7.     try
8.     {
9.         string thisUrl = webInSite.Url.ToString();
10.         var blob = UsageBlob.GetUsageBlob(thisUrl, true);
11.         var now = DateTime.Now;
12.         var minDate = new DateTime(now.Year, now.Month, now.Day);
13.         var browsers = from b in blob.Browsers.UsageRecords
14.                         select new
15.                         {
16.                             Name = b.Key,
17.                             Hits = (
18.                                     from v in b.Values
19.                                     where v.Date <= minDate
20.                                     select v.Count
21.                                     ).Sum(),
22.                             Percentage = 0
23.                         };
24.  
25.         var totalHits = browsers.Sum(b => b.Hits);
26.         if (totalHits > 0)
27.         {
28.             browsers = from b in browsers
29.                         orderby b.Hits descending
30.                         select new
31.                         {
32.                             Name = b.Name,
33.                             Hits = b.Hits,
34.                             Percentage = (int)Math.Round(((double)b.Hits / totalHits) * 100)
35.                         };
36.         }
37.         intHits = totalHits;
38.     }
39.     catch (Exception ex)
40.     {
41.         globalMethods.WriteULSEntry(ex, TraceSeverity.Unexpected, null);
42.     }
43.  
44.     return intHits;  
45. }