Getting to SharePoint Usage Data
I was challenged recently. It was claimed that there was no way to get usage data out of SharePoint beyond the normal SiteActions -> Site Usage Reports page, which isn't terribly useful if you need actual numbers.
Sure, that page includes some nice graphs and some of the data that is available and it saves the typical user from having to create a common bunch of graphs by hand and even has an option to export the data to Excel. The problem is the data being exported does not include actual hit data, but averages over the last 30 days. If you need more detailed information than that, it is not readily available from this summary page. Which was where the challenge began.
Simple Usage Reports – Just the facts!
SharePoint 2003 had a hidden usage page at /_layouts/1033/usagedetails.aspx and that had decent data. The good news is that this is still hidden in 2007 but has been moved just slightly to /_layouts/usagedetails.aspx (it redirects automatically). It has detailed data for Page, User, OS, Browser and Referrer and can slice the data into a Monthly Summary or a Daily Summary.
The bad news is that this data cannot be directly exported in any form – but it does copy-paste very nicely into Excel so it is situationally useful. However, most people really don't want to be doing this manually all the time.
Technically, I had beaten the challenge there, but my programmer instincts kicked in because I kept thinking: "There has to be a better way!" So I kept looking and found a bunch more.
Getting the data remotely
The same data shown above is also available via http GET using the owssvr.dll, as shown below. To get the last 30 days, change the BlobType parameter to "Old"
Ah, but there is a catch. The data it returns is a BLOB (Binary Large OBject) and your browser doesn't know what to do with it. Fortunately, there is a BlobParser (Download) available from Microsoft that can split this data out. Catch #2 is that this parser is a C++ Windows application and most of us are in a C#/VB.Net web-based world. The textbox that this app uses to output the parsed data is also Excel-friendly so that data can be copy-pasted here as well. Useful, but still not quite what I was looking for.
If you are interested in pursuing this, the format of the blob data is available on MSDN for C++. Yeah, you'll have to convert it manually to whatever language you are using.
Using the SharePoint object Model
SPSite.UsageInfo [link] – top level stats but nothing like what a typical stat request requires
In order to get actual stats, you need to go to the SPWeb for each site you want info on and call GetUsageData(), passing the proper parameters for the data desired. Pretty simple and this returns the same data as the BLOB above only in a more useful form, a DataTable. From here, you can do whatever you want with it. Export it, save it to disk, stream it to the browser, toss it in a database, etc.. You get the idea. (Very Important – remember to .Dispose() of your objects!)
Of course, this means deploying code onto your servers, but you are getting pretty good at that by now, aren't you?
[[ is there a web service? ]]
[[ Direct SQL ]] – include normal caveat from MS: don't hit the DB directly cuz it might change in a service pack … blah blah blah
So, the next time you hear someone say that there is no way to get usage data out of SharePoint, you can tell the right where to go! (Here, I mean. What were YOU thinking?)