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.

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 ) 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.

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

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 ). 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…. 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 ! 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?! 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 ? Almost every query, will involve @AssetGuid (a.k.a Aggregation ID).

This is the class of mine (well, not mine actually ), 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 . Sorry to Microsoft, I’ve already done it (peeking into your own class and created the same one). Hope it helps .

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 !

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.

And if your web analytics looks like this,

you should be happy , 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.

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