May 19, 2011

How to Get SharePoint 2010 Web Analytics Programmatically? Finally Found!

You probably know that one biggest challenge is how to get SharePoint 2010 Web Analytics data in Web Analytics Report programmatically. It’s a little stupid (I suppose) if Microsoft doesn’t provide API to access this cool feature. Although there’s an API in a method like GetUsageData(), but this thing doesn’t enough. We want more detailed and mimicking the SharePoint Web Analytics Report.Where

I’d rather write my own code about HttpModule or use a third party Analytics feature like Google Analytics than searching entire database for those kind of reports. But, that’s just became a nightmare if you see that the portal is not published to the Internet.

Well, after 2 days full searching (really – no sleeping at all Surprised smile) in entire SharePoint 2010 Web Analytics Database, I found how to do it. By the way, accessing Database generated by SharePoint 2010 isn’t really supported by Microsoft. But I think it’s OK as long as you don’t write or update any data to them, go on, express yourself.

For my environment, my Web Analytics Reporting Database is in WebAnalytics_RPT_Service_DB. You might want to check what’s your Web Analytics Reporting Database, check it out on SharePoint Central Administration, then go to Manage Service Applications, click to your Web Analytics Service Application name.

image
Highlight your WA Service Application, and click properties in the ribbon.

image
Click Next on the dialog, and you’ll see your Reporting Database Name.

Now we might want to test for the queries to the web analytics service. Actually, I don’t have much time to wrote what could be the query for Top Pages, or any other WA Queries, (I’ll write on next post Winking smile). But if you like, you can use SQL Server Profiler, attach to your Reporting DB, and view some specific WA Reports in Site Settings, and voila, there’s the query.

Let’s see, I have one example of query, and you can use it. Here’s one of the query…

Code Snippet
  1. exec sp_executesql
  2. N'SELECT TOP (@MaxRow) [PageId], [Frequency], [Percentage]
  3. FROM [dbo].[fn_WA_GetTopPages](@Start, @End, @AssetGuid, default)
  4. WHERE [PageId] LIKE ''%posts/post.aspx%''
  5. ORDER BY [Frequency] DESC',
  6. N'@Start int,@End int,@AssetGuid uniqueidentifier,@MaxRow int',
  7. @Start=20110327,@End=20110425,@AssetGuid='C629FD58-0E21-4C6F-F8E4-681352410415',@MaxRow=10

You can set the Start time in @Start with int data type, and End time in @End. And also you might want to remember, the @AssetGuid is unique and represents each object in SharePoint.I thought that @AssetGuid might be the SPSite or SPWeb GUID, but it’s not. @AssetGuid is the SPSite, SPWeb, SPWebApplication, or SPFarm object, calculated with MD5 Hash (psssst….Don't tell anyone smile I tell ya…don’t tell anyone… this is according to the Microsoft’s DLL, Microsoft.Office.Server.WebAnalytics.AggregationIdGenerator inside Microsoft.Office.Server.WebAnalytics, Version=14.0.0.0).


I think that almost imposible if you don’t create custom class to create @AssetGuid which is named as Aggregation ID. After I looked deeper into Microsoft’s DLL for Web Analytics, finally Hot smile! There’s one class which in charge in creating Aggregation ID, but it’s declared as internal, so any other class like mine, can’t read that class. So, I’ve created one same as Microsoft’s.


UPDATE: guess what?! What When I’m searching about those queries in google, I’ve found a detailed description and documentation about Analytics Report query, and it’s released by Microsoft since 7/13/2009. Here’s the link [MS-MAVA]: Microsoft Office SharePoint Server (MOSS) Analytics View Access Protocol Specification. You can download the PDF’s to make you more comfortable to read at. So, I don’t really need to write all detailed queries to my next post right Open-mouthed smile? Almost every query, will involve @AssetGuid (a.k.a Aggregation ID).


This is the class of mine (well, not mine actually Angel), and remember, involving 2 classes. This class calculates MD5 Hash for any SharePoint objects.

Code Snippet
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Security.Cryptography;
  6. using System.Globalization;
  7.  
  8. namespace Rdz.SharePoint.WebAnalytics
  9. {
  10.     internal static class CommonMethods
  11.     {
  12.         internal static class Hasher
  13.         {
  14.             internal static byte[] ComputeHash(string input)
  15.             {
  16.                 if (string.IsNullOrEmpty(input))
  17.                     return null;
  18.                 return MD5.Create().ComputeHash(Encoding.Unicode.GetBytes(input));
  19.             }
  20.  
  21.             internal static string ConvertHashToString(byte[] hash)
  22.             {
  23.                 if (hash == null)
  24.                     return null;
  25.                 StringBuilder builder = new StringBuilder("0x");
  26.                 for (int i = 0; i < hash.Length; i++)
  27.                 {
  28.                     builder.Append(hash[i].ToString("X2", CultureInfo.InvariantCulture));
  29.                 }
  30.                 return builder.ToString();
  31.             }
  32.         }
  33.     }
  34. }

And this is the main class, AggregationIdGenerator.

Code Snippet
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using Microsoft.SharePoint;
  6. using Microsoft.SharePoint.Administration;
  7.  
  8. namespace Rdz.SharePoint.WebAnalytics
  9. {
  10.     public static class AggregationIdGenerator
  11.     {
  12.         private static Guid GetAggregationId(string id)
  13.         {
  14.             return new Guid(CommonMethods.Hasher.ComputeHash(id));
  15.         }
  16.  
  17.         public static Guid GetAggregationIdForFarm()
  18.         {
  19.             return GetAggregationId(GetFarmId().ToString());
  20.         }
  21.  
  22.         public static Guid GetAggregationIdForFarm(Guid farmId)
  23.         {
  24.             if (farmId == Guid.Empty)
  25.                 return Guid.Empty;
  26.             return GetAggregationId(farmId.ToString());
  27.         }
  28.  
  29.         public static Guid GetAggregationIdForServiceApplication(SPServiceApplication serviceApplication)
  30.         {
  31.             if (serviceApplication == null)
  32.                 throw new ArgumentNullException("serviceApplication");
  33.             return GetAggregationId(serviceApplication.Id.ToString());
  34.         }
  35.  
  36.         public static Guid GetAggregationIdForServiceApplication(Guid serviceApplicationId)
  37.         {
  38.             if (serviceApplicationId == Guid.Empty)
  39.                 return Guid.Empty;
  40.             return GetAggregationId(serviceApplicationId.ToString());
  41.         }
  42.  
  43.         public static Guid GetAggregationIdForSite(SPSite site)
  44.         {
  45.             if (site == null)
  46.                 throw new ArgumentNullException("site");
  47.             return GetAggregationId(site.ID.ToString());
  48.         }
  49.  
  50.         public static Guid GetAggregationIdForSite(Guid siteId)
  51.         {
  52.             if (siteId == Guid.Empty)
  53.                 return Guid.Empty;
  54.             return GetAggregationId(siteId.ToString());
  55.         }
  56.  
  57.         public static Guid GetAggregationIdForWeb(SPWeb web)
  58.         {
  59.             if (web == null)
  60.                 throw new ArgumentNullException("web");
  61.             return GetAggregationId(web.Site.ID.ToString() + web.ID.ToString());
  62.         }
  63.  
  64.         public static Guid GetAggregationIdForWeb(string webUrl)
  65.         {
  66.             if (webUrl == null)
  67.                 throw new ArgumentNullException("webUrl");
  68.             using (SPSite site = new SPSite(webUrl))
  69.             {
  70.                 if (site != null)
  71.                     using (SPWeb web = site.OpenWeb())
  72.                     {
  73.                         if (web != null)
  74.                             return GetAggregationId(site.ID.ToString() + web.ID.ToString());
  75.                     }
  76.             }
  77.             return Guid.Empty;
  78.         }
  79.  
  80.         public static Guid GetAggregationIdForWeb(Guid siteId, Guid webId)
  81.         {
  82.             if (siteId == Guid.Empty)
  83.                 return Guid.Empty;
  84.             if (webId == Guid.Empty)
  85.                 return Guid.Empty;
  86.             return GetAggregationId(siteId.ToString() + webId.ToString());
  87.         }
  88.  
  89.         public static Guid GetAggregationIdForWebApplication(SPWebApplication webApplication)
  90.         {
  91.             if (webApplication == null)
  92.                 throw new ArgumentNullException("webApplication");
  93.             return GetAggregationId(webApplication.Id.ToString());
  94.         }
  95.  
  96.         public static Guid GetAggregationIdForWebApplication(Guid webApplicationId)
  97.         {
  98.             if (webApplicationId == Guid.Empty)
  99.                 return Guid.Empty;
  100.             return GetAggregationId(webApplicationId.ToString());
  101.         }
  102.  
  103.         private static Guid GetFarmId()
  104.         {
  105.             SPFarm local = SPFarm.Local;
  106.             if (local == null)
  107.                 throw new InvalidOperationException("Server not joined in farm: " + System.Net.Dns.GetHostName());
  108.             return local.Id;
  109.         }
  110.     }
  111. }

You can play and work with those 2 classes Winking smile. Sorry to Microsoft, I’ve already done it (peeking into your own class and created the same one). Hope it helps Open-mouthed smile.

UPDATED:
For anyone who do have a problem when getting Aggregation ID instantly, you can read through this post about how to get it. I’ve created a little tools to get Aggregation ID using input URL. Of course this tools must be running in SharePoint box. And another post on how to query the Web Analytics Report from it’s database, you can look at this post how to do it. If after using this tools, and you’ve tried to query web analytics and no results, you should think again Smile with tongue out!

No, I mean you should look back and see what you’ve done. Are you creating the Web Analytics properly? Is the Web Analytics data is valid? I mean, you can look at the SharePoint web analytics at your SharePoint site, go to Site Settings and then click Site Web Analytics Report. If there’s no data in there, then you should look again at your Web Application configurations.

image

And if your web analytics looks like this,

image

you should be happy Winking smile, because there’s no need to worry. Check again your Web Analytics Service Application Association. Is it already attached to your Web Application? You can look at your Central Administration.

image

image

Then if you do have a problem, you can (again) verify the Web Analytics Reporting Database at my post above Open-mouthed smile. Hope it helps, folks………….
.
.
.
And… click +1 if you do like!