How to use WebAnalytics API – FrontEndDataRetriever.QueryData

This post is a contribution from Jaishree Thiyagarajan, an engineer with the SharePoint Developer Support team.

To use WebAnalytics API, first we need to add reference to Microsoft.Office.Server.WebAnalytics.dll and Microsoft.Office.Server.WebAnalytics.UI.dll.  These DLLs can be located in GAC (C:\Windows\Assembly\GAC_MSIL\).

The WebAnalytics DB (Report DB) has many Table-Valued-functions, which we can leverage programmatically through FrontEndDataRetriever.QueryData.

Check out the example given below.  I’ve used “fn_WA_GetNumberOfClickthroughs”, this function will retrieve the number of hits per URL.

List of available functions can be found in this article.

Sample below.

 try
 {
     using (SPSite site = new SPSite("https://siteURL/"))
     {
         AggregationContext aggregationContext = AggregationContext.GetContext(site);
         if (aggregationContext != null)
         {
             List<ViewParameterValue> viewParamsList = new List<ViewParameterValue>();
             viewParamsList.Add(new ViewParameterValue("StartDateId", DateTimeToDateId(DateTime.UtcNow.AddMonths(-6))));
             viewParamsList.Add(new ViewParameterValue("EndDateId", DateTimeToDateId(DateTime.UtcNow)));
             viewParamsList.Add(new ViewParameterValue("GroupByPageId", true));
             DataPacket dataPacket = FrontEndDataRetriever.QueryData(aggregationContext, null, "fn_WA_GetNumberOfClickthroughs", viewParamsList, null, GetSortOrder("Frequency", OrderType.Descending), 1, 25000, false);
             if (dataPacket.DataTable != null)
             {
                 //Datatable manipulation
             }
             else
             {
                 //No datatable available
             }
             Console.ReadLine();
         }
     }
 }
 catch (Exception ex)
 {
     Console.WriteLine(ex.Message);
 }

Comments

• Anonymous
  January 01, 2003
  Hello, Could you please let me know what is the error being returned? I tested your code, it returns the expected results. Also, do you see any data being returned in UI (Site Actions -> Site settings ->  Site Web Analytics reports )?

• Anonymous
  August 14, 2012
  The sample does not include a definition for DateTimeToDateId or GetSortOrder. I tried to implement those myself, but making the query returns an error. The implementation is as below. What am i doing wrong? private List<SortOrder> GetSortOrder(string frequency, OrderType @descending)        {            List<SortOrder> sortOrders = new List<SortOrder>();            sortOrders.Add(new SortOrder(frequency, descending));            return sortOrders;        }        private int DateTimeToDateId(DateTime dateTime)        {            return (((dateTime.Year*100) + dateTime.Month)*100 + dateTime.Day);        }

• Anonymous
  March 01, 2013
  I have an example on how to get the Summary Report using PowerShell here: gallery.technet.microsoft.com/.../Get-SharePoint-Web-19cd2137

• Anonymous
  August 30, 2013
  Could you help me in getting the Condition parameter filled. ie., I need to apply logged in user loginname as a condition to Condtion parameter(wherecondition).   FrontEndDataRetriever.QueryData(aggregationContext, null, "fn_WA_GetNumberOfClickthroughs", viewParamsList, NULL, GetSortOrder("Frequency", OrderType.Descending), 1, 25000, false); Currently it is null could you help me to implement the condition or some sample code which explains this implementation.(Condition Class).

• Anonymous
  August 05, 2014
  Here are the definitions of DateTimeToDateId and GetSortOrder:
  
  public static int DateTimeToDateId(DateTime dt)
  {
  if (string.IsNullOrEmpty(dt.ToString()))
  {
  return 0;
  }
  return int.Parse(dt.ToString("yyyyMMdd", CultureInfo.InvariantCulture), CultureInfo.InvariantCulture);
  }
  
  
  private static List GetSortOrder(string sortColumn, OrderType order)
  {
  List list = new List();
  SortOrder item = null;
  item = new SortOrder(sortColumn, order);
  list.Add(item);
  return list;
  }
  
  

• Anonymous
  May 07, 2015
  Where is this DLL located? It's on the Sharepoint server in the GAC, but I installed the 2010 SDK and I didn't get it.

• Anonymous
  May 07, 2015
  I.E. it doesn't seem to come with the SDK. Can I get it without installing SP 2010 specifically?