Creating an External Content Type to Surface ULS Log Data Into a SharePoint 2010 List

In my previous post regarding enhancements to diagnostic logging within SharePoint 2010, I touched upon the Awesome Fact[tm] that log data is now also being stored within the WSS_Logging database in SharePoint. Today, to further show some of the possibilities of surfacing and interacting with that data, I am going to walk through building an External Content Type, using Business Connectivity Services through SharePoint Designer 2010, to surface this data within a read-only list in SharePoint.

Warning – the data and configuration below are just examples. This is not entirely properly configured, but, this example is used as just that, to show you how easily it is to surface data in 2010 from external systems, and to expand on my previous post on the great new enhancements in 2010 regarding diagnostic logging. Your mileage may vary. Now, on with the show!

So, let’s kick this off. Open up SharePoint Designer 2010 to any site you have created in your environment, and select External Content Types off of the Site Objects navigation bar on the left.

Then click on the New External Content Type icon in the New section of the External Content Types Ribbon

Click on the New External Content Type text in blue next to Name under External Content Type Information to rename the new External Content Type to something useful. In this case, I am naming my External Content Type ULS Logs

Now, click on the Click here to discover external data sources and define operations link next to External System

This will launch the Operations Designer window, we want to click on Add Connection to add a new data source to link to our BCS External Content Type

You will then be presented with a pop-up dialog box, for the External Data Source Type Selection, choose SQL Server from the Data Source Type drop-down

and then click OK. Another dialog window will then appear asking for the SQL Server connection details. Enter in your SQL server name as the Database Server, and enter in WSS_Logging in  the Database Name field, and click OK

WSS_Logging will now appear as a collapsed tree-structure within your Data Source Explorer window

Expand that down to views, and select the ULSTraceLog by right-clicking on the view, and choosing New Read Item Operation from the context menu

You will then be presented with the Read Item screen. Select Next >

Be sure to check off the Map to Identifier checkbox (this will automatically set the PartitionId as the identifier)

And then select RowId from the left-side of the screen, and do the same for this as well, and then click Next >

Then perform the same actions for both the PartitionId as well as the RowId for the Return Parameters Configuration screen

and then click Finish. We also need to create a Read List operation as well for this to work. So, right-click again on the WSS_Logging table, but this time choose New  Read List Operation

Leave the defaults as-is, and select Next >

On the next screen, Filter Parameters Configuration, you will see a message at the bottom in the Errors and Warnings text area, that you should at the very least, create a Limit type filter, so you do not try to bring back too many rows from the database at once.

So, let’s so this. Click on the Add Filter Parameter button right above the Errors and Warnings text area

Set the Data Source Element as PartitionId (our main identifier), and then click the (Click to Add) link next to Filter

And then in the Filter Configuration dialog box that appears, set a name for this filter in the New Filter field, select Limit from the Filter Type field, and select PartitionId from the Filter Field drop-down, and check off Is Default to set this as the default filter.

And then click OK, and then enter in a Default Value for the filter. I am choosing a limit of 2000, because as we all well know from 2007, 2000 items is our general rule of thumb, as performance tends to degrade when working with more than 2000 items in a list.

Now click Next >

In the Return Parameter Configuration screen, map the identifiers for both PartitionId and RowId, as we did with our Read Item configuration

Now, go through each of the fields, and set them all as Read-Only

And for our LogTime field, select the Timestamp Field checkbox, as this is a timestamp.

NOTE: Only one field can be used as a timestamp type field

Once you have done this, click Finish, and then click Save at the top of the screen to save your new External Content Type

Then, go up to the ribbon, and select Create Lists & Form from the Lists & Forms section of the ribbon

Now, in the next window, set the new List Name, the default Read Item Operation, the System Instance (Data Connection), and a List Description (optional), and Create Infopath Form (also optional), and click OK

SPD2010 will then connect to your site, and create the new list!

Now, lets go back to our site, and see what we have….There we are, our ULS Logs list!

That wasn’t so bad, was it?

To state again, this was just an example of the ease in which you can configure External Data Lists, External Content Types using BCS (Business Connectivity Services) created through SharePoint Designer 2010 in SharePoint 2010!