Parsing the SharePoint Usage BLOB

A couple of days ago a friend of mine asked if I knew how to access MOSS 2007 usage statistics programatically, but he wanted to be able to see usage data not only from the previous 31 days but from the previous months as well! Without hesitation I mentioned that it was impossible, since the object model only returns information for the last 31 days, and then only up to a maximum of 2000 records. He then reminded me about how SharePoint Designer has all these nitfy usage reports which go back several months, and it was obvious that SPD was getting hold of this information somehow.

Armed with Fiddler, I set out to discover where good ol' SPD was getting this information. Checking out the requests logged by Fiddler, I discovered SPD was making some GET requests to the DLL called owssvr.dll with the following query string parameters:

• Cmd=GetUsageBlog
• BlobType=Current

or, alternatively:

• Cmd=GetUsageBlog
• BlobType=Old

Regrettably, the response was binary (I guess they don't call it Usage BLOB for nothing), and therefore, I had no idea of how to parse the thing.

My next clue came in the form of an unmanaged C++ code sample from Microsoft called, ingeniously, Usage Blob Parser. Now that I knew the structure of the mysterious Usage BLOB, I could write a parser in my beloved C# and have access to this information without the restraint of having to use SPD to do it!

Usage BLOB structure

The usage BLOB comes in two flavours, as can be deduced from the BlobType query string parameter above, the current BLOB and the old BLOB. The current BLOB contains the information with which we are already familiar, that is, the last 31 days of usage records; the old BLOB, in turn, returns usage records from the preceding 31 months.

As mentioned before, an usage BLOB contains usage records (or hit vectors as they are called in the abovementioned Microsoft sample), which in turn, are divided into seven categories:

• Browsers
• Documents
• Operating Systems
• Query Strings
• Referring Domains
• Referring URL's
• Visiting Users

They correspond to the equivalent categories found in SharePoint Designer's usage reports.

Each one of the usage records contains 31 values, each corresponding to the number of hits accumulated during a day (in the case of the current BLOB) or a month (in the case of the old BLOB), and the day or month to which the hits correspond.

Parsing the BLOB

Armed with this knowledge, and the sample code, you will be able to download and parse the SharePoint Usage BLOB.

The UsageBlob class contains a method with two overloads called GetUsageBlob, one of the overloads takes a URL, a boolean value indicating whether you want the current or the old BLOB and a NetworkCredential to authenticate the request; the other overload takes a stream (must be seekable, so no NetworkStream, please) and you get to download the BLOB by yourself.

Querying the Usage BLOB to get the information you want shouldn't be too difficult, as in the following example, where we get the browser usage data for the current month in a similiar format as the SharePoint Designer's browser report:

var now = DateTime.Now;
var minDate = new DateTime(now.Year, now.Month, 1);
var browsers = from b in blob.Browsers.UsageRecords
               select new { 
                    Name = b.Key, 
                    Hits = (
                            from v in b.Values
                            where v.Date >= minDate
                            select v.Count
                            ).Sum(),
                    Percentage = 0
                    };

var totalHits = browsers.Sum(b => b.Hits);
if (totalHits > 0)
{
    browsers = from b in browsers
               orderby b.Hits descending
               select new
                   {
                       Name = b.Name,
                       Hits = b.Hits,
                       Percentage = (int)Math.Round(((double)b.Hits / totalHits) * 100)
                   };
}

Well, hope you find the information and the sample code useful. Feel free to comment and/or ask questions.

C# Usage BLOB Parser Solution: Download.