Extending The SharePoint 2010 Health & Usage – Part 2: Writing a Custom Usage Provider

This is the second article in a 4 part series were I discuss the new Health & Usage Services built into SharePoint 2010 and how they can be extended to build some very interesting solutions. In this article I will discuss the process around creating a custom usage provider, dive into some internals, and provide code examples which will pull it all together.


Other articles in this series are as follows:

  1. Feature and Capability Overview
  2. Writing a Custom Usage Provider (this article)
  3. Writing Custom Reports
  4. Writing a Custom Usage Receiver


Code Download: Microsoft.SP.Usage.zip (160 kb)

The Scenario

As a reminder, I will walk you through the building a solution to track file downloads from SharePoint Document Libraries and report on the results. Its key that this solution be scalable so we will create a custom usage provider, add a few custom usage reports in Central Administration, and we will create a usage receiver to help us display download information within a Web Part for each SPSite within our environment.


As mentioned in the first article, a Usage Provider can be used to collect and store additional usage information in either the Usage Database or a custom data store. Its typical to use a custom usage provider when there is usage occurring which you would like to track or additional details for a usage event which is not being collected for which you would like to collect. These two scenarios are important because there is a ton of usage data being collected already by the OOB providers and it does not make since to collect the same data twice. For example, lets say you wanted to track aspx page requests; while you could write a custom provider to track these requests it’s a bit silly since the RequestUsage provider collects and logs this information into the Usage Database and its trivial to parse through the data to get a view of the aspx requests. For our scenario we want to track downloads of documents from document libraries and while much of the information we are going to collect is already available in the RequestUsage provider we do have a couple key parameter(s) missing; the List ID and fact the List ID represents a document library. While the URL is available its error prone to parse the URL in an attempt to glean which requests are for document downloads.

This solution calls for 3 primary components, the Usage Entry, the Usage Definition, and the component which will detect the download and log the fact it occurred. The Usage Entry and Usage Definition will use base classes from the SharePoint namespace Microsoft.SharePoint.Administration while the actual component which does the logging will be implemented as a custom Asp.Net HttpModule. Our provider will be simple and strait forward (because that is how I role) as we will leverage the Usage Database as our central store and we will not require any additional processing. A standard Visual Studio 2010 SharePoint project is our starting point where we will create a Farm solution which will deploy our single assembly into the GAC. We will wind up with a Farm scoped Feature which will register our custom Usage Provider as well as Web Application scoped feature which will register our custom logger HttpModule into the associated web application’s web.config using the much loved and hated SPWebConfigModification API.

Usage Entry

The Usage entry is the class which will be used to store the parameters we want to collect and record for each document download. This first step naturally is to decide which parameters we wish to collect and give them proper names. The key thing to remember about this step is that everything you decide to collect will be stored in the Usage Database (should you choose to store your data here and not another custom data store). The table below lists out the parameters I intend to collect for each download usage event. As you can see I have the SQL and .Net type listed as well as the column names which will be used within SQL. We will use the SQL specific values later in our custom Usage Provider but its good to understand the translation at this point.

Column Name Column Type Description
WebAppId uniqueidentifier/Guid Web Application ID
SiteId uniqueidentifier/Guid Site Collection ID
WebId uniqueidentifier/Guid Site ID
ListId uniqueidentifier/Guid List ID of the Document Library
ItemId uniqueidentifier/Guid Item ID of the file being downloaded
Url nvarchar(256)/String Full URL
FileName nvarchar(256)/String file name of the file being downloaded
Extension nvarchar(256)/String extension of the file being downloaded
Size bigint/long the size of the downloaded file
UserAddress nvarchar(46)/String the source/client IP address


So given the table above you may be thinking, why are we not logging the user name of the user who made the request/download.Well the short answer is, we are. For each usage entry a number of default parameters/columns are collected for us automatically. As you can see from the table below, which lists all default OOB parameters, the UserLogin will take care of collecting the user name for us so there is no need to duplicate effort here.

Column Name Column Type Description
LogTime datetime The time the actual event occurred. This is different from RowCreatedTime as that value represents the time which the event was logged into the database.
MachineName nvarchar(128) The machine which is recording this event
FarmId uniqueidentifier The Farm ID
SiteSubscriptionId uniqueidentifier For multi tenant environments this is the tenant ID
UserLogin nvarchar(300) The user performing the request
CorrelationId uniqueidentifier The Correlation ID which is unique identifier for the usage request.


So now that we know the data we want to collect its time to start coding the usage entry. This class, which I have named DownloadUsageEntry, derives from the SharePoint abstract class SPUsageEntry and we need to mark the class with the [Serializeable] attribute. Our Usage Entry class also needs to implement the ISerializableUsageEntry interface which uses a key/value pair type of serialization scheme through a specific constructor and through the GetObjectData method.

namespace Microsoft.SharePoint.Administration
    public interface ISerializableUsageEntry
        void GetObjectData(SPKeyValuePairSerializationInfo info);


The DownloadUsageEntry class will actually not have all that much code since its primary a storage mechanism. In fact most of the code is around implementing the ISerializableUsageEntry interface. In the sample solution download you can open the DownloadUsageEntry.cs and take a look at the pattern that I used to implement this interface, for me, like any good pattern, its easy to understand, repeatable, and above all, its correct.

So here is the constructor and deserialization:

        internal DownloadUsageEntry(SPKeyValuePairSerializationInfo info) : base(info)
            info.TryGetValue<Guid>(Constants.WebAppIdColumnName, out _webAppId);
            info.TryGetValue<Guid>(Constants.SiteIdColumnName, out _siteId);
            info.TryGetValue<Guid>(Constants.WebIdColumnName, out _webId);
            info.TryGetValue<Guid>(Constants.ListIdColumnName, out _listId);
            info.TryGetValue<Guid>(Constants.ItemIdColumnName, out _itemId);
            info.TryGetValue<String>(Constants.UrlColumnName, out _url);
            info.TryGetValue<String>(Constants.FileNameColumnName, out _fileName);
            info.TryGetValue<String>(Constants.ExtensionColumnName, out _extension);
            info.TryGetValue<long>(Constants.SizeColumnName, out _size);
            info.TryGetValue<String>(Constants.UserAddressColumnName, out _userAddress);



And here is the serialization:

        void ISerializableUsageEntry.GetObjectData(SPKeyValuePairSerializationInfo info)
            info.AddValue(Constants.WebAppIdColumnName, _webAppId);
            info.AddValue(Constants.SiteIdColumnName, _siteId);
            info.AddValue(Constants.WebIdColumnName, _webId);
            info.AddValue(Constants.ListIdColumnName, _listId);
            info.AddValue(Constants.ItemIdColumnName, _itemId);
            info.AddValue(Constants.UrlColumnName, _url);
            info.AddValue(Constants.FileNameColumnName, _fileName);
            info.AddValue(Constants.ExtensionColumnName, _extension);
            info.AddValue(Constants.SizeColumnName, _size);
            info.AddValue(Constants.UserAddressColumnName, _userAddress);


The public fields:

        public Guid WebAppId { get { return _webAppId; } set { _webAppId = value; } }
        public Guid SiteId { get { return _siteId; } set { _siteId = value; } }
        public Guid WebId { get { return _webId; } set { _webId = value; } }
        public Guid ListId { get { return _listId; } set { _listId = value; } }
        public Guid ItemId { get { return _itemId; } set { _itemId = value; } }
        public string Url { get { return _url; } set { _url = value; } }
        public string FileName { get { return _fileName; } set { _fileName = value; } }
        public string Extension { get { return _extension; } set { _extension = value; } }
        public long Size { get { return _size; } set { _size = value; } }
        public string UserAddress { get { return _userAddress; } set { _userAddress = value; } }


You may have noticed the pattern I chose to use does not use the field shortcut syntax such as: public Guid WebAppId { get; set; }, instead I have a private member variable backing the field. The reason for this is because in the constructor the method to get the values out of the SPKeyValuePairSerializationInfo object takes an out parameter were the actual value is used and a field reference cannot be passed as an out parameter.

In addition to the interface implementation the class should override the ParentType field to return the .net type of the custom Usage Provider we will write later. The last piece of this class is the fields we will use for the parameters which will be used by the HttpModule to pass the values collected for a usage event into our class local variables.

Usage Definition

The Usage Definition is the component which acts are our traffic cop or manager for the Provider. Its here were we define our SQL Table and columns which will be provisioned within the Usage DB. We start by creating a class that derives from SharePoint abstract SPUsageProvider and overrides the TableName, Columns, UsageEntryType, and Description fields supplying our own values.

    public sealed class DownloadUsageProvider : SPUsageProvider
        private static SPColumnDefinition[] s_columns = new SPColumnDefinition[]
            new SPColumnDefinition(Constants.WebAppIdColumnName, SqlDbType.UniqueIdentifier),
            new SPColumnDefinition(Constants.SiteIdColumnName, SqlDbType.UniqueIdentifier),
            new SPColumnDefinition(Constants.WebIdColumnName, SqlDbType.UniqueIdentifier),
            new SPColumnDefinition(Constants.ListIdColumnName, SqlDbType.UniqueIdeIdentifier),
            new SPColumnDefinition(Constants.ItemIdColumnName, SqlDbType.UniqueIdentifier),
            new SPColumnDefinition(Constants.UrlColumnName, SqlDbType.NVarChar, 256),
            new SPColumnDefinition(Constants.FileNameColumnName, SqlDbType.NVarChar, 256),
            new SPColumnDefinition(Constants.ExtensionColumnName, SqlDbType.NVarChar, 256),
            new SPColumnDefinitnition(Constants.SizeColumnName, SqlDbType.BigInt),
            new SPColumnDefinition(Constants.UserAddressColumnName, SqlDbType.NVarChar, 46)
        public DownloadUsageProvider() { }

        public DownloadUsageProvider(SPFarm farm) : base(farm) { }

        public override string TableName
            get { return Constants.DownloadUsageProviderName; }
        public override IList<SPColumnDefinition> Columns
            get { return s_columns.ToList(); }

        public override Type UsageEntryType
            get { return typeof(DownloadUsageEntry); }

        public override string Description
            get { return Constants.DownloadUsageProviderDescription; }
        public static DownloadUsageProvider Local
               get { return SPUsageDefinition.GetLocal<DownloadUsageProvider>(); }
        public static void Register()
            var usage = DownloadUsageProvider.Local;
            if (usage != null && usage.Enabled == false)
                usage.Enabled = true;

        public static void Unregister()
            var usage = DownloadUsageProvider.Local;
            if (usage != null && usage.Enabled == true)
                usage.Enabled = false;


The TableName and Description fields are just strings which I just pull out of a static constants class. The table name for this example is DownloadUsage and we do not need to owner specify the name, eg do not prefix with a dbo. We use and array of SPColumnDefinition objects to define each column of information from our Usage Entry class. The SPColumnDefinition defines the name of the column and its SQL type. The class has a couple of constructors, one which takes the column name and SQL type, and another constructor which takes an additional parameter which you must use when the SQL Type is a string type such as SqlDBType.NVarChar and this parameter defines the length of the string. There is a one to one mapping of SPColumnDefinition to the columns which will be created within the Usage DB for our provider tables and order within the array matters as that will be the order within the table. In addition, as mentioned previously, there are a number of OOB columns which we get for free. The columns are hosted within 32 tables which are created for our custom provider automatically. The tables sport the name of our provider followed by _partitionx where X is the number designator (see Part 1 for more information). The Usage Manager is also kind enough to create a SQL view which encompasses the 32 tables, as well as it creates two stored procedures, prc_Enum{provider-name} and prc_Insert{provider-name}, as well as a Tabled Value Function. The stored procedures are used by the Usage Manager to write the Usage Information into the SQL Tables so we do not use these directly ourselves.

Since the Download Usage provider code supplied is a fairly simple solution you may be wondering, if you so chose, how you can leverage an external data store. What you do not see in the code above is several more overrides (which I will stub out for you in the code download accompanying this series). There are 4 overrides or hooks into the Usage data processing pipeline which you may choose to override.

  • ImportEntries – This method is called just as usage entries are ready to be imported. It takes a enumerator or SPUsageEntry objects which you can choose to cast to your Usage Entry class and do any additional processing or manipulation before being written into the data store.
  • WriteDataRowToServer – This method is passed in the table name, columns, log time, machine name, and row information for each usage entry. Should you choose to store usage information outside of the Usage DB its this method which you will want to override to control the redirection of where the usage information will be stored.
  • ProcessData – This method is called once a day by the Usage Daily Timer Job and takes no input parameters. The purpose of this method is to do any daily processing of usage entries such as aggregation or additional processing which may be required by your provider.
  • TruncateData – This method too is called once a day by the Usage Daily Timer job and again takes no input parameters. The purpose of this method is to do any daily truncating, cleaning, or archiving of Usage entries from a custom data store.

One last point on the ProcessData and TruncateDate methods, if you are developing a custom solution which uses this methods you may be thinking debugging these methods is as simple as executing the Usage Daily Timer Job. For the ProcessData you would be correct, this method is called each time this job is executed and so if you execute the job multiple times within a single day to debug your custom provider this process will work. The TruncateData is a bit different however and does a check within the Usage Daily Timer Job which ensures it will only call TruncateData once per calendar day (as defined by the server’s time zone). Keep this in mind if you are waiting on your TruncateData breakpoint to hit, it may take a while.

Back to the code — the Local field and Register/Unregister methods are not required by this class but are, as you would guess, for registration and un-registration of the provider. I simply added them as statics to this class but you could locate them anywhere within your solution. The methods themselves are pretty self explanatory and we simply just set the Enabled field on a local instance of our Provider and call Update(). Once the provider is registered you may notice the SQL schema objects have not been created; don’t freak out – this is not a bug, these objects will be created on the next run of the Usage Data Import timer job assuming there is Usage information recorded for this provider. There is a bug however when you unregister a custom usage provider were the Usage DB schema is not cleaned of the tables, view, stored procedures, etc. This is not the case for Health Providers as they seem to work fine because of a call into the stored procedure, prc_CleanObjectsHelper, which cleans out the SQL Schema. Usage Providers just don’t call this stored procedure with SharePoint 2010 RTM. Manually calling prc_CleanObjectsHelper passing the name of your provider will clean out each table, view, and stored procedure created for your provider however it leaves behind a single User Defined Function (UDF) which you can drop for yourself if needed. This may be the case if you are developing a custom usage provider and you need to iterate on your Usage Entry/Definition design. Just be aware that if the DB is not clean when you attempt to instantiate a custom usage provider the process may fail or you will not see the new definition represented in the DB until you clean out the DB Schema. Its my understanding this is on the plate to be fixed if not already fixed in one of the later CUs.

Logging the Download Event

With our custom Download Usage Provider defined its time to start looking at how we actually record a document download. While not specifically part of the custom Usage Providers its this part of the code which actually makes the Usage Provider dance and its probably the most challenging. While SharePoint does provide a variety of hooks such as list, web, and workflow event handlers is often the case the usage you are interested in capturing does not have an associated event handler. For example, I would love to have crawl event handlers such as: CrawlStarted, CrawlStarting, CrawlCompleted, ItemCrawled, but I regress. For our scenario there is not an ItemDownloaded event handler on a SharePoint list so we will need to write a custom HttpModule to capture the download event. There are 3 download scenarios we need to capture and subsequently log:

  1. User clicks on a document in a library and downloads
  2. User opens a document in Office Web Applications (OWA) and then choose the Open in Word or Open in PowerPoint link.
  3. User chooses send to -> Download a copy from the item control menu

I won’t go into all the details around how I detect the various ways a user may download a document from a document library but the code will all be included in the solution download (once available). What I will talk about is the part of the HttpModule were we log into our custom Usage Provider. The code below is a snippet from the HttpModule which logs the Usage event. We first get a reference to the SPUsageManager  which we will call LogUsage() passing our custom Usage Entry object, DownloadUsageEntry. As you can see we need to populate each of the fields with the data we wish to collect. Note OOB properties such as the user name, correlation ID, and machine name are auto collected by the base SPUsageEntry class so we do not need to supply those values.

            //make is so...
            bool result = false;

                SPUsageManager usageManager = SPUsageManager.Local;
                if (usageManager != null)

                    DownloadUsageEntry entry = new DownloadUsageEntry
                        WebAppId = SPContext.Current.Web.Site.WebApplication.Id,
                        SiteId = SPContext.Current.Web.Site.ID,
                        WebId = SPContext.Current.Web.ID,
                        ListId = library.ID,
                        ItemId = file.Item.UniqueId,
                        Url = context.Request.Url.ToString(),
                        UserAddress = context.Request.UserHostAddress,
                        Size = file.Length,
                        FileName = file.Name,
                        Extension = Path.GetExtension(file.Name)

                    result = usageManager.LogUsage(entry);


            catch (ThreadAbortException) { throw; }
            catch {/*Best Effort*/}




For our Download Usage Provider there are not many knobs to turn when it comes to configuration. We have the ability to turn off or on the provider but that is really about all there is unless you want to tweak the 2 Usage Timer jobs which control the importing of the usage data into the Usage DB and the daily Timer job which does aggregation; more information on these Timer Jobs can be found in Part 1. Custom Usage Providers which use external data stores outside of the Usage Database may have additional configuration needs which will need to be addressed specifically for each such provider.



As you can see its really easy to build a custom SharePoint 2010 Usage Provider (how many things in SharePoint can you say that about). But the real money shot is leveraging the data collected with the provider within custom solutions and reporting. In the next article in this series I will show you how to create custom reports on the data which is being collected. While I am off working on that article I will leave you with a simple screen shot of a query I did to get the top 10 downloaded documents for the month of April. Trivial, right!

9 thoughts on “Extending The SharePoint 2010 Health & Usage – Part 2: Writing a Custom Usage Provider

  1. Your high quality articles are so great, and can we buy some ads from you? If you agree, just emial me the ad type and fee per month. If you own some other high quality related blogs, selling ads would be welcomed.


  2. Just want to say your article is as amazing. The clearness in your post is simply excellent and i could assume you are an expert on this subject. Fine with your permission allow me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please carry on the rewarding work.


  3. Just want to say your article is as amazing. The clearness in your post is simply excellent and i could assume you are an expert on this subject. Fine with your permission allow me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please carry on the rewarding work.


  4. Yes indeed, Web Design Bangalore is right, I have acquired lots of useful data from your post. Everything were discussed in details,. Your blog content was constructed very very nicely and almost perfect. I really get along with reading it and had been very entertained and knowledgeable! Thumbs Up!


  5. Hi,

    How to troubleshoot the Custom Usage provider registration?
    I’ve build the project, deploy and install the farm-level solution successfully, but tables in the database aren’t created. Http module is working properly, requests are handled but they are never logged.



    1. The tables will be created once your data is imported from the file based version into the DB, this as you would expect happens via a timer job as well.


  6. There is an error occured when trying to deploy this solution.
    How can I resolve That

    liscense.licx :
    ComponentArt.Web.Visualization.Charting.Chart, ComponentArt.Web.Visualization.Charting, Version=2011.1.1134.35, Culture=neutral, PublicKeyToken=9bc9f846553156bb


    Error 2 Unable to resolve type ‘ComponentArt.Web.Visualization.Charting.Chart,
    ComponentArt.Web.Visualization.Charting, Version=2011.1.1134.35, Culture=neutral,


    1. The project includes a demo page with a graph on it from ComponentArt – you can safely remove this reference or you can get a license from ComponentArt.com if you wish.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s