RSS Feed Feed your read!

Scot Hillier is an independent consultant and Microsoft SharePoint Most Valuable Professional focused on creating solutions for Information Workers with SharePoint, Office, and related .NET technologies. A frequent speaker at TechEd and SharePoint Connections, he is also the author many books on Microsoft technologies including 5 for SharePoint 2010. Scot splits his time between consulting on SharePoint projects and training for Critical Path Training. Scot is a former U. S. Navy submarine officer and graduate of the Virginia Military Institute. Scot can be reached at scot@shillier.com

Archives

January 2010 (17)
February 2010 (1)
March 2010 (1)
April 2010 (2)
May 2010 (1)
June 2010 (1)
August 2010 (4)
September 2010 (6)
October 2010 (3)
November 2010 (2)
February 2011 (1)
March 2011 (4)
April 2011 (3)
May 2011 (1)
August 2011 (1)
March 2012 (1)
April 2012 (1)
July 2012 (3)
August 2012 (1)
September 2012 (1)
October 2012 (2)
November 2012 (3)
January 2013 (2)
February 2013 (3)
March 2013 (3)
April 2013 (3)
May 2013 (1)
September 2013 (2)
November 2013 (5)
January 2014 (2)
February 2014 (2)
March 2014 (1)
April 2014 (1)
May 2014 (1)
July 2014 (1)

Links

IT Unity
SharePoint Team Blog
Andrew Connell's Blog

Tag Cloud

Apps, Books, Business Data Connectivity, Claims, Conferences, JavaScript, jQuery, MOSS 2007 Archived Post, PowerShell, REST and CSOM, Search, SharePoint 2010, SharePoint 2013, Web Content Management,

Using Business Connectivity Services to Display SharePoint 2010 ULS Logs 

Tags: SharePoint 2010, Business Data Connectivity

When things go wrong in SharePoint, the Unified Logging Service (ULS) logs are one of the main places to go for more information. The problem is that these text files are often hard to read and digest. In this post, I'll take a look at using Business Connectivity Services (BCS) to create a list of log entries that are easier to read, sort, and search.
 
Diagnostic Data Provider: Trace Log
In order to get started with the solution, you must first enable the Diagnostic Data Provider: Trace Log timer job. This timer job writes the ULS Log entries into the WSS_Logging database and creates a view called ULSTraceLog that can be used as the basis for a BCS solution. Follow these steps to enable the job:
 
1. Open Central Administration
2. Click Monitoring>Review Job Definitions>Diagnostic Data Provider: Trace Log
3. Click the Enable button
4. Click the Run Now button
 
Here's the View in SQL Server
ULSTraceLog View
 
Database Permissions
Before you can create the External Content Types against the view, you must grant yourself permissions to the WSS_Logging database. You should also give permissions to the account that you are using to run the BDC Service Application and the account being used for the Central Administration Application Pool.
 
Creating the External Content Type
Now it's time to create the ECT. Start the SharePoint Designer and Open the Central Administration web site. Follow these steps to create the new ECT:
 
1. When SPD opens, click the External Content Types object to display a list of ECTs.
 
2. Now click "New External Content Type" on the ribbon
New ECT Button
 
3. Name the new ECT "LogEntry" and disable list synchronization as shown in the following image.
Log Entry ECT
 
4. Now click "Operations Design View" on the ribbon.
Ops Design View button
 
5. Click the Add Connection button and add a new connection to the WSS_Logging database by connecting with your credentials.
SQL Connection Dialog
 
6. Expand the new data source, open the Views folder and locate the ULSTraceLog view. Select this view. Then right-click the view and select "New Read List Operation" from the context menu.
 
7. When the Wizard starts, name the new operations "RecentLogEntries" and click Next.
 
8. On the Next Screen, click the "Add Filter Parameter" button.
 
9. Change the Properties to refer to RowID and set a default value of 100.
Properties
 
10. Now click the "Click to Add" hyperlink. Add a Limit filter to restrict how many rows are returned.
Limit Filter
 
11. Click Next
 
12. On the next screen, select the RowID field. Then check the "Map to Identifier" box. Then uncheck the "Required" box. Pick the other fields and clean up their display names.
 
13. Click Finish. The default view of the list is completed.
 
14. Now you'll create a view that is filtered by Correlation ID so that events are easier to find. Select the ULSTraceLog view again. Right-click the view and select "New Read List Operation" from the context menu.
 
15. When the Wizard starts, name the new operations "LogEntriesByCorrelationID" and click Next.
 
16. On the Next Screen, click the "Add Filter Parameter" button.
 
17. Select the CorrelationId field as the Data Source Element.
 
18. Now click the "Click to Add" link to define the filter.
 
19. Define a new Comparison Filter that can be used to filter out events that do not match the Correlation ID.
Comparison Filter
 
20. Click Next
 
21. On the next screen, select the RowID field. Then check the "Map to Identifier" box. Then uncheck the "Required" box. Pick the other fields and clean up their display names.
 
22. Click Finish. The filtered view of the list is completed.
 
23. Now you'll create an operation to show the deatils of an individual log entry. Right-click the view and select "New Read Item Operation" from the context menu. Follow the steps of the wizard. No filters are required for this operation. When you finish this operation, the ECT is complete.
 
24. Select File>Save from the menu.
 
Altering the Connection Strategy
We are interested in showing the ULS Logs in Central Administration without having to grant permissions on the database for everyone. So, we will change the database connection strategy to use the account running the application pool. Follow these steps:
 
1. Run the following PowerShell script to allow the ECT to connect to the database using the credentials of the application pool.
 
NOTE: The application pool account is a highly-trusted account that has significant privileges. Enabling RevertToSelf authentication is a potential security risk.
 
$bdc = Get-SPServiceApplication | where {$_ -match "Business Data Connectivity Service"}
$bdc.RevertToSelfAllowed = $true
$bdc.Update();

 
2. Click the "Edit Connection Properties" button for the data source. In the "Connection Properties" dialog, change the "Authentication Mode" to "BDC Identity" and click OK.
RevertToSelf
 
3. Save the ECT
 
Granting Permissions
Although the ECT uses the account of the application pool to access the database, you must still grant users the rights to use the ECT model itself. This is done through the BDC Service Application be following these steps:
 
1. Open Central Administration
2. Click Application Management>Manage Service Applications
3. In the list of service applications, click "Business Data Connectivity Service"
4. Select the LogEntry ECT
5. Click "Set Metadata Store Permissions"
6. Grant users and groups Execute rights for the ECT as needed.
 
If you do not get this right, then you will see the error message "Access Denied by Business Connectivity Services" when you try to use the ECT in SharePoint.
 
Using the External Content Type
At this point, the ECT is ready to use. Although you could simply make an External List from the ECT, we want to use the Business Data Web Parts to allow us to enter a Correlation ID for searching. In order to use the Business Data web parts, you must enable the Enterprise features for the Central Administration Site Collection and Site.
 
Now, you just need a page to put the web part on. You can certainly create a new page, but for this walkthrough, we'll just drop the part on the Monitoring page. Follow these steps:
 
1. Open Central Administration>Monitoring
2. Select Site Actions>Edit Page
3. Click Add a Web Part
4. Add the Business data List Web Part
5. Open the Tool Pane
6. Click the Select External Content Type button
7. Select the ECT you just created
8. Select the "Log Entries By Correlation ID" view
9. Click the OK button
10. Click the Stop Editing button
 
Now you should see the Business Data List web part and have the ability to enter a Correlation ID. If you need a Correlation ID for experimenting, just grab one from the ULS logs. Enter the ID and see the corresponding entries. Remember that the entries will only be available after the timer job runs.
Log Entry
 
Extra Credit: Fixing Up the Recent Items View
The default view for the ECT is the "Recent Items" view. This is a great view because it allows you to see the latest items entered in the database. Un fortunately, the database view is not sorted by entry date. So, in this section we'll fix up the view.
 
1. Open the SharePoint Designer if not already open.
2. Click on the External Content Types object
3. Locate the LogEntry ECT and select it (don't click it directly because that will open the summary view)
4. Click "Export BDC Model" and export the model to your desktop.
5. Rename the exported file so that it has an XML extension.
6. Open the File in Visual Studio 2010.
7. In the RecentLogEntries method, edit the SQL statement so that it sorts by log entry time as follows:
 

SELECT TOP(@RowId) [PartitionId] , [RowId] , [LogTime] , [MachineName] , [ProcessID] , [ProcessName] , [ThreadID] , [Area] , [Category] , [Level] , [EventID] , [Message] , [CorrelationId] , [RowCreatedTime] FROM [dbo].[ULSTraceLog] ORDER BY [LogTime] DESC

 

8. Locate the Version attribute of the Entity element and change it to be "2.0.0.0"
9. Save the file 
10. Open Central Administration
11. Click Application Management>Manage Service Applications
12. In the list of service applications, click "Business Data Connectivity Service"
13. In the BDC Service Application, locate the LogEntry delete the entire ECT Model.
14. Click the "Import" button.
15. On the Import page, be sure to check the Permissions box.
16. Click the Browse button and browse to your modified ECT model.
17. Upload the model. Check the errors and warnings carefully.
18. Now you can drop another Business Data List web part and use the deafult view to see the latest ULS Log entries directly in Central Administration.
 
Another thing to try would be creating an External List.
 
Still another interesting thing would be to make the ECT a content source for search and then simply search the logs using keywords.
 
Have fun!
 
Posted by Scot Hillier on 5-Aug-10
0 Comments  |  Trackback Url  |  Link to this post | Bookmark this post with:        
 

Links to this post

Comments