Linq and the Usage DB
The new SharePoint 2010 Usage DB is officially know the “Logging DB”. No one I know actually calls it that though, so I am careful to include both in this post. Anyway, I recently updated some of my code to read from the Usage DB instead of IIS logs and Performance Counters. It was the first time I had used Linq to connect to a SQL db, and it was pretty easy I have to admit. Nonetheless, it did cost me a few hours of searching and typing, so I will post the stuff here to save anyone else the bother.
Some things you should know about the Usage DB ahead of time.
1) It is organized into many partitioned tables, one being active at a time and the partitions wrapping around and eventually overwriting one another. This would make accessing the data complicated, so there are views that make these partitioned tables go away. You access the data over views.
2) The number of partitioned tables can be configured, changing the amount of data and the length of time that it is retained. As I recall this can be done with a PowerShell command and results in all the current data being lost. 31 days is the max retention I believe. I should check all this …
3) The Usage DB tends to quickly become one of the biggest databases on the system, so many admins will want to turn it off, or reconfigure it to store only a small amount of data.
4) Querying the Usage DB can impact your running system, so if you really want to party on the data, pull a backup and restore it somewhere else and use that.
5) The Usage DB will have sensitive data in it including PII data since it records the urls, user login, etc. I suppose someone needs to write a cleanser for it.
Anyway, here is some code to access a single table (The RequestUsage View) in the Usage DB using Linq. I used Visual Studio 2008 for this. Have fun.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.SqlClient;
namespace TestLinq
{
class LINQQueryExpressions
{
[System.Data.Linq.Mapping.Table(Name = "RequestUsage")]
public class SharePointRequest
{
[System.Data.Linq.Mapping.Column(Name = "PartitionId")]
public byte PartitionId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "RowId")]
public Guid RowId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "LogTime")]
public DateTime LogTime { get; set; }
[System.Data.Linq.Mapping.Column(Name = "MachineName")]
public string MachineName { get; set; }
[System.Data.Linq.Mapping.Column(Name = "FarmId")]
public Guid FarmId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "SiteSubscriptionId")]
public Guid SiteSubscriptionId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "UserLogin")]
public string UserLogin { get; set; }
[System.Data.Linq.Mapping.Column(Name = "CorrelationId")]
public Guid CorrelationId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "WebApplicationId")]
public Guid WebApplicationId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "ServerUrl")]
public string ServerUrl { get; set; }
[System.Data.Linq.Mapping.Column(Name = "SiteId")]
public Guid SiteId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "SiteUrl")]
public string SiteUrl { get; set; }
[System.Data.Linq.Mapping.Column(Name = "WebId")]
public Guid WebId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "WebUrl")]
public string WebUrl { get; set; }
[System.Data.Linq.Mapping.Column(Name = "DocumentPath")]
public string DocumentPath { get; set; }
[System.Data.Linq.Mapping.Column(Name = "ContentTypeId")]
public string ContentTypeId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "QueryString")]
public string QueryString { get; set; }
[System.Data.Linq.Mapping.Column(Name = "BytesConsumed")]
public int BytesConsumed { get; set; }
[System.Data.Linq.Mapping.Column(Name = "HttpStatus")]
public short HttpStatus { get; set; }
[System.Data.Linq.Mapping.Column(Name = "SessionId")]
public string SessionId { get; set; }
[System.Data.Linq.Mapping.Column(Name = "ReferrerUrl")]
public string ReferrerUrl { get; set; }
[System.Data.Linq.Mapping.Column(Name = "ReferrerQueryString")]
public string ReferrerQueryString { get; set; }
[System.Data.Linq.Mapping.Column(Name = "Browser")]
public string Browser { get; set; }
[System.Data.Linq.Mapping.Column(Name = "UserAgent")]
public string UserAgent { get; set; }
[System.Data.Linq.Mapping.Column(Name = "UserAddress")]
public string UserAddress { get; set; }
[System.Data.Linq.Mapping.Column(Name = "RequestCount")]
public short RequestCount { get; set; }
[System.Data.Linq.Mapping.Column(Name = "QueryCount")]
public short QueryCount { get; set; }
[System.Data.Linq.Mapping.Column(Name = "QueryDurationSum")]
public long QueryDurationSum { get; set; }
[System.Data.Linq.Mapping.Column(Name = "OperationCount")]
public long OperationCount { get; set; }
[System.Data.Linq.Mapping.Column(Name = "Duration")]
public long Duration { get; set; }
[System.Data.Linq.Mapping.Column(Name = "RequestType")]
public string RequestType { get; set; }
[System.Data.Linq.Mapping.Column(Name = "Title")]
public string Title { get; set; }
[System.Data.Linq.Mapping.Column(Name = "RowCreatedTime")]
public DateTime RowCreatedTime { get; set; }
}
static void Process()
{
// Specify the data source.
string dbname = "mcm05_usagedb";
string connectionString = "Data Source=(local);Initial Catalog="+
dbname+";Integrated Security=SSPI;";
var sqlcct = new SqlConnection(connectionString);
sqlcct.Open();
if (sqlcct.State != System.Data.ConnectionState.Open)
{
Console.WriteLine("Could not open - " +
" Ver:"+sqlcct.ServerVersion+
" State:" + sqlcct.State);
Console.ReadLine();
return;
}
var dc = new DataContext(sqlcct);
var reqtable = dc.GetTable<SharePointRequest>();
IQueryable<SharePointRequest> reqquery = from req in reqtable select req;
// Execute the query.
foreach (var r in reqquery)
{
Console.WriteLine("Part:"+r.PartitionId + " Row" + r.RowId+" "+ r.UserLogin);
}
Console.ReadLine();
}
static void Main()
{
Process();
}
}
}