19 Mei 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.

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 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=

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;
  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.             }
  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;
  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.         }
  17.         public static Guid GetAggregationIdForFarm()
  18.         {
  19.             return GetAggregationId(GetFarmId().ToString());
  20.         }
  22.         public static Guid GetAggregationIdForFarm(Guid farmId)
  23.         {
  24.             if (farmId == Guid.Empty)
  25.                 return Guid.Empty;
  26.             return GetAggregationId(farmId.ToString());
  27.         }
  29.         public static Guid GetAggregationIdForServiceApplication(SPServiceApplication serviceApplication)
  30.         {
  31.             if (serviceApplication == null)
  32.                 throw new ArgumentNullException("serviceApplication");
  33.             return GetAggregationId(serviceApplication.Id.ToString());
  34.         }
  36.         public static Guid GetAggregationIdForServiceApplication(Guid serviceApplicationId)
  37.         {
  38.             if (serviceApplicationId == Guid.Empty)
  39.                 return Guid.Empty;
  40.             return GetAggregationId(serviceApplicationId.ToString());
  41.         }
  43.         public static Guid GetAggregationIdForSite(SPSite site)
  44.         {
  45.             if (site == null)
  46.                 throw new ArgumentNullException("site");
  47.             return GetAggregationId(site.ID.ToString());
  48.         }
  50.         public static Guid GetAggregationIdForSite(Guid siteId)
  51.         {
  52.             if (siteId == Guid.Empty)
  53.                 return Guid.Empty;
  54.             return GetAggregationId(siteId.ToString());
  55.         }
  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.         }
  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.         }
  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.         }
  89.         public static Guid GetAggregationIdForWebApplication(SPWebApplication webApplication)
  90.         {
  91.             if (webApplication == null)
  92.                 throw new ArgumentNullException("webApplication");
  93.             return GetAggregationId(webApplication.Id.ToString());
  94.         }
  96.         public static Guid GetAggregationIdForWebApplication(Guid webApplicationId)
  97.         {
  98.             if (webApplicationId == Guid.Empty)
  99.                 return Guid.Empty;
  100.             return GetAggregationId(webApplicationId.ToString());
  101.         }
  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.

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.


And if your web analytics looks like this,


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.



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!

37 komentar:

  1. Took me time to read all the comments, but I really enjoyed the article. It proved to be very helpful to me and I am sure to all the commenters here! It's always nice when you can not only be informed, but also entertained! I'm sure you had joy writing this article.

  2. FYI, this kind of thing, it really stressed me up. After I found the solution, it's just like running on finish line at the first row. :D
    Anyway, hope it helps (and cheers)....

  3. Hi there,

    first of all thanks for your article and the link to the protocol specification.

    Just a short question: Am I right that I can use "fn_WA_GetTopPages" to get the hits at a specific page within a given time?
    The solution would be to create a class for getting the MD5 hash and then for creating an SQL query in order to use the MD5 hash to get the analytics data I want, right?

    Thanks in advance,

  4. That's correct Andreas. Actually, that class what I've wrote above, really really similar to the Microsoft's class. Why? coz the Microsoft's class is declared as internal, so I can't add reference to that class to get AggregationId.

    The class is getting the MD5 Hash from SharePoint objects, then you can use the generated aggregatorid to query the toppages. Mostly of the queries described in MS-MAVA (in the link I've provided above), needs this aggregationid.

    Secondly, yes, you can use "fn_WA_GetTopPages" to get hits at a specific page and date range. Yes, you can use any queries described in MS-MAVA. As long as not modifying the web analytics but queries, I think it's OK.

    Sorry if my english is bad. still learning.

  5. Hi,

    Are you writing a custom ASP.NET /SQL application to pull these data ?

  6. Even you can do it from PHP, desktop app, coz it's just query to get web analytics. Don't need to use SharePoint API to get this data.

  7. what could be advantage using this other than WSS_logging?

  8. Well, like what I've said before, I'm just want to get report which is really-really similar to SharePoint Web Analytic Report in every Site Collection Settings and Site Settings.

    I actually haven't read any literature about WSS_Logging, but based on my searchs, WSS_Logging is providing basic report, usage request, feature request, ULS Logs, performance counters, timer job usage request, etc. You can look at http://sharepointmalaya.blogspot.com/2011_03_01_archive.html.

    But one thing for sure, the process is really complicated. And the 'end-data' served in Reporting Analytics DB. Briefly, the process is like this, SharePoint HttpModule (to record all requests and response from http) >> WSS_UsageApplication (the data still in raw format in here, but you can still query from here with extended filters) >> Staging Analytics DB >> Reporting Analytics DB.

  9. Hi Radityo,

    I can only find this article when I look for Web analytical reports..Thanks for gr8 research,, I'm bit behind in catching up the technical bits in this article.. first question how and where do you place these custom classes? Web.config?!

    Any help will be appreciated.

  10. Just custom classes, DLL. Deploy those DLLs to Global Assembly Cache (C:\Windows\Assembly) by dragging it into those folder. Add your webpart/app/whatsoever reference to those 2 DLLs, and you can start using it.

  11. Thank you so so much..

  12. Hi , on the tecnical side this approach is owsome but i think we lose Microsoft support even for Reading data from that Db. Im probing Microsoft and i'll post a comment on that.


  13. Yeah right, Scoutman,
    I don't think my approach is the best. I still waiting and would prefer Microsoft SP Object Model to do more about this.

    But since there's no props or method I could use, then this is the another solutions of mine. I hope Microsoft would create another approach (or updates on next Service Packs), so everyone's happy and safe.

  14. Hi Radityo,

    Would it be alright if you can show us an example for this? I am quite new into sharepoint and it seems that i need to do something like this. I see the 2 classes but i still don't really know how to use it programatically.

    Very sorry if this request is quite noob.

  15. Hi Radityo,

    I'm trying to develop my own web analytics webpart within Sharepoint 2010 so your post has really helped me to understand the access to the analytics database and their functions. I did a console application in VS to test the MD5 hashing calculation but the guid obtained using the hash value doesn't match with any aggregation id stored in my web analytics database, thus I can't get any results, and I don't understand why.

    Thanks for the knowledge shared in this blog and hope you could get me some idea about my problem.


  16. Well, Anonymous (at Wednesday, December 14, 2011 3:20:00 AM GMT+07:00),

    you can create 2 classes on your solution and use those 2 classes to get the web analytics Aggregation ID. That's the point!

    Because almost every operations in this query, involving Aggregation ID, which is returned by calculating MD5 hash of the SPSite or SPWeb GUID.

    From then, you can look at MS-MAVA about detailed query that you can use on Reporting Web Analytics Database.

  17. And, for Anonymous (at Thursday, December 15, 2011 7:16:00 PM GMT+07:00),

    you can look at this post, you can see my update below it.

  18. How can we show the up down arrow and "new" icon against the page. How to identify this?

  19. For Anonymous (at Sunday, December 18, 2011 2:23:00 AM GMT+07:00), I'm a little confuse with your question. Would you mind to show me what and where is the up down arrow, and what "new" icon do you mean?

  20. hey..awesome post
    m newbie in shrepoint....
    could you provide me any working code or something for web analytic

    1. Just follow this post, there's an update sometimes.

  21. Btw, if you need to publish analytics data on the site pages, in our HarePoint Analytics for SharePoint we have a special web part for this purpose:


    WBR, Alexander

    1. thanks for sharing @agorlach... nice thing though...

  22. You could just use AnalyticsReportFunction, like this-

    using Microsoft.Office.Server.WebAnalytics.Reporting;

    AnalyticsReportFunction ar = new AnalyticsReportFunction(); // this is public class, but no document about it.
    object[,] result = ar.GetWebAnalyticsReportData(
    "2", // report level,
    "TopPageForPageReport", // report name,
    DateTime.Today.AddDays(-30.0), // start date,
    DateTime.Today); // end date,

    1. U sure? Will test it if I have a time.
      It seems promising... :P

      Anyway, thanks maaaan!

  23. Very informative post.I have really enjoyed in reading let me try in programming also.Hope it works as it seems.Thanks for sharing.

  24. Hallo Radityo,

    Many thanks for a very useful post!

    I have a problem with Web Anayltics configuration. How can i configure this so that site owner or user are not able to access it but it is still enabled. The reason is to allow third party softwares such as Control Point having access to Web Analytics but to prevent other users (esp. site owners) due to data privacy regulations.

    Any feedback would be appreciated



    1. Billy, thanks for the comments.
      Well that's pretty abnormal, since Site Owners always have access to this things. Well, pretty clear from its name, Site Owners.
      You could use lower level than Site Owners for that users, i.e.: Designer or Manage Hierarchy.
      Another way, is to modify the settings.aspx right from the LAYOUTS folder of SharePoint server, and change the link of Web Analytics to be displayed for a user with certain permission level. But I wouldn't recommend that, since that would change the whole SharePoint Web Application.

    2. Many thanks for your time and in particular with your useful feedback so quickly, Radityo! Very much appreciated!



  25. AggregationContext.GetContext can be used to get the AggregationId

  26. I am trying to use SP.GetUsageData returns NULL data and GetUsageBlob returns response object length = 0 . Usage timer jobs are running fine on regular intervals.

    What could be the reason both methods are not returning expected results.

    My environment is SharePoint 2010 and is it recommend to use GetUsageBlob as per standard practice?

    I want to create report which will display "Number of visits/user on every site collection"

    Any thoughts?

  27. Great post! but how can i retreive query results data to sharepoint using visual studio??

  28. Here is an example on how to get the Web Analytics summary report using PowerShell: http://gallery.technet.microsoft.com/office/Get-SharePoint-Web-19cd2137

    1. That's a great PowerShell. I should try it sometime.... :D


[give me your ideas, hopes, comments and compliments below...]
[aaand...... +1 if you need to....]