Advertisements

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.

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

Leave the defaults as-is, and select Next >

image

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.

image

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

image

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

image

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.

image

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.

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

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

image

image

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!

 

Advertisements

About Geoff Varosky
Geoff Varosky is a Senior Architect for BlueMetal Architects, based out of Watertown, MA. He has been architecting and developing web based applications his entire career, and has been working with SharePoint for the past 13 years. Geoff is an active member of the SharePoint community, Co-Founder and Co-Organizer of the Boston Area SharePoint Users Group, co-founder for the Boston Office 365 Users Group, co-organizer for SharePoint Saturday Boston and speaks regularly at SharePoint events and user groups.

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

  1. Kevin says:

    The view “ULSTraceLog” does not appear to exist in my 2010 WSS_Logging database… Is there a configuration setting to enable it to appear there?

    • Kevin – to enable the ULS Trace Log, please see this article by David Frette:

      http://davidfrette.wordpress.com/2010/02/18/ulstracelog-isnt-there/

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: