Sharepoint 2010 – access site usage statistics

OK, so you made the assumption that Microsofts Sharepoint team would have provided a simple way to access something as common (and not to mention extremely useful!) as site usage statistics. Which pages and resources are accessed

• at what time
• for how long
• how often
• the browser type
• http status
• and last but definately not least: by who.

And not only did you assume this would be easily accessible, you also told a client you’d have it done in a couple of hours. I assume some of you that ended up here either got lucky straight away, or you have already spent way more time than expected and added yet another element to your list of reasons why Sharepoint will make you loose your hair prematurely.

If you assumed that this data should be available somewhere you were right. The trick is to know how to access it from your custom web part. The data is stored in the WSS_Logging database, in a table called RequestUsage. You can run a simple query just to see what’s in there and which fields you have access to:

SELECT *
FROM [RequestUsage] 

Then you can start getting a bit more creative. You see, the RequestUsage table contains statistics for all sorts of requests – like requests to the MetadataWebService. So depending on your needs, filter out the type of document types / extensions / paths you are interested in. I needed just to know the hit count of .aspx pages in a specific sub site, so my query looked somewhat like this:

SELECT distinct(DocumentPath), COUNT(*) as DocCount
    FROM [RequestUsage]
    WHERE DocumentPath LIKE '/Marketing%'
      AND ( DocumentPath LIKE '%.aspx'   )
GROUP BY DocumentPath      

This is not a lesson in SQL, but that query gives me a nice list of all .aspx documents accessed under the Marketing sub site with the corresponding hit count. To get the top 5 out of that result, you’ll end up with something like this:

with cte as (
    SELECT distinct(DocumentPath), COUNT(*) as DocCount
    FROM [RequestUsage]
    WHERE DocumentPath LIKE '/Marketing%'
      AND ( DocumentPath LIKE '%.aspx'   )
    GROUP BY DocumentPath )
select top 5 DocumentPath, DocCount from cte order by DocCount desc

So we have a query we would like to run, and then you can display the result in a web part. There are numerous ways to do that, and I won’t cover it here but I’ll show the C# code for running this query. The following code snippet is put together from my actual project, and should give you enough information to piece together a working method. Take note of how the connection string for the WSS_Logging database is retrieved:

// run with elevated privileges
SPSecurity.RunWithElevatedPrivileges(delegate()
{
    string connectionString = SPUsageApplication.DefaultApplication.UsageDatabase.DatabaseConnectionString;
    SqlConnection connection = new SqlConnection(connectionString);

    // build your query
    StringBuilder sb = new StringBuilder();
    sb.Append("with cte ");
    sb.Append("as ");
    sb.Append("( ");
    sb.Append("SELECT distinct(DocumentPath), COUNT(*) as DocCount ");
    sb.Append("FROM [RequestUsage] ");

    // ...
    // add all the WHERE filters you want here
    // ...

    sb.Append(" GROUP BY DocumentPath ");
    sb.Append("");
    sb.Append(") select top " + ItemCount + " DocumentPath, DocCount from cte order by DocCount desc");
    string sql = sb.ToString();

    SqlCommand command = new SqlCommand();
    command.Connection = connection;
    command.CommandText = string.Format(sql, SPContext.Current.Web.ServerRelativeUrl);
    command.CommandType = System.Data.CommandType.Text;
    command.Connection.Open();

    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        // access the fields, for example document name
        string docName = reader["DocumentPath"].ToString().Substring(reader["DocumentPath"].ToString().LastIndexOf('/') + 1);
        docName = HttpUtility.UrlDecode(docName);

        // build an HTML table, add items to a list or whatever you prefer
        // ..
    }

    // close the db connection
    command.Connection.Close();

}

The magic lies in how you build and define the filter to suit your requirements.