Usage Reports in SharePoint

Ayshwarya Ramakrishnan
Aug 30, 2013
30.2k
0
0
Article

Reports on usage has been a part of the SharePoint platform for a long time now.

The following table gives an overview of the OOTB reports in each version.

Version	Path	Reports
Microsoft 2007	"Site" - > "Site Settings" -> "Site Collection Usage summary" Site collection reports: http://sitecollection/_layouts/SpUsageSite.aspx Site reports: http://sitecollection/_layouts/SpUsageWeb.aspx Site Usage Report: http://sitecollection/_layouts/usageDetails.aspx Site Usage Summary: http://sitecollection//Usage.aspx	Average requests per day over past 30 days Distinct users over past 30 days Distinct users yesterday Requests yesterday Queries Over Past 30 Days Top Pages (average requests per day over past 30 days) Top Users (average requests per day over past 30 days) Top referring pages (past 30 days) Top destination pages (past 30 days) Top Queries Over Previous 30 Days Requests per day (past 30 days) Top referring hosts (past 30 days) Search results top destination pages
SP2010	"Central Administration Web Analytics Reports" - "Summary" "Site" - > "Site Settings" -> "Site Web Analytics reports" "Site" - > "Site Settings" -> "Site Collection Web Analytics reports"	Total & average number of page views Number of page views per day Total & average number of daily unique visitors Total & average number of referrers Total & average number of search queries Number of Site Collections Top pages Top visitors Top referrers Top destinations Top browsers Number of sites
SP2013	"Site Settings" -> "Site Web Analytics Reports"	Number of page views Number of unique visitors Number of referrers Top pages Top visitors Top referrers Top browsers

For any reports on data within the last 30 days, we can create the reports by accessing the object model using the method "GetUsageData()" of the SPWeb object (or GetUsageBlob for SP 2010).

Another way is to access the database for the reports. Microsoft, however, does not permit directly querying (and ofcourse modifying) the SharePoint databases, according to the following articles, for the scenario of getting reports beyond 30 days, this seems to be permitted.

MOSS 2007 - http://www.sharepointwithattitude.com/archives/41
SharePoint 2010 / 13 - http://msdn.microsoft.com/en-us/library/gg193966(v=office.14).aspx#MonitoringSharePoint2010_LoggingDB

In MOSS 2007, when we look at the usage details, they all get into the SharedServices databases in tables begininng with "ANL*".

We could write a query as in the following to obtain reports of our choice:

private string GetTotalsQuery(Guid SiteID)
{
    StringBuilder query = new StringBuilder();
    query.Append("SELECT ANLDay.FullDate, ANLHit.UserId,ANLUser.UserName, ANLResource.SiteGuid, ANLResource.FullUrl");
    query.Append(" FROM ANLDay WITH (NOLOCK) LEFT JOIN (ANLResource WITH (NOLOCK)");
    query.Append(" INNER JOIN ANLHit WITH (NOLOCK) ON dbo.ANLHit.ResourceId = ANLResource.ResourceId");
    query.Append(" AND ANLResource.SiteGuid = " + "\'" + SiteID.ToString() + "\'" + ")ON dbo.ANLHit.DayId = ANLDay.DayId ");
    query.Append("LEFT OUTER JOIN ANLUser WITH (NOLOCK)ON dbo.ANLHit.UserId = ANLUser.UserId");
    query.Append(" WHERE convert (Date, ANLDay.FullDate) >= DATEADD(month, -3, CURRENT_TIMESTAMP) AND ANLResource.SiteGuid IS NOT NULL");
    query.Append(" GROUP BY ANLDay.FullDate,ANLHit.UserId,ANLUser.UserName,ANLResource.SiteGuid,ANLResource.FullUrl ");
    query.Append(" ORDER BY ANLDay.FullDate ASC");
}

And invoke the query against the portal context as in the following:

using (SqlCommand command = new SqlCommand())
{
    SqlDataReader reader = null;
    command.CommandText = GetTotalsQuery(site.ID);
    command.CommandType = CommandType.Text;
    PortalContext portalContext = PortalApplication.GetContext(site.ID);
    Type ContextType = portalContext.GetType();
    System.Reflection.PropertyInfo _SQLConnection = ContextType.GetProperty("AnalyticsSqlSession", BindingFlags.NonPublic | BindingFlags.Instance);
    object SQLConnection = _SQLConnection.GetValue(portalContext, null);
    Type SQLConnectionType = SQLConnection.GetType();
    MethodInfo ExecuteSQL = SQLConnectionType.GetMethod("ExecuteReader", new Type[1] { typeof(SqlCommand) });
    reader = ExecuteSQL.Invoke(SQLConnection, new object[1] { command }) as SqlDataReader;
    table.Load(reader);
    foreach (DataRow dr in table.Rows)
    {
         dtUsageReportsResults.Rows.Add(dr.ItemArray);
     }
}

In SharePoint 2010/13, there are no SharedServices databases. The usage data goes into a separate database called "WSS_logging". This database has tables such as "AnalysisServicesRequests_Partition*", "AnalysisServicesUnloads_Partition*", "ExportUsage_Partition*", "FeatureUsage_Partition*", "ImportUsage_Partition*", "RequestUsage_Partition*", "SandboxedRequests_Partition*", "SiteInventory_Partition*", "TimerJobUsage_Partition*", "WorkflowUsageTelemetry_Partition*" and so on..

There are certain pre-defined views (see the following screenshot) on these tables, that aggregates data from the relevant partition tables and provides all data in one place.

A look at RequestUsage view data:

We would need to query these views directly from our Object model (such as in the following) to get useful report data customized to our needs.

StringBuilder query = new StringBuilder();

query.Append("SELECT distinct FeatureUsage.SiteUrl,FeatureUsage.LogTime, FeatureUsage.UserLogin");

query.Append(" FROM dbo.FeatureUsage LEFT JOIN");

query.Append(" dbo.RequestUsage ON dbo.FeatureUsage.CorrelationId = dbo.RequestUsage.CorrelationId");

query.Append(" where FeatureUsage.SiteUrl like '%"+siteCollURL+"%'");

query.Append(" and Datediff(day,GETDATE(),convert (Date, FeatureUsage.LogTime)) <= 30 ");

query.Append(" order by FeatureUsage.LogTime ");

One thing to keep in mind is that the data in the "ANL*" tables (in MOSS 2007) and the "WSS_Logging" tables (in SharePoint 2010) usually get deleted after some time (typically 1 month, although that is configurable). For usage details beyond 1 month, we need to either look at the IIS logs or look into the backups (if taken) of these database tables and query them.